写在前面

• Spring所有内容

Spring-JdbcTemplate

基本使用

//设置数据源
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
druidDataSource.setUrl("jdbc:mysql:///dbtest2");
druidDataSource.setUsername("root");
druidDataSource.setPassword("123456789");
//创建模板
JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(druidDataSource);
//执行
int count = jdbcTemplate.update("insert into account values (null,?,?)", "huangwu", 5000);
System.out.println(count);

具体操作

• 添加

String sql = "insert into commpany values (null, ?, ?, ?, ?)";
//自动释放资源，以参数列表的形式传递sql值
int update = jdbcTemplate.update(sql, "dawsda", 40, "市场部", "hangzhou");

• 删除

String sql = "Delete from commpany where id = ?";
//自动释放资源，以参数列表的形式传递sql值
int update = jdbcTemplate.update(sql, 10);

• 查询一条记录，以map形式

String sql = "select * from commpany where id = ?";
//查询的结果集长度只能为1
Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap(sql, 1);
stringObjectMap.forEach((s, o) -> System.out.println(s + " - " + o));

• 查询多条记录，list形式

sql = "select * from commpany";
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
maps.forEach(map -> map.forEach((s, o) -> System.out.println(s + " - " + o)));

• 查询多条记录，封装成对象

List<Employee> employees = jdbcTemplate.query(sql, new RowMapper<Employee>() {
    @Override
    public Employee mapRow(ResultSet resultSet, int i) throws SQLException {
        int anInt = resultSet.getInt("id");
        String name = resultSet.getString("name");
        int age = resultSet.getInt("age");
        String dep_name = resultSet.getString("dep_name");
        String dep_location = resultSet.getString("dep_location");
        return new Employee(name, anInt, age, dep_name, dep_location);
    }
});
for (Employee employee : employees) {
    System.out.println(employee);
}

• 利用temp中的实现类，快速封装对象 new BeanPropertyRowMapper<类型>(类型.class)

List<Employee> employees = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Employee>(Employee.class));
System.out.println(employees);

• 返回一条数据，快速封装

public void testQuery2() {
    Account account = jdbcTemplate.queryForObject("select * from account where id = ?", new BeanPropertyRowMapper<Account>(Account.class), 1);
    System.out.println(account);
}

• 查询数量，返回基本数据类型的数据，不需要调用接口，设置基本数据类型的封装类的字节码即可

Long count = jdbcTemplate.queryForObject("select count('id') from account", Long.class);
System.out.println(count);
Double balance = jdbcTemplate.queryForObject("select balance from account where id = ?", Double.class, 1);
System.out.println(balance);

返回自动生成的主键id

//jdbc返回自动生成的id
PreparedStatementCreator creator = new PreparedStatementCreator() {
    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
        //用原始jdbc完成
        PreparedStatement statement = connection.prepareStatement("insert into system_user values (null, ?, ?, ?, ?)", PreparedStatement.RETURN_GENERATED_KEYS);
        statement.setString(1,user.getUsername());
        statement.setString(2,user.getEmail());
        statement.setString(3,user.getPassword());
        statement.setString(4,user.getPhoneNum());
        return statement;
    }
};
GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(creator, keyHolder);
long userId = keyHolder.getKey().longValue();