JOOQ initializing DAO Best Approach

Question

I want to know Best practices for initilizing JOOQ generated DAO. Now,I am using following approach for initilization of JOOQ generated DAO. In following case StudentDao is JOOQ generated.

public class ExtendedStudentDAO extends StudentDao {
    public ExtendedStudentDAO () {
        super();
    }

    public ExtendedStudentDAO (Connection connection) {
        Configuration configuration = DSL.using(connection,
                JDBCUtils.dialect(connection)).configuration();

        this.setConfiguration(configuration);
    }

    //adding extra methods to DAO using DSL
    public String getStudentName(Long ID)
            throws SQLException {

        try (Connection connection = ServiceConnectionManager.getConnection()) {

            DSLContext dslContext = ServiceConnectionManager
                    .getDSLContext(connection);

            Record1<String> record = dslContext
                    .select(Student.Name)
                    .from(Student.Student)
                    .where(Student.ID
                            .equal(ID)).fetchOne();

            if (record != null) {
                return record.getValue(Student.Name);
            }

            return null;
        }
    }
}

and I have doubt with using above DAO my example code is below.

try (Connection connection = ServiceConnectionManager.getConnection()) {

ExtendedStudentDAO extendedStudentDAO =new ExtendedStudentDAO(connection);

Student stud=new Student();
.....
....

//insert method is from Generated DAO
extendedStudentDAO.insert(stud); 

//this method is added in extended class
extendedStudentDAO.getStudentName(12);

}

Answer 1

There are two ways to look at this kind of initialisation:

Create DAOs every time you need them

Your approach is correct, but might be considered a bit heavy. You're creating a new DAO every time you need it.

As of jOOQ 3.7, a DAO is a pretty lightweight object. The same is true for the Configuration that wraps your Connection.

Once your project evolves (or in future jOOQ versions), that might no longer be true, as your Configuration initialisation (or jOOQ's DAO initialisation) might become heavier.

But this is a small risk, and it would be easy to fix:

Use dependency injection to manage DAO or Configuration references

Most people will set up only a single jOOQ Configuration for their application, and also only a single DAO instance (per DAO type), somewhere in a service. In this case, your Configuration must not share the Connection reference, but provide a Connection to jOOQ via the ConnectionProvider SPI. In your case, that seems trivial enough:

class MyConnectionProvider implements ConnectionProvider {
    @Override
    public Connection acquire() {
         return ServiceConnectionManager.getConnection();
    }

    @Override
    public void release(Connection connection) {
         try {
             connection.close();
         }
         catch (SQLException e) {
             throw new DataAccessException("Error while closing", e);
         }
    }
}