Cannot execute INSERT in a read-only transaction when extending auto-generated DAO classes

Question

I have been using jOOQ for some time and all is good. I am using PostgreSQL as a database in spring boot application. I am using jOOQ Code generation to generate DAOs, POJOs etc.. Also, using spring-boot-starter-jooq version 3.2.2 and it uses jooq version 3.18.9

Now I have to extend auto generated DAO like below:

@Repository
public class SomeRepositoryImpl extends SomeDao {

    private final DSLContext dslContext;

    public SomeRepositoryImpl(DSLContext dslContext) {
        super(dslContext.configuration());
        this.dslContext = dslContext;
    }

    
    public void save(Example example) {
        insert(example);
    }
}

Mentioned above SomeDao, Example example are auto generated DAO and POJO respectively. insert() method from super class or auto generated DAO of jOOQ.

If I call someRepositoryImpl.save(example) from service layer, repository save throws error as below:

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.jooq.exception.DataAccessException: SQL [insert into "public"."example" ("name", "domain", "description", "website", "language_id") values (?, ?, ?, ?, ?) returning "public"."example"."example_id"]; ERROR: cannot execute INSERT in a read-only transaction] with root cause

org.postgresql.util.PSQLException: ERROR: cannot execute INSERT in a read-only transaction
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse
and so on....

I am not using @Transactional(readOnly = true) anywhere in my code.

But, if I use mentioned below code in SomeRepositoryImpl then it works:

    @Transactional
    public void save(Example example) {
        insert(example);
    }

Also, if I use mentioned below code which uses the generated DAO directly in service layer then it works:

@Autowired
SomeDao someDao;

someMethod(Example example){
someDao.insert(example);
}

• What am I missing and it throws exception?
• Also in spring boot is this the best way to extend Dao and provide configuration?

Answer 1

Access to database outside of any declared @Transactional context is read-only by default.

It is done to avoid changes in entities being propagated unintentionally to the Database outside of a transaction.

See SimpleJpaRepository javadoc which will show you that the class itself is annotated with @Transactional(readOnly = true) which makes all methods "read-only" by-default, except the one annotated with @Transactional (like delete or save)