峰Spring4学习（7）spring对JDBC的支持

第一节：

工程结构：

1）student.java:

package com.cy.model;

public class Student {
    private int id;
    private String name;
    private int age;

    public Student() {
        super();
        // TODO Auto-generated constructor stub
    }

    public Student(String name, int age) {
        super();
        this.name = name;
        this.age = age;
    }

    public Student(int id, String name, int age) {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
    }

}

2)StudentDao.java/StudentDaoImpl.java:

package com.cy.dao;

import java.util.List;

import com.cy.model.Student;

public interface StudentDao {
    public int addStudent(Student student);

    public int updateStudent(Student student);

    public int deleteStudent(int id);

    public List<Student> findStudents();
}

package com.cy.dao.impl;

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

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.cy.dao.StudentDao;
import com.cy.model.Student;

public class StudentDaoImpl implements StudentDao {

    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public int addStudent(Student student) {
        String sql = "insert into t_student values (null, ?, ?)";
        Object params[] = new Object[]{student.getName(), student.getAge()};
        return jdbcTemplate.update(sql, params);
    }

    @Override
    public int updateStudent(Student student) {
        String sql = "update t_student set name=?, age=? where id = ?";
        Object params[] = new Object[]{student.getName(), student.getAge(), student.getId()};
        return jdbcTemplate.update(sql, params);
    }

    @Override
    public int deleteStudent(int id) {
        String sql = "delete from t_student where id = ?";
        Object params[] = new Object[]{id};
        return jdbcTemplate.update(sql, params);
    }

    /**
     * 查询所有的学生
     */
    @Override
    public List<Student> findStudents() {
        String sql = "select * from t_student";
        final List<Student> studentList = new ArrayList<Student>();
        jdbcTemplate.query(sql, new RowCallbackHandler(){

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                studentList.add(student);
            }

        });
        return studentList;
    }

}

3)StudentService.java/StudentServiceImpl.java:

package com.cy.service;

import java.util.List;

import com.cy.model.Student;

public interface StudentService {

    public int addStudent(Student student);

    public int updateStudent(Student student);

    public int deleteStudent(int id);

    public List<Student> findStudents();
}

package com.cy.service.impl;

import java.util.List;

import com.cy.dao.StudentDao;
import com.cy.model.Student;
import com.cy.service.StudentService;

public class StudentServiceImpl implements StudentService{

    private StudentDao studentDao;

    public void setStudentDao(StudentDao studentDao) {
        this.studentDao = studentDao;
    }

    @Override
    public int addStudent(Student student) {
        return studentDao.addStudent(student);
    }

    @Override
    public int updateStudent(Student student) {
        return studentDao.updateStudent(student);
    }

    @Override
    public int deleteStudent(int id) {
        return studentDao.deleteStudent(id);
    }

    @Override
    public List<Student> findStudents() {
        return studentDao.findStudents();
    }

}

4)beans.xml配置文件：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <context:property-placeholder location="jdbc.properties"/>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <bean id="studentDao" class="com.cy.dao.impl.StudentDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean> 

    <bean id="studentService" class="com.cy.service.impl.StudentServiceImpl">
        <property name="studentDao" ref="studentDao"></property>
    </bean> 

</beans>

5）jdbc.properties：

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/db_spring
jdbc.username=root
jdbc.password=root

6)测试代码：

package com.cy.test;

import java.util.List;

import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.cy.model.Student;
import com.cy.service.StudentService;

public class T {
    private ApplicationContext ac;

    @Before
    public void setUp() throws Exception {
        ac=new ClassPathXmlApplicationContext("beans.xml");
    }

    @Test
    public void addStudent() {
        StudentService studentService=(StudentService)ac.getBean("studentService");
        int addNums=studentService.addStudent(new Student("晶", 1));
        if(addNums==1){
            System.out.println("添加成功");
        }
    }

    @Test
    public void updateStudent() {
        StudentService studentService=(StudentService)ac.getBean("studentService");
        int updateNums=studentService.updateStudent(new Student(4, "杨静", 10));
        if(updateNums==1){
            System.out.println("修改成功");
        }
    }

    @Test
    public void deleteStudent() {
        StudentService studentService=(StudentService)ac.getBean("studentService");
        int deleteNums = studentService.deleteStudent(4);
        if(deleteNums==1){
            System.out.println("删除成功！");
        }
    }

    @Test
    public void findStudents() {
        StudentService studentService=(StudentService)ac.getBean("studentService");
        List<Student> studentList = studentService.findStudents();
        for(Student student : studentList){
            System.out.println(student);
        }
    }
}

查询所有学生findStudents方法console打印：

第二节：

1.通过继承JdbcDaoSupport，我们原来的JdbcTemplate就不用写了：

根据上面项目的代码，做些修改：

1)StudentDaoImpl.java:

package com.cy.dao.impl;

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

import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.cy.dao.StudentDao;
import com.cy.model.Student;

public class StudentDaoImpl extends JdbcDaoSupport implements StudentDao {

    @Override
    public int addStudent(Student student) {
        String sql = "insert into t_student values (null, ?, ?)";
        Object params[] = new Object[]{student.getName(), student.getAge()};
        return this.getJdbcTemplate().update(sql, params);
    }

    @Override
    public int updateStudent(Student student) {
        String sql = "update t_student set name=?, age=? where id = ?";
        Object params[] = new Object[]{student.getName(), student.getAge(), student.getId()};
        return this.getJdbcTemplate().update(sql, params);
    }

    @Override
    public int deleteStudent(int id) {
        String sql = "delete from t_student where id = ?";
        Object params[] = new Object[]{id};
        return this.getJdbcTemplate().update(sql, params);
    }

    /**
     * 查询所有的学生
     */
    @Override
    public List<Student> findStudents() {
        String sql = "select * from t_student";
        final List<Student> studentList = new ArrayList<Student>();
        this.getJdbcTemplate().query(sql, new RowCallbackHandler(){

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                studentList.add(student);
            }

        });
        return studentList;
    }

}

2)beans.xml配置：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <context:property-placeholder location="jdbc.properties"/>

    <bean id="studentDao" class="com.cy.dao.impl.StudentDaoImpl">
        <property name="dataSource" ref="dataSource"></property>
    </bean> 

    <bean id="studentService" class="com.cy.service.impl.StudentServiceImpl">
        <property name="studentDao" ref="studentDao"></property>
    </bean> 

</beans>

测试代码不变，执行都ok；

2.NamedParameterJdbcTemplate 的使用：

1）StudentDaoImpl.java:

package com.cy.dao.impl;

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

import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.cy.dao.StudentDao;
import com.cy.model.Student;

public class StudentDaoImpl implements StudentDao {

    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public void setNamedParameterJdbcTemplate(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }

    @Override
    public int addStudent(Student student) {
        String sql = "insert into t_student values (null, :name, :age)";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("name", student.getName())
            .addValue("age", student.getAge());
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    @Override
    public int updateStudent(Student student) {
        String sql = "update t_student set name=:name, age=:age where id =:id";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("name", student.getName())
            .addValue("age", student.getAge())
            .addValue("id", student.getId());
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    @Override
    public int deleteStudent(int id) {
        String sql = "delete from t_student where id = :id";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id", id);
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    /**
     * 查询所有的学生
     */
    @Override
    public List<Student> findStudents() {
        String sql = "select * from t_student";
        final List<Student> studentList = new ArrayList<Student>();
        namedParameterJdbcTemplate.query(sql, new RowCallbackHandler(){

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                studentList.add(student);
            }

        });
        return studentList;
    }

}

2)beans.xml配置：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <context:property-placeholder location="jdbc.properties"/>

    <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg ref="dataSource"></constructor-arg>
    </bean>

    <bean id="studentDao" class="com.cy.dao.impl.StudentDaoImpl">
        <property name="namedParameterJdbcTemplate" ref="namedParameterJdbcTemplate"></property>
    </bean> 

    <bean id="studentService" class="com.cy.service.impl.StudentServiceImpl">
        <property name="studentDao" ref="studentDao"></property>
    </bean> 

</beans>

测试代码不变，测试ok

----------------------------------