Spring JDBC是本教程的主题。数据库是大多数企业应用程序不可或缺的一部分。因此,当谈到Java EE框架时,与JDBC的良好集成非常重要。
目录[ 隐藏 ]
Spring Framework提供了与JDBC API的出色集成,并提供JdbcTemplate
了我们可以用来避免来自我们的数据库操作逻辑的bolier-plate代码的实用程序类,例如Opening / Closing Connection,ResultSet,PreparedStatement等。
让我们首先看一个简单的Spring JDBC示例应用程序,然后我们将看到JdbcTemplate类如何帮助我们轻松编写模块化代码,而不必担心资源是否正确关闭。
用于开发基于Spring的应用程序的Spring Tool Suite非常有用,因此我们将使用STS来创建Spring JDBC应用程序。我们的最终项目结构如下图所示。
从STS菜单创建一个简单的Spring Maven项目,您可以选择您喜欢的任何名称,或者将我的项目名称作为SpringJDBCExample。
首先,我们需要在maven项目pom.xml文件中包含Spring JDBC和Database驱动程序。我的最终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/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>org.springframework.samples</groupId><artifactId>SpringJDBCExample</artifactId><version>0.0.1-SNAPSHOT</version><properties><!-- Generic properties --><java.version>1.6</java.version><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding><!-- Spring --><spring-framework.version>4.0.2.RELEASE</spring-framework.version><!-- Logging --><logback.version>1.0.13</logback.version><slf4j.version>1.7.5</slf4j.version></properties><dependencies><!-- Spring and Transactions --><dependency><groupId>org.springframework</groupId><artifactId>spring-context</artifactId><version>${spring-framework.version}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-tx</artifactId><version>${spring-framework.version}</version></dependency><!-- Spring JDBC Support --><dependency><groupId>org.springframework</groupId><artifactId>spring-jdbc</artifactId><version>${spring-framework.version}</version></dependency><!-- MySQL Driver --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.0.5</version></dependency><!-- Logging with SLF4J & LogBack --><dependency><groupId>org.slf4j</groupId><artifactId>slf4j-api</artifactId><version>${slf4j.version}</version><scope>compile</scope></dependency><dependency><groupId>ch.qos.logback</groupId><artifactId>logback-classic</artifactId><version>${logback.version}</version><scope>runtime</scope></dependency></dependencies>
</project>
大部分内容是由STS自动生成的,但我更新了Spring Framework版本,使用最新版本为4.0.2.RELEASE。
我们还添加了所需的工件spring-jdbc和mysql-connector-java。第一个包含Spring JDBC支持类,第二个包含数据库驱动程序。我正在使用MySQL数据库进行测试,因此我添加了MySQL JConnector jar依赖项。如果您正在使用其他一些RDBMS,那么您应该在依赖项中进行相应的更改。
让我们创建一个简单的表,我们将在我们的应用程序中使用CRUD操作示例。
CREATE TABLE `Employee` (`id` int(11) unsigned NOT NULL,`name` varchar(20) DEFAULT NULL,`role` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
我们将使用DAO Pattern进行JDBC操作,所以让我们创建一个将为Employee表建模的java bean。
package com.journaldev.spring.jdbc.model;public class Employee {private int id;private String name;private String role;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 String getRole() {return role;}public void setRole(String role) {this.role = role;}@Overridepublic String toString(){return "{ID="+id+",Name="+name+",Role="+role+"}";}
}
对于DAO模式,我们首先会有一个接口来声明我们想要实现的所有操作。
package com.journaldev.spring.jdbc.dao;import java.util.List;import com.journaldev.spring.jdbc.model.Employee;//CRUD operations
public interface EmployeeDAO {//Createpublic void save(Employee employee);//Readpublic Employee getById(int id);//Updatepublic void update(Employee employee);//Deletepublic void deleteById(int id);//Get Allpublic List<Employee> getAll();
}
package com.journaldev.spring.jdbc.dao;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;import javax.sql.DataSource;import com.journaldev.spring.jdbc.model.Employee;public class EmployeeDAOImpl implements EmployeeDAO {private DataSource dataSource;public void setDataSource(DataSource dataSource) {this.dataSource = dataSource;}@Overridepublic void save(Employee employee) {String query = "insert into Employee (id, name, role) values (?,?,?)";Connection con = null;PreparedStatement ps = null;try{con = dataSource.getConnection();ps = con.prepareStatement(query);ps.setInt(1, employee.getId());ps.setString(2, employee.getName());ps.setString(3, employee.getRole());int out = ps.executeUpdate();if(out !=0){System.out.println("Employee saved with id="+employee.getId());}else System.out.println("Employee save failed with id="+employee.getId());}catch(SQLException e){e.printStackTrace();}finally{try {ps.close();con.close();} catch (SQLException e) {e.printStackTrace();}}}@Overridepublic Employee getById(int id) {String query = "select name, role from Employee where id = ?";Employee emp = null;Connection con = null;PreparedStatement ps = null;ResultSet rs = null;try{con = dataSource.getConnection();ps = con.prepareStatement(query);ps.setInt(1, id);rs = ps.executeQuery();if(rs.next()){emp = new Employee();emp.setId(id);emp.setName(rs.getString("name"));emp.setRole(rs.getString("role"));System.out.println("Employee Found::"+emp);}else{System.out.println("No Employee found with id="+id);}}catch(SQLException e){e.printStackTrace();}finally{try {rs.close();ps.close();con.close();} catch (SQLException e) {e.printStackTrace();}}return emp;}@Overridepublic void update(Employee employee) {String query = "update Employee set name=?, role=? where id=?";Connection con = null;PreparedStatement ps = null;try{con = dataSource.getConnection();ps = con.prepareStatement(query);ps.setString(1, employee.getName());ps.setString(2, employee.getRole());ps.setInt(3, employee.getId());int out = ps.executeUpdate();if(out !=0){System.out.println("Employee updated with id="+employee.getId());}else System.out.println("No Employee found with id="+employee.getId());}catch(SQLException e){e.printStackTrace();}finally{try {ps.close();con.close();} catch (SQLException e) {e.printStackTrace();}}}@Overridepublic void deleteById(int id) {String query = "delete from Employee where id=?";Connection con = null;PreparedStatement ps = null;try{con = dataSource.getConnection();ps = con.prepareStatement(query);ps.setInt(1, id);int out = ps.executeUpdate();if(out !=0){System.out.println("Employee deleted with id="+id);}else System.out.println("No Employee found with id="+id);}catch(SQLException e){e.printStackTrace();}finally{try {ps.close();con.close();} catch (SQLException e) {e.printStackTrace();}}}@Overridepublic List<Employee> getAll() {String query = "select id, name, role from Employee";List<Employee> empList = new ArrayList<Employee>();Connection con = null;PreparedStatement ps = null;ResultSet rs = null;try{con = dataSource.getConnection();ps = con.prepareStatement(query);rs = ps.executeQuery();while(rs.next()){Employee emp = new Employee();emp.setId(rs.getInt("id"));emp.setName(rs.getString("name"));emp.setRole(rs.getString("role"));empList.add(emp);}}catch(SQLException e){e.printStackTrace();}finally{try {rs.close();ps.close();con.close();} catch (SQLException e) {e.printStackTrace();}}return empList;}}
CRUD操作的实现很容易理解。如果您想了解有关DataSource的更多信息,请阅读JDBC DataSource示例。
如果查看上面的所有类,它们都使用标准JDBC API,并且没有对Spring JDBC框架的引用。当我们创建Spring Bean配置文件并定义bean时,Spring JDBC框架类就会出现。我们将在Spring Bean上下文文件中创建DataSource并将其设置为我们的DAO实现类。
我的Spring Bean配置文件如下所示。
<?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="employeeDAO" class="com.journaldev.spring.jdbc.dao.EmployeeDAOImpl"><property name="dataSource" ref="dataSource" /></bean><bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"><property name="driverClassName" value="com.mysql.jdbc.Driver" /><property name="url" value="jdbc:mysql://localhost:3306/TestDB" /><property name="username" value="pankaj" /><property name="password" value="pankaj123" /></bean></beans>
首先,我们要创建一个类的DataSource对象DriverManagerDataSource
。该类提供了我们可以使用的DataSource的基本实现。我们将MySQL数据库URL,用户名和密码作为属性传递给DataSource bean。
再次将dataSource bean设置为EmployeeDAOImpl
bean,我们已准备好使用Spring JDBC实现。实现是松散耦合的,如果我们想切换到其他实现或转移到其他数据库服务器,我们所需要的只是在bean配置中进行相应的更改。这是Spring JDBC框架提供的主要优势之一。
让我们编写一个简单的测试类来确保一切正常。
package com.journaldev.spring.jdbc.main;import java.util.List;
import java.util.Random;import org.springframework.context.support.ClassPathXmlApplicationContext;import com.journaldev.spring.jdbc.dao.EmployeeDAO;
import com.journaldev.spring.jdbc.model.Employee;public class SpringMain {public static void main(String[] args) {//Get the Spring ContextClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("spring.xml");//Get the EmployeeDAO BeanEmployeeDAO employeeDAO = ctx.getBean("employeeDAO", EmployeeDAO.class);//Run some tests for JDBC CRUD operationsEmployee emp = new Employee();int rand = new Random().nextInt(1000);emp.setId(rand);emp.setName("Pankaj");emp.setRole("Java Developer");//CreateemployeeDAO.save(emp);//ReadEmployee emp1 = employeeDAO.getById(rand);System.out.println("Employee Retrieved::"+emp1);//Updateemp.setRole("CEO");employeeDAO.update(emp);//Get AllList<Employee> empList = employeeDAO.getAll();System.out.println(empList);//DeleteemployeeDAO.deleteById(rand);//Close Spring Contextctx.close();System.out.println("DONE");}}
我使用随机类为员工ID生成随机数。当我们运行上面的程序时,我们得到以下输出。
Mar 25, 2014 12:54:18 PM org.springframework.context.support.ClassPathXmlApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@4b9af9a9: startup date [Tue Mar 25 12:54:18 PDT 2014]; root of context hierarchy
Mar 25, 2014 12:54:18 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring.xml]
Mar 25, 2014 12:54:19 PM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
Employee saved with id=726
Employee Found::{ID=726,Name=Pankaj,Role=Java Developer}
Employee Retrieved::{ID=726,Name=Pankaj,Role=Java Developer}
Employee updated with id=726
[{ID=726,Name=Pankaj,Role=CEO}]
Employee deleted with id=726
Mar 25, 2014 12:54:19 PM org.springframework.context.support.ClassPathXmlApplicationContext doClose
INFO: Closing org.springframework.context.support.ClassPathXmlApplicationContext@4b9af9a9: startup date [Tue Mar 25 12:54:18 PDT 2014]; root of context hierarchy
DONE
如果你看看DAO实现类,我们打开和关闭Connection,PreparedStatements和ResultSet有很多样板代码。如果有人忘记正确关闭资源,这可能会导致资源泄漏。我们可以使用org.springframework.jdbc.core.JdbcTemplate
class来避免这些错误。Spring JdbcTemplate是Spring JDBC核心包中的中心类,它提供了许多方法来执行查询并自动解析ResultSet以获取Object或Object列表。
我们所需要的只是将参数提供为Object数组并实现Callback接口,例如PreparedStatementSetter
和RowMapper
用于映射参数或将ResultSet数据转换为bean对象。
让我们看一下EmployeeDAO的另一个实现,我们将使用Spring JdbcTemplate类来执行不同类型的查询。
package com.journaldev.spring.jdbc.dao;import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;import javax.sql.DataSource;import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;import com.journaldev.spring.jdbc.model.Employee;public class EmployeeDAOJDBCTemplateImpl implements EmployeeDAO {private DataSource dataSource;public void setDataSource(DataSource dataSource) {this.dataSource = dataSource;}@Overridepublic void save(Employee employee) {String query = "insert into Employee (id, name, role) values (?,?,?)";JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);Object[] args = new Object[] {employee.getId(), employee.getName(), employee.getRole()};int out = jdbcTemplate.update(query, args);if(out !=0){System.out.println("Employee saved with id="+employee.getId());}else System.out.println("Employee save failed with id="+employee.getId());}@Overridepublic Employee getById(int id) {String query = "select id, name, role from Employee where id = ?";JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);//using RowMapper anonymous class, we can create a separate RowMapper for reuseEmployee emp = jdbcTemplate.queryForObject(query, new Object[]{id}, new RowMapper<Employee>(){@Overridepublic Employee mapRow(ResultSet rs, int rowNum)throws SQLException {Employee emp = new Employee();emp.setId(rs.getInt("id"));emp.setName(rs.getString("name"));emp.setRole(rs.getString("role"));return emp;}});return emp;}@Overridepublic void update(Employee employee) {String query = "update Employee set name=?, role=? where id=?";JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);Object[] args = new Object[] {employee.getName(), employee.getRole(), employee.getId()};int out = jdbcTemplate.update(query, args);if(out !=0){System.out.println("Employee updated with id="+employee.getId());}else System.out.println("No Employee found with id="+employee.getId());}@Overridepublic void deleteById(int id) {String query = "delete from Employee where id=?";JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);int out = jdbcTemplate.update(query, id);if(out !=0){System.out.println("Employee deleted with id="+id);}else System.out.println("No Employee found with id="+id);}@Overridepublic List<Employee> getAll() {String query = "select id, name, role from Employee";JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);List<Employee> empList = new ArrayList<Employee>();List<Map<String,Object>> empRows = jdbcTemplate.queryForList(query);for(Map<String,Object> empRow : empRows){Employee emp = new Employee();emp.setId(Integer.parseInt(String.valueOf(empRow.get("id"))));emp.setName(String.valueOf(empRow.get("name")));emp.setRole(String.valueOf(empRow.get("role")));empList.add(emp);}return empList;}}
查看Spring JdbcTemplate的上述代码的重点是:
要使用Spring JdbcTemplate实现,我们只需要更改Spring Bean配置文件中的employeeDAO类,如下所示。
<bean id="employeeDAO" class="com.journaldev.spring.jdbc.dao.EmployeeDAOJDBCTemplateImpl"><property name="dataSource" ref="dataSource" />
</bean>
当您运行主类时,Spring JdbcTemplate实现的输出将类似于上面使用普通JDBC实现的输出。这就是Spring JDBC示例教程的全部内容,从下面的链接下载示例项目并使用它来了解更多信息。
下载Spring JDBC Project
转载来源:https://www.journaldev.com/2593/spring-jdbc-example
版权声明:本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间:8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态