JDBCTemplate的简单使用

为了方便理解，本文章采用需求+案例的方法来解释JDBCTemplate的使用。

首先需要导入jar包，这是我现在使用的jar包

cowtransfer.com/s/d190e48b2… 点击链接查看 [ jdbcTemplate jar包.zip ] ，或访问奶牛快传 cowtransfer.com 输入传输口令 hrn729 查看。

需求：
  1、修改1号数据的SAL为10000
  2、添加一条记录
  3、删除刚才添加的记录
  4、查询id为1的记录，将其封装为Map集合
  5、查询所有的记录，将其封装为List集合
  6、查询所有的记录，将其封装为Emp对象的List集合
  7、查询总记录数

下为实现代码jdbcTemplateDemo2.java

package com.sun.jdbctemplate;

import com.sun.domain.Emp;
import com.sun.utils.JDBCUtils;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class jdbcTemplateDemo2 {
    //junit单元测试，可以让方法独立执行
    //1、获取JDBCTemplate对象
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    //1、修改1号数据的SAL为10000
    @Test
    public void test1(){
        //2、定义sql
        String sql = "update emp set SAL = 10000 where id = 2001";
        //3、执行sql
        int count = template.update(sql);
        System.out.println(count);
    }

    //2、添加一条记录
    @Test
    public void test2(){
        String sql = "insert into emp(ID,ENAME,DEPTNO) values(?,?,?)";
        int count = template.update(sql, 2015, "AAAA", 10);
        System.out.println(count);
    }

    //3、删除刚才添加的记录
    @Test
    public void test3(){
        String sql = "delete from emp where id = ?";
        int count = template.update(sql, 2015);
        System.out.println(count);
    }

    //4、查询id为1的记录，将其封装为Map集合
    //注意：这个方法查询的结果集的长度只能是1
    @Test
    public void test4(){
        String sql = "select * from emp where id = ?";
        Map<String, Object> map = template.queryForMap(sql, 2001);
        System.out.println(map);
        //{ID=2001, ENAME=SMITH, JOB=CLERK, AGE=30, HIREDATE=2006-07-27, SAL=10000, COMM=null, DEPTNO=20}
    }

    //5、查询所有的记录，将其封装为List集合
    @Test
    public void test5(){
        String sql = "select * from emp";
        //将每一条数据封装为Map集合，再将Map集合封装进List
        List<Map<String, Object>> list = template.queryForList(sql);
        for (Map<String, Object> stringObjectMap : list){
            System.out.println(stringObjectMap);
        }
    }

    //6、查询所有的记录，将其封装为Emp对象的List集合
    @Test
    public void test6(){
        String sql = "select * from emp";
        List<Emp> list = template.query(sql, new RowMapper<Emp>() {
            @Override
            public Emp mapRow(ResultSet rs, int i) throws SQLException {
                Emp emp = new Emp();
                int id = rs.getInt("ID");
                String ename = rs.getString("ENAME");
                String job = rs.getString("JOB");
                int age = rs.getInt("AGE");
                Date hiredate = rs.getDate("HIREDATE");
                int sal = rs.getInt("SAL");
                int comm = rs.getInt("COMM");
                int deptno = rs.getInt("DEPTNO");

                emp.setID(id);
                emp.setENAME(ename);
                emp.setJOB(job);
                emp.setAGE(age);
                emp.setHIREDATE(hiredate);
                emp.setSAL(sal);
                emp.setCOMM(comm);
                emp.setDEPTNO(deptno);

                return emp;
            }
        });

        for (Emp emp : list) {
            System.out.println(emp);
        }
    }
    //下方法也可得到test6的结果，6_2代码简介方便，但效率不如6
    @Test
    public void test6_2(){
        String sql = "select * from emp";
        List<Emp> list = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
        for (Emp emp : list) {
            System.out.println(emp);
        }
    }

    //7、查询总记录数
    @Test
    public void test7(){
        String sql = "select count(ID) from emp";
        Long total = template.queryForObject(sql, Long.class);
        System.out.println(total);
    }
}

实体类Emp.java

实体类，用于封装数据
封装Emp表数据的JavaBean

package com.sun.domain;

import java.util.Date;

//包含Emp的实体类
public class Emp {
    private Integer ID;
    private String ENAME;
    private String JOB;
    private Integer AGE;
    private Date HIREDATE;
    private Integer SAL;
    private Integer COMM;
    private Integer DEPTNO;

    //get和set方法
    public Integer getID() {
        return ID;
    }

    public void setID(Integer ID) {
        this.ID = ID;
    }

    public String getENAME() {
        return ENAME;
    }

    public void setENAME(String ENAME) {
        this.ENAME = ENAME;
    }

    public String getJOB() {
        return JOB;
    }

    public void setJOB(String JOB) {
        this.JOB = JOB;
    }

    public Integer getAGE() {
        return AGE;
    }

    public void setAGE(Integer AGE) {
        this.AGE = AGE;
    }

    public Date getHIREDATE() {
        return HIREDATE;
    }

    public void setHIREDATE(Date HIREDATE) {
        this.HIREDATE = HIREDATE;
    }

    public Integer getSAL() {
        return SAL;
    }

    public void setSAL(Integer SAL) {
        this.SAL = SAL;
    }

    public Integer getCOMM() {
        return COMM;
    }

    public void setCOMM(Integer COMM) {
        this.COMM = COMM;
    }

    public Integer getDEPTNO() {
        return DEPTNO;
    }

    public void setDEPTNO(Integer DEPTNO) {
        this.DEPTNO = DEPTNO;
    }

    //toString

    @Override
    public String toString() {
        return "Emp{" +
                "ID=" + ID +
                ", ENAME='" + ENAME + ''' +
                ", JOB='" + JOB + ''' +
                ", AGE=" + AGE +
                ", HIREDATE=" + HIREDATE +
                ", SAL=" + SAL +
                ", COMM=" + COMM +
                ", DEPTNO=" + DEPTNO +
                '}';
    }
}

以及数据库emp表