Spring in Action（八）：Spring中使用JDBC

感觉项目中使用注解的情况比较多，因此本文并没有按照书中的内容实现。

1、加入所需JDBC和MYSQL所需依赖

    <dependencies>        <!--使用单元测试所需jar包-->        <dependency>            <groupId>junit</groupId>            <artifactId>junit</artifactId>            <version>4.12</version>            <scope>test</scope>        </dependency>        <dependency>            <groupId>org.springframework</groupId>            <artifactId>spring-test</artifactId>            <version>4.3.8.RELEASE</version>        </dependency>        <!--spring相关包-->        <dependency>            <groupId>org.springframework</groupId>            <artifactId>spring-web</artifactId>            <version>4.3.8.RELEASE</version>        </dependency>        <dependency>            <groupId>org.springframework</groupId>            <artifactId>spring-webmvc</artifactId>            <version>4.3.8.RELEASE</version>        </dependency>        <!--servlet-api-->        <dependency>            <groupId>javax.servlet</groupId>            <artifactId>javax.servlet-api</artifactId>            <version>3.1.0</version>        </dependency>        <!--jdbc-->        <dependency>            <groupId>org.springframework</groupId>            <artifactId>spring-jdbc</artifactId>            <version>4.3.8.RELEASE</version>        </dependency>        <!--mysql-->        <dependency>            <groupId>mysql</groupId>            <artifactId>mysql-connector-java</artifactId>            <version>6.0.5</version>        </dependency>    </dependencies>

2、Spring配置文件中配置数据源

<?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:context="http://www.springframework.org/schema/context"       xmlns:mvc="http://www.springframework.org/schema/mvc"       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd       http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd">    <!--自动扫描-->    <context:component-scan base-package="com.springmvc.*"></context:component-scan>    <mvc:annotation-driven></mvc:annotation-driven>    <!--配置数据源-->    <bean id="datasource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>        <property name="url" value="jdbc:mysql://localhost:3306/db_test"></property>        <property name="username" value="root"></property>        <property name="password" value="root"></property>    </bean></beans>

3、数据库中的数据，数据库名为db_test，表名为Student

4、创建Student bean

package com.springmvc.model;public class Student {    private int id;    private String name;    private int 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;    }}

5、DAO层：StudentDAO和StudentDAOImpl，里面包含一个获取所有Student的方法

public interface StudentDAO {    /**     * 获取所有的学生     * @return     */    public List  <Student> getAllStudent();}

@Repositorypublic class StudentDAOImpl implements StudentDAO {    @Autowired    private DataSource dataSource;//数据源    public List<Student> getAllStudent() {        Connection connection = null;        PreparedStatement pstmt = null;        List<Student> studentList = new ArrayList<Student>();        try {            connection = dataSource.getConnection();//获取数据库连接            pstmt = connection.prepareStatement("SELECT * FROM STUDENT");            ResultSet rs = pstmt.executeQuery();//执行查询            while (rs.next()) {                Student student = new Student();                student.setName(rs.getString("name"));                student.setAge(rs.getInt("age"));                studentList.add(student);            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            //关闭连接            try {                if (pstmt != null) {                    pstmt.close();                }                if(connection!=null){                    connection.close();                }            } catch (SQLException e) {                e.printStackTrace();            }        }        return studentList;    }}

6、Service层：StudentService和StudentServiceImpl

public interface StudentService {    public List<Student> getAllStudent();}

@Servicepublic class StudentServiceImpl implements StudentService{    @Autowired    private StudentDAO studentDAO;    public List<Student> getAllStudent() {        return studentDAO.getAllStudent();    }}

7、测试

package com.springmvc.jdbc;import com.springmvc.model.Student;import com.springmvc.service.StudentService;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.test.context.ContextConfiguration;import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;import java.util.List;@RunWith(SpringJUnit4ClassRunner.class)//自动创建Spring的应用上下文@ContextConfiguration(locations = { "classpath*:/springmvc-servlet.xml" })public class StudentTest {    @Autowired    private StudentService studentService;    @Test    public void getAllStudentTest(){        List<Student> studentList=studentService.getAllStudent();        for(Student student:studentList){            System.out.println(student.getName());            System.out.println(student.getAge());            System.out.println("--------------------");        }    }}

运行结果：

lisin19--------------------wangwu21--------------------