Java——数据库编程JDBC之JDBCTemplate的使用简化数据库操作步骤（含增删改查练习实例源码）

public class JdbcTemplateDemo1 {
    public static void main(String[] args) {
        //1、导入jar包
        //2、创建JDBCTemplate对象
        JdbcTemplate template = new JdbcTemplate(JdbcUtils.getDataSource());
        //3、调用方法
        String sql = "update account set balance = 5000 where id = ?";
        int count = template.update(sql, 1);
        System.out.println(count); //返回值为1
    }
}

public class JdbcTemplateDemo2 {
    //Juint单元测试，让方法独立执行，不依赖于主方法
    //1、获取JdbcTemplate对象
    private JdbcTemplate template = new JdbcTemplate(JdbcUtils.getDataSource());

//    1）修改id=1的salary为888；
    @Test
    public void test1(){
        String sql = "update emp set salary = 888 where id = 1";
        int count = template.update(sql);
        System.out.println(count);
    }
//    2）添加一条记录；
    @Test
    public void test2(){
        String sql = "insert into emp(id,name,gender,salary,join_date,dept_id) values(6,'张三','男',100,'2020-01-01',1) ";
        int count = template.update(sql);
        System.out.println(count);
    }
//    3）删除刚才添加的记录；
    @Test
    public void test3(){
        String sql = "delete from emp where id = ?";
        int count = template.update(sql,6);
        System.out.println(count);
    }
//    4）查询id=1的记录，将结果封装为Map集合；
    @Test
    public void test4(){
        String sql = "select * from emp where id = ?";
        Map<String, Object> map = template.queryForMap(sql, 1);
        System.out.println(map);
        //结果：{id=1, NAME=孙悟空, gender=男, salary=888.0, join_date=2013-02-24, dept_id=1}
    }

//    5）查询所有记录，将其封装为List集合；
    @Test
    public void test5(){
        String sql = "select * from emp";
        List<Map<String, Object>> list = template.queryForList(sql);
        for (Map<String, Object> stringObjectMap : list) {
            System.out.println(stringObjectMap);
        }
/*        {id=1, NAME=孙悟空, gender=男, salary=888.0, join_date=2013-02-24, dept_id=1}
        {id=2, NAME=猪八戒, gender=男, salary=3600.0, join_date=2010-12-02, dept_id=2}
        {id=3, NAME=唐僧, gender=男, salary=9000.0, join_date=2008-08-08, dept_id=2}
        {id=4, NAME=白骨精, gender=女, salary=5000.0, join_date=2015-10-07, dept_id=3}
        {id=5, NAME=蜘蛛精, gender=女, salary=4500.0, join_date=2011-03-14, dept_id=1}*/
    }
//    6）查询所有记录，将其封装为Emp对象的List集合；
    @Test
    public void test6() {
        String sql = "select * from emp";
        List<Emp> empList = 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 name = rs.getString("name");
                String gender = rs.getString("gender");
                double salary = rs.getDouble("salary");
                Date join_date = rs.getDate("join_date");
                int dept_id = rs.getInt("dept_id");
                emp.setId(id);
                emp.setName(name);
                emp.setGender(gender);
                emp.setSalary(salary);
                emp.setJoin_date(join_date);
                emp.setDept_id(dept_id);
                return emp;
            }
        });
        for (Emp emp : empList) {
            System.out.println(emp);
        }
    }
/*    Emp{id=1, name='孙悟空', gender='男', salary=888.0, joindate=2013-02-24, dept_id=1}
    Emp{id=2, name='猪八戒', gender='男', salary=3600.0, joindate=2010-12-02, dept_id=2}
    Emp{id=3, name='唐僧', gender='男', salary=9000.0, joindate=2008-08-08, dept_id=2}
    Emp{id=4, name='白骨精', gender='女', salary=5000.0, joindate=2015-10-07, dept_id=3}
    Emp{id=5, name='蜘蛛精', gender='女', salary=4500.0, joindate=2011-03-14, dept_id=1}*/

    //    6）查询所有记录，将其封装为Emp对象的List集合；简化的形式
    @Test
    public void test6_2() {
        String sql = "select * from emp";
        List<Emp> empList = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
        for (Emp emp : empList) {
            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);  //5
    }

}