SpringJDBC详解

Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象简化JDBC的开发

步骤：

• 1.导入jar包
  2.创建JdbcTemplate对象。依赖于数据源Datasource
  JdbcTemplate template = new JdbcTemplate(ds);
  3.调用JdbcTemplate的方法来完成CRUD的操作
  *update():执行DML语句。增、删、改语句
  *queryForMap()：查询结果将结果集封装为map集合,将列名作为key，将值作为value，将这条纪录封装为一个map集合
  *注意：这个方法的查询结果集长度只能是1
  *queryForList():查询结果将结果集封装为list集合，
  *注意：将每一条记录封装为一个map集合，再将map集合装载到list集合中
  *query():查询结果，将结果封装为JavaBean对象
  *query的参数:RowMapper
  *一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装
  *new BeanPropertyRowMapper<类型>(类型.class)
  *queryForObject():查询结果，将结果封装为对象
  *一般用于聚合函数的查询

• 4.练习：

   *需求：
   	1.修改1号数据password为456
   	2.添加一条数据
   	3.删除刚才添加的数据
   	4.查询aid为1的纪录，将其封装为map集合
   	5.查询所有记录，将其封装为List
   	6.查询所有记录，将其封装为Emp对象的List集合
   	7.查询总记录数
  

package Bean;

public class Admin {
    private int aid;
    private String username;
    private String password;

    public int getAid() {
        return aid;
    }

    public void setAid(int aid) {
        this.aid = aid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "admin{" +
                "aid=" + aid +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

package JDBCTemplate;

import Bean.Admin;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import util.JDBCDruidUtils;

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

public class AdminJdbcTemplateTest {
    /*      1.修改1号数据password为456
        	2.添加一条数据
        	3.删除刚才添加的数据
        	4.查询aid为1的纪录，将其封装为map集合
        	5.查询所有记录，将其封装为List
        	6.查询所有记录，将其封装为Admin对象的List集合
        	7.查询总记录数*/
    public static void main(String[] args) {
        AdminJdbcTemplateTest ajt = new AdminJdbcTemplateTest();
        ajt.test1();
        ajt.test2();
        ajt.test3();
        ajt.test4();
        ajt.test5();
        ajt.test6();
        ajt.test6_2();
        ajt.test7();
    }
    private JdbcTemplate template = new JdbcTemplate(JDBCDruidUtils.getDataSource());
    public void test1(){
        String sql = "update admin set password = 456 where aid = ?";
        int count = template.update(sql,1);
        System.out.println("test1:");
        System.out.println(count);
    }
    public void test2(){
        String sql = "insert into admin values(?,?,?)";
        int count = template.update(sql,5,"chenqi","123");
        System.out.println("test2:");
        System.out.println(count);
    }
    public void test3(){
        String sql = "delete from admin where aid = ? ";
        int count = template.update(sql,5);
        System.out.println("test3:");
        System.out.println(count);
    }
    public void test4(){
        String sql = "select * from admin where aid = ? ";
        Map<String,Object> map = template.queryForMap(sql,1);
        System.out.println("test4:");
        System.out.println(map);
    }
    public void test5(){
        String sql = "select * from admin";
        List<Map<String, Object>> list = template.queryForList(sql);
        System.out.println("test5:");
        for (Map<String, Object> map : list) {
            System.out.println(map);
        }
    }
    public void test6(){
        String sql = "select * from admin";
        List<Admin> list = template.query(sql, new RowMapper<Admin>() {
            @Override
            public Admin mapRow(ResultSet resultSet, int i) throws SQLException {
                Admin admin = new Admin();
                int aid = resultSet.getInt("aid");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                admin.setAid(aid);
                admin.setUsername(username);
                admin.setPassword(password);
                return admin;
            }
        });
        System.out.println("test6:");
        for (Admin admin : list) {
            System.out.println(list);
        }
    }
    public void test6_2(){
        String sql = "select * from admin";
        List<Admin> list = template.query(sql, new BeanPropertyRowMapper<Admin>(Admin.class));
        System.out.println("test6_2:");
        for (Admin admin : list) {
            System.out.println(list);
        }
    }
    public void test7(){
        String sql = "select count(aid) from admin ";
        Long total = template.queryForObject(sql, long.class);
        System.out.println("test7:");
        System.out.println(total);
    }

}