Spring JDBC

前言

记录一下Spring框架的JDBC的用法，方便以后查看

一、JDBC代码实例

1、修改1号数据的 salary 为 10000

int count = template.update("update emp set salary = 10000 where id = 1001");
System.out.println(count);

2、添加一条记录

int count = template.update("insert into emp values (1022,'wusong',4,1004,'2021-7-28',10000,10000,20)");
System.out.println(count);

3、删除刚才添加的记录

int count = template.update("delete from emp where id = 1021");
System.out.println(count);

4、查询id为1的记录，将其封装为Map集合（在这里SQL语句使用的是prepareStatement方式）

String sql = "select * from emp where id = ?";
Map<String, Object> stringObjectMap = template.queryForMap(sql,1001);
System.out.println(stringObjectMap);

5、查询所有记录，将其封装为List

List<Map<String, Object>> maps = template.queryForList("select * from emp");
System.out.println(maps);

6、查询所有记录，将其封装为Emp对象的List集合

List<Emp> query = template.query("select * from emp", new BeanPropertyRowMapper<Emp>(Emp.class));
System.out.println(query);

7、查询总记录数

Map<String, Object> m = template.queryForMap("select count(id) from emp");
System.out.println(m);

8、自定义方法（与第六种一起看，只不过是重写了RowMapper方法）

String sql= "select * from emp";
List<Emp> query = template.query(sql, new RowMapper<Emp>() {
    @Override
    public Emp mapRow(ResultSet rs, int i) throws SQLException {
        Emp emp = new Emp();
        int id = rs.getInt(1);
        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.setId(id);
        emp.setEname(ename);
        emp.setJobId(job_id);
        emp.setMgr(mgr);
        emp.setJoindate(joindate);
        emp.setSalary(salary);
        emp.setBonus(bonus);
        emp.setDeptId(dept_id);
        return emp;
    }
});
System.out.println(query);

总结

基本涵盖了所有的基本用法，供自己以后参考。