1.数据插入-Insert

一灰灰blog约 2029 字大约 7 分钟

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

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，目前我没有找到可以直接使用的接口，如果有这方面的需求，可以参考下我上面的使用姿势

1.数据插入-Insert

I. 环境

1. 配置相关

2. 测试db

II. 使用姿势

1. sql直接插入一条数据

2. 参数替换方式插入

3. 通过Statement方式插入

4. 插入并返回主键id

5. 批量插入

6. 测试

II. 扩展

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

2. 小结

IV. 其他

0. 项目

1.数据插入-Insert

# I. 环境

# 1. 配置相关

# 2. 测试db

# II. 使用姿势

# 1. sql直接插入一条数据

# 2. 参数替换方式插入

# 3. 通过Statement方式插入

# 4. 插入并返回主键id

# 5. 批量插入

# 6. 测试

# II. 扩展

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

# 2. 小结

# IV. 其他

# 0. 项目

I. 环境

1. 配置相关

2. 测试db

II. 使用姿势

1. sql直接插入一条数据

2. 参数替换方式插入

3. 通过Statement方式插入

4. 插入并返回主键id

5. 批量插入

6. 测试

II. 扩展

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

2. 小结

IV. 其他

0. 项目