spring jdbc_Spring JDBC示例

最新推荐文章于 2024-03-05 10:36:57 发布

cunchi4221

最新推荐文章于 2024-03-05 10:36:57 发布

阅读量470

点赞数

文章标签：数据库 spring java mysql mybatis

原文链接：https://www.journaldev.com/2593/spring-jdbc-example

版权

spring jdbc

Spring JDBC is the topic of this tutorial. Databases are integral part of most of the Enterprise Applications. So when it comes to a Java EE framework, having good integration with JDBC is very important.

Spring JDBC是本教程的主题。数据库是大多数企业应用程序不可或缺的一部分。因此，对于Java EE框架，与JDBC的良好集成非常重要。

Spring JDBC (Spring JDBC)

Spring Framework provides excellent integration with JDBC API and provides JdbcTemplate utility class that we can use to avoid bolier-plate code from our database operations logic such as Opening/Closing Connection, ResultSet, PreparedStatement etc.

Spring Framework提供了与JDBC API的出色集成，并提供了JdbcTemplate实用程序类，我们可以使用它来避免数据库操作逻辑（如打开/关闭连接，ResultSet，PreparedStatement等）中的浮标代码。

Let’s first look at a simple Spring JDBC example application and then we will see how JdbcTemplate class can help us in writing modular code with ease, without worrying whether resources are closed properly or not.

首先让我们看一个简单的Spring JDBC示例应用程序，然后我们将了解JdbcTemplate类如何帮助我们轻松编写模块化代码，而不必担心资源是否正确关闭。

Spring Tool Suite to develop Spring based applications is very helpful, so we will use STS to create our Spring JDBC application. Our final project structure will look like below image.

使用Spring Tool Suite开发基于Spring的应用程序非常有帮助，因此我们将使用STS创建我们的Spring JDBC应用程序。我们的最终项目结构将如下图所示。

Create a simple Spring Maven Project from the STS Menu, you can choose whatever name you like or stick with my project name as SpringJDBCExample.

从STS菜单创建一个简单的Spring Maven项目，您可以选择喜欢的任何名称，也可以保留我的项目名称为SpringJDBCExample。

Spring JDBC依赖关系 (Spring JDBC Dependencies)

First of all we need to include Spring JDBC and Database drivers in the maven project pom.xml file. My final pom.xml file looks like below.

首先，我们需要在maven项目pom.xml文件中包含Spring JDBC和数据库驱动程序。我的最终pom.xml文件如下所示。

<project xmlns="https://maven.apache.org/POM/4.0.0" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="https://maven.apache.org/POM/4.0.0 https://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>

Most of the part is automatically generated by STS, however I have update Spring Framework version to use latest version as 4.0.2.RELEASE.

大部分零件是由STS自动生成的，但是我已经更新了Spring Framework版本，以使用最新版本为4.0.2.RELEASE。

Also we have added required artifacts spring-jdbc and mysql-connector-java. First one contains the Spring JDBC support classes and second one is database driver. I am using MySQL database for our testing purposes, so I have added MySQL JConnector jar dependencies. If you are using some other RDBMS then you should make the corresponding changes in the dependencies.

此外，我们还添加了必需的构件spring-jdbc和mysql-connector-java 。第一个包含Spring JDBC支持类，第二个包含数据库驱动程序。我出于测试目的而使用MySQL数据库，因此添加了MySQL JConnector jar依赖项。如果使用其他RDBMS，则应在依赖项中进行相应的更改。

Spring JDBC示例–数据库设置 (Spring JDBC Example – Database Setup)

Let’s create a simple table that we will use in our application for CRUD operations example.

让我们创建一个简单的表，在我们的应用程序中将其用于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;

Spring JDBC示例–模型类 (Spring JDBC Example – Model Class)

We will use DAO Pattern for JDBC operations, so let’s create a java bean that will model our Employee table.

我们将使用DAO模式进行JDBC操作，因此让我们创建一个Java Bean，它将为Employee表建模。

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;
	}
	
	@Override
	public String toString(){
		return "{ID="+id+",Name="+name+",Role="+role+"}";
	}
}

Spring JDBC示例– DAO接口和实现 (Spring JDBC Example – DAO Interface and Implementation)

For DAO pattern, we will first have an interface declaring all the operations we want to implement.

对于DAO模式，我们首先有一个接口，该接口声明了我们要实现的所有操作。

package com.journaldev.spring.jdbc.dao;

import java.util.List;

import com.journaldev.spring.jdbc.model.Employee;

//CRUD operations
public interface EmployeeDAO {
	
	//Create
	public void save(Employee employee);
	//Read
	public Employee getById(int id);
	//Update
	public void update(Employee employee);
	//Delete
	public void deleteById(int id);
	//Get All
	public 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;
	}

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

	@Override
	public 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;
	}

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

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

	@Override
	public 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;
	}

}

The implementation of CRUD operations are simple to understand. If you want to learn more about DataSource, please read JDBC DataSource Example.

CRUD操作的实现很容易理解。如果您想了解有关DataSource的更多信息，请阅读JDBC DataSource Example 。

Spring JDBC示例– Bean配置 (Spring JDBC Example – Bean Configuration)

If you look at all the classes above, they are all using standard JDBC API and there is no reference to Spring JDBC framework. Spring JDBC framework classes comes into picture when we create Spring Bean Configuration file and define the beans. We will create the DataSource in the Spring Bean context file and set it to our DAO implementation class.

如果您查看上述所有类，它们都使用标准的JDBC API，并且没有对Spring JDBC框架的引用。当我们创建Spring Bean配置文件并定义bean时，Spring JDBC框架类就会出现。我们将在Spring Bean上下文文件中创建DataSource并将其设置为我们的DAO实现类。

My Spring Bean Configuration file looks like below.

我的Spring Bean配置文件如下所示。

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="https://www.springframework.org/schema/beans"
	xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="https://www.springframework.org/schema/beans https://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>

First of all we are creating a DataSource object of class DriverManagerDataSource. This class provides the basic implementation of DataSource that we can use. We are passing MySQL database URL, username and password as properties to the DataSource bean.

首先，我们创建类DriverManagerDataSource的DataSource对象。此类提供了我们可以使用的DataSource的基本实现。我们正在将MySQL数据库URL，用户名和密码作为属性传递给DataSource bean。

Again dataSource bean is set to the EmployeeDAOImpl bean and we are ready with our Spring JDBC implementation. The implementation is loosely coupled and if we want to switch to some other implementation or move to other database server, all we need is to make corresponding changes in the bean configurations. This is one of the major advantage provided by Spring JDBC framework.

再次将dataSource bean设置为EmployeeDAOImpl bean，我们就可以使用Spring JDBC实现了。实现是松散耦合的，如果我们要切换到其他实现或转移到其他数据库服务器，我们所需要做的就是在Bean配置中进行相应的更改。这是Spring JDBC框架提供的主要优势之一。

Spring JDBC测试类 (Spring JDBC Test Class)

Let’s write a simple test class to make sure everything is working fine.

让我们编写一个简单的测试类，以确保一切正常。

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 Context
		ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("spring.xml");
		
		//Get the EmployeeDAO Bean
		EmployeeDAO employeeDAO = ctx.getBean("employeeDAO", EmployeeDAO.class);
		
		//Run some tests for JDBC CRUD operations
		Employee emp = new Employee();
		int rand = new Random().nextInt(1000);
		emp.setId(rand);
		emp.setName("Pankaj");
		emp.setRole("Java Developer");
		
		//Create
		employeeDAO.save(emp);
		
		//Read
		Employee emp1 = employeeDAO.getById(rand);
		System.out.println("Employee Retrieved::"+emp1);
		
		//Update
		emp.setRole("CEO");
		employeeDAO.update(emp);
		
		//Get All
		List<Employee> empList = employeeDAO.getAll();
		System.out.println(empList);
		
		//Delete
		employeeDAO.deleteById(rand);
		
		//Close Spring Context
		ctx.close();
		
		System.out.println("DONE");
	}

}

I am using Random Class to generate random number for employee id. When we run above program, we get following output.

我正在使用随机类为员工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

Spring JdbcTemplate示例 (Spring JdbcTemplate Example)

If you look at the DAO implementation class, there is a lot of boiler-plate code where we are opening and closing Connection, PreparedStatements and ResultSet. This can lead to resource leak if someone forgets to close the resources properly. We can use org.springframework.jdbc.core.JdbcTemplate class to avoid these errors. Spring JdbcTemplate is the central class in Spring JDBC core package and provides a lot of methods to execute queries and automatically parse ResultSet to get the Object or list of Objects.

如果您查看DAO实现类，则有很多样板代码在其中打开和关闭Connection，PreparedStatements和ResultSet。如果有人忘记正确关闭资源，则可能导致资源泄漏。我们可以使用org.springframework.jdbc.core.JdbcTemplate类来避免这些错误。 Spring JdbcTemplate是Spring JDBC核心软件包中的中心类，它提供了许多方法来执行查询并自动解析ResultSet以获得Object或Objects列表。

All we need is to provide the arguments as Object array and implement Callback interfaces such as PreparedStatementSetter and RowMapper for mapping arguments or converting ResultSet data to bean objects.

我们所需要的只是将参数提供为Object数组，并实现Callback接口（例如PreparedStatementSetter和RowMapper以映射参数或将ResultSet数据转换为Bean对象。

Let’s look at another implementation of EmployeeDAO where we will use Spring JdbcTemplate class for executing different types of queries.

让我们看一下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;
	}
	
	@Override
	public 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());
	}

	@Override
	public 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 reuse
		Employee emp = jdbcTemplate.queryForObject(query, new Object[]{id}, new RowMapper<Employee>(){

			@Override
			public 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;
	}

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

	@Override
	public 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);
	}

	@Override
	public 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;
	}

}

Important points to look into above code for Spring JdbcTemplate are:

上面的Spring JdbcTemplate代码研究的重点是：

Use of Object array to pass PreparedStatement arguments, we could also use PreparedStatementSetter implementation but passing Object array seems easy to use.
使用Object数组传递PreparedStatement参数，我们也可以使用PreparedStatementSetter实现，但是传递Object数组似乎易于使用。
No code related to opening and closing connections, statements or result set. All that is handled internally by Spring JdbcTemplate class.
没有与打开和关闭连接，语句或结果集相关的代码。所有这些都由Spring JdbcTemplate类在内部处理。
RowMapper anonymous class implementation to map the ResultSet data to Employee bean object in queryForObject() method.
RowMapper匿名类实现，用于将ResultSet数据映射到queryForObject（）方法中的Employee bean对象。
queryForList() method returns list of Map whereas Map contains the row data mapped with key as the column name and value from the database row matching the criteria.
queryForList（）方法返回Map的列表，而Map包含以key作为列名映射的行数据，并且包含与标准匹配的数据库行中的值。

To use Spring JdbcTemplate implementation, all we need is to change the employeeDAO class in the Spring Bean configuration file as shown below.

要使用Spring JdbcTemplate实现，我们需要做的就是更改Spring Bean配置文件中的employeeDAO类，如下所示。

<bean id="employeeDAO" class="com.journaldev.spring.jdbc.dao.EmployeeDAOJDBCTemplateImpl">
	<property name="dataSource" ref="dataSource" />
</bean>

When you will run the main class, the output of Spring JdbcTemplate implementation will be similar to the one seen above with normal JDBC implementation. That’s all for Spring JDBC Example tutorial, download the sample project from below link and play around with it to learn more.

当您运行主类时，Spring JdbcTemplate实现的输出将与上面使用普通JDBC实现看到的输出类似。 Spring JDBC Example教程的全部内容，请从下面的链接下载示例项目，并进行试用以了解更多信息。

Download Spring JDBC Project 下载Spring JDBC Project

翻译自: https://www.journaldev.com/2593/spring-jdbc-example

spring jdbc

cunchi4221

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
spring jdbc_Spring JDBC示例

spring jdbcSpring JDBC is the topic of this tutorial. Databases are integral part of most of the Enterprise Applications. So when it comes to a Java EE framework, having good integration with JDBC is ...
复制链接

扫一扫