The technologies used are :
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>springJdbcBatchOperations</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.batchOperations;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
@Configuration
public class Conf {
@Value("classpath:schema.sql")
private Resource schemaScript;
@Value("classpath:data.sql")
private Resource dataScript;
@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 DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
final DataSourceInitializer initializer = new DataSourceInitializer();
initializer.setDataSource(dataSource);
initializer.setDatabasePopulator(databasePopulator());
return initializer;
}
private DatabasePopulator databasePopulator() {
final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
populator.addScript(schemaScript);
populator.addScript(dataScript);
return populator;
}
@Bean
public JdbcTemplate jdbcTemplate() {
JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource());
return jdbcTemplate;
}
@Bean
public UserDao userDao() {
UserDaoImpl userDao = new UserDaoImpl();
userDao.setJdbcTemplate(jdbcTemplate());
return userDao;
}
}
package javaspringexamples.springJDBC.batchOperations;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
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);
List<Long> usersIds = new ArrayList<>();
usersIds.add(65L);
usersIds.add(256L);
usersIds.add(999L);
UserDao userDao = applicationContext.getBean(UserDao.class);
List<User> users = userDao.find(usersIds);
for (User user : users) {
System.out.println(user.toString());
user.setName("Waaaaaaw");
user.setUserName("No no noooooooooo");
}
User u = new User();
u.setId(36L);
u.setName(":-|");
users.add(u);
userDao.update(users);
users = userDao.find(usersIds);
for (User user : users) {
System.out.println(user.toString());
}
}
}
package javaspringexamples.springJDBC.batchOperations;
import org.springframework.dao.DataAccessException;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class UpdateFailedException extends DataAccessException {
public UpdateFailedException(String msg) {
super(msg);
}
}
package javaspringexamples.springJDBC.batchOperations;
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.batchOperations;
import java.util.List;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public interface UserDao {
List<User> find(List<Long> userIds);
void update(final List<User> users);
}
package javaspringexamples.springJDBC.batchOperations;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class UserDaoImpl implements UserDao {
private JdbcTemplate jdbcTemplate;
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
private UserRowMapper userRowMapper = new UserRowMapper();
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
}
public List<User> find(List<Long> userIds) {
SqlParameterSource sqlParameterSource = new MapSqlParameterSource("userIds", userIds);
return namedParameterJdbcTemplate.query("select * from user where id in (:userIds)", sqlParameterSource,
userRowMapper);
}
public void update(final List<User> users) {
int[] counts = jdbcTemplate.batchUpdate("update user set (name, user_name, locked) = (?,?,?) where id = ?",
new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i) throws SQLException {
User user = users.get(i);
ps.setString(1, user.getName());
ps.setString(2, user.getUserName());
ps.setBoolean(3, user.isLocked());
ps.setLong(4, user.getId());
}
public int getBatchSize() {
return users.size();
}
});
int i = 0;
for (int count : counts) {
if (count == 0)
throw new UpdateFailedException("Row not updated :" + i);
i++;
}
}
}
package javaspringexamples.springJDBC.batchOperations;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class UserRowMapper implements 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.setAccessTime(rs.getTimestamp("access_time"));
user.setLocked(rs.getBoolean("locked"));
return user;
}
}
MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (999L, 'Mounir SAHRANI', 'msahrani', '2019-01-01',false); MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (256L, 'Laurent SOURDEAU', 'lsourdeau', '2019-06-01',false); MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (65L, 'John SMITH', 'jsmith', '2019-06-01',true);
CREATE TABLE IF NOT EXISTS USER (ID BIGINT IDENTITY PRIMARY KEY, NAME VARCHAR(60), USER_NAME VARCHAR(60), ACCESS_TIME TIMESTAMP, LOCKED BOOLEAN);
Get the sources of the example from the following GitHub url
]]>The technologies used are :
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
]]>The technologies used are :
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>springJdbcRunningSQLQueries</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.runningSqlQueries;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
@Configuration
public class Conf {
@Value("classpath:schema.sql")
private Resource schemaScript;
@Value("classpath:data.sql")
private Resource dataScript;
@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 DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
final DataSourceInitializer initializer = new DataSourceInitializer();
initializer.setDataSource(dataSource);
initializer.setDatabasePopulator(databasePopulator());
return initializer;
}
private DatabasePopulator databasePopulator() {
final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
populator.addScript(schemaScript);
populator.addScript(dataScript);
return populator;
}
@Bean
public JdbcTemplate jdbcTemplate() {
JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource());
return jdbcTemplate;
}
@Bean
public UserDao userDao() {
UserDaoImpl userDao = new UserDaoImpl();
userDao.setJdbcTemplate(jdbcTemplate());
return userDao;
}
}
package javaspringexamples.springJDBC.runningSqlQueries;
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 = userDao.findById(999L);
System.out.println(user.getId());
System.out.println(user.getName());
System.out.println(user.getAccessTime());
System.out.println(user.isLocked());
}
}
package javaspringexamples.springJDBC.runningSqlQueries;
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.runningSqlQueries;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public interface UserDao {
public User findById(long usertId);
}
package javaspringexamples.springJDBC.runningSqlQueries;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class UserDaoImpl implements UserDao {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public User findById(long userId) {
return jdbcTemplate.queryForObject("SELECT id, name, user_name, access_time, 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.setAccessTime(rs.getTimestamp("access_time"));
user.setLocked(rs.getBoolean("locked"));
return user;
}
}, userId);
}
}
MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (999L, 'Mounir SAHRANI', 'msahrani', '2019-01-01',false); MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (256L, 'Laurent SOURDEAU', 'lsourdeau', '2019-06-01',false);
CREATE TABLE IF NOT EXISTS USER (ID BIGINT IDENTITY PRIMARY KEY, NAME VARCHAR(60), USER_NAME VARCHAR(60), ACCESS_TIME TIMESTAMP, LOCKED BOOLEAN);
Get the sources of the example from the following GitHub url
]]>The technologies used are :
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>springJdbcQueriesInClause</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.queriesInClause;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
@Configuration
public class Conf {
@Value("classpath:schema.sql")
private Resource schemaScript;
@Value("classpath:data.sql")
private Resource dataScript;
@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 DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
final DataSourceInitializer initializer = new DataSourceInitializer();
initializer.setDataSource(dataSource);
initializer.setDatabasePopulator(databasePopulator());
return initializer;
}
private DatabasePopulator databasePopulator() {
final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
populator.addScript(schemaScript);
populator.addScript(dataScript);
return populator;
}
@Bean
public JdbcTemplate jdbcTemplate() {
JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource());
return jdbcTemplate;
}
@Bean
public UserDao userDao() {
UserDaoImpl userDao = new UserDaoImpl();
userDao.setJdbcTemplate(jdbcTemplate());
return userDao;
}
}
package javaspringexamples.springJDBC.queriesInClause;
import java.sql.SQLException;
import java.util.List;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
public class Main {
public static void main(String[] args) throws SQLException {
AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(Conf.class);
UserDao userDao = applicationContext.getBean(UserDao.class);
List<User> users = userDao.find(new Long[] { 65L, 999L });
for (User user : users)
System.out.println(user.toString());
}
}
package javaspringexamples.springJDBC.queriesInClause;
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.queriesInClause;
import java.util.List;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public interface UserDao {
public List<User> find(Long... userIds);
}
package javaspringexamples.springJDBC.queriesInClause;
import java.util.Arrays;
import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class UserDaoImpl implements UserDao {
private JdbcTemplate jdbcTemplate;
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
private UserRowMapper userRowMapper = new UserRowMapper();
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
}
public List<User> find(Long... userIds) {
SqlParameterSource sqlParameterSource = new MapSqlParameterSource("userIds", Arrays.asList(userIds));
return namedParameterJdbcTemplate.query("select * from user where id in (:userIds)", sqlParameterSource,
userRowMapper);
}
}
package javaspringexamples.springJDBC.queriesInClause;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class UserRowMapper implements 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.setAccessTime(rs.getTimestamp("access_time"));
user.setLocked(rs.getBoolean("locked"));
return user;
}
}
MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (999L, 'Mounir SAHRANI', 'msahrani', '2019-01-01',false); MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (256L, 'Laurent SOURDEAU', 'lsourdeau', '2019-06-01',false); MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (65L, 'John SMITH', 'jsmith', '2019-06-01',true);
CREATE TABLE IF NOT EXISTS USER (ID BIGINT IDENTITY PRIMARY KEY, NAME VARCHAR(60), USER_NAME VARCHAR(60), ACCESS_TIME TIMESTAMP, LOCKED BOOLEAN);
Get the sources of the example from the following GitHub url
]]>The technologies used are :
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>springJdbcPreparedStatements</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.preparedStatements;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
@Configuration
public class Conf {
@Value("classpath:schema.sql")
private Resource schemaScript;
@Value("classpath:data.sql")
private Resource dataScript;
@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 DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
final DataSourceInitializer initializer = new DataSourceInitializer();
initializer.setDataSource(dataSource);
initializer.setDatabasePopulator(databasePopulator());
return initializer;
}
private DatabasePopulator databasePopulator() {
final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
populator.addScript(schemaScript);
populator.addScript(dataScript);
return populator;
}
@Bean
public JdbcTemplate jdbcTemplate() {
JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource());
return jdbcTemplate;
}
@Bean
public UserDao userDao() {
UserDaoImpl userDao = new UserDaoImpl();
userDao.setJdbcTemplate(jdbcTemplate());
return userDao;
}
}
package javaspringexamples.springJDBC.preparedStatements;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
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);
List<User> users = new ArrayList<>();
users.addAll(userDao.findByUserName("jsmith"));
users.addAll(userDao.findLocked(false));
users.add(userDao.findById(65L));
for (User user : users)
System.out.println(user.toString());
}
}
package javaspringexamples.springJDBC.preparedStatements;
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.preparedStatements;
import java.util.List;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public interface UserDao {
public User findById(long usertId);
public List<User> findByUserName(String userName);
public List<User> findLocked(boolean locked);
}
package javaspringexamples.springJDBC.preparedStatements;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class UserDaoImpl implements UserDao {
private JdbcTemplate jdbcTemplate;
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
private UserRowMapper userRowMapper = new UserRowMapper();
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
}
public User findById(long userId) {
return jdbcTemplate.queryForObject("SELECT id, name, user_name, access_time, locked FROM USER WHERE id = ?",
userRowMapper, userId);
}
public List<User> findByUserName(String userName) {
PreparedStatementCreatorFactory psCreatorFactory = new PreparedStatementCreatorFactory(
"SELECT id, name, user_name, access_time, locked FROM USER WHERE user_name like ?",
new int[] { Types.VARCHAR });
return jdbcTemplate.query(psCreatorFactory.newPreparedStatementCreator(new Object[] { userName }),
userRowMapper);
}
public List<User> findLocked(boolean locked) {
return jdbcTemplate.query("SELECT id, name, user_name, access_time, locked FROM USER WHERE locked = ?",
new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setBoolean(1, locked);
}
}, userRowMapper);
}
}
package javaspringexamples.springJDBC.preparedStatements;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class UserRowMapper implements 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.setAccessTime(rs.getTimestamp("access_time"));
user.setLocked(rs.getBoolean("locked"));
return user;
}
}
MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (999L, 'Mounir SAHRANI', 'msahrani', '2019-01-01',false); MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (256L, 'Laurent SOURDEAU', 'lsourdeau', '2019-06-01',false); MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (65L, 'John SMITH', 'jsmith', '2019-06-01',true);
CREATE TABLE IF NOT EXISTS USER (ID BIGINT IDENTITY PRIMARY KEY, NAME VARCHAR(60), USER_NAME VARCHAR(60), ACCESS_TIME TIMESTAMP, LOCKED BOOLEAN);
Get the sources of the example from the following GitHub url
]]>The technologies used are :
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>springJdbcNamedParameter</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.namedParameter;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
@Configuration
public class Conf {
@Value("classpath:schema.sql")
private Resource schemaScript;
@Value("classpath:data.sql")
private Resource dataScript;
@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 DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
final DataSourceInitializer initializer = new DataSourceInitializer();
initializer.setDataSource(dataSource);
initializer.setDatabasePopulator(databasePopulator());
return initializer;
}
private DatabasePopulator databasePopulator() {
final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
populator.addScript(schemaScript);
populator.addScript(dataScript);
return populator;
}
@Bean
public JdbcTemplate jdbcTemplate() {
JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource());
return jdbcTemplate;
}
@Bean
public UserDao userDao() {
UserDaoImpl userDao = new UserDaoImpl();
userDao.setJdbcTemplate(jdbcTemplate());
return userDao;
}
}
package javaspringexamples.springJDBC.namedParameter;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
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);
List<User> users = new ArrayList<>();
users.addAll(userDao.findByUserName("jsmith"));
users.addAll(userDao.findLocked(false));
users.add(userDao.findById(65L));
for (User user : users)
System.out.println(user.toString());
}
}
package javaspringexamples.springJDBC.namedParameter;
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.namedParameter;
import java.util.List;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public interface UserDao {
public User findById(long usertId);
public List<User> findByUserName(String userName);
public List<User> findLocked(boolean locked);
}
package javaspringexamples.springJDBC.namedParameter;
import java.util.Collections;
import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class UserDaoImpl implements UserDao {
private JdbcTemplate jdbcTemplate;
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
private UserRowMapper userRowMapper = new UserRowMapper();
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
}
public User findById(long userId) {
return jdbcTemplate.queryForObject("SELECT id, name, user_name, access_time, locked FROM USER WHERE id = ?",
userRowMapper, userId);
}
public List<User> findByUserName(String userName) {
return namedParameterJdbcTemplate.query(
"SELECT id, name, user_name, access_time, locked FROM USER WHERE user_name = :userName",
Collections.singletonMap("userName", userName), userRowMapper);
}
public List<User> findLocked(boolean locked) {
return namedParameterJdbcTemplate.query(
"SELECT id, name, user_name, access_time, locked FROM USER WHERE locked = :locked",
Collections.singletonMap("locked", locked), userRowMapper);
}
}
package javaspringexamples.springJDBC.namedParameter;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class UserRowMapper implements 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.setAccessTime(rs.getTimestamp("access_time"));
user.setLocked(rs.getBoolean("locked"));
return user;
}
}
MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (999L, 'Mounir SAHRANI', 'msahrani', '2019-01-01',false); MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (256L, 'Laurent SOURDEAU', 'lsourdeau', '2019-06-01',false); MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (65L, 'John SMITH', 'jsmith', '2019-06-01',true);
CREATE TABLE IF NOT EXISTS USER (ID BIGINT IDENTITY PRIMARY KEY, NAME VARCHAR(60), USER_NAME VARCHAR(60), ACCESS_TIME TIMESTAMP, LOCKED BOOLEAN);
Get the sources of the example from the following GitHub url
]]>The technologies used are :
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
]]>The technologies used are :
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>springJdbcInitializingDb</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> </dependencies> </project>
package javaspringexamples.springJDBC.InitializingDb;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.context.support.ClassPathXmlApplicationContext;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class Main {
public static void main(String[] args) throws SQLException {
ClassPathXmlApplicationContext applicationContext = new ClassPathXmlApplicationContext("classpath:conf.xml");
DataSource dataSource = applicationContext.getBean("dataSource", DataSource.class);
Connection connection = dataSource.getConnection();
System.out.println(connection.isClosed());
connection.close();
System.out.println(connection.isClosed());
}
}
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/jdbc
http://www.springframework.org/schema/jdbc/spring-jdbc-4.0.xsd">
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="org.h2.Driver" />
<property name="url"
value="jdbc:h2:tcp://localhost/~/javaspringexamples" />
<property name="username" value="sa" />
<property name="password" value="" />
</bean>
<!-- <jdbc:embedded-database id="dataSource" type="H2"/> -->
<jdbc:initialize-database
data-source="dataSource">
<jdbc:script location="classpath:schema.sql" />
<jdbc:script location="classpath:data.sql" />
</jdbc:initialize-database>
</beans>
MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (999, 'Mounir SAHRANI', 'msahrani', '2019-01-01',false); MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (256, 'Laurent SOURDEAU', 'lsourdeau', '2019-06-01',false);
CREATE TABLE IF NOT EXISTS USER (ID BIGINT IDENTITY PRIMARY KEY, NAME VARCHAR(60), USER_NAME VARCHAR(60), ACCESS_TIME TIMESTAMP, LOCKED BOOLEAN);
Get the sources of the example from the following GitHub url
]]>The technologies used are :
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>springJdbcConfiguringPooledDatasource</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>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> <version>1.4</version> </dependency> <dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.5.3</version> </dependency> </dependencies> </project>
package javaspringexamples.springJDBC.pooledDatasource;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
@Configuration
public class ConfigForBasicDataSource {
@Bean(destroyMethod = "close")
public DataSource dataSource() {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName("org.h2.Driver");
dataSource.setUrl("jdbc:h2:tcp://localhost/~/javaspringexamples");
dataSource.setUsername("sa");
dataSource.setPassword("");
return dataSource;
}
}
package javaspringexamples.springJDBC.pooledDatasource;
import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
@Configuration
public class ConfigForComboPooledDataSource {
@Bean(destroyMethod = "close")
public DataSource dataSource() throws Exception {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("org.h2.Driver");
dataSource.setJdbcUrl("jdbc:h2:tcp://localhost/~/javaspringexamples");
dataSource.setUser("sa");
dataSource.setPassword("");
return dataSource;
}
}
package javaspringexamples.springJDBC.pooledDatasource;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class Main {
public static void main(String[] args) throws SQLException {
// Instatiating for BasicDataSource
AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(
ConfigForBasicDataSource.class);
DataSource dataSource = applicationContext.getBean("dataSource", DataSource.class);
Connection connection = dataSource.getConnection();
System.out.println(connection.isClosed());
connection.close();
System.out.println(connection.isClosed());
// Instatiating for ComboPooledDatasource
applicationContext = new AnnotationConfigApplicationContext(ConfigForComboPooledDataSource.class);
dataSource = applicationContext.getBean("dataSource", DataSource.class);
connection = dataSource.getConnection();
System.out.println(connection.isClosed());
connection.close();
System.out.println(connection.isClosed());
}
}
Get the sources of the example from the following GitHub url
]]>The technologies used are :
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>springJdbcEmbeddedDbSupport</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>junit</groupId> <artifactId>junit</artifactId> <version>4.11</version> <scope>test</scope> </dependency> </dependencies> </project>
package javaspringexamples.springJDBC.EmbeddedDbSupport;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.context.support.ClassPathXmlApplicationContext;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class Main {
public static void main(String[] args) throws SQLException {
ClassPathXmlApplicationContext applicationContext = new ClassPathXmlApplicationContext("classpath:conf.xml");
DataSource dataSource = applicationContext.getBean("dataSource", DataSource.class);
Connection connection = dataSource.getConnection();
System.out.println(connection.isClosed());
connection.close();
System.out.println(connection.isClosed());
}
}
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/jdbc
http://www.springframework.org/schema/jdbc/spring-jdbc-4.0.xsd">
<jdbc:embedded-database id="dataSource"
type="H2">
<jdbc:script location="classpath:schema.sql" />
<jdbc:script location="classpath:data.sql" />
</jdbc:embedded-database>
</beans>
insert into USER (NAME, USER_NAME, LOCKED) values ('Mounir SAHRANI','javaspringexamples',false);
CREATE TABLE USER (ID BIGINT IDENTITY PRIMARY KEY, NAME VARCHAR(50), USER_NAME VARCHAR(50), LOCKED BOOLEAN);
package javaspringexamples.springJDBC.EmbeddedDbSupport;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;
/**
*
* @author mounir.sahrani@gmail.com
*
*/
public class EmbeddedDataSourceTest {
private DataSource dataSource;
@Before
public void setUp() {
dataSource = new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.H2).addScript("classpath:schema.sql")
.addScript("classpath:data.sql").build();
}
@Test
public void testDataAccessLogic() throws SQLException {
Connection connection = dataSource.getConnection();
Assert.assertFalse(connection.isClosed());
connection.close();
Assert.assertTrue(connection.isClosed());
}
@After
public void tearDown() {
((EmbeddedDatabase) dataSource).shutdown();
}
}
Get the sources of the example from the following GitHub url
]]>