【Spring Boot】Spring Boot JDBC 示例 | 数据库连接演示工具版本Maven 依赖数据源和连接池JdbcTemplate : 使用 @Autowired 进行依赖注入RowMapperJdbcTemplate：运行 SQL 查询事务管理Spring Boot REST + JDBC + MySQL CRUD 完整示例测试应用程序参考文献源码下载

101 阅读 0 评论 67 点赞

我是靠谱客的博主机智星月，这篇文章主要介绍【Spring Boot】Spring Boot JDBC 示例 | 数据库连接演示工具版本Maven 依赖数据源和连接池JdbcTemplate : 使用 @Autowired 进行依赖注入RowMapperJdbcTemplate：运行 SQL 查询事务管理Spring Boot REST + JDBC + MySQL CRUD 完整示例测试应用程序参考文献源码下载，现在分享给大家，希望可以做个参考。

文章目录

演示工具版本
Maven 依赖
数据源和连接池
JdbcTemplate : 使用 @Autowired 进行依赖注入
RowMapper
JdbcTemplate：运行 SQL 查询
- a. JdbcTemplate.queryForObject :
- b. JdbcTemplate.query:
- c. JdbcTemplate.update:
- CURD 示例
事务管理
Spring Boot REST + JDBC + MySQL CRUD 完整示例
- 项目结构
- 表：articles
- pom.xml
- application.properties
- Article.java
- ArticleRowMapper.java
- IArticleDAO.java
- ArticleDAO.java
- IArticleService.java
- ArticleService.java
- ArticleController.java
- MyApplication.java
- RestClientUtil.java
测试应用程序
- 1. 使用Eclipse
- 2. 使用Maven命令
- 3. 使用可执行的JAR
- 测试
参考文献
源码下载

本页将介绍Spring boot JDBC的例子。

Spring提供了JdbcTemplate类，用于使用JDBC进行数据库操作。

JdbcTemplate类是自动配置的，我们在我们的类中使用@Autowire注解来获得它的对象，该类用spring构造型（如@Component）注解。

JdbcTemplate提供了诸如queryForObject(), query(), update()等方法来执行数据库操作。

在application.properties文件中，我们配置了DataSource和连接池。Spring boot默认选择了Tomcat池（注:在Spring Boot 1.x中，Tomcat连接池是默认连接池，但在Spring Boot 2.x中，HikariCP是默认连接池。本文使用的是Spring Boot 1.5.6.RELEASE，如果想详细理解请看下面两篇文章）。

【Spring Boot】Spring Boot Tomcat 连接池使用示例
【Spring Boot 】Spring Boot + HikariCP 连接池使用示例

事务管理是通过使用spring @Transactional注解在类级或方法级进行的。

Spring JDBC提供了RowMapper接口，用于将数据库表行与java对象进行映射。如果表的列名和java实体字段名相同，那么我们可以直接使用Spring JDBC BeanPropertyRowMapper将行与java对象映射。

在这一页，我们将提供一个使用JdbcTemplate的CRUD操作。我们将创建一个Spring boot REST应用程序，在MySQL数据库中使用JDBC执行CREATE、READ、UPDATE和DELETE操作。现在请看完整的例子，一步一步来。

演示工具版本

Java 8
Spring Boot 1.5.6.RELEASE
Maven 3.3
MySQL 5.5
Eclipse Mars

Maven 依赖

Spring JDBC依赖可以通过使用spring-boot-starter-jdbc或spring-boot-starter-data-jpa Spring Boot启动器解决。找到它们的maven依赖项。我们可以使用以下maven依赖。

复制代码

1
2
3
4
5
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

或者我们可以使用下面的maven依赖来解决spring JDBC的依赖。

复制代码

1
2
3
4
5
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

数据源和连接池

数据源和连接池是在application.properties文件中使用spring.datasource前缀配置的。Spring boot使用javax.sql.DataSource接口来配置数据源。假设我们想集成MySQL，那么我们将配置DataSource如下。

application.properties

复制代码

1
2
3
4
5
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/concretepage
spring.datasource.username=root
spring.datasource.password=

现在我们将配置连接池。为了提高性能和并发性，Spring Boot 1.x默认使用tomcat池。当我们使用spring-boot-starter-jdbc或spring-boot-starter-data-jpa进行JDBC依赖注入时，tomcat-jdbc会被自动解决。我们对tomcat连接池的配置如下。

复制代码

1
2
3
4
5
spring.datasource.tomcat.max-wait=20000
spring.datasource.tomcat.max-active=50
spring.datasource.tomcat.max-idle=20
spring.datasource.tomcat.min-idle=15

我们需要在application.properties文件中添加上述配置。

JdbcTemplate : 使用 @Autowired 进行依赖注入

JdbcTemplate是处理JDBC的核心类。它执行SQL查询并获取其结果。为了使用JdbcTemplate，我们需要在我们的应用程序中使用依赖注入将其实例化。我们可以在带有spring构造型注释的类中自动连接JdbcTemplate，如@Component、@Service、@Repository和@Controller。

使用@Autowired与属性找到JdbcTemplate的依赖注入。

复制代码

1
2
3
4
5
6
7
8
9
@Transactional
@Repository
public class ArticleDAO {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    -----------------
}

现在使用带有构造函数的@Autowired进行依赖注入。

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
@Transactional
@Repository
public class ArticleDAO {
    private final JdbcTemplate jdbcTemplate;
    @Autowired
    public ArticleDAO(JdbcTemplate jdbcTemplate) {
	  this.jdbcTemplate = jdbcTemplate;
    }

    -----------------
}

RowMapper

Spring JDBC提供了RowMapper接口，用于将行与java对象映射。我们需要创建我们自己的实现RowMapper接口的类来将行与java对象映射。找到实现RowMapper接口的示例代码。

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper; 
public class ArticleRowMapper implements RowMapper<Article> {
   @Override
   public Article mapRow(ResultSet row, int rowNum) throws SQLException {
	Article article = new Article();
	article.setArticleId(row.getInt("articleId"));
	article.setTitle(row.getString("title"));
	article.setCategory(row.getString("category"));
	return article;
   }
}

我们可以将ArticleRowMapper与JdbcTemplate一起使用，如下所示。

复制代码

1
2
3
4
5
6
public List<Article> getAllArticles() {
   String sql = "SELECT articleId, title, category FROM articles";
   RowMapper<Article> rowMapper = new ArticleRowMapper();
   return this.jdbcTemplate.query(sql, rowMapper);
}

Spring JDBC提供了BeanPropertyRowMapper，实现了RowMapper。我们可以直接使用它来代替自定义RowMapper。当表的列名和我们实体类的字段名相同时，我们就使用BeanPropertyRowMapper。那么我们可以把上面的代码改成如下。

复制代码

1
2
3
4
5
6
public List<Article> getAllArticles() {
   String sql = "SELECT articleId, title, category FROM articles";
   RowMapper<Article> rowMapper = new BeanPropertyRowMapper<Article>(Article.class);
   return this.jdbcTemplate.query(sql, rowMapper);
}

JdbcTemplate：运行 SQL 查询

JdbcTemplate提供了运行DML和DDL SQL查询的方法。找到其中一些的例子。

a. JdbcTemplate.queryForObject :

复制代码

1
2
<T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args)

该方法使用RowMapper为一个给定的SQL查询获取数据作为一个对象。SQL查询可以有绑定的参数。查找参数的描述。

sql: 包含绑定参数的SQL。

rowMapper: RowMapper实现类的对象。RowMapper将为每行映射一个对象。

args: 绑定到查询的参数。

b. JdbcTemplate.query:

复制代码

1
2
<T> List<T> query(String sql,RowMapper<T> rowMapper)

该方法执行静态查询，并使用RowMapper将行映射到java对象。查找参数的描述。

sql: 要执行的SQL查询。

rowMapper: RowMapper实现类的对象。RowMapper将为每行映射一个对象。

c. JdbcTemplate.update:

复制代码

1
2
int update(String sql, Object... args)

该方法执行插入、更新和删除语句。查找参数的描述。

sql: 要执行的SQL查询。

args: 绑定到查询的参数。

CURD 示例

现在我们将执行CREATE、READ、UPDATE和DELETE（CRUD）操作。

1. CREATE: 找到CREATE操作的示例代码片段。

复制代码

1
2
3
4
5
public void addArticle(Article article) {
   String sql = "INSERT INTO articles (articleId, title, category) values (?, ?, ?)";
   jdbcTemplate.update(sql, article.getArticleId(), article.getTitle(), article.getCategory());
}

2. READ: 查找READ操作的示例代码片段。

复制代码

1
2
3
4
5
6
public List<Article> getAllArticles() {
   String sql = "SELECT articleId, title, category FROM articles";
   RowMapper<Article> rowMapper = new BeanPropertyRowMapper<Article>(Article.class);		
   return this.jdbcTemplate.query(sql, rowMapper);
}

上述方法将返回一个对象的列表。如果我们想获取一个单一的对象，我们可以写READ操作代码，如下。

复制代码

1
2
3
4
5
6
7
public Article getArticleById(int articleId) {
	String sql = "SELECT articleId, title, category FROM articles WHERE articleId = ?";
	RowMapper<Article> rowMapper = new BeanPropertyRowMapper<Article>(Article.class);	
	Article article = jdbcTemplate.queryForObject(sql, rowMapper, articleId);
	return article;
}

3. UPDATE : 查找UPDATE操作的示例代码片段。

复制代码

1
2
3
4
5
6

public void updateArticle(Article article) {
    String sql = "UPDATE articles SET title=?, category=? WHERE articleId=?";
    jdbcTemplate.update(sql, article.getTitle(), article.getCategory(), article.getArticleId());
}

4. DELETE : 查找DELETE操作的示例代码片段。

复制代码

1
2
3
4
5
public void deleteArticle(int articleId) {
	String sql = "DELETE FROM articles WHERE articleId=?";
	jdbcTemplate.update(sql, articleId);
}

事务管理

对于事务管理，我们需要在类级或方法级使用Spring的@Transactional注解，其中我们使用JdbcTemplate来运行SQL查询。使用@Transactional意味着在插入或更新操作中的任何失败，完整的操作会被回滚。Spring 的@Transactional在类级的使用方法如下。

复制代码

1
2
3
4
5
6
@Transactional
@Repository
public class ArticleDAO {
-------------------
}

当我们在类的层面上使用@Transactional时，该类的所有方法都将成为事务性的。如果我们想让选定的方法成为事务性的，我们需要在方法级别使用@Transactional，如下所示。

复制代码

1
2
3
4
5
@Transactional
public void addArticle(Article article) {
  //Database operation using JdbcTemplate
}

Spring Boot REST + JDBC + MySQL CRUD 完整示例

项目结构

在这里插入图片描述

表：articles

复制代码

1
2
3
4
5
6
7
8
9
CREATE TABLE `articles` (
	`articleId` INT(5) NOT NULL AUTO_INCREMENT,
	`title` VARCHAR(200) NOT NULL,
	`category` VARCHAR(100) NOT NULL,
	PRIMARY KEY (`articleId`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB

pom.xml

复制代码

<?xml version="1.0" encoding="UTF-8"?>
<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>com.concretepage</groupId>
	<artifactId>spring-boot-demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
	<name>spring-demo</name>
	<description>Spring Boot Demo Project</description>
	<parent>
	        <groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.6.RELEASE</version>
	</parent>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
	    <dependency>
		 <groupId>org.springframework.boot</groupId>
		 <artifactId>spring-boot-starter-web</artifactId>
	    </dependency>
	    <dependency>
		 <groupId>org.springframework.boot</groupId>
		 <artifactId>spring-boot-starter-data-jpa</artifactId>
	    </dependency>	
	    <dependency>
		 <groupId>mysql</groupId>
		 <artifactId>mysql-connector-java</artifactId>
	    </dependency>	 
    	    <dependency>
                 <groupId>org.springframework.boot</groupId>
                 <artifactId>spring-boot-devtools</artifactId>
                 <optional>true</optional>
            </dependency> 
	</dependencies> 
	<build>
	   <plugins>
		<plugin>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-maven-plugin</artifactId>
		</plugin>
	    </plugins>
	</build>
</project>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
<?xml version="1.0" encoding="UTF-8"?>
<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>com.concretepage</groupId>
	<artifactId>spring-boot-demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
	<name>spring-demo</name>
	<description>Spring Boot Demo Project</description>
	<parent>
	        <groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.6.RELEASE</version>
	</parent>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
	    <dependency>
		 <groupId>org.springframework.boot</groupId>
		 <artifactId>spring-boot-starter-web</artifactId>
	    </dependency>
	    <dependency>
		 <groupId>org.springframework.boot</groupId>
		 <artifactId>spring-boot-starter-data-jpa</artifactId>
	    </dependency>	
	    <dependency>
		 <groupId>mysql</groupId>
		 <artifactId>mysql-connector-java</artifactId>
	    </dependency>	 
    	    <dependency>
                 <groupId>org.springframework.boot</groupId>
                 <artifactId>spring-boot-devtools</artifactId>
                 <optional>true</optional>
            </dependency> 
	</dependencies> 
	<build>
	   <plugins>
		<plugin>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-maven-plugin</artifactId>
		</plugin>
	    </plugins>
	</build>
</project>

application.properties

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
#DataSource Configuration
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/concretepage
spring.datasource.username=root
spring.datasource.password=

#Connection Pool Configuration
spring.datasource.tomcat.max-wait=20000
spring.datasource.tomcat.max-active=50
spring.datasource.tomcat.max-idle=20
spring.datasource.tomcat.min-idle=15

Article.java

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
package com.concretepage.entity;
public class Article { 
        private int articleId;  
        private String title;
	private String category;
	public int getArticleId() {
		return articleId;
	}
	public void setArticleId(int articleId) {
		this.articleId = articleId;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getCategory() {
		return category;
	}
	public void setCategory(String category) {
		this.category = category;
	}
}

ArticleRowMapper.java

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
package com.concretepage.entity;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class ArticleRowMapper implements RowMapper<Article> {
	@Override
	public Article mapRow(ResultSet row, int rowNum) throws SQLException {
		Article article = new Article();
		article.setArticleId(row.getInt("articleId"));
		article.setTitle(row.getString("title"));
		article.setCategory(row.getString("category"));
		return article;
	}
}

IArticleDAO.java

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
package com.concretepage.dao;
import java.util.List;
import com.concretepage.entity.Article;
public interface IArticleDAO {
    List<Article> getAllArticles();
    Article getArticleById(int articleId);
    void addArticle(Article article);
    void updateArticle(Article article);
    void deleteArticle(int articleId);
    boolean articleExists(String title, String category);
}

ArticleDAO.java

复制代码

package com.concretepage.dao;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.concretepage.entity.Article;
import com.concretepage.entity.ArticleRowMapper;
@Transactional
@Repository
public class ArticleDAO implements IArticleDAO {
	@Autowired
        private JdbcTemplate jdbcTemplate;
	@Override
	public Article getArticleById(int articleId) {
		String sql = "SELECT articleId, title, category FROM articles WHERE articleId = ?";
		RowMapper<Article> rowMapper = new BeanPropertyRowMapper<Article>(Article.class);
		Article article = jdbcTemplate.queryForObject(sql, rowMapper, articleId);
		return article;
	}
	@Override
	public List<Article> getAllArticles() {
		String sql = "SELECT articleId, title, category FROM articles";
                //RowMapper<Article> rowMapper = new BeanPropertyRowMapper<Article>(Article.class);
		RowMapper<Article> rowMapper = new ArticleRowMapper();
		return this.jdbcTemplate.query(sql, rowMapper);
	}	
	@Override
	public void addArticle(Article article) {
		//Add article
		String sql = "INSERT INTO articles (articleId, title, category) values (?, ?, ?)";
		jdbcTemplate.update(sql, article.getArticleId(), article.getTitle(), article.getCategory());
		
		//Fetch article id
		sql = "SELECT articleId FROM articles WHERE title = ? and category=?";
		int articleId = jdbcTemplate.queryForObject(sql, Integer.class, article.getTitle(), article.getCategory());
		
		//Set article id 
		article.setArticleId(articleId);
	}
	@Override
	public void updateArticle(Article article) {
		String sql = "UPDATE articles SET title=?, category=? WHERE articleId=?";
		jdbcTemplate.update(sql, article.getTitle(), article.getCategory(), article.getArticleId());
	}
	@Override
	public void deleteArticle(int articleId) {
		String sql = "DELETE FROM articles WHERE articleId=?";
		jdbcTemplate.update(sql, articleId);
	}
	@Override
	public boolean articleExists(String title, String category) {
		String sql = "SELECT count(*) FROM articles WHERE title = ? and category=?";
		int count = jdbcTemplate.queryForObject(sql, Integer.class, title, category);
		if(count == 0) {
    		        return false;
		} else {
			return true;
		}
	}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
package com.concretepage.dao;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.concretepage.entity.Article;
import com.concretepage.entity.ArticleRowMapper;
@Transactional
@Repository
public class ArticleDAO implements IArticleDAO {
	@Autowired
        private JdbcTemplate jdbcTemplate;
	@Override
	public Article getArticleById(int articleId) {
		String sql = "SELECT articleId, title, category FROM articles WHERE articleId = ?";
		RowMapper<Article> rowMapper = new BeanPropertyRowMapper<Article>(Article.class);
		Article article = jdbcTemplate.queryForObject(sql, rowMapper, articleId);
		return article;
	}
	@Override
	public List<Article> getAllArticles() {
		String sql = "SELECT articleId, title, category FROM articles";
                //RowMapper<Article> rowMapper = new BeanPropertyRowMapper<Article>(Article.class);
		RowMapper<Article> rowMapper = new ArticleRowMapper();
		return this.jdbcTemplate.query(sql, rowMapper);
	}	
	@Override
	public void addArticle(Article article) {
		//Add article
		String sql = "INSERT INTO articles (articleId, title, category) values (?, ?, ?)";
		jdbcTemplate.update(sql, article.getArticleId(), article.getTitle(), article.getCategory());
		
		//Fetch article id
		sql = "SELECT articleId FROM articles WHERE title = ? and category=?";
		int articleId = jdbcTemplate.queryForObject(sql, Integer.class, article.getTitle(), article.getCategory());
		
		//Set article id 
		article.setArticleId(articleId);
	}
	@Override
	public void updateArticle(Article article) {
		String sql = "UPDATE articles SET title=?, category=? WHERE articleId=?";
		jdbcTemplate.update(sql, article.getTitle(), article.getCategory(), article.getArticleId());
	}
	@Override
	public void deleteArticle(int articleId) {
		String sql = "DELETE FROM articles WHERE articleId=?";
		jdbcTemplate.update(sql, articleId);
	}
	@Override
	public boolean articleExists(String title, String category) {
		String sql = "SELECT count(*) FROM articles WHERE title = ? and category=?";
		int count = jdbcTemplate.queryForObject(sql, Integer.class, title, category);
		if(count == 0) {
    		        return false;
		} else {
			return true;
		}
	}
}

IArticleService.java

复制代码

1
2
3
4
5
6
7
8
9
10
11
package com.concretepage.service;
import java.util.List;
import com.concretepage.entity.Article;
public interface IArticleService {
     List<Article> getAllArticles();
     Article getArticleById(int articleId);
     boolean addArticle(Article article);
     void updateArticle(Article article);
     void deleteArticle(int articleId);
}

ArticleService.java

复制代码

package com.concretepage.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.concretepage.dao.IArticleDAO;
import com.concretepage.entity.Article;
@Service
public class ArticleService implements IArticleService {
	@Autowired
	private IArticleDAO articleDAO;
	@Override
	public Article getArticleById(int articleId) {
		Article obj = articleDAO.getArticleById(articleId);
		return obj;
	}	
	@Override
	public List<Article> getAllArticles(){
		return articleDAO.getAllArticles();
	}
	@Override
	public synchronized boolean addArticle(Article article){
                if (articleDAO.articleExists(article.getTitle(), article.getCategory())) {
    	          return false;
                } else {
    	          articleDAO.addArticle(article);
    	          return true;
                }
	}
	@Override
	public void updateArticle(Article article) {
		articleDAO.updateArticle(article);
	}
	@Override
	public void deleteArticle(int articleId) {
		articleDAO.deleteArticle(articleId);
	}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
package com.concretepage.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.concretepage.dao.IArticleDAO;
import com.concretepage.entity.Article;
@Service
public class ArticleService implements IArticleService {
	@Autowired
	private IArticleDAO articleDAO;
	@Override
	public Article getArticleById(int articleId) {
		Article obj = articleDAO.getArticleById(articleId);
		return obj;
	}	
	@Override
	public List<Article> getAllArticles(){
		return articleDAO.getAllArticles();
	}
	@Override
	public synchronized boolean addArticle(Article article){
                if (articleDAO.articleExists(article.getTitle(), article.getCategory())) {
    	          return false;
                } else {
    	          articleDAO.addArticle(article);
    	          return true;
                }
	}
	@Override
	public void updateArticle(Article article) {
		articleDAO.updateArticle(article);
	}
	@Override
	public void deleteArticle(int articleId) {
		articleDAO.deleteArticle(articleId);
	}
}

ArticleController.java

复制代码

package com.concretepage.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.util.UriComponentsBuilder;
import com.concretepage.entity.Article;
import com.concretepage.service.IArticleService;
@Controller
@RequestMapping("user")
public class ArticleController {
	@Autowired
	private IArticleService articleService;
	@GetMapping("article/{id}")
	public ResponseEntity<Article> getArticleById(@PathVariable("id") Integer id) {
		Article article = articleService.getArticleById(id);
		return new ResponseEntity<Article>(article, HttpStatus.OK);
	}
	@GetMapping("articles")
	public ResponseEntity<List<Article>> getAllArticles() {
		List<Article> list = articleService.getAllArticles();
		return new ResponseEntity<List<Article>>(list, HttpStatus.OK);
	}
	@PostMapping("article")
	public ResponseEntity<Void> addArticle(@RequestBody Article article, UriComponentsBuilder builder) {
                boolean flag = articleService.addArticle(article);
                if (flag == false) {
        	   return new ResponseEntity<Void>(HttpStatus.CONFLICT);
                }
                HttpHeaders headers = new HttpHeaders();
                headers.setLocation(builder.path("/article/{id}").buildAndExpand(article.getArticleId()).toUri());
                return new ResponseEntity<Void>(headers, HttpStatus.CREATED);
	}
	@PutMapping("article")
	public ResponseEntity<Article> updateArticle(@RequestBody Article article) {
		articleService.updateArticle(article);
		return new ResponseEntity<Article>(article, HttpStatus.OK);
	}
	@DeleteMapping("article/{id}")
	public ResponseEntity<Void> deleteArticle(@PathVariable("id") Integer id) {
		articleService.deleteArticle(id);
		return new ResponseEntity<Void>(HttpStatus.NO_CONTENT);
	}	
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
package com.concretepage.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.util.UriComponentsBuilder;
import com.concretepage.entity.Article;
import com.concretepage.service.IArticleService;
@Controller
@RequestMapping("user")
public class ArticleController {
	@Autowired
	private IArticleService articleService;
	@GetMapping("article/{id}")
	public ResponseEntity<Article> getArticleById(@PathVariable("id") Integer id) {
		Article article = articleService.getArticleById(id);
		return new ResponseEntity<Article>(article, HttpStatus.OK);
	}
	@GetMapping("articles")
	public ResponseEntity<List<Article>> getAllArticles() {
		List<Article> list = articleService.getAllArticles();
		return new ResponseEntity<List<Article>>(list, HttpStatus.OK);
	}
	@PostMapping("article")
	public ResponseEntity<Void> addArticle(@RequestBody Article article, UriComponentsBuilder builder) {
                boolean flag = articleService.addArticle(article);
                if (flag == false) {
        	   return new ResponseEntity<Void>(HttpStatus.CONFLICT);
                }
                HttpHeaders headers = new HttpHeaders();
                headers.setLocation(builder.path("/article/{id}").buildAndExpand(article.getArticleId()).toUri());
                return new ResponseEntity<Void>(headers, HttpStatus.CREATED);
	}
	@PutMapping("article")
	public ResponseEntity<Article> updateArticle(@RequestBody Article article) {
		articleService.updateArticle(article);
		return new ResponseEntity<Article>(article, HttpStatus.OK);
	}
	@DeleteMapping("article/{id}")
	public ResponseEntity<Void> deleteArticle(@PathVariable("id") Integer id) {
		articleService.deleteArticle(id);
		return new ResponseEntity<Void>(HttpStatus.NO_CONTENT);
	}	
}

MyApplication.java

要启动网络服务，请在java应用程序中运行以下类。

复制代码

1
2
3
4
5
6
7
8
9
10
package com.concretepage;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class MyApplication {  
	public static void main(String[] args) {
	   SpringApplication.run(MyApplication.class, args);
        }       
}

RestClientUtil.java

为了测试REST网络服务，在java应用程序中运行下面的类。

复制代码

package com.concretepage.client;
import java.net.URI;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpMethod;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.client.RestTemplate;
import com.concretepage.entity.Article;
public class RestClientUtil {
    public void getArticleByIdDemo() {
    	HttpHeaders headers = new HttpHeaders();
    	headers.setContentType(MediaType.APPLICATION_JSON);
        RestTemplate restTemplate = new RestTemplate();
	String url = "http://localhost:8080/user/article/{id}";
        HttpEntity<String> requestEntity = new HttpEntity<String>(headers);
        ResponseEntity<Article> responseEntity = restTemplate.exchange(url, HttpMethod.GET, requestEntity, Article.class, 1);
        Article article = responseEntity.getBody();
        System.out.println("Id:"+article.getArticleId()+", Title:"+article.getTitle()
                 +", Category:"+article.getCategory());      
    }
    public void getAllArticlesDemo() {
	HttpHeaders headers = new HttpHeaders();
	headers.setContentType(MediaType.APPLICATION_JSON);
        RestTemplate restTemplate = new RestTemplate();
	String url = "http://localhost:8080/user/articles";
        HttpEntity<String> requestEntity = new HttpEntity<String>(headers);
        ResponseEntity<Article[]> responseEntity = restTemplate.exchange(url, HttpMethod.GET, requestEntity, Article[].class);
        Article[] articles = responseEntity.getBody();
        for(Article article : articles) {
              System.out.println("Id:"+article.getArticleId()+", Title:"+article.getTitle()
                      +", Category: "+article.getCategory());
        }
    }
    public void addArticleDemo() {
    	HttpHeaders headers = new HttpHeaders();
    	headers.setContentType(MediaType.APPLICATION_JSON);
        RestTemplate restTemplate = new RestTemplate();
	String url = "http://localhost:8080/user/article";
	Article objArticle = new Article();
	objArticle.setTitle("Spring REST Security using Hibernate");
	objArticle.setCategory("Spring");
        HttpEntity<Article> requestEntity = new HttpEntity<Article>(objArticle, headers);
        URI uri = restTemplate.postForLocation(url, requestEntity);
        System.out.println(uri.getPath());    	
    }
    public void updateArticleDemo() {
    	HttpHeaders headers = new HttpHeaders();
    	headers.setContentType(MediaType.APPLICATION_JSON);
        RestTemplate restTemplate = new RestTemplate();
	String url = "http://localhost:8080/user/article";
	Article objArticle = new Article();
	objArticle.setArticleId(1);
	objArticle.setTitle("Update:Java Concurrency");
	objArticle.setCategory("Java");
        HttpEntity<Article> requestEntity = new HttpEntity<Article>(objArticle, headers);
        restTemplate.put(url, requestEntity);
    }
    public void deleteArticleDemo() {
    	HttpHeaders headers = new HttpHeaders();
    	headers.setContentType(MediaType.APPLICATION_JSON);
        RestTemplate restTemplate = new RestTemplate();
	String url = "http://localhost:8080/user/article/{id}";
        HttpEntity<Article> requestEntity = new HttpEntity<Article>(headers);
        restTemplate.exchange(url, HttpMethod.DELETE, requestEntity, Void.class, 1);        
    }
    public static void main(String args[]) {
    	RestClientUtil util = new RestClientUtil();
        //util.getArticleByIdDemo();
    	//util.addArticleDemo();
    	//util.updateArticleDemo();
    	//util.deleteArticleDemo();
    	util.getAllArticlesDemo();    	
    }    
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
package com.concretepage.client;
import java.net.URI;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpMethod;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.client.RestTemplate;
import com.concretepage.entity.Article;
public class RestClientUtil {
    public void getArticleByIdDemo() {
    	HttpHeaders headers = new HttpHeaders();
    	headers.setContentType(MediaType.APPLICATION_JSON);
        RestTemplate restTemplate = new RestTemplate();
	String url = "http://localhost:8080/user/article/{id}";
        HttpEntity<String> requestEntity = new HttpEntity<String>(headers);
        ResponseEntity<Article> responseEntity = restTemplate.exchange(url, HttpMethod.GET, requestEntity, Article.class, 1);
        Article article = responseEntity.getBody();
        System.out.println("Id:"+article.getArticleId()+", Title:"+article.getTitle()
                 +", Category:"+article.getCategory());      
    }
    public void getAllArticlesDemo() {
	HttpHeaders headers = new HttpHeaders();
	headers.setContentType(MediaType.APPLICATION_JSON);
        RestTemplate restTemplate = new RestTemplate();
	String url = "http://localhost:8080/user/articles";
        HttpEntity<String> requestEntity = new HttpEntity<String>(headers);
        ResponseEntity<Article[]> responseEntity = restTemplate.exchange(url, HttpMethod.GET, requestEntity, Article[].class);
        Article[] articles = responseEntity.getBody();
        for(Article article : articles) {
              System.out.println("Id:"+article.getArticleId()+", Title:"+article.getTitle()
                      +", Category: "+article.getCategory());
        }
    }
    public void addArticleDemo() {
    	HttpHeaders headers = new HttpHeaders();
    	headers.setContentType(MediaType.APPLICATION_JSON);
        RestTemplate restTemplate = new RestTemplate();
	String url = "http://localhost:8080/user/article";
	Article objArticle = new Article();
	objArticle.setTitle("Spring REST Security using Hibernate");
	objArticle.setCategory("Spring");
        HttpEntity<Article> requestEntity = new HttpEntity<Article>(objArticle, headers);
        URI uri = restTemplate.postForLocation(url, requestEntity);
        System.out.println(uri.getPath());    	
    }
    public void updateArticleDemo() {
    	HttpHeaders headers = new HttpHeaders();
    	headers.setContentType(MediaType.APPLICATION_JSON);
        RestTemplate restTemplate = new RestTemplate();
	String url = "http://localhost:8080/user/article";
	Article objArticle = new Article();
	objArticle.setArticleId(1);
	objArticle.setTitle("Update:Java Concurrency");
	objArticle.setCategory("Java");
        HttpEntity<Article> requestEntity = new HttpEntity<Article>(objArticle, headers);
        restTemplate.put(url, requestEntity);
    }
    public void deleteArticleDemo() {
    	HttpHeaders headers = new HttpHeaders();
    	headers.setContentType(MediaType.APPLICATION_JSON);
        RestTemplate restTemplate = new RestTemplate();
	String url = "http://localhost:8080/user/article/{id}";
        HttpEntity<Article> requestEntity = new HttpEntity<Article>(headers);
        restTemplate.exchange(url, HttpMethod.DELETE, requestEntity, Void.class, 1);        
    }
    public static void main(String args[]) {
    	RestClientUtil util = new RestClientUtil();
        //util.getArticleByIdDemo();
    	//util.addArticleDemo();
    	//util.updateArticleDemo();
    	//util.deleteArticleDemo();
    	util.getAllArticlesDemo();    	
    }    
}

测试应用程序

为了测试该应用程序，首先在MySQL中创建表，如例子中给出的。现在我们可以通过以下方式运行REST网络服务。

1. 使用Eclipse

使用页面末尾的下载链接下载项目的源代码。

将该项目导入eclipse。

使用命令提示符，进入项目的根文件夹并运行。

复制代码

1
2
mvn clean eclipse:eclipse

然后在eclipse中刷新该项目。点击Run as -> Java Application来运行主类MyApplication。

Tomcat服务器将被启动。

2. 使用Maven命令

下载项目的源代码。使用命令提示符进入项目的根文件夹并运行命令。

复制代码

1
2
mvn spring-boot:run

Tomcat服务器将被启动。

3. 使用可执行的JAR

使用命令提示符，转到项目的根文件夹并运行该命令。

复制代码

1
2
mvn clean package

我们将在目标文件夹中得到可执行的spring-boot-demo-0.0.1-SNAPSHOT.jar。以下列方式运行这个JAR。

复制代码

1
2
java -jar target/spring-boot-demo-0.0.1-SNAPSHOT.jar

Tomcat服务器将被启动。

测试

现在我们已经准备好测试这个应用程序了。要运行客户端，在eclipse中进入RestClientUtil类，点击Run as -> Java Application。

我们也可以用Postman测试应用程序。
在这里插入图片描述

参考文献

【1】Spring Boot Reference Guide
【2】Spring Boot REST + JPA + Hibernate + MySQL Example
【3】Spring Boot JDBC Example

源码下载

提取码：mao4

spring-boot-jdbc-example.zip

最后

以上就是机智星月最近收集整理的关于【Spring Boot】Spring Boot JDBC 示例 | 数据库连接演示工具版本Maven 依赖数据源和连接池JdbcTemplate : 使用 @Autowired 进行依赖注入RowMapperJdbcTemplate：运行 SQL 查询事务管理Spring Boot REST + JDBC + MySQL CRUD 完整示例测试应用程序参考文献源码下载的全部内容，更多相关【Spring内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。

本文分类：# Spring Boot
浏览次数：101 次浏览
发布日期：2023-11-22 10:40:05
本文链接：https://www.kaopuke.com/article/k-p-k_13_u_23_o_10_fx_14__23_gw.html

【Spring Boot】Spring Boot JDBC 示例 | 数据库连接演示工具版本Maven 依赖数据源和连接池JdbcTemplate : 使用 @Autowired 进行依赖注入RowMapperJdbcTemplate：运行 SQL 查询事务管理Spring Boot REST + JDBC + MySQL CRUD 完整示例测试应用程序参考文献源码下载

文章目录

演示工具版本

Maven 依赖

数据源和连接池

JdbcTemplate : 使用 @Autowired 进行依赖注入

RowMapper

JdbcTemplate：运行 SQL 查询

a. JdbcTemplate.queryForObject :

b. JdbcTemplate.query:

c. JdbcTemplate.update:

CURD 示例

事务管理

Spring Boot REST + JDBC + MySQL CRUD 完整示例

项目结构

表：articles

pom.xml

application.properties

Article.java

ArticleRowMapper.java

IArticleDAO.java

ArticleDAO.java

IArticleService.java

ArticleService.java

ArticleController.java

MyApplication.java

RestClientUtil.java

测试应用程序

1. 使用Eclipse

2. 使用Maven命令

3. 使用可执行的JAR

测试

参考文献

源码下载

最后

评论列表共有 0 条评论

发表评论取消回复

文章目录

演示工具版本

Maven 依赖

数据源和连接池

JdbcTemplate : 使用 @Autowired 进行依赖注入

RowMapper

JdbcTemplate：运行 SQL 查询

a. JdbcTemplate.queryForObject :

b. JdbcTemplate.query:

c. JdbcTemplate.update:

CURD 示例

事务管理

Spring Boot REST + JDBC + MySQL CRUD 完整示例

项目结构

表：articles

pom.xml

application.properties

Article.java

ArticleRowMapper.java

IArticleDAO.java

ArticleDAO.java

IArticleService.java

ArticleService.java

ArticleController.java

MyApplication.java

RestClientUtil.java

测试应用程序

1. 使用Eclipse

2. 使用Maven命令

3. 使用可执行的JAR

测试

参考文献

源码下载

最后

相关文章

评论列表共有 0 条评论

发表评论 取消回复

微信扫一扫：分享

发表评论取消回复