乐趣区

SpringBoot高级篇JdbcTemplate之数据插入使用姿势详解

jiezi

db 操作可以说是 java 后端的必备技能了，实际项目中，直接使用 JdbcTemplate 的机会并不多，大多是 mybatis，hibernate，jpa 或者是 jooq，然后前几天写一个项目，因为 db 操作非常简单，就直接使用 JdbcTemplate，然而悲催的发现，对他的操作并没有预期中的那么顺畅，所以有必要好好的学一下 JdbcTemplate 的 CURD；本文为第一篇，插入数据

<!– more –>

I. 环境

1. 配置相关

使用 SpringBoot 进行 db 操作引入几个依赖，就可以愉快的玩耍了，这里的 db 使用 mysql，对应的 pom 依赖如 

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
</dependencies>

接着就是 db 的配置信息, 下面是连接我本机的数据库配置 

## DataSource
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.driver-class-name= com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=

2. 测试 db

创建一个测试 db

CREATE TABLE `money` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT ''COMMENT' 用户名 ',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT '钱',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

II. 使用姿势

直接引入 jdbcTemplate，注入即可，不需要其他的操作 

@Autowired
private JdbcTemplate jdbcTemplate;

1. sql 直接插入一条数据

直接写完整的插入 sql，这种方式比较简单粗暴 

private boolean insertBySql() {
    // 简单的 sql 执行
    String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES (' 一灰灰 blog', 100, 0);";
    return jdbcTemplate.update(sql) > 0;
}

2. 参数替换方式插入

这种插入方式中，sql 使用占位符？，然后插入值通过参数传入即可 

private boolean insertBySqlParams() {String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES (?, ?, ?);";
    return jdbcTemplate.update(sql, "一灰灰 2", 200, 0) > 0;
}

3. 通过 Statement 方式插入

通过 Statement 可以指定参数类型，这种插入方式更加安全，有两种常见的方式，注意设置参数时，起始值为 1，而不是通常说的 0 

private boolean insertByStatement() {String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES (?, ?, ?);";
    return jdbcTemplate.update(sql, new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement preparedStatement) throws SQLException {preparedStatement.setString(1, "一灰灰 3");
            preparedStatement.setInt(2, 300);
            byte b = 0;
            preparedStatement.setByte(3, b);
        }
    }) > 0;
}

private boolean insertByStatement2() {String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES (?, ?, ?);";
    return jdbcTemplate.update(new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "一灰灰 4");
            preparedStatement.setInt(2, 400);
            byte b = 0;
            preparedStatement.setByte(3, b);
            return preparedStatement;
        }
    }) > 0;
}

4. 插入并返回主键 id

这个属于比较常见的需求了，我希望获取插入数据的主键 id，用于后续的业务使用; 这时就需要用 KeyHolder

/**
 * 新增数据，并返回主键 id
 *
 * @return
 */
private int insertAndReturnId() {String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES (?, ?, ?);";
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            // 指定主键
            PreparedStatement preparedStatement = connection.prepareStatement(sql, new String[]{"id"});
            preparedStatement.setString(1, "一灰灰 5");
            preparedStatement.setInt(2, 500);
            byte b = 0;
            preparedStatement.setByte(3, b);
            return preparedStatement;
        }
    }, keyHolder);
    return keyHolder.getKey().intValue();
}

看上面的实现，和前面差不多，但是有一行需要额外注意，在获取 Statement 时，需要制定主键，否则会报错 

// 指定主键
PreparedStatement preparedStatement = connection.prepareStatement(sql, new String[]{"id"});

5. 批量插入

基本插入看完之后，再看批量插入，会发现和前面的姿势没有太大的区别，无非是传入一个数组罢了，如下面的几种使用姿势 

private void batchInsertBySql() {String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES" +
            "('Batch 一灰灰 blog', 100, 0), ('Batch 一灰灰 blog 2', 100, 0);";
    int[] ans = jdbcTemplate.batchUpdate(sql);
    System.out.println("batch insert by sql:" + JSON.toJSONString(ans));
}

private void batchInsertByParams() {String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES (?, ?, ?);";

    Object[] param1 = new Object[]{"Batch 一灰灰 3", 200, 0};
    Object[] param2 = new Object[]{"Batch 一灰灰 4", 200, 0};
    int[] ans = jdbcTemplate.batchUpdate(sql, Arrays.asList(param1, param2));
    System.out.println("batch insert by params:" + JSON.toJSONString(ans));
}

private void batchInsertByStatement() {String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES (?, ?, ?);";

    int[] ans = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {if (i == 0) {preparedStatement.setString(1, "batch 一灰灰 5");
            } else {preparedStatement.setString(1, "batch 一灰灰 6");
            }
            preparedStatement.setInt(2, 300);
            byte b = 0;
            preparedStatement.setByte(3, b);
        }

        @Override
        public int getBatchSize() {return 2;}
    });
    System.out.println("batch insert by statement:" + JSON.toJSONString(ans));
}

6. 测试

接下来我们测试下上面的代码执行情况 

@Component
public class InsertService {
    /**
     * 简单的新增一条数据
     */
    public void basicInsert() {System.out.println("basic insert:" + insertBySql());
        System.out.println("insertBySqlParams:" + insertBySqlParams());
        System.out.println("insertByStatement:" + insertByStatement());
        System.out.println("insertByStatement2:" + insertByStatement2());
        System.out.println("insertAndReturn:" + insertAndReturnId());

        List<Map<String, Object>> result = jdbcTemplate.queryForList("select * from money");
        System.out.println("after insert, the records:\n" + result);
    }
    
    /**
     * 批量插入数据
     */
    public void batchInsert() {batchInsertBySql();
        batchInsertByParams();
        batchInsertByStatement();}
}

@SpringBootApplication
public class Application {public Application(InsertService insertService) {insertService.basicInsert();
        insertService.batchInsert();}

    public static void main(String[] args) {SpringApplication.run(Application.class);
    }
}

输出结果如 

basic insert: true
insertBySqlParams: true
insertByStatement: true
insertByStatement2: true
insertAndReturn: 5
after insert, the records:
[{id=1, name= 一灰灰 blog, money=100, is_deleted=false, create_at=2019-04-08 10:22:50.0, update_at=2019-04-08 10:22:50.0}, {id=2, name= 一灰灰 2, money=200, is_deleted=false, create_at=2019-04-08 10:22:55.0, update_at=2019-04-08 10:22:55.0}, {id=3, name= 一灰灰 3, money=300, is_deleted=false, create_at=2019-04-08 10:22:55.0, update_at=2019-04-08 10:22:55.0}, {id=4, name= 一灰灰 4, money=400, is_deleted=false, create_at=2019-04-08 10:22:55.0, update_at=2019-04-08 10:22:55.0}, {id=5, name= 一灰灰 5, money=500, is_deleted=false, create_at=2019-04-08 10:22:55.0, update_at=2019-04-08 10:22:55.0}]
batch insert by sql: [2]
batch insert by params: [1,1]
batch insert by statement: [1,1]

II. 扩展

1. 批量插入并返回主键 id

上面还漏了一个批量插入时，也需要返回主键 id，改怎么办？

直接看 JdbcTemplate 的接口，并没有发现类似单个插入获取主键的方式，是不是意味着没法实现呢？

当然不是了，既然没有提供，我们完全可以依葫芦画瓢，自己实现一个 ExtendJdbcTemplate，首先看先单个插入返回 id 的实现如

接下来，我们自己的实现可以如下 

public class ExtendJdbcTemplate extends JdbcTemplate {public ExtendJdbcTemplate(DataSource dataSource) {super(dataSource);
    }

    public int[] batchUpdate(final String sql, final BatchPreparedStatementSetter pss,
            final KeyHolder generatedKeyHolder) throws DataAccessException {return execute(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {return conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            }
        }, ps -> {
            try {int batchSize = pss.getBatchSize();
                int totalRowsAffected = 0;
                int[] rowsAffected = new int[batchSize];
                List generatedKeys = generatedKeyHolder.getKeyList();
                generatedKeys.clear();
                ResultSet keys = null;
                for (int i = 0; i < batchSize; i++) {pss.setValues(ps, i);
                    rowsAffected[i] = ps.executeUpdate();
                    totalRowsAffected += rowsAffected[i];
                    try {keys = ps.getGeneratedKeys();
                        if (keys != null) {RowMapper rowMapper = new ColumnMapRowMapper();
                            RowMapperResultSetExtractor rse = new RowMapperResultSetExtractor(rowMapper, 1);
                            generatedKeys.addAll(rse.extractData(keys));
                        }
                    } finally {JdbcUtils.closeResultSet(keys);
                    }
                }
                if (logger.isDebugEnabled()) {
                    logger.debug("SQL batch update affected" + totalRowsAffected + "rows and returned" +
                            generatedKeys.size() + "keys");
                }
                return rowsAffected;
            } finally {if (pss instanceof ParameterDisposer) {((ParameterDisposer) pss).cleanupParameters();}
            }
        });
    }
}

封装完毕之后，我们的使用姿势可以为 

@Autowired
private ExtendJdbcTemplate extendJdbcTemplate;

private void batchInsertAndReturnId() {String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES (?, ?, ?);";

    GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
    extendJdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {if (i == 0) {preparedStatement.setString(1, "batch 一灰灰 7");
            } else {preparedStatement.setString(1, "batch 一灰灰 8");
            }
            preparedStatement.setInt(2, 400);
            byte b = 0;
            preparedStatement.setByte(3, b);
        }

        @Override
        public int getBatchSize() {return 2;}
    }, generatedKeyHolder);

    System.out.println("batch insert and return id");
    List<Map<String, Object>> objectMap = generatedKeyHolder.getKeyList();
    for (Map<String, Object> map : objectMap) {System.out.println(map.get("GENERATED_KEY"));
    }
}

然后测试执行，输出结果如下

2. 小结

本篇主要介绍使用 JdbcTemplate 插入数据的几种常用姿势，分别从单个插入和批量插入进行了实例演示，包括以下几种常见姿势

  • update(sql)
  • update(sql, param1, param2...)
  • update(sql, new PreparedStatementCreator(){})
  • update(new PreparedStatementSetter(){})
  • update(new PreparedStatementCreator(){}, new GeneratedKeyHolder())

批量插入姿势和上面差不多，唯一需要注意的是，如果你想使用批量插入，并获取主键 id，目前我没有找到可以直接使用的接口，如果有这方面的需求，可以参考下我上面的使用姿势

IV. 其他

  • 190407-SpringBoot 高级篇 JdbcTemplate 之数据插入使用姿势详解
  • 190412-SpringBoot 高级篇 JdbcTemplate 之数据查询上篇
  • 工程：https://github.com/liuyueyi/spring-boot-demo
  • 项目：https://github.com/liuyueyi/spring-boot-demo/blob/master/spring-boot/101-jdbctemplate

2. 声明

尽信书则不如，以上内容，纯属一家之言，因个人能力有限，难免有疏漏和错误之处，如发现 bug 或者有更好的建议，欢迎批评指正，不吝感激

  • 微博地址: 小灰灰 Blog
  • QQ：一灰灰 /3302797840

退出移动版