Java Spring JDBC详解

第四章、Spring JDBC

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

步骤：

• 导入jar包

• 创建JdbcTemplate对象。依赖于数据源DataSource

• 调用JdbcTemplate的方法来完成CRUD的操作

  代码实现

<dependencies>
        <!--jdbcTemplate-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>4.1.2.RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.1.2.RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>4.1.2.RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>4.1.2.RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.1.1</version>
            <scope>compile</scope>
        </dependency>
    </dependencies>

1. update():执行DML语句。增、删、改语句

//获取JdbcTemplate对象
    private JdbcTemplate template =  new JdbcTemplate(JDBCutils.getDataSource());
    //1.1添加一条记录
    //无参方法
    public void add(){
        String sql = "insert student3 value(?,?,?,?,?,?,?)";
        int count = template.update(sql, 10, "黄药师", 20, "男", "桃花岛", 66, 99);
    }
    
//有参方法
public void add2(Student student){
    String sql = "insert student3 value(?,?,?,?,?,?,?)";
    int count = template.update(sql,student.getId(),
    student.getName(),student.getAge(),   
    student.getSex(),student.getAddress(),
    student.getMath(),student.getEnglish());
}

//1.2删除一条记录
public void delect(){
    String sql = "DELETE from student3 where id = ?";
    int count = template.update(sql, 10);
}

//1.3修改记录
//无参方法
public void update1(){
    String sql = "update student3 set math = 99 where id = 10";
    int count = template.update(sql);
}

//有参方法
public void update2(Student student){
    String sql = "update student3 set math = 99 where id = ？";
    int count = template.update(sql,emp.getId());
}

2. queryForMap():查询结果将结果集封装为map集合，将列名作为key，将值作为value 将这条记录封装为一个map集合,这个方法查询的结果集长度只能是1。

//2.查询单条记录，将其封装为map集合；map集合的结果集长度只能为1
  public void selectMap(){
      String sql = "select * from student3 where id = ?";
      Map<String, Object> map = template.queryForMap(sql, 10);
      System.out.println(map);
  }

3. queryForList():查询结果将结果集封装为list集合,将每一条记录封装为一个Map集合，再将Map集合装载到List集合中。

 //3.查询所有记录，将其封装为list集合
    public void  selectList(){
        String sql = "select * from student3";
        List<Map<String, Object>> list = template.queryForList(sql);
        System.out.println(list);
    }

4. query():查询结果，将结果封装为JavaBean对象，一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装。

//4.封装为JavaBean对象的list集合
   public void selectJavaBean(){
        String sql = "select * from student3";
        //指定泛型
     		//new BeanPropertyRowMapper<类型>(类型.class)
        List<Student> list = template.query(sql, new BeanPropertyRowMapper<Student>(Student.class));
        //增强for循环遍历集合
        for (Student student : list) {
            System.out.println(student);
        }
    }

5. 查询指定对象，返回对象全部信息

		/*

   		查询指定对象，返回对象全部信息
     	@param student 查询时只有学生的姓名和性别
    	@return student 返回查询到学生的所有结果，没有查询到返回null值
   */
    public Student selectStudent(Student(student){
      	try {
              String sql = "select * from student3 where name = ? and sex = ?";
              Student student1 = template.queryForObject(sql,
          		new BeanPropertyRowMapper<Student>(Student.class),
          		student .getName(), student .getSex());
              		return student1;
          	} catch (DataAccessException e) {
              		e.printStackTrace();	
              		return null;
          	}
    }

  @Test
  //单元测试类，可单独运行；
    public void test(){
        Student student = new Student ();
        student .setName("黄药师");
        student .setSex("男");
				Demo1DaoImpl demo1Dao = new Demo1DaoImpl();
    		Emp emp1 = demo1Dao.selectStudent(student);
    		System.out.println(student1);
		}

6. queryForObject：查询结果，将结果封装为对象，一般用于聚合函数的查询。

//5.查询总记录数
    public void count(){
        String sql = "select count(id) from student3";
        //指定泛型
        Long total = template.queryForObject(sql, Long.class);
        System.out.println(total);
    }

四、附录

maven中央仓库网址
封装student3表的JavaBean（实体类），基本数据类不能接收null，所以变量得定义为引用数据类型。

代码展示

//基本数据类不能接收null，所以定义为引用数据类型
//封装student3表的JavaBean
public class Student {
    private Integer id;
    private String name;
    private Integer age;
    private String sex;
    private String address;
    private Integer math;
    private Integer english;
		public Integer getId() {return id;}
		public void setId(Integer id) {this.id = id;}
		public String getName() {return name;}
		public void setName(String name) {this.name = name;}
		public Integer getAge() {return age;}
		public void setAge(Integer age) {this.age = age;}
		public String getSex() {return sex;}
		public void setSex(String sex) {this.sex = sex;}
		public String getAddress() {return address;}
		public void setAddress(String address) {this.address = address;}
		public Integer getMath() {return math;}
		public void setMath(Integer math) {this.math = math;}
		public Integer getEnglish() {return english;}
		public void setEnglish(Integer english) {this.english = english;}

		@Override
		public String toString() {
    		return "Emp{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", age=" + age +
            ", sex='" + sex + '\'' +
            ", address='" + address + '\'' +
            ", math=" + math +
            ", english=" + english +
         '}';
    }
}