This example presents the basic concept of running DML operations in Spring JDBC.
The technologies used are :
-
– Spring 4.3.18
– H2 1.3
– Lombok 1.18
– JDK 1.80
– Maven 3.3.9
You can convert this example to an Eclipse IDE project by going to folder where is the pom.xml is, and use the command :
mvn eclipse:eclipse
Inspired from « Beginning Spring » Mert Caliskan, Kenan Sevindik, Rod Johnson (Foreword by), Jürgen Höller (Foreword by).
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>javaspringexamples.springJDBC.dmlOperations</groupId> <artifactId>dmlOperations</artifactId> <version>0.0.1-SNAPSHOT</version> <properties> <maven.compiler.source>1.8</maven.compiler.source> <maven.compiler.target>1.8</maven.compiler.target> </properties> <dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.3.18.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>4.3.18.RELEASE</version> </dependency> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <version>1.3.175</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.6</version> </dependency> </dependencies> </project>
package javaspringexamples.springJDBC.dmlOperations;
import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.jdbc.object.SqlUpdate;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
@Configuration
public class Conf {
@Bean
public DataSource dataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("org.h2.Driver");
dataSource.setUrl("jdbc:h2:tcp://localhost/~/javaspringexamples");
dataSource.setUsername("sa");
dataSource.setPassword("");
return dataSource;
}
@Bean
public JdbcTemplate jdbcTemplate() {
JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource());
return jdbcTemplate;
}
@Bean
public UserDao userDao() {
UserDaoJdbcImpl userDao = new UserDaoJdbcImpl();
userDao.setJdbcTemplate(jdbcTemplate());
userDao.setUserByIdQuery(userByIdQuery());
userDao.setUserInsert(userInsert());
userDao.setUserUpdate(userUpdate());
userDao.setUserDelete(userDelete());
return userDao;
}
@Bean
public MappingSqlQuery<User> userByIdQuery() {
UserFindByIdQuery query = new UserFindByIdQuery(dataSource());
return query;
}
@Bean
public SqlUpdate userInsert() {
UserInsert userInsert = new UserInsert(dataSource());
return userInsert;
}
@Bean
public SqlUpdate userUpdate() {
UserUpdate userUpdate = new UserUpdate(dataSource());
return userUpdate;
}
@Bean
public SqlUpdate userDelete() {
UserDelete userDelete = new UserDelete(dataSource());
return userDelete;
}
}
package javaspringexamples.springJDBC.dmlOperations;
import org.springframework.dao.DataAccessException;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class ExceptionDeleteFailed extends DataAccessException {
public ExceptionDeleteFailed(String msg) {
super(msg);
}
}
package javaspringexamples.springJDBC.dmlOperations;
import org.springframework.dao.DataAccessException;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class ExceptionInsertFailed extends DataAccessException {
public ExceptionInsertFailed(String msg) {
super(msg);
}
}
package javaspringexamples.springJDBC.dmlOperations;
import org.springframework.dao.DataAccessException;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class ExceptionUpdateFailed extends DataAccessException {
public ExceptionUpdateFailed(String msg) {
super(msg);
}
}
package javaspringexamples.springJDBC.dmlOperations;
import java.sql.SQLException;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class Main {
public static void main(String[] args) throws SQLException {
AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(Conf.class);
UserDao userDao = applicationContext.getBean(UserDao.class);
User user = new User();
user.setName("Wow");
user.setUserName("Wiw");
user.setLocked(true);
userDao.insert(user);
user = userDao.find(user.getId());
System.out.println(user.getId() + "," + user.getName() + "," + user.getUserName() + "," + user.isLocked());
user.setLocked(false);
userDao.update(user);
user = userDao.find(user.getId());
System.out.println(user.isLocked());
userDao.delete(user.getId());
user = userDao.find(user.getId());
System.out.println(user);
}
}
package javaspringexamples.springJDBC.dmlOperations;
import java.util.Date;
import lombok.Data;
import lombok.ToString;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
@Data
public class User {
private long id;
private String name;
private String userName;
@ToString.Exclude
private Date accessTime;
private boolean locked;
}
package javaspringexamples.springJDBC.dmlOperations;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public interface UserDao {
public void insert(User user);
public void update(User user);
public void delete(long userId);
public User find(long userId);
}
package javaspringexamples.springJDBC.dmlOperations;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.jdbc.object.SqlUpdate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class UserDaoJdbcImpl implements UserDao {
private JdbcTemplate jdbcTemplate;
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
private MappingSqlQuery<User> userByIdQuery;
private SqlUpdate userInsert;
private SqlUpdate userUpdate;
private SqlUpdate userDelete;
public void setUserInsert(SqlUpdate userInsert) {
this.userInsert = userInsert;
}
public void setUserUpdate(SqlUpdate userUpdate) {
this.userUpdate = userUpdate;
}
public void setUserDelete(SqlUpdate userDelete) {
this.userDelete = userDelete;
}
public void setUserByIdQuery(MappingSqlQuery<User> userByIdQuery) {
this.userByIdQuery = userByIdQuery;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
}
public void insert(User user) {
GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
int count = userInsert.update(new Object[] { user.getName(), user.getUserName(), user.isLocked() }, keyHolder);
if (count != 1)
throw new ExceptionInsertFailed("Cannot insert user");
user.setId(keyHolder.getKey().longValue());
}
public void update(User user) {
int count = userUpdate.update(user.getName(), user.getUserName(), user.isLocked(), user.getId());
if (count != 1)
throw new ExceptionUpdateFailed("Cannot update user");
}
public void delete(long userId) {
int count = userDelete.update(userId);
if (count != 1)
throw new ExceptionDeleteFailed("Cannot delete user");
}
public User find(long userId) {
return userByIdQuery.findObject(userId);
}
}
package javaspringexamples.springJDBC.dmlOperations;
import java.sql.Types;
import javax.sql.DataSource;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class UserDelete extends SqlUpdate {
public UserDelete(DataSource dataSource) {
super(dataSource, "delete user where id = ?");
setParameters(new SqlParameter[]{new SqlParameter(Types.BIGINT)});
compile();
}
}
package javaspringexamples.springJDBC.dmlOperations;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import javax.sql.DataSource;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.MappingSqlQuery;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class UserFindByIdQuery extends MappingSqlQuery<User> {
public UserFindByIdQuery(DataSource dataSource) {
super(dataSource, "select id,name,user_name,locked from user where id = ?");
declareParameter(new SqlParameter(Types.BIGINT));
compile();
}
@Override
protected User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getLong("id"));
user.setName(rs.getString("name"));
user.setUserName(rs.getString("user_name"));
user.setLocked(rs.getBoolean("locked"));
return user;
}
}
package javaspringexamples.springJDBC.dmlOperations;
import java.sql.Types;
import javax.sql.DataSource;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class UserInsert extends SqlUpdate {
public UserInsert(DataSource dataSource) {
super(dataSource, "insert into user(name,user_name,locked) values(?,?,?)");
setParameters(new SqlParameter[] { new SqlParameter(Types.VARCHAR), new SqlParameter(Types.VARCHAR),
new SqlParameter(Types.BOOLEAN) });
setReturnGeneratedKeys(true);
setGeneratedKeysColumnNames(new String[] { "id" });
compile();
}
}
package javaspringexamples.springJDBC.dmlOperations;
import java.sql.Types;
import javax.sql.DataSource;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class UserUpdate extends SqlUpdate {
public UserUpdate(DataSource dataSource) {
super(dataSource, "update user set (name,user_name, locked) = (?,?,?) where id=?");
setParameters(new SqlParameter[] { new SqlParameter(Types.VARCHAR), new SqlParameter(Types.VARCHAR),
new SqlParameter(Types.BOOLEAN), new SqlParameter(Types.BIGINT) });
compile();
}
}
Get the sources of the example from the following GitHub url