Java中JdbcTemplate基本使用

1、增删改

// 添加
String sql_add = "insert into dmp_user values (?,?,?,?)" ;
jdbcTemplate.update(sql_add,3,"张三","13802541234","男") ;
jdbcTemplate.update(sql_add,4,"李四","13002541234","女") ;
// 修改
String sql_update = "update dmp_user set name = 王五 where id = 3" ;
int count0 = jdbcTemplate.update(sql_update) ;
System.out.println("修改的条数：" + count0);
// 删除
String sql_del = "delete from dmp_user where id in (3,4)" ;
int count1 = jdbcTemplate.update(sql_del) ;
System.out.println("修改的条数：" + count1);

日志：

修改的条数：1
修改的条数：2

2、查询

// 查询int整数
String sql_int = "select count(1) count from dmp_user" ;
int count = jdbcTemplate.queryForObject(sql_int,Integer.class) ;
System.out.println("查询int整数：" + count);

// 查询long整数
String sql_long = "select count(1) count from dmp_user" ;
Long count_long = jdbcTemplate.queryForObject(sql_long,Long.class) ;
System.out.println("查询long整数：" + count_long);

// 查询String
String sql_string = "select name from dmp_user where id = 1" ;
String str = jdbcTemplate.queryForObject(sql_string,String.class) ;
System.out.println("查询String：" + str);

// 查询map集合
String sql_map = "select * from dmp_user where id = 1" ;
Map<String,Object> map = jdbcTemplate.queryForMap(sql_map) ;
System.out.println("查询map集合:" );
System.out.println(map);

// 查询List集合
String sql_list = "select * from dmp_user" ;
List<Map<String,Object>> list = jdbcTemplate.queryForList(sql_list) ;
System.out.println("查询List集合:" );
System.out.println(list);

// 查询自定义对象1（RowMapper）
String sql = "select * from dmp_user" ;
List<DmpUser> admins = jdbcTemplate.query(sql, new RowMapper<DmpUser>() {
          
   
    @Override
    public DmpUser mapRow(ResultSet resultSet, int i) throws SQLException {
          
   
        DmpUser user = new DmpUser() ;
        user.setId(resultSet.getInt("id")) ;
        user.setName(resultSet.getString("name")) ;
        user.setPhone(resultSet.getString("phone")) ;
        user.setSex(resultSet.getString("sex")) ;
        return user;
    }
}) ;
System.out.println("查询自定义对象1（RowMapper）:" );
System.out.println(admins);

// 查询自定义对象1（BeanPropertyRowMapper）
String sql_b = "select * from dmp_user" ;
List<DmpUser> users = jdbcTemplate.query(sql_b,new BeanPropertyRowMapper<>(DmpUser.class)) ;
System.out.println("查询自定义对象1（BeanPropertyRowMapper）:" );
System.out.println(users);

日志：

查询int整数：4
查询long整数：4
查询String：许七安
查询map集合:
{
          
   id=1, name=许七安, phone=13809378502, sex=男}
查询List集合:
[{
          
   id=1, name=许七安, phone=13809378502, sex=男}, {
          
   id=2, name=店小二, phone=15302549899, sex=男}, {
          
   id=3, name=张三, phone=13802541234, sex=男}, {
          
   id=4, name=李四, phone=13002541234, sex=女}]
查询自定义对象1（RowMapper）:
[DmpUser(id=1, name=许七安, phone=13809378502, sex=男), DmpUser(id=2, name=店小二, phone=15302549899, sex=男), DmpUser(id=3, name=张三, phone=13802541234, sex=男), DmpUser(id=4, name=李四, phone=13002541234, sex=女)]
查询自定义对象1（BeanPropertyRowMapper）:
[DmpUser(id=1, name=许七安, phone=13809378502, sex=男), DmpUser(id=2, name=店小二, phone=15302549899, sex=男), DmpUser(id=3, name=张三, phone=13802541234, sex=男), DmpUser(id=4, name=李四, phone=13002541234, sex=女)]

dmp_user脚本

CREATE TABLE `dmp_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) 

INSERT INTO `dmp_user` VALUES (1, 许七安, 13809378502, 男);
INSERT INTO `dmp_user` VALUES (2, 店小二, 15302549899, 男);
INSERT INTO `dmp_user` VALUES (3, 张三, 13802541234, 男);
INSERT INTO `dmp_user` VALUES (4, 李四, 13002541234, 女);