对比传统方式访问数据库和SpringData访问数据库

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

我们在写代码的时候应该一边写一边测试，这样的话可以尽快的找到错误，在代码写多了之后去找错误的话不容易给错误定位

传统方式访问数据库

1：创建一个Maven web项目

2：修改pom.xml为以下内容

3：创建一个数据库表User，包括id，name，age三个字段，并向其中添加数据

4：创建一个配置文件db.properties，用于存放所有和数据库有关的配置

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3307/test
jdbc.username=root
jdbc.password=root

5：开发一个JDBC相关工具类com.gx.help.JdbcUtil

package com.gx.help;import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;/**
* 这是一个JDBC工具类
* @author Administrator
*
*包含的内容
*1：获取Connection
*2：释放资源（关闭Connection，Statement（PrepareStatement），ResultSet）
*/
public class JdbcUtil {
/**
* 获取Connection
* @return 返回所获得的JDBC的Connection
* @throws SQLException
* @throws ClassNotFoundException
* @throws IOException
*/
public static Connection getConnection() throws SQLException, ClassNotFoundException, IOException{
//从配置文件获取数据
InputStream inputStream = JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
//驱动程序名
String driver=properties.getProperty("jdbc.driver");
//url指向要访问的数据库名
String url=properties.getProperty("jdbc.url");
//MySQL配置时的用户名和密码
String username=properties.getProperty("jdbc.username");
String password=properties.getProperty("jdbc.password");
Class.forName(driver);
Connection conn = DriverManager.getConnection(url,username,password);
return conn;
}
/**
* 释放资源（使用Statement的情况下）
* @param resultSet
* @param statement
* @param connection
*/
public static void close(ResultSet resultSet,Statement statement,Connection connection){
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(statement!=null){
try {
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 释放资源（使用PrepareStatement的情况下）
* @param resultSet
* @param preparedStatement
* @param connection
*/
public static void close(ResultSet resultSet,PreparedStatement preparedStatement,Connection connection){
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(preparedStatement!=null){
try {
preparedStatement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}

5：创建对象模型com.gx.domain.User

package com.gx.domain;public class User {
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;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
}}

6：创建Dao的接口com.gx.dao.UserDao

package com.gx.dao;import java.util.List;import com.gx.domain.User;
/**
* UserDao访问接口
* @author Administrator
*
*/
public interface UserDao {
//查询所有用户
public List<User> finaAll();
}

7：创建Dao的实现类com.gx.dao.impl.UserDaoImpl

package com.gx.dao.impl;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;import com.gx.dao.UserDao;
import com.gx.domain.User;
import com.gx.help.JdbcUtil;
/**
* UserDao访问接口实现类，这个实现类通过最原始的JDBC的操作实现
* @author Administrator
*
*/
public class UserDaoImpl implements UserDao{
/**
* 查询所有学生
*/
public List<User> finaAll(){
List<User> users = new ArrayList<User>();
Connection conn=null;
PreparedStatement preparedStatement = null;
ResultSet result = null;
try {
String sql = "select * from user";
conn = JdbcUtil.getConnection();
preparedStatement = conn.prepareStatement(sql);
result = preparedStatement.executeQuery();
while(result.next()){
User user = new User();
user.setId(result.getInt("id"));
user.setName(result.getString("name"));
user.setAge(result.getInt("age"));
users.add(user);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
JdbcUtil.close(result, preparedStatement, conn);
}
return users;
}
}

8：在JDBC相关工具类com.gx.help.JdbcUtil里面添加测试方法

@Test
public void testUserQuery(){
UserDao udao = new UserDaoImpl();
List<User> users = udao.finaAll();
for(User user:users){
System.out.println(user.toString());
}
}

9：运行测试类

10：添加save方法并测试

UserDao里面的代码

//添加学生
public void save(User user);

UserDaoImpl里面的代码

/**
* 添加学生
*/
public void save(User user){
Connection conn=null;
PreparedStatement preparedStatement = null;
ResultSet result = null;
try {
String sql = "insert into user(id,name,age) value(?,?,?)";
conn = JdbcUtil.getConnection();
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setInt(1,user.getId());
preparedStatement.setString(2,user.getName());
preparedStatement.setInt(3,user.getAge());
preparedStatement.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
JdbcUtil.close(result, preparedStatement, conn);
}
}

JdbcUtilTest里面的代码

@Test
public void testUserSave(){
UserDao udao = new UserDaoImpl();
User user = new User();
user.setAge(12);
user.setId(6);
user.setName("minuobaci");
udao.save(user);
}

测试结果

传统方式访问数据库的第二种方式（使用spring框架内置jdbc模板）

1：创建Maven项目

2：创建一个数据库表User，包括id，name，age三个字段，并向其中添加数据

3：修改pom.xml配置文件为以下内容

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.gx</groupId>
<artifactId>SpringData</artifactId>
<packaging>war</packaging>
<version>0.0.1-SNAPSHOT</version>
<name>SpringData Maven Webapp</name>
<url>http://maven.apache.org</url>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>

<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.0.1</version>
<scope>provided</scope>
</dependency>

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>

<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.5.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.5.RELEASE</version>
</dependency>
</dependencies>
<build>
<finalName>SpringData</finalName>
</build>
</project>

4：在src/main/resources里面添加spring配置文件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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd"> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="username" value="root" />
<property name="password" value="root" />
<property name="url" value="jdbc:mysql://localhost:3307/test" />
</bean></beans>

5：添加测试类com.gx.JdbcUtilTest测试配置文件是否正确

package com.gx;import javax.sql.DataSource;import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;import junit.framework.Assert;public class JdbcUtilTest {
private ApplicationContext ctx = null;
@Before
public void setup(){
System.out.println("setup is working......");
ctx = new ClassPathXmlApplicationContext("beans.xml");
}
@After
public void destory(){
ctx = null;
System.out.println("destory is working......");
}
@Test
public void testDataSource(){
System.out.println("testDataSource is working......");
DataSource dataSource = (DataSource) ctx.getBean("dataSource");//dataSource是配置文件里面beans的id
Assert.assertNotNull(dataSource);
}
}

6：运行测试类

7：继续注入JdbcTemplate（在beans.xml文件里面添加以下代码）

8：同理，测试一下是否正确（在测试类com.gx.JdbcUtilTest里面添加以下代码）

@Test
public void testJdbcTemplate(){
System.out.println("testJdbcTemplate is working......");
JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
Assert.assertNotNull(jdbcTemplate);
}

10：测试结果

11：定义接口UserDao（可以直接使用“传统方式访问数据库”里面的UserDao）

12：创建UserDao的实现类UserDaoImpl

package com.gx.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.gx.dao.UserDao;
import com.gx.domain.User;
/**
* UserDao访问接口实现类，这个实现类通过最Spring jdbc的方式操作
* @author Administrator
*
*/
public class UserDaoImpl implements UserDao{
//注入jdbcTemplate
private JdbcTemplate jdbcTemplate;
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
/**
* 查询所有学生
*/
public List<User> finaAll(){
final List<User> users = new ArrayList<User>();
String sql = "select * from user"; jdbcTemplate.query(sql, new RowCallbackHandler(){ public void processRow(ResultSet result) throws SQLException {
User user = new User();
user.setId(result.getInt("id"));
user.setName(result.getString("name"));
user.setAge(result.getInt("age"));
users.add(user);
} }); return users;
}
/**
* 添加学生
*/
public void save(User user){
String sql = "insert into user(id,name,age) value(?,?,?)";
jdbcTemplate.update(sql,new Object[]{user.getId(),user.getName(),user.getAge()});
}
}

13：因为UserDAOImpl是使用spring Jdbc的方式来实现的，所以我们必须将UserDAOImpl配置到beans.xml文件里面去，并将我们使用到的JdbcTemplate注入其中，否则UserDAOImpl将无法使用JdbcTemplate

14：编写测试方法（修改测试类com.gx.JdbcUtilTest为以下代码）

package com.gx;import java.util.List;import javax.sql.DataSource;import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;import com.gx.dao.UserDao;
import com.gx.domain.User;import junit.framework.Assert;public class JdbcUtilTest {
private ApplicationContext ctx = null;
private UserDao userDao = null;
@Before
public void setup(){
System.out.println("setup is working......");
ctx = new ClassPathXmlApplicationContext("beans.xml");
userDao = (UserDao)ctx.getBean("UserDao");//此处的UserDao对应beans.xml文件里面的bean的ID
}
@After
public void destory(){
ctx = null;
System.out.println("destory is working......");
} @Test
public void testQuery(){
List<User> users = userDao.finaAll();
for(User user:users){
System.out.println(user);
}
} @Test
public void testSave(){
User user = new User();
user.setAge(12);
user.setId(7);
user.setName("minuo");
userDao.save(user);
}
}

15：测试查询的结果

16：测试添加的结果

我们可以看到，上面两种方法的对比中第二种方法：使用spring框架内置jdbc模板。使用的代码更少，更简单，因为像Connection等操作都不在需要我们来做了，他们都交由spring来管理了

两种弊端分析

1：两个方法的Dao涉及到非常多的代码

2：Dao的实现由很多重复的代码

3：如果要开发分页这些的方法还要重新封装

使用SpringData访问数据库

1：创建Maven项目修改pom.xml配置文件为以下代码

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.gx</groupId>
<artifactId>SpringData</artifactId>
<packaging>war</packaging>
<version>0.0.1-SNAPSHOT</version>
<name>SpringData Maven Webapp</name>
<url>http://maven.apache.org</url>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>

<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.0.1</version>
<scope>provided</scope>
</dependency>

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>

<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.5.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.5.RELEASE</version>
</dependency>

<dependency>
<groupId>org.springframework.data</groupId>
<artifactId>spring-data-jpa</artifactId>
<version>1.8.0.RELEASE</version>
</dependency>

<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-entitymanager</artifactId>
<version>4.3.6.Final</version>
</dependency>
</dependencies>
<build>
<finalName>SpringData</finalName>
</build>
</project>

2：创建一个数据库表User，包括id，name，age三个字段，并向其中添加数据

3：创建一个配置文件spring-data-beans.xml内容如下

<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:tx="http://www.springframework.org/schema/tx"
xmlns:jpa="http://www.springframework.org/schema/data/jpa"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd"> 
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="username" value="root" />
<property name="password" value="root" />
<property name="url" value="jdbc:mysql://localhost:3307/test" />
</bean> 
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="dataSource" ref="dataSource"/>

<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"/>
</property>

<property name="packagesToScan" value="com.gx"/>

<property name="jpaProperties">
<props>
<prop key="hibernate.ejb.naming_strategy">org.hibernate.cfg.ImprovedNamingStrategy</prop>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.format_sql">true</prop>
<prop key="hibernate.hbm2ddl.auto">update</prop>
</props>
</property>
</bean> 
<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
<property name="entityManagerFactory" ref="entityManagerFactory"/>
</bean> 
<tx:annotation-driven transaction-manager="transactionManager"/> 
<jpa:repositories base-package="com.gx" entity-manager-factory-ref="entityManagerFactory"/> 
<context:component-scan base-package="com.gx"/></beans>

4：创建对象模型com.gx.domain.User

package com.gx.entity;import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
/*
* 这个Entity的注解会告诉配置文件这是一个实体类
* 配置文件会根据EntityManagerFactory来判断是否有这个表
* 如果没有会字动生成
*/
@Entity
public class User {
/* 最好使用封装之后的类型
* 因为在后面的类UserRepository适用的时候会继承泛型类Repository<User,Integer>，这里的Integer就是主键Id的类型
*/
private Integer id;
private String name;
private Integer age;
@GeneratedValue//这个注解是告诉配置文件这个id是自增的
@Id//这个注解是告诉配置文件这是一个id
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
/*这个注解是设置表user里面的name字段的长度为20，默认不为空
* 如果不设置的话默认是255
*/
@Column(length=20,nullable=false)
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;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
}}

5：定义接口com.gx.repository.UserRepository用于访问数据库

package com.gx.repository;import java.util.List;import org.springframework.data.repository.Repository;import com.gx.entity.User;public interface UserRepository extends Repository<User, Integer>{
//查找全部学生信息
public List<User> findAll();
//保存学生信息
public void save(User user);
}

6：添加测试类com.gx.SpringData

package com.gx;import java.util.List;import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;import com.gx.entity.User;
import com.gx.repository.UserRepository;public class SpringData {
//获取上下文
private ApplicationContext ctx = null;
private UserRepository userRepository = null;
@Before
public void setup(){
System.out.println("setup is working......");
ctx = new ClassPathXmlApplicationContext("spring-data-beans.xml");
userRepository = ctx.getBean(UserRepository.class);
}
@After
public void destory(){
ctx = null;
System.out.println("destory is working......");
} @Test
public void testSave(){
User user = new User();
user.setAge(12);
user.setId(2);
user.setName("baci");
userRepository.save(user);
System.out.println("testSave is working......");
} @Test
public void testFindAll(){
List<User> users = userRepository.findAll();
for(User user:users){
System.out.println(user);
}
}
}