对比传统方式访问数据库和SpringData访问数据库
我们在写代码的时候应该一边写一边测试,这样的话可以尽快的找到错误,在代码写多了之后去找错误的话不容易给错误定位
传统方式访问数据库
1:创建一个Maven web项目
2:修改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> <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api --> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>3.0.1</version> <scope>provided</scope> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.38</version> </dependency> </dependencies> <build> <finalName>SpringData</finalName> </build> </project>
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> <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api --> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>3.0.1</version> <scope>provided</scope> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.38</version> </dependency> <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc --> <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文件里面添加以下代码)
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <!-- 因为是引用,所以不能用value,需要用ref --> <property name="dataSource" ref="dataSource" /> </bean>
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
<bean id="UserDao" class="com.gx.dao.impl.UserDaoImpl"> <property name="jdbcTemplate" ref="jdbcTemplate" /> </bean>
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> <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api --> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>3.0.1</version> <scope>provided</scope> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.38</version> </dependency> <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc --> <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> <!-- https://mvnrepository.com/artifact/org.springframework.data/spring-data-jpa --> <dependency> <groupId>org.springframework.data</groupId> <artifactId>spring-data-jpa</artifactId> <version>1.8.0.RELEASE</version> </dependency> <!-- hibernate-entity --> <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> <!-- 配置EntityManagerFactory --> <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"> <property name="dataSource" ref="dataSource"/> <!-- 设置jpa适配器 --> <property name="jpaVendorAdapter"> <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"/> </property> <!-- 采用注解的方式,扫描包下面的java类 --> <property name="packagesToScan" value="com.gx"/> <!-- jpa的相关配置 --> <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><!--执行的时候是否显示SQL--> <prop key="hibernate.format_sql">true</prop><!--执行的时候SQL是否格式化--> <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"/> <!--配置spring data 需要扫描的包--> <jpa:repositories base-package="com.gx" entity-manager-factory-ref="entityManagerFactory"/> <!--在这个配置里面的内容spring都能够自动找到,更方便--> <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); } } }
7:运行testSave测试save方法
7.1:控制台
7.2:数据库
8:运行testFindAll测试findAll方法
关于spring data的更新在