springboot2+jpa+hikari+oracle实战

1.技术选型

jpa：简单方便，基于Spring构建，简化了持久层开发，只要继承Repository就有简单的增删改查功能了，且自带分页功能

hikari：最快的数据库连接池，springboot2自带，不需要用到监控页面的话推荐使用hikari

oracle：生产环境最常用

2.代码实例

主要依赖pom.xml:

 1     <dependencies>
 2         <dependency>
 3             <groupId>org.springframework.boot</groupId>
 4             <artifactId>spring-boot-starter-web</artifactId>
 5         </dependency>
 6         <dependency>
 7             <groupId>org.springframework.boot</groupId>
 8             <artifactId>spring-boot-starter-data-jpa</artifactId>
 9         </dependency>
10         <dependency>
11             <groupId>com.oracle</groupId>
12             <artifactId>ojdbc7</artifactId>
13             <version>12.1.0.1.0</version>
14         </dependency>
15         <!--很不错的一个工具类库-hutool，推荐使用-->
16         <dependency>
17             <groupId>cn.hutool</groupId>
18             <artifactId>hutool-all</artifactId>
19             <version>5.4.2</version>
20         </dependency>

配置文件application.yml:

 1 spring:
 2   datasource:
 3     ##使用hikari数据源
 4     type: com.zaxxer.hikari.HikariDataSource
 5     ##使用oracle连接驱动
 6     driver-class-name: oracle.jdbc.driver.OracleDriver
 7     ##数据库连接地址
 8     url: jdbc:oracle:thin:@000.000.000.000:1520:orcl
 9     ##数据库连接用户名
10     username: xxx
11     ##数据库连接密码
12     password: xxxxxx
13     hikari:
14       ##池中维护的最小空闲连接数
15       minimum-idle: 5
16       ##池中最大连接数，包括闲置和使用中的连接
17       maximum-pool-size: 15
18       ##自动提交从池中返回的连接
19       auto-commit: true
20       ##连接允许在池中闲置的最长时间
21       idle-timeout: 30000
22       ##连接池的用户定义名称，主要出现在日志记录和JMX管理控制台中以识别池和池配置
23       pool-name: DatebookHikariCP
24       ##池中连接最长生命周期
25       max-lifetime: 18000000
26       ##等待来自池的连接的最大毫秒数
27       connection-timeout: 30000
28       ##验证该连接是否是有效的查询语句
29       connection-test-query: select 1 from dual

jpa自动生成实体类TestUserEntity:

 1 package com.project.demo.spring.boot.model;
 2 
 3 import javax.persistence.*;
 4 import java.util.Objects;
 5 
 6 /**
 7  * @author:wk
 8  * @date:2020/9/16
 9  */
10 @Entity
11 @Table(name = "TEST_USER", schema = "TW_E", catalog = "")
12 public class TestUserEntity {
13     private String name;
14     private String password;
15     private String phoneNo;
16     private String remark;
17     private String id;
18 
19     @Basic
20     @Column(name = "NAME")
21     public String getName() {
22         return name;
23     }
24 
25     public void setName(String name) {
26         this.name = name;
27     }
28 
29     @Basic
30     @Column(name = "PASSWORD")
31     public String getPassword() {
32         return password;
33     }
34 
35     public void setPassword(String password) {
36         this.password = password;
37     }
38 
39     @Basic
40     @Column(name = "PHONE_NO")
41     public String getPhoneNo() {
42         return phoneNo;
43     }
44 
45     public void setPhoneNo(String phoneNo) {
46         this.phoneNo = phoneNo;
47     }
48 
49     @Basic
50     @Column(name = "REMARK")
51     public String getRemark() {
52         return remark;
53     }
54 
55     public void setRemark(String remark) {
56         this.remark = remark;
57     }
58 
59     @Override
60     public boolean equals(Object o) {
61         if (this == o) return true;
62         if (o == null || getClass() != o.getClass()) return false;
63         TestUserEntity that = (TestUserEntity) o;
64         return Objects.equals(name, that.name) &&
65                 Objects.equals(password, that.password) &&
66                 Objects.equals(phoneNo, that.phoneNo) &&
67                 Objects.equals(remark, that.remark);
68     }
69 
70     @Override
71     public int hashCode() {
72         return Objects.hash(name, password, phoneNo, remark);
73     }
74 
75     @Id
76     @Column(name = "ID")
77     public String getId() {
78         return id;
79     }
80 
81     public void setId(String id) {
82         this.id = id;
83     }
84 }

创建TestUserRepository，继承JpaRepository,可实现简单的增删改查，TestUserEntity和String参数为实体类和主键，加@Query注解实现自定义查询语句:

 1 package com.project.demo.spring.boot.repository;
 2 
 3 import com.project.demo.spring.boot.model.TestUserEntity;
 4 import org.springframework.data.jpa.repository.JpaRepository;
 5 import org.springframework.data.jpa.repository.Query;
 6 import org.springframework.data.repository.query.Param;
 7 
 8 import java.util.List;
 9 
10 /**
11  * @author:wk
12  * @date:2020/9/16
13  */
14 public interface TestUserRepository extends JpaRepository<TestUserEntity, String> {
15 
16     /**
17      * 自定义查询语句接口
18      *
19      * @param name
20      * @return
21      */
22     @Query(nativeQuery = true, value = "SELECT * FROM TEST_USER WHERE name = :name  ")
23     List<TestUserEntity> findUserByName(@Param("name") String name);
24 }

测试用的controller:

 1 package com.project.demo.spring.boot.controller;
 2 
 3 import cn.hutool.core.collection.CollectionUtil;
 4 import com.project.demo.spring.boot.model.TestUserEntity;
 5 import com.project.demo.spring.boot.repository.TestUserRepository;
 6 import org.springframework.beans.factory.annotation.Autowired;
 7 import org.springframework.stereotype.Controller;
 8 import org.springframework.web.bind.annotation.*;
 9 
10 import java.util.List;
11 
12 /**
13  * @author:wk
14  * @date:2020/9/11
15  */
16 @ResponseBody
17 @Controller
18 @RequestMapping("/demo")
19 public class TestUserController {
20     @Autowired
21     private TestUserRepository testUserRepository;
22 
23     @GetMapping("/getAllUser")
24     public List<TestUserEntity> getAllUser() {
25         List<TestUserEntity> allUser = testUserRepository.findAll();
26         return allUser;
27     }
28 
29     @PostMapping("/saveAllUser")
30     public List<TestUserEntity> saveAllUser(@RequestBody List<TestUserEntity> resource) {
31         List<TestUserEntity> allUser = CollectionUtil.newArrayList(resource);
32         List<TestUserEntity> successUser = testUserRepository.saveAll(allUser);
33         return successUser;
34     }
35 
36     @DeleteMapping("/deleteUserById")
37     public String deleteUserById(@RequestBody String ids) {
38         List<String> idList = CollectionUtil.toList(ids.split(","));
39         idList.parallelStream().forEach(id -> testUserRepository.deleteById(id));
40         return "success";
41     }
42 
43     @GetMapping("/findUserByName")
44     public List<TestUserEntity> findUserByName(@RequestBody String name) {
45         List<TestUserEntity> allUser = testUserRepository.findUserByName(name);
46         return allUser;
47     }
48 
49 }

3.演示

新增两个用户，添加成功：

获取到所有的user：

调用自定义查询接口，通过name查找用户：

删除id为1和2的用户

成功删除后查询为空：