This article was intended to write about spring transactions, but JdbcTemplate-related functionality is used in most cases of transactions, so let's start with JdbcTemplate.
What is JdbcTemplate?
To review, the original way to operate db in java is pure jdbc. Whether every operation db needs to load database drivers, get connections, get PreparedStatement, execute sql, close PreparedStatement, close connections, etc. is still cumbersome. Spring provides a module to encapsulate JDBC operations, making them simpler.JdbcTemplate is Spring's encapsulation of JDBC to make it easier to use.
Let's see how JdbcTemplate actually works.
Steps for using JdbcTemplate
-
Create Data Source
-
Create JdbcTemplate, new JdbcTemplate(dataSource)
-
Call JdbcTemplate's method to manipulate db, such as add-delete check
public class DataSourceUtils {
public static DataSource getDataSource() {
org.apache.tomcat.jdbc.pool.DataSource dataSource = new org.apache.tomcat.jdbc.pool.DataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/javacode2018?characterEncoding=UTF-8");
dataSource.setUsername("root");
dataSource.setPassword("root123");
dataSource.setInitialSize(5);
return dataSource;
}
}
@Test
public void test0() {
//1. Create Data Source
DataSource dataSource = DataSourceUtils.getDataSource();
//2. Create JdbcTemplate, new JdbcTemplate(dataSource)
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
//3. Call JdbcTemplate's method to manipulate db, such as add-delete check
List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from t_user");
System.out.println(maps);
}
output
[{id=114, name=Passerby}, {id=115, name=java High concurrency}, {id=116, name=spring series}]
t_user table data
mysql> select id,name from t_user; +-----+---------------+ | id | name | +-----+---------------+ | 114 | Passerby | | 115 | java High concurrency | | 116 | spring series | +-----+---------------+ 3 rows in set (0.00 sec)
The above query returns all the records of the t_user table and a set, in which a Map represents a row of records, a key is the column name, and a value is the value corresponding to the column.
Did you find it particularly convenient to just use a simple line of code like jdbcTemplate.queryForList("select * from t_user") to get your data?
Let's continue to explore more powerful and useful features.
Add, delete, modify operations
The methods in JdbcTemplate that start with update are used to perform add, delete, and change operations. Here are a few common ones.
No Parameters
Api
int update(final String sql)
case
@Test
public void test1() {
DataSource dataSource = DataSourceUtils.getDataSource();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
int updateRows = jdbcTemplate.update("INSERT INTO t_user (name) VALUE ('maven series')");
System.out.println("Number of rows affected:" + updateRows);
}
Reference 1
Api
int update(String sql, Object... args)
case
Use? As a placeholder in sql.
@Test
public void test2() {
DataSource dataSource = DataSourceUtils.getDataSource();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
int updateRows = jdbcTemplate.update("INSERT INTO t_user (name) VALUE (?)", "mybatis series");
System.out.println("Number of rows affected:" + updateRows);
}
Reference 2
Api
int update(String sql, PreparedStatementSetter pss)
Setting parameters through the PreparedStatementSetter is a functional interface with a setValues method that passes a PreparedStatement parameter through which we can manually set the value of the parameter.
case
@Test
public void test3() {
DataSource dataSource = DataSourceUtils.getDataSource();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
int updateRows = jdbcTemplate.update("INSERT INTO t_user (name) VALUE (?)", new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setString(1, "mysql series");
}
});
System.out.println("Number of rows affected:" + updateRows);
}
Get the value of the self-incrementing column
Api
public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder)
case
@Test
public void test4() {
DataSource dataSource = DataSourceUtils.getDataSource();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String sql = "INSERT INTO t_user (name) VALUE (?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
int rowCount = jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
//Manually create the PreparedStatement, noting the second parameter: Statement.RETURN_GENERATED_KEYS
PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, "Get the value of the self-incrementing column");
return ps;
}
}, keyHolder);
System.out.println("New Record id: " + keyHolder.getKey().intValue());
}
output
New Record id: 122
mysql> select id,name from t_user; +-----+-----------------------+ | id | name | +-----+-----------------------+ | 114 | Passerby | | 115 | java High concurrency | | 116 | spring series | | 117 | maven series | | 118 | mysql series | | 122 | Get the value of the self-incrementing column | +-----+-----------------------+ 6 rows in set (0.00 sec)
Bulk add-delete change operation
Api
int[] batchUpdate(final String[] sql); int[] batchUpdate(String sql, List<Object[]> batchArgs); int[] batchUpdate(String sql, List<Object[]> batchArgs, int[] argTypes);
case
@Test
public void test5() {
DataSource dataSource = DataSourceUtils.getDataSource();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
List<Object[]> list = Arrays.asList(
new Object[]{"Lau Andy"},
new Object[]{"Guo Fucheng"},
new Object[]{"Jacky Cheung"},
new Object[]{"Dawn"});
int[] updateRows = jdbcTemplate.batchUpdate("INSERT INTO t_user (name) VALUE (?)", list);
for (int updateRow : updateRows) {
System.out.println(updateRow);
}
}
Query operation
Query a column and a single row
Api
/** * sql: sql executed, if there are parameters, parameter placeholders? * requiredType: The java type corresponding to a column of returned data, such as String * args: ?Placeholder corresponding parameter list **/ <T> T queryForObject(String sql, Class<T> requiredType, @Nullable Object... args)
case
@Test
public void test6() {
DataSource dataSource = DataSourceUtils.getDataSource();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String name = jdbcTemplate.queryForObject("select name from t_user where id = ?", String.class, 114);
System.out.println(name);
}
output
Passerby
Corresponding data in db
mysql> select name from t_user where id = 114; +--------+ | name | +--------+ | Passerby | +--------+ 1 row in set (0.00 sec)
Use Attention
Error if sql query in queryForObject has no result
A record with id 0 does not exist
mysql> select name from t_user where id = 0; Empty set (0.00 sec)
@Test
public void test7() {
DataSource dataSource = DataSourceUtils.getDataSource();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String name = jdbcTemplate.queryForObject("select name from t_user where id = ?", String.class, 0);
System.out.println(name);
}
Run, an exception EmptyResultDataAccessException pops up, expecting a record to be returned, but actually no record was found, which does not match the expected result, so an error was reported
org.springframework.dao.EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0 at org.springframework.dao.support.DataAccessUtils.nullableSingleResult(DataAccessUtils.java:97) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:784)
How to solve this problem requires querying multiple rows, that is, the queryForList-related method mentioned below returns an empty List when there is no result, and we can write on this empty List.
Query one column and many rows
Api
Method starting with queryForList.
<T> List<T> queryForList(String sql, Class<T> elementType); <T> List<T> queryForList(String sql, Class<T> elementType, @Nullable Object... args); <T> List<T> queryForList(String sql, Object[] args, Class<T> elementType); <T> List<T> queryForList(String sql, Object[] args, int[] argTypes, Class<T> elementType);
Be careful:
Although the above T is generic, it only supports single data type Integer.class String.class, which is not supported by self-defined beans. (So it is used to query single column data)
elementType: What type of query results need to be converted to, such as String, Integer, Double.
case
@Test
public void test8() {
DataSource dataSource = DataSourceUtils.getDataSource();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
//<T> List<T> queryForList(String sql, Class<T> elementType);
List<String> list1 = jdbcTemplate.queryForList("select name from t_user where id>131", String.class);
System.out.println("list1:" + list1);
//<T> List<T> queryForList(String sql, Class<T> elementType, @Nullable Object... args);
List<String> list2 = jdbcTemplate.queryForList("select name from t_user where id>?", String.class, 131);
System.out.println("list2:" + list2);
//<T> List<T> queryForList(String sql, Object[] args, Class<T> elementType);
List<String> list3 = jdbcTemplate.queryForList("select name from t_user where id>?", new Object[]{131}, String.class);
System.out.println("list3:" + list3);
//<T> List<T> queryForList(String sql, Object[] args, int[] argTypes, Class<T> elementType);
List<String> list4 = jdbcTemplate.queryForList("select name from t_user where id>?", new Object[]{131}, new int[]{java.sql.Types.INTEGER}, String.class);
System.out.println("list4:" + list4);
}
output
list1:[Guo Fucheng, Jacky Cheung, Dawn] list2:[Guo Fucheng, Jacky Cheung, Dawn] list3:[Guo Fucheng, Jacky Cheung, Dawn] list4:[Guo Fucheng, Jacky Cheung, Dawn]
sql result:
mysql> select name from t_user where id>131; +-----------+ | name | +-----------+ | Guo Fucheng | | Jacky Cheung | | Dawn | +-----------+ 3 rows in set (0.00 sec)
Query single-line records to convert them into an object
Api
<T> T queryForObject(String sql, RowMapper<T> rowMapper); <T> T queryForObject(String sql, Object[] args, RowMapper<T> rowMapper); <T> T queryForObject(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper); <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args);
One of the parameters of these methods is a rowMapper parameter, a row mapper that maps the results of the current row to a custom object.
@FunctionalInterface
public interface RowMapper<T> {
/**
* @param ResultSet Result Set
* @param The row of the current result set
* @return Result object of the current row, mapping the results of the current row to a custom object to return
*/
@Nullable
T mapRow(ResultSet rs, int rowNum) throws SQLException;
}
The JdbcTemplate traverses the ResultSet internally, then loops through RowMapper#mapRow to get the result of the current row, dropping it back into the List, as follows:
List<T> results = new ArrayList<>();
int rowNum = 0;
while (rs.next()) {
results.add(this.rowMapper.mapRow(rs, rowNum++));
}
return results;
case
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class User {
private Integer id;
private String name;
}
@Test
public void test9() {
DataSource dataSource = DataSourceUtils.getDataSource();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String sql = "select id,name from t_user where id = ?";
//Query user information with id 34
User user = jdbcTemplate.queryForObject(sql, new RowMapper<User>() {
@Nullable
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getInt(1));
user.setName(rs.getString(1));
return user;
}
}, 134);
System.out.println(user);
}
output
User(id=134, name=134)
Use Attention
When the sql query in queryForObject has no result, an error is raised and a row of records must be returned
Query a single line record and return the specified javabean
RowMapper has an implementation class BeanPropertyRowMapper that maps the results to javabean s.
@Test
public void test10() {
DataSource dataSource = DataSourceUtils.getDataSource();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String sql = "select id,name from t_user where id = ?";
//Query user information with id 34
RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
User user = jdbcTemplate.queryForObject(sql, rowMapper, 134);
System.out.println(user);
}
Query for multiple columns and rows, one Map for each row
Api
List<Map<String, Object>> queryForList(String sql); List<Map<String, Object>> queryForList(String sql, Object... args);
Each row results in a Map with key lowercase and value corresponding to the column.
case
@Test
public void test11() {
DataSource dataSource = DataSourceUtils.getDataSource();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String sql = "select id,name from t_user where id>?";
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, 130);
System.out.println(maps);
}
output
[{id=131, name=Lau Andy}, {id=132, name=Guo Fucheng}, {id=133, name=Jacky Cheung}, {id=134, name=Dawn}]
Query multiple columns and rows, map results to javabean
Api
<T> List<T> query(String sql, RowMapper<T> rowMapper, @Nullable Object... args)
case
@Test
public void test12() {
DataSource dataSource = DataSourceUtils.getDataSource();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String sql = "select id,name from t_user where id>?";
List<User> maps = jdbcTemplate.query(sql, new RowMapper<User>() {
@Nullable
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getInt(1));
user.setName(rs.getString(1));
return user;
}
}, 130);
System.out.println(maps);
}
Run Output
[User(id=131, name=Lau Andy), User(id=132, name=Guo Fucheng), User(id=133, name=Jacky Cheung), User(id=134, name=Dawn)]
A simpler way to use BeanPropertyRowMapper
@Test
public void test13() {
DataSource dataSource = DataSourceUtils.getDataSource();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String sql = "select id,name from t_user where id>?";
List<User> maps = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class), 130);
System.out.println(maps);
}
output
[User(id=131, name=Lau Andy), User(id=132, name=Guo Fucheng), User(id=133, name=Jacky Cheung), User(id=134, name=Dawn)]
summary
-
Using a method that starts with getObject in JdbcTemplate requires sql to return a record or an error will be reported
-
BeanPropertyRowMapper maps row records to JavaBeans
-
JdbcTemplate uses templates to manipulate jdbc very easily and the code is very concise. However, it does not have the function of dynamic sql. That is, it generates the specified SQL dynamically by parameters. mybatis does a good job in dynamic sql. You can choose what you want when you use it.
Case Source
git Address: https://gitee.com/javacode2018/spring-series This case corresponds to the source code: spring-series\lesson-003-jdbctemplate\src\main\java\com\javacode2018\jdbctemplate\demo1\Demo1Test.java
All the case code for Pedestrian java will be put on this one in the future. watch for everyone to keep an eye on the dynamics.