I use Springboot3 and Jooq to create my project and define a custom delete method deleteByUsername follow jooq delete guide
@Repository
public class UserRepository extends UserDao {
@Autowired
public UserRepository(Configuration configuration) {
super(configuration);
}
/* --- some query method can run sucessful in run and test---
........
*/
public void deleteByUsername(String username) {
ctx().delete(USER).where(USER.USERNAME.eq(username)).execute();
}
}
The UserRepository is extend UserDao so ctx() is return a DSLContext instance.
@Repository
public class UserDao extends AbstractSpringDAOImpl<UserRecord, jooq.tables.pojos.User, Long> {
/**
* Create a new UserDao without any configuration
*/
public UserDao() {
super(User.USER, jooq.tables.pojos.User.class);
}
/**
* Fetch records that have <code>id IN (values)</code>
*/
public List<jooq.tables.pojos.User> fetchById(Long... values) {
return fetch(User.USER.ID, values);
}
/**
* Fetch a unique record that has <code>id = value</code>
*/
public jooq.tables.pojos.User fetchOneById(Long value) {
return fetchOne(User.USER.ID, value);
}
.....more
}
UserDao is generate by jooq code generate plugin when if you set up with
generate.apply {
isDeprecated = false
isRecords = true
isImmutablePojos = false
isFluentSetters = true
isDaos = true // will create dao layer by jooq
isSpringDao = true
isSpringAnnotations = true
}
All *Dao is extend DAOImpl, and ctx() is the way to get DSLContext in dao layer
public abstract class DAOImpl<R extends UpdatableRecord<R>, P, T> implements DAO<R, P, T> {
private final Table<R> table;
private final Class<P> type;
private RecordMapper<R, P> mapper;
private Configuration configuration;
/**
* Inject a configuration.
* <p>
* This method is maintained to be able to configure a <code>DAO</code>
* using Spring. It is not exposed in the public API.
*/
public /* non-final */ void setConfiguration(Configuration configuration) {
this.mapper = ((FieldsImpl<R>) table.recordType()).mapper(this.configuration = Tools.configuration(configuration), type);
}
public /* non-final */ DSLContext ctx() {
return configuration().dsl();
}
}
When i run deleteByUsername in springboot test it was failed, and throw exception below.
But jooqDeleteRunSuccess run suceess. The userRepository.delete method which defined in jooqDeleteRunSuccess is jooq generated dao layer's method, it's not custom method.
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@AutoConfigureWebTestClient
public class SignE2ETest {
@Autowired private WebTestClient webClient;
@Autowired private UserRepository userRepository;
@Test
void jooqDeleteRunSuccess() {
User stubUser = new User();
stubUser.setUsername("test_5fab32c22a3e");
stubUser.setPassword("test_eab28b939ba1");
userRepository.insert(stubUser);
userRepository.delete(userRepository.fetchOneByUsername("test_5fab32c22a3e"));
}
@Test
void myDeleteRunFailed() {
User stubUser = new User();
stubUser.setUsername("test_5fab32c22a3e");
stubUser.setPassword("test_eab28b939ba1");
userRepository.insert(stubUser);
userRepository.deleteByUsername("test_5fab32c22a3e");
}
}
org.jooq.exception.DataAccessException: SQL [delete from "mjga"."user" where "mjga"."user"."username" = ?]; ERROR: cannot execute DELETE in a read-only transaction
at org.jooq_3.18.6.POSTGRES.debug(Unknown Source)
at org.jooq.impl.Tools.translate(Tools.java:3470)
at org.jooq.impl.Tools.translate(Tools.java:3458)
at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:801)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:360)
at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:115)
at com.mjga.repository.UserRepository.deleteByUsername(UserRepository.java:61)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
.....
As you see there is no explicit config to set postgresql to READ-ONLY mode, I don't know why this READ-ONLY exception happend?
Why myDeleteRunFailed throw READ-ONLY exception but jooqDeleteRunSuccess not?
How to fix it?
dependencies {
implementation("org.springframework.boot:spring-boot-starter-actuator")
implementation("org.springframework.boot:spring-boot-starter-jooq")
implementation("org.springframework.boot:spring-boot-starter-mail")
implementation("org.springframework.boot:spring-boot-starter-quartz")
implementation("org.springframework.boot:spring-boot-starter-security")
implementation("org.springframework.boot:spring-boot-starter-validation")
implementation("org.springframework.boot:spring-boot-starter-web")
implementation("org.springframework.boot:spring-boot-starter-websocket")
implementation("org.springframework.boot:spring-boot-starter-aop")
implementation("org.apache.commons:commons-lang3:3.13.0")
implementation("org.apache.commons:commons-collections4:4.4")
implementation("org.springdoc:springdoc-openapi-starter-webmvc-ui:2.2.0")
implementation("org.jooq:jooq-codegen:3.18.6")
implementation("org.jooq:jooq-meta:3.18.6")
implementation("com.auth0:java-jwt:4.4.0")
implementation("org.testcontainers:junit-jupiter:1.19.0")
implementation("org.testcontainers:postgresql:1.19.0")
implementation("org.testcontainers:testcontainers-bom:1.19.0")
runtimeOnly("org.postgresql:postgresql")
compileOnly("org.projectlombok:lombok")
developmentOnly("org.springframework.boot:spring-boot-devtools")
developmentOnly("org.springframework.boot:spring-boot-docker-compose")
testImplementation("org.springframework.boot:spring-boot-testcontainers:3.1.2")
testImplementation("org.springframework.boot:spring-boot-docker-compose")
testImplementation("org.springframework.boot:spring-boot-starter-webflux")
testImplementation("org.springframework.boot:spring-boot-starter-test")
testImplementation("org.springframework.security:spring-security-test")
jooqGenerator("org.postgresql:postgresql")
annotationProcessor("org.springframework.boot:spring-boot-configuration-processor")
annotationProcessor("org.projectlombok:lombok")
}
services:
postgres:
container_name: postgres
image: 'postgres:15.4'
environment:
POSTGRES_DB: postgres
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
ports:
- ${DATABASE_PORT}:5432
volumes:
- ./db.d/init:/docker-entrypoint-initdb.d
- ${DATABASE_STORE}:/var/lib/postgresql/data
restart: on-failure
I have checked my DB read_only status
SHOW default_transaction_read_only;
SHOW transaction_read_only;
off
off
The problem, as Frisk and Lukas Eder mentioned, was due to some Spring Boot setup making the transactional behavior default to "read-only."
When using JOOQ with Spring, the DAO layer extends AbstractSpringDAOImpl, which has the @Transactional(readOnly = true) annotation at the class level. This caused UserRepository to be treated as read-only for all its methods.
To solve the problem, I added the @Transactional annotation at the method level to override the class-level annotation, like this:
@Transactional
public void deleteByUsername(String username) {
ctx().delete(USER).where(USER.USERNAME.eq(username)).execute();
}