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(); }

@Repository public 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(); } @Service public 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("--------------------"); } } } 运行结果：

lisin 19 -------------------- wangwu 21 --------------------