JDBCTemplate执行DML语句和JDBCTemplate执行DQL语句

练习：

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

代码实现：

package cn.itcust.jdbctemplate;

import cn.itcust.Utils.DruidUtils;
import org.junit.Test;
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 {
    //获取JDBCTemplate对象
    static JdbcTemplate template = new JdbcTemplate(DruidUtils.getDs());
    //Junit单元测试,可以让方法独立执行

    /**
     * 修改1号数据的salary为10000
     */
    @Test
    public void Test01() {
        //定义sql
        String sql = "update emp set salary = 10000 where id = 1001";
        //执行sql
        int count = template.update(sql);
        if (count == 0) {
            System.out.println("失败");
        } else {
            System.out.println("成功");
        }
    }

    /**
     * 添加一条记录
     */
    @Test
    public void Test2() {
        String sql = "insert into emp(id,ename,dept_id) values(?,?,?)";
        int count = template.update(sql, 1015, "光头强", 10);
        System.out.println(count);
    }

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

    /**
     * 查询id为1的记录，将其封装为Map集合
     */
    @Test
    public void Test4() {
        String sql = "select * from emp where id = ?";
        Map<String, Object> map = template.queryForMap(sql, 1001);
        System.out.println(map);
    }


    /**
     * 查询所有记录，将其封装为List
     */
    @Test
    public void Test5() {
        String sql = "select * from emp";
        List<Map<String, Object>> list = template.queryForList(sql);
        list.forEach(stringObjectMap -> {
            System.out.println(stringObjectMap);
        });
    }

    /**
     * 查询所有记录，将其封装为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 rowNum) throws SQLException {
                Emp emp = new Emp();
                int id = rs.getInt("id");
                String ename = rs.getString("ename");
                int job_id = rs.getInt("job_id");
                int mgr = rs.getInt("mgr");
                Date joindate = rs.getDate("joindate");
                double salary = rs.getDouble("salary");
                double bonus = rs.getDouble("bonus");
                int dept_id = rs.getInt("dept_id");
                emp.id = id;
                emp.ename = ename;
                emp.job_id = job_id;
                emp.mgr = mgr;
                emp.joindate = joindate;
                emp.salary = salary;
                emp.bonus = bonus;
                emp.dept_id = dept_id;

                return emp;
            }
        });
        list.forEach(emp -> {
            System.out.println(emp);
        });

    }


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