SpringBoot整合JdbcTemplate

简介

JdbcTemplate主要提供以下五种类型的方法：

• execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句
• update、batchUpdate方法：用于执行新增、修改、删除等语句
• query方法及queryForXXX方法：用于执行查询相关的语句
• call方法：用于执行数据库存储过程和函数相关的语句

示例 准备工作：

在数据库db_test下创建tb_dept

CREATE TABLE `tb_dept`  (
  `deptno` tinyint(2) UNSIGNED NOT NULL  COMMENT '部门编号',
  `dname` varchar(14) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门地址',
  PRIMARY KEY (`deptno`) USING BTREE
) ENGINE = InnoDB  CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `tb_dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `tb_dept` VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO `tb_dept` VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `tb_dept` VALUES (40, 'OPERATIONS', 'BOSTON');

对应的实体类：

/**
 * 部门
 * @author HC
 *
 */
public class Dept {
	/**
	 * 部门编号
	 */
    private Integer deptno;
    /**
     * 部门名称
     */
    private String dname;
    /**
     * 部门地址
     */
    private String loc;

    //……getter/setter、默认构造方法、全参构造方法
}

第一步：创建Maven项目，添加如下依赖

    org.springframework.boot
    spring-boot-starter-jdbc


    mysql
    mysql-connector-java
    runtime


    org.springframework.boot
    spring-boot-starter-test
    test


    junit
    junit
    4.12
    test

第二步：修改application.yml：

Spring:
  datasource:
    url: jdbc:mysql://localhost:3306/db_test?useSSL=false&serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF8&autoReconnect=true&failOverReadOnly=false
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver

第三步：编写Dao接口：

public interface DeptDao {
    int insert(Dept dept);
    int update(Dept dept);
    int delete(Integer deptno);
    Dept selectByDeptno(Integer deptno);
    List selectAll();
    List selectAllDeptnos();
}

第四步：编写Dao实现类：

@Repository
public class DeptDaoImpl implements DeptDao {
    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public int insert(Dept dept) {
        String sql = "insert into tb_dept values (?,?,?)";
        int res = jdbcTemplate.update(sql, dept.getDeptno(), dept.getDname(), dept.getLoc());
        return res;
    }

    @Override
    public int update(Dept dept) {
        String sql = "update tb_dept set dname = ?, loc = ? where deptno = ?";
        int res = jdbcTemplate.update(sql, dept.getDname(), dept.getLoc(), dept.getDeptno());
        return res;
    }

    @Override
    public int delete(Integer deptno) {
        String sql = "delete from tb_dept where deptno = ?";
        int res = jdbcTemplate.update(sql, deptno);
        return res;
    }

    @Override
    public Dept selectByDeptno(Integer deptno) {
        String sql = "select * from tb_dept where deptno = ?";
        Dept dept = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(Dept.class), deptno);
        return dept;
    }

    @Override
    public List selectAll() {
        String sql = "select * from tb_dept";
        List res = jdbcTemplate.query(sql, new BeanPropertyRowMapper(Dept.class));
        return res;
    }

    @Override
    public List selectAllDeptnos() {
        String sql = "select deptno from tb_dept";
        List res = jdbcTemplate.queryForList(sql, Integer.class);
        return res;
    }
}

第五步：提供Dao测试代码：

@SpringBootTest(classes = WegoApplication.class)
public class DeptDaoTest {

    @Resource
    private DeptDao deptDao;

    @Test
    void fun(){
        System.out.println(deptDao);
    }

    @Test
    public void insert() {
        Dept dept = new Dept(123, "123", "123");
        int res = deptDao.insert(dept);
        System.out.println(res);
    }

    @Test
    public void update() {
        Dept dept = new Dept(123, "abc", "abc");
        int res = deptDao.update(dept);
        System.out.println(res);
    }

    @Test
    public void delete() {
        int res = deptDao.delete(123);
        System.out.println(res);
    }

    @Test
    public void selectByDeptno() {
        Dept dept = deptDao.selectByDeptno(10);
        System.out.println(dept);
    }

    @Test
    public void selectAll() {
        List depts = deptDao.selectAll();
        depts.forEach(System.out::println);
    }

    @Test
    void  selectAllDeptnos(){
        List allDeptnos = deptDao.selectAllDeptnos();
        System.out.println(allDeptnos);
    }
}