sql数据库JDBCTemplate和JPA使用

《SpringBoot实战派》一书第八章用ORM操作SQL数据库，具体操作过程和踩坑实录。 第一，由于本台计算机上没有安装mysql，在练习书中代码的时候，误以为本机不需要数据库便可以运行代码，导致卡了好久。 第二，把当时学习Mysql重拾起来，算是学以致用。 第三，遇到问题就一步一步解决问题，有挫败感正常，学会越挫越勇，转移挫败感逐渐找回信心直到正反馈的出现。任何人学习代码都是这个过程。

文章目录

0.认识JDBC Template1.使用JDBCTemplate实现数据的增加、删除、修改和查询2.用JPA构建mysql实体数据表

0.认识JDBC Template

JDBC(Java DataBase Connectivity)是Java用于连接数据库的规范，也就是用于执行数据库SQL语句的Java API。它是由一组用Java语言编写的类和接口组成，为大部分关系型数据库提供访问接口。

JDBC需要每次进行数据库连接，然后处理SQL语句、传值、关闭数据库。如果都由开发人员编写代码，则很容易出错，可能会出现使用完成之后，数据库连接忘记关闭的情况。这容易导致连接被占用而降低性能，为了减少这种可能的错误，减少开发人员的工作量，JDBCTemplate被设计出来。

JDBCTemplate JDBCTemplate=JDBC+Template，是对JDBC的封装。它更便于程序实现，替我们完成所有的JDBC底层工作。因此，对于数据库的操作，再不需要每次都进行连接、打开、关闭了。

JDBC和JDBCTemplate就像仓库管理员，负责从仓库中存取物品。而后者不需要每次进入都开门，取完关门，因为有电动门自动控制。

1.使用JDBCTemplate实现数据的增加、删除、修改和查询

项目结构 添加依赖 要使用JDBCTemplate，则需要添加其Starter依赖。因为要操作数据库，所有也需要配置数据库(以mysql为例)的连接依赖

<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> <version>8.0.20</version> </dependency>

添加完依赖之后，还需要配置数据库的连接信息。这样JDBC才能正常连接到数据库。在application.properties配置文件中配置数据库的地址和用户信息

//配置ip地址，编码，时区和SSL spring.datasource.url=jdbc:mysql://127.0.0.1/book?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=true //用户名 spring.datasource.username=root //密码 spring.datasource.password= spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

User.java 新建一个测试实体类User，实现RowMapper类，重写mapRow方法，以便实现字段和数据表字段映射。映射是指把Java中设置的实体字段和mysql数据库的字段对应起来，因为实体id可以对应数据库字段的u_id，也可以对应id、那么等。如果不重写，则程序不知道如何对应。

package com.example.demo.model; import lombok.Data; import org.springframework.jdbc.core.RowMapper; import java.sql.ResultSet; import java.sql.SQLException; @Data public class User implements RowMapper<User> { private int id; private String username; private String password; //必须重写mapRow方法 @Override public User mapRow(ResultSet resultSet,int i) throws SQLException{ User user=new User(); user.setId(resultSet.getInt("id")); user.setUsername(resultSet.getString("username")); user.setPassword(resultSet.getString("password")); return user; } }

JDBCTemplate提供了以下操作数据的3个方法

方法含义execute表示执行，用于直接执行sql语句update表示更新，包括新增、修改、删除操作query表示查询

1）创建数据表 在使用JDBCTemplate之前，需要在控制器中注入JDBCTemplate，然后可以通过execute方法执行sql语句

2）添加数据 通过update方法

3)查询修改删除数据 执行sql字符串里面的SQL语句，通过query，update方法

UserController.java

package com.example.demo.controller; import com.example.demo.model.User; import org.omg.PortableInterceptor.SYSTEM_EXCEPTION; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.ArrayList; import java.util.List; import java.util.Map; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Value; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit4.SpringRunner; import static org.junit.Assert.*; @SpringBootTest @RunWith(SpringRunner.class) /** * @author longzhonghua * @data 2/24/2019 9:51 AM */ @RestController @RequestMapping("user") public class UserController { @Autowired private JdbcTemplate jdbcTemplate; @Test //创建数据表 @GetMapping("createUserTable") public void createUserTable() throws Exception { String sql = "CREATE TABLE `user` (\n" + " `id` int(10) NOT NULL AUTO_INCREMENT,\n" + " `username` varchar(100) DEFAULT NULL,\n" + " `password` varchar(100) DEFAULT NULL,\n" + " PRIMARY KEY (`id`)\n" + ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;\n" + "\n"; jdbcTemplate.execute(sql); } @Test //saveUserTest //添加一个测试数据 @GetMapping("saveUserTest") public void saveUserTest()throws Exception { String sql = "INSERT INTO user (USERNAME,PASSWORD) VALUES ('longzhiran','123456')"; int rows = jdbcTemplate.update(sql); System.out.println(rows); } @Test //updateUserPassword?id=1&passWord=12345678 @GetMapping("updateUserPassword") public void updateUserPassword() throws Exception { Integer id=1; String passWord="999888"; String sql = "UPDATE user SET PASSWORD = ? WHERE ID = ?"; int rows = jdbcTemplate.update(sql, passWord, id); System.out.println(rows); } @Test //deleteUserById?id=1 @GetMapping("deleteUserById") public void deleteUserById() throws Exception { String sql = "DELETE FROM user WHERE ID = ?"; int rows = jdbcTemplate.update(sql, 1); System.out.println(rows); } @Test //getUserByName?userName=longzhiran @GetMapping("getUserByName") public void getUserByName()throws Exception { String name="longzhonghua"; String sql = "SELECT * FROM user WHERE USERNAME = ?"; List<User> list = jdbcTemplate.query(sql, new User(), new Object[]{name}); for(User user:list){ System.out.println(user); } } @Test //getAll @GetMapping("list") public void list() throws Exception { String sql = "SELECT * FROM user limit 1000"; List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper(User.class)); for(User userLists:userList){ System.out.println(userLists); } } }

运行项目 然后运行test创建数据表 打开book数据库，发现多了一张名为user的表

运行测试 在mysql中查看，发现多了一个用户 运行测试

在mysql中查看，密码已经修改

运行测试

在mysql中查看 id=1的用户信息已经删除

2.用JPA构建mysql实体数据表

第一步：安装mysql，这里笔者安装的是mysql 8.0.20 安装请移步笔者的另一篇博客：Mysql8.0.20下载和安装

第二步：创建数据库book

第三步就是IDEA中的操作

0.添加依赖 需要注意的是：注意mysql的版本需要设置正确

<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.20</version> <scope>runtime</scope> </dependency>

1.配置文件

这里的数据库用户名和密码是自己的，一般为root和root

spring.datasource.url=jdbc:mysql://127.0.0.1/book?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=true spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.jpa.properties.hibernate.hbm2ddl.auto=update spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect spring.jpa.show-sql= true spring.thymeleaf.cache=false server.port=8080

当时报错：

org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

学习《Spring Boot实战派》时由于没有本台电脑上没有安装mysql，所以自然连不上，需要先安装mysql 然后创建数据库book。

后面踩过的坑 遇到问题

o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: S1009 o.h.engine.jdbc.spi.SqlExceptionHelper : Unable to load authentication plugin 'caching_sha2_password'.

解决方法：更改mysql依赖的版本

然后接着报错

o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 08001 o.h.engine.jdbc.spi.SqlExceptionHelper : Public Key Retrieval is not allowed

解决方案：cmd中登录mysql数据库 mysql -u root -p 输入密码

又遇到错误

o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1049, SQLState: 42000 o.h.engine.jdbc.spi.SqlExceptionHelper : Unknown database 'book'

新建数据库book，使得本机存在book数据库

Article.java

package com.example.demo.entity; import com.sun.org.apache.xpath.internal.operations.Bool; import lombok.Data; import javax.persistence.*; import javax.validation.constraints.NotEmpty; import javax.validation.constraints.Size; import java.io.Serializable; import java.util.Arrays; import java.util.List; @Entity @Data public class Article implements Serializable{ @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private long id; @Column(nullable = false,unique = true) @NotEmpty(message = "标题不能为空") private String title; @Column(columnDefinition = "enum('图','图文','文')") private String type; private Boolean available=Boolean.FALSE; @Size(min=0,max=20) private String keyword; @Size(max=255) private String description; @Column(nullable = false) private String body; @Transient private List keywordlists; public List getKeywordlists(){ return Arrays.asList(this.keyword.trim().split("|")); } public void setKeywordlists(List keywordlists){ this.keywordlists=keywordlists; } }

运行项目之后会生成数据表如下图