Article 42 of spring's Way to God: Playing with JdbcTemplate

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. There is a module in spring that encapsulates JDBC operations to make 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 dataSource = DataSourceUtils.getDataSource();
    //2.Establish JdbcTemplate，new JdbcTemplate(dataSource)
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    //3.call JdbcTemplate Method operation db，If Additions, Deletions, Changes and Examination
    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 returned t_ All records of the user table return a set, which is a Map, which represents a row of records, key is the column name, and 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 in sql? As a placeholder.

@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 {
            //Manual Creation PreparedStatement，Notice 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:

The above T is generic, but only supports single data type Integer.class String.class, which is not supported by self-defined beans. (so it's 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 id User information for 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 id User information for 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.

Source: https://mp.weixin.qq.com/s?__ Biz=MzA5MTkxMDQ4MQ==&mid=2648936449&idx=2&sn=da1e98e5914821 f040d5530e8ca9d9bc&scene=21#wechat_ Redirect

Posted by RightNow21 on Fri, 05 Nov 2021 09:36:34 -0700

Programmer Group

Article 42 of spring's Way to God: Playing with JdbcTemplate

What is JdbcTemplate?

Steps for using JdbcTemplate

Add, delete, modify operations

No Parameters

Api

case

Reference 1

Api

case

Reference 2

Api

case

Get the value of the self-incrementing column

Api

case

Bulk add-delete change operation

Api

case

Query operation

Query a column and a single row

Api

case

Use Attention

Query one column and many rows

Api

case

Query single-line records to convert them into an object

Api

case

Use Attention

Query a single line record and return the specified javabean

Query for multiple columns and rows, one Map for each row

Api

case

Query multiple columns and rows, map results to javabean

Api

case

summary

Case Source

Hot Keywords