Integrate JOOQ With Spring and Perform CRUD Operations.

Introduction:

In the last POST we discuss about  generate Java classes using JOOQ maven generator. These java files are represent table in database. Like hibernate Jboss tools are used to generate entities corresponding to the table. Today we perform CRUD Operations with the help of JOOQ. For Generating java classes, refer to my previous POST. In this we Integrate  JOOQ with SPRING framework. There are may blog post that help me to create this example and also the JOOQ website, which share lots of example. 

Step 1:

The Dependencies that we need : 

Step 2:

Create the class to handle JOOQ exception in standard exception. Mostly the framework follow the standard SQLException framework, in which the framework specific exception are wrap into standard SQL exception so it can maintain easily. Now we create the class for handle JOOQ exception and wrap into Standard Exception. 

import org.jooq.ExecuteContext;
import org.jooq.SQLDialect;
import org.jooq.impl.DefaultExecuteListener;
import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
import org.springframework.jdbc.support.SQLExceptionTranslator;
import org.springframework.jdbc.support.SQLStateSQLExceptionTranslator;

public class JOOQToSpringExceptionTransformer extends DefaultExecuteListener {
  private static final long serialVersionUID = -5749466061513426635L;
  @Override
  public void exception(ExecuteContext ctx) {
 SQLDialect dialect = ctx.configuration().dialect();
 SQLExceptionTranslator sqlExceptionTranslator = null;
 if(dialect != null){
  sqlExceptionTranslator = new SQLErrorCodeSQLExceptionTranslator(dialect.getName());
 }else{
  sqlExceptionTranslator = new SQLStateSQLExceptionTranslator();
 }
 
 ctx.exception(sqlExceptionTranslator.translate("JOOQ", ctx.sql(), ctx.sqlException()));
  }
}

In this :
DefaultExecuteListener : The DefaultExecuteListener class is the public default implementation of the ExecuteListener interface which provides listener methods for different life cycle events of a single query execution.

Step 3: 

In this we Cover Database Java Based Configuration: 

Properties File : application.properties

#Database Configuration
db.driver=com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/jooq_test
db.username=test
db.password=root

#jOOQ Configuration
jooq.sql.dialect=MYSQL

Database Configuration file: PersistenceConfiguration.java

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSource;
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.impl.DataSourceConnectionProvider;
import org.jooq.impl.DefaultConfiguration;
import org.jooq.impl.DefaultDSLContext;
import org.jooq.impl.DefaultExecuteListenerProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy;
import org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy;
import org.springframework.transaction.annotation.EnableTransactionManagement;

/**
 * @author Programmers
 *
 */
@Configuration
@ComponentScan({"com.the13star.service.test", "com.the13star.service.impl", "com.the13star.dao.impl" })
@EnableTransactionManagement
@PropertySource("classpath:application.properties")
public class PersistenceContext {

 @Autowired
 private Environment environment;

 @Bean(destroyMethod = "close") // destroyMethod attribute is used to close the bean
 public DataSource dataSource() {
  BasicDataSource dataSource = new BasicDataSource();
  dataSource.setDriverClassName(environment.getRequiredProperty("db.driver").trim());
  dataSource.setUrl(environment.getRequiredProperty("db.url").trim());
  dataSource.setUsername(environment.getRequiredProperty("db.username").trim());
  dataSource.setPassword(environment.getRequiredProperty("db.password").trim());
  dataSource.setInitialSize(5);
  dataSource.setMaxTotal(5);
  return dataSource;
 }

 // To delay opening a jdbc connection until the first actual sql statement
 // happens use LazyConnectionDataSourceProxy
 @Bean
 public LazyConnectionDataSourceProxy lazyConnectionDataSource() {
  return new LazyConnectionDataSourceProxy(dataSource());
 }

 // Configure jOOQ's ConnectionProvider to use Spring's
 // TransactionAwareDataSourceProxy,
 // which can dynamically discover the transaction context
 /**
  * Configure the TransactionAwareDataSourceProxy bean. This bean ensures
  * that all JDBC connection are aware of Spring-managed transactions. In
  * other words, JDBC connections participates in thread-bound transactions
  */
 @Bean
 public TransactionAwareDataSourceProxy transactionAwareDataSource() {
  return new TransactionAwareDataSourceProxy(lazyConnectionDataSource());
 }

 /**
  * Configure the DataSourceTransactionManager bean. We must pass the
  * LazyConnectionDataSourceProxy bean as as constructor argument when we
  * create a new DataSourceTransactionManager object.
  */
 @Bean
 public DataSourceTransactionManager dataSourceTransactionManager() {
  return new DataSourceTransactionManager(lazyConnectionDataSource());
 }

 /**
  * Configure the DataSourceConnectionProvider bean. jOOQ will get the used
  * connections from the DataSource given as a constructor argument. We must
  * pass the TransactionAwareDataSourceProxy bean as a constructor argument
  * when we create a new DataSourceConnectionProvider object. This ensures
  * that the queries created jOOQ participate in Spring-managed transactions.
  */
 @Bean
 public DataSourceConnectionProvider connectionProvider() {
  return new DataSourceConnectionProvider(transactionAwareDataSource());
 }

 @Bean
 public JOOQToSpringExceptionTransformer jooqToSpringExceptionTranslator() {
  return new JOOQToSpringExceptionTransformer();
 }

 /**
  * Invoking an internal, package-private constructor for the example
  * Implement your own Configuration for more reliable behaviour
  */
 @Bean
 public DefaultConfiguration configuration() {
  DefaultConfiguration configuration = new DefaultConfiguration();
  configuration.set(connectionProvider());
  configuration.set(new DefaultExecuteListenerProvider(
    jooqToSpringExceptionTranslator()));

  String sqlDialect = environment.getRequiredProperty("jooq.sql.dialect");
  SQLDialect dialect = SQLDialect.valueOf(sqlDialect);
  configuration.set(dialect);

  return configuration;

 }

 /**
  * Configure the DSL object, optionally overriding jOOQ Exceptions with
  * Spring Exceptions. We use this bean when we are creating database queries
  * with jOOQ.
  */
 @Bean
 public DSLContext dslContext() {
  return new DefaultDSLContext(configuration());
 }

 /**
  * We use this bean to create the database schema for database when our
  * application is started (If you don’t use an embedded database, you don’t
  * have to configure this bean).
  */
 /*
 @Bean
 public DataSourceInitializer dataSourceInitializer() {
  DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
  dataSourceInitializer.setDataSource(dataSource());
  
  ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator();
  databasePopulator.addScript(new ClassPathResource(environment.getRequiredProperty("")));
  
  dataSourceInitializer.setDatabasePopulator(databasePopulator);
  return dataSourceInitializer;
 }*/
}

Step 4: 

Create Service layer : 

import java.util.List;

import com.the13star.dbmetadata.tables.records.UserDetailRecord;

public interface UserDetailService {
 public void saveUserDetail(int id, String name, int age);

 public List getAllUsers();

 public UserDetailRecord getUserByID(int i);

 public int updateUserById(UserDetailRecord userDetailRecord);

 public int deleteUserById(int id);
}

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.the13star.dao.UserDetailDao;
import com.the13star.dbmetadata.tables.records.UserDetailRecord;
import com.the13star.service.UserDetailService;

/**
 * @author Programmers
 *
 */
@Service
public class UserDetailServiceImpl implements UserDetailService {
 
 @Autowired
 private UserDetailDao userDetailDao;

 /* (non-Javadoc)
  * @see com.the13star.service.UserDetailService#saveUserDetail(int, java.lang.String, int)
  */
 @Override
 public void saveUserDetail(int id, String name, int age) {
  UserDetailRecord record = new UserDetailRecord();
  record.setId(id);
  record.setName(name);
  record.setAge(age);
  
  userDetailDao.insertNewUser(record);
 }

 @Override
 public List getAllUsers() {
  return userDetailDao.getAllUsers();
 }

 @Override
 public UserDetailRecord getUserByID(int id) {
  return userDetailDao.getUserByID(id);
 }

 @Override
 public int updateUserById(UserDetailRecord userDetailRecord) {
  return userDetailDao.updateUserById(userDetailRecord);
 }

 @Override
 public int deleteUserById(int id) {
  return userDetailDao.deleteUserById(id);
 }

}

Step 5:

Create Dao Layer : 

import java.util.List;

import com.the13star.dbmetadata.tables.records.UserDetailRecord;


/**
 * @author Programmers
 *
 */
public interface UserDetailDao {

 public void insertNewUser(UserDetailRecord userDetailRecord);

 public List getAllUsers();

 public UserDetailRecord getUserByID(int id);

 public int updateUserById(UserDetailRecord userDetailRecord);

 public int deleteUserById(int id);
}

import java.util.ArrayList;
import java.util.List;

import org.jooq.DSLContext;
import org.jooq.DeleteConditionStep;
import org.jooq.DeleteWhereStep;
import org.jooq.InsertValuesStep3;
import org.jooq.Result;
import org.jooq.UpdateConditionStep;
import org.jooq.UpdateSetFirstStep;
import org.jooq.UpdateSetMoreStep;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.the13star.dao.UserDetailDao;
import com.the13star.dbmetadata.tables.UserDetail;
import com.the13star.dbmetadata.tables.records.UserDetailRecord;

/**
 * @author Programmers
 *
 */
@Repository
public class UserDetailDaoImpl implements UserDetailDao {

 @Autowired
 DSLContext dslContext;

 public void insertNewUser(UserDetailRecord userDetailRecord) {
  InsertValuesStep3 userdetails = dslContext
    .insertInto(UserDetail.USER_DETAIL, UserDetail.USER_DETAIL.ID,
      UserDetail.USER_DETAIL.NAME, UserDetail.USER_DETAIL.AGE);
  userdetails.values(userDetailRecord.getId(),
    userDetailRecord.getName(), userDetailRecord.getAge());
  userdetails.execute();
 }

 @Override
 public List getAllUsers() {
  Result userDetails = dslContext
    .fetch(UserDetail.USER_DETAIL);
  return new ArrayList<>(userDetails);
 }

 @Override
 public UserDetailRecord getUserByID(int id) {
  return dslContext.fetchOne(UserDetail.USER_DETAIL,
    UserDetail.USER_DETAIL.ID.equal(id));
 }

 @Override
 public int updateUserById(UserDetailRecord userDetailRecord) {
  UpdateSetFirstStep updateSetFirstStep = dslContext
    .update(UserDetail.USER_DETAIL);
  UpdateSetMoreStep updateSetMoreStep = updateSetFirstStep
    .set(UserDetail.USER_DETAIL.NAME, userDetailRecord.getName())
    .set(UserDetail.USER_DETAIL.AGE, userDetailRecord.getAge());
  UpdateConditionStep updateConditionStep = updateSetMoreStep
    .where(UserDetail.USER_DETAIL.ID.equal(userDetailRecord.getId()));
  return updateConditionStep.execute();
 }

 @Override
 public int deleteUserById(int id) {
  DeleteWhereStep deleteWhereStep = dslContext.delete(UserDetail.USER_DETAIL);
  DeleteConditionStep deleteConditionStep = deleteWhereStep.where(UserDetail.USER_DETAIL.ID.equal(id));
  return deleteConditionStep.execute();
 }

}

Step 6: 

Launch Our Code:

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.stereotype.Component;

import com.the13star.configurations.PersistenceContext;
import com.the13star.dbmetadata.tables.records.UserDetailRecord;
import com.the13star.service.UserDetailService;

/**
 * @author Programmers
 *
 */
@Component
public class UserDetailTest {
 
 @Autowired
 private UserDetailService userDetailService;
 /**
  * @param args
  */ 
 
 private void start() {
  //userDetailService.saveUserDetail(3, "MICKY", 21);
  List userDetails = userDetailService.getAllUsers();
  for(UserDetailRecord record : userDetails){
   System.out.println(record);
  }
  /*
  UserDetailRecord record = userDetailService.getUserByID(1);
  System.out.println(record);*/
  /*
  UserDetailRecord userDetailRecord = new UserDetailRecord();
  userDetailRecord.setId(3);
  userDetailRecord.setName("Micky");
  userDetailRecord.setAge(26);
  int result = userDetailService.updateUserById(userDetailRecord);*/
  /*
  int result = userDetailService.deleteUserById(2);
  System.out.println("Result : "+result);*/
 }
 
 public static void main(String[] args) {
  AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(PersistenceContext.class);
  UserDetailTest userDetailTest = applicationContext.getBean(UserDetailTest.class);
  userDetailTest.start();
  applicationContext.close();
 }

}

For downloading the example code, go to the this link: 

JOOQ Reverse Engineering Using Maven Generator

Introduction : 

JOOQ is used to write type safe SQL queries using JAVA. Some time it is difficult to write SQL queries for Java Developer, because Java Developer is familiar with Object Oriented World and with its own language syntax. SQL is a different language and developers need to learn new language for deal with data base using queries. For the sake of learning new language syntax and new structure, The JOOQ provide a way to use queries using Java Programming Syntax. There are lots of rich ORM tools which also provide the way to implement queries using Java Programming Syntax, these queries called CRITERIA queries. But these ORM tools have some Limitations , so if the programmer only need to apply types safety queries, the JOOQ is the best way to do this. There are lots of things that JOOQ provide. For JOOQ detail go to JOOQ Home Page. JOOQ also provide some classes or function that are familiar for SQL developer like for select query JOOQ provide select method in class. 

JOOQ also provide the reverse engineering for generate Java POJO's or Entities from existing table in database. They provide several approaches to use generator to generate Entities. In this POST we discuss about Maven Generator for generate Entities.  

Step 1:

Add the Generator plugin in Maven pom.xml. 

 3.3.1



 
  org.jooq
  jooq
  ${jooq-version}
 

 
  org.jooq
  jooq-meta
  ${jooq-version}
 

 
  org.jooq
  jooq-codegen
  ${jooq-version}
 




 
 org.jooq
 jooq-codegen-maven
 ${jooq-version}

 
 
   
   
   generate
  
  
 

 
  
   mysql
   mysql-connector-java
   5.1.30
  
 

 
 
  
  
   com.mysql.jdbc.Driver
   jdbc:mysql://localhost/jooq_test
   root
   root
  

  

  
   org.jooq.util.DefaultGenerator
   
    org.jooq.util.mysql.MySQLDatabase
    .*
    
    jooq_test
   
   
    com.the13star.entities
    src/main/java
   
  

For JOOQ we need some dependencies to add in the pom.xml file. But the generator plugin also need some dependencies for plugin use only. That dependencies are declare in the generator plugin. If we already declare dependencies in dependency block and not declare in plugin the generator will not run. We again need to declare dependencies in the generator plugin, because plugin use its individual dependencies for run like MySQL dependency in the above code. If maven provide the way to use dependencies of dependency block in plugin please discuss.
In this we use DefaultGenerator for generate Entities, you can also use your custom generator.

Step 2

For run the generator use Maven install command from eclipse or command line, after generator run, there are three packages structures are automatically create in your project. 

The three pakcages are 

1. com.the13star.dbmetadata: According to its name it is used to represent meta information of database table. Like Kyes.java contain information regarding primary key's etc. 

2.  com.the13star.dbmetadata.tables: This package contain the information of db tables like. Field in table, types of column etc. One java file represent one table. according to above example there is only one table in DB. 

3.  com.the13star.dbmetadata.tables.records: This package represent the record of table or represent the one row in table. We will also said that, this is our Java POJO or Entity file.