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:
good idea
ReplyDelete