SpringBoot整合JDBCTemplate

发布于：2020年9月8日

一、环境搭建

1. 引入依赖

// WEB
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-web</artifactId>
</dependency>

//  JDBC 
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

// 使用 Swagger 进行接口测试
<dependency>
  <groupId>io.springfox</groupId>
  <artifactId>springfox-swagger2</artifactId>
  <version>2.9.2</version>
</dependency>
<dependency>
  <groupId>io.springfox</groupId>
  <artifactId>springfox-swagger-ui</artifactId>
  <version>2.9.2</version>
</dependency>

// mysql
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <scope>runtime</scope>
</dependency>

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
// druid
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.1.23</version>
</dependency>

2.数据库脚本

DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(50) NOT NULL COMMENT '用户名',
  `nick_name` varchar(150) DEFAULT NULL COMMENT '昵称',
  `avatar` varchar(150) DEFAULT NULL COMMENT '头像',
  `password` varchar(100) DEFAULT NULL COMMENT '密码',
  `salt` varchar(40) DEFAULT NULL COMMENT '加密盐',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `mobile` varchar(100) DEFAULT NULL COMMENT '手机号',
  `status` tinyint(4) DEFAULT NULL COMMENT '状态  0：禁用   1：正常',
  `dept_id` bigint(20) DEFAULT NULL COMMENT '机构ID',
  `create_by` varchar(50) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `last_update_by` varchar(50) DEFAULT NULL COMMENT '更新人',
  `last_update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` tinyint(4) DEFAULT '0' COMMENT '是否删除  -1：已删除  0：正常',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8 COMMENT='用户管理';

application.properties:

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test
spring.datasource.username=root
spring.datasource.password=******

二、CRUD

1.编写对应的实体类

public class SysUser {
    private Long id;

    private String name;

    private String nickName;

    private String avatar;

    private String password;

    private String salt;

    private String email;

    private String mobile;

    private Byte status;

    private Long deptId;

    private String createBy;

    private Date createTime;

    private String lastUpdateBy;

    private Date lastUpdateTime;

    private Byte delFlag;
  
   // set 和 get 省略
}

2.编写对应的 CRUD Repository

@Repository
public class SysUserRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public Integer insert(SysUser user) {
        String sql = "insert into sys_user(id, name, password) values(?,?,?)";
        return jdbcTemplate.update(sql, user.getId(), user.getName(), user.getPassword());
    }

    public Integer insert1(SysUser sysUser) {
        String sql = "insert into sys_user(id, name, password) values(:id, :name, :password)";
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(this.jdbcTemplate.getDataSource());
        return namedParameterJdbcTemplate.update(sql, new BeanPropertySqlParameterSource(sysUser));
    }

   // 推荐使用这种方式，可以不用传所有的参数
    public Integer insert2(SysUser sysUser) {
        String sql = "insert into sys_user(id, name, password) values(:id, :name, :password)";
        Map<String, Object> map = new HashMap<>(16);
        map.put("id", sysUser.getId());
        map.put("name", sysUser.getName());
        map.put("password", sysUser.getPassword());
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(this.jdbcTemplate.getDataSource());
        return namedParameterJdbcTemplate.update(sql, map);
    }

    public Integer update(SysUser sysUser) {
        String sql = "update sys_user set name = ?, password = ? where id = ?";
        Object[] args = {sysUser.getName(), sysUser.getPassword(), sysUser.getId()};
        int[] argTypes = {Types.VARCHAR, Types.VARCHAR, Types.BIGINT};
        return this.jdbcTemplate.update(sql, args, argTypes);
    }

    public List<Map<String, Object>> queryAllListMap() {
        String sql = "select * from sys_user";
        return this.jdbcTemplate.queryForList(sql);
    }

   // 这里要写一个 SysUserMapper 实现 RowMapper
    public SysUser queryById(Long id) {
        String sql = "select * from sys_user where id = ?";
        Object[] args = {id};
        int[] argTypes = {Types.BIGINT};
        List<SysUser> list = this.jdbcTemplate.query(sql, args, argTypes, new SysUserMapper());
        if (StringUtils.isEmpty(list)) {
            return list.get(0);
        }
        else {
            return null;
        }
    }

    public Integer deleteById(Long id) {
        String sql = "delete from sys_user where id = ?";
        Object[] args = { id };
        int[] argTypes = { Types.BIGINT };
        return this.jdbcTemplate.update(sql, args, argTypes);
    }

}

3.对应的 Controller 层

@RestController
public class SysUserController {

    @Autowired
    private SysUserRepository sysUserRepository;

    @PutMapping("/insert")
    public Integer insert(@RequestBody SysUser sysUser) {
        int i = sysUserRepository.insert(sysUser);
        return i;
    }

    @PutMapping("/insert1")
    public Integer insert1(@RequestBody SysUser sysUser) {
        int i = sysUserRepository.insert1(sysUser);
        return i;
    }

    @PutMapping("/insert2")
    public Integer insert2(@RequestBody SysUser sysUser) {
        int i = sysUserRepository.insert2(sysUser);
        return i;
    }

    @PostMapping("/update")
    public Integer update(@RequestBody SysUser sysUser) {
        int i = sysUserRepository.update(sysUser);
        return i;
    }

    @GetMapping("/list")
    public List<Map<String, Object>> queryAllListMap() {
        return sysUserRepository.queryAllListMap();
    }

    @GetMapping("/id1")
    public SysUser queryById(Long id) {
        return sysUserRepository.queryById(id);
    }

    @DeleteMapping("/id3")
    public Integer deleteById(Long id) {
        return sysUserRepository.deleteById(id);
    }
}