This example presents the basic concept of using jdbcTemplate 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</groupId> <artifactId>springJdbcConfiguringJdbcTemplate</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> <scope>provided</scope> </dependency> </dependencies> </project>
package javaspringexamples.springJDBC.JdbcTemplate;
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;
/**
*
* @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());
return userDao;
}
}
package javaspringexamples.springJDBC.JdbcTemplate;
import org.springframework.dao.DataAccessException;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class ExceptionDeleteFailed extends DataAccessException {
public ExceptionDeleteFailed(String msg) {
super(msg);
}
}
package javaspringexamples.springJDBC.JdbcTemplate;
import org.springframework.dao.DataAccessException;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class ExceptionInsertFailed extends DataAccessException {
public ExceptionInsertFailed(String msg) {
super(msg);
}
}
package javaspringexamples.springJDBC.JdbcTemplate;
import org.springframework.dao.DataAccessException;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class ExceptionUpdateFailed extends DataAccessException {
public ExceptionUpdateFailed(String msg) {
super(msg);
}
}
package javaspringexamples.springJDBC.JdbcTemplate;
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("Waaaaw");
user.setUserName("Wiiiiiw");
user.setLocked(false);
userDao.insert(user);
user = userDao.find(user.getId());
System.out.println(user.getName() + ", " + user.getUserName() + ", " + user.isLocked());
user.setLocked(true);
userDao.update(user);
user = userDao.find(user.getId());
System.out.println(user.getName() + ", " + user.getUserName() + ", " + user.isLocked());
userDao.delete(user.getId());
user = userDao.find(user.getId());
System.out.println(user);
}
}
package javaspringexamples.springJDBC.JdbcTemplate;
import java.util.Date;
import lombok.Data;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
@Data
public class User {
private long id;
private String name;
private String userName;
private Date accessTime;
private boolean locked;
}
package javaspringexamples.springJDBC.JdbcTemplate;
/**
*
* @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.JdbcTemplate;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class UserDaoJdbcImpl implements UserDao {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public void insert(User user) {
PreparedStatementCreatorFactory psCreatorFactory = new PreparedStatementCreatorFactory(
"insert into user(name, user_name, locked) values(?,?,?)",
new int[] { Types.VARCHAR, Types.VARCHAR, Types.BOOLEAN });
KeyHolder keyHolder = new GeneratedKeyHolder();
int count = jdbcTemplate.update(psCreatorFactory.newPreparedStatementCreator(
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 = jdbcTemplate.update("update user set (name, user_name, locked) = (?,?,?) where id = ?",
user.getName(), user.getUserName(), user.isLocked(), user.getId());
if (count != 1)
throw new ExceptionUpdateFailed("Cannot update user");
}
public void delete(long userId) {
int count = jdbcTemplate.update("delete user where id = ?", userId);
if (count != 1)
throw new ExceptionDeleteFailed("Cannot delete user");
}
public User find(long userId) {
try {
return jdbcTemplate.queryForObject("select id, name, user_name, locked from user where id = ?",
new RowMapper<User>() {
public 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;
}
}, userId);
} catch (EmptyResultDataAccessException e) {
return null;
}
}
}
Get the sources of the example from the following GitHub url