一、前言
在 postgresSQL压缩版安装与基本命令 我们讲过如何部署,本章节我们讲解如何在springboot中使用postgresSQL，同时学习JPA使用。
二、引入依赖
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| <dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.22</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
|
三、application.yml
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
| spring:
datasource:
druid:
# 数据库访问配置, 使用druid数据源
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://localhost:5432/springboot?currentSchema=spring
username: spring
password: spring#123
# 连接池配置
initial-size: 5
min-idle: 5
max-active: 20
# 连接等待超时时间
max-wait: 30000
# 配置检测可以关闭的空闲连接间隔时间
time-between-eviction-runs-millis: 60000
# 配置连接在池中的最小生存时间
min-evictable-idle-time-millis: 300000
validation-query: select '1'
test-while-idle: true
test-on-borrow: false
test-on-return: false
# 打开PSCache，并且指定每个连接上PSCache的大小
pool-prepared-statements: true
max-open-prepared-statements: 20
max-pool-prepared-statement-per-connection-size: 20
# 配置监控统计拦截的filters, 去掉后监控界面sql无法统计, 'wall'用于防火墙
filters: stat,wall
# Spring监控AOP切入点，如x.y.z.service.*,配置多个英文逗号分隔
aop-patterns: com.wno704.boot.service.*
# WebStatFilter配置
web-stat-filter:
enabled: true
# 添加过滤规则
url-pattern: /*
# 忽略过滤的格式
exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
# StatViewServlet配置
stat-view-servlet:
enabled: true
# 访问路径为/druid时，跳转到StatViewServlet
url-pattern: /druid/*
# 是否能够重置数据
reset-enable: false
# 需要账号密码才能访问控制台
login-username: druid
login-password: druid123
# IP白名单
# allow: 127.0.0.1
# IP黑名单（共同存在时，deny优先于allow）
# deny: 192.168.1.218
# 配置StatFilter
filter:
stat:
log-slow-sql: true
jpa:
hibernate:
ddl-auto: update # 第一次建表create 后面用update，要不然每次重启都会新建表
show-sql: true
database: postgresql
properties:
hibernate:
dialect: org.hibernate.dialect.PostgreSQL9Dialect
temp:
use_jdbc_metadata_defaults: false
|
注意： 如果需要指定postgresql连接的schema，9.4开始通过关键字currentSchema指定 jdbc:postgresql://localhost:5432/springboot?currentSchema=myschema ；旧版本通过searchpath指定 jdbc:postgresql://localhost:5432/springboot?searchpath=myschema 。
四、编码
创建实体 Student.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| @Getter
@Setter
@Entity
@Table(name = "student",schema="spring")
public class Student implements Serializable {
private static final long serialVersionUID = -339516038496531943L;
@Id
@Column(name="sno")
private Integer sno;
@Column(name="sname")
private String name;
@Column(name="ssex")
private String sex;
}
|
创建dao：StudentDao.java
1
2
3
4
5
6
7
8
| @Component
public interface StudentDao extends JpaRepository<Student, Integer> {
Student findStudentBySno(int sno);
@Query(value = "select * from student where sname = :sname",nativeQuery = true)
List<Student> getByName(@Param("sname") String sname);
}
|
注意:
1.这里这里是interface，不是class。
2.JpaRepository里面的泛型，第一个是实体类，第二个是主键的类型。
3.由于JpaRepository里面已经有一些接口了，如deleteAll，findOne等， 我们直接调用即可。
4.我们也可以根据自己的情况来实现自己的接口,如上面的getByName方法，jpql语句和hql语句差不多，我们这里只需要写接口，不需要写实现，spring boot会帮忙自动实现，如果**@Query中有*号**，要在尾部加上nativeQuery = true。
创建service与实现：
StudentService.java
1
2
3
4
5
6
7
8
| public interface StudentService {
Student add(Student student);
Student update(Student student);
void deleteBysno(int sno);
List<Student> queryStudentsListMap();
Student queryStudentBySno(int sno);
List<Student> queryStudentByName(String name);
}
|
StudentServiceImpl.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
26
27
28
29
30
31
32
33
34
35
36
| @Service("studentService")
public class StudentServiceImpl implements StudentService {
@Autowired
private StudentDao studentDao;
@Override
public Student add(Student student) {
return this.studentDao.save(student);
}
@Override
public Student update(Student student) {
return this.studentDao.saveAndFlush(student);
}
@Override
public void deleteBysno(int sno) {
this.studentDao.deleteById(sno);
}
@Override
public List<Student> queryStudentsListMap() {
return this.studentDao.findAll();
}
@Override
public Student queryStudentBySno(int sno) {
return this.studentDao.findStudentBySno(sno);
}
@Override
public List<Student> queryStudentByName(String name) {
return this.studentDao.getByName(name);
}
}
|
创建controller:TestController.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
26
27
28
29
30
31
32
33
34
35
36
37
| @RestController
public class TestController {
@Autowired
private StudentService studentService;
@RequestMapping( value = "/querystudent", method = RequestMethod.GET)
public Student queryStudentBySno(int sno) {
return this.studentService.queryStudentBySno(sno);
}
@RequestMapping( value = "/querystudentByname", method = RequestMethod.GET)
public List<Student> querystudentByname(String name) {
return this.studentService.queryStudentByName(name);
}
@RequestMapping( value = "/addstudent", method = RequestMethod.POST)
public Student addstudent(Student student) {
return this.studentService.add(student);
}
@RequestMapping( value = "/updatestudent", method = RequestMethod.POST)
public Student updatestudent(Student student) {
return this.studentService.update(student);
}
@RequestMapping( value = "/queryallstudent", method = RequestMethod.GET)
public List<Student> queryallstudent() {
return this.studentService.queryStudentsListMap();
}
@RequestMapping( value = "/deletestudent", method = RequestMethod.GET)
public void deletestudent(int sno) {
this.studentService.deleteBysno(sno);
}
}
|
五、测试
5.1 增加与查询
增加
访问 http://127.0.0.1:8080/addstudent ,传入参数：sno=1&name=wno704&sex=1
![]()
查询
访问 http://127.0.0.1:8080/querystudentByname?name=wno704
![]()
5.2 修改
修改
访问 http://127.0.0.1:8080/updatestudent ,传入参数：sno=1&name=wno704--1&sex=1
![]()
验证
访问 http://127.0.0.1:8080/queryallstudent
![]()
5.3 删除
删除
访问 http://127.0.0.1:8080/deletestudent?sno=1
![]()
验证
访问 http://127.0.0.1:8080/queryallstudent
![]()
六、方法名匹配
在StudentServiceImpl中定义按照规则命名的方法，JPA可以将其自动转换为SQL，而免去手动编写的烦恼，比如定义如下方法：
Student findStudentBySno(int sno);
JPA会自动将其转换为如下的SQL：
select * from student where sno = ?
下面简单罗列方法命名规则：
| 关键词 | Demo | JPQL 语句片段 |
|---|
| And | findByLastnameAndFirstname | … where x.lastname = ?1 and x.firstname = ?2 |
| Or | findByLastnameOrFirstname | … where x.lastname = ?1 or x.firstname = ?2 |
| Is,Equals | findByFirstname,findByFirstnameIs,findByFirstnameEquals | … where x.firstname = ?1 |
| Between | findByStartDateBetween | … where x.startDate between ?1 and ?2 |
| LessThan | findByAgeLessThan | … where x.age < ?1 |
| LessThanEqual | findByAgeLessThanEqual | … where x.age ?? ?1 |
| GreaterThan | findByAgeGreaterThan | … where x.age > ?1 |
| GreaterThanEqual | findByAgeGreaterThanEqual | … where x.age >= ?1 |
| After | findByStartDateAfter | … where x.startDate > ?1 |
| Before | findByStartDateBefore | … where x.startDate < ?1 |
| IsNull | findByAgeIsNull | … where x.age is null |
| IsNotNull,NotNull | findByAge(Is)NotNull | … where x.age not null |
| Like | findByFirstnameLike | … where x.firstname like ?1 |
| NotLike | findByFirstnameNotLike | … where x.firstname not like ?1 |
| StartingWith | findByFirstnameStartingWith | … where x.firstname like ?1 (parameter bound with appended %) |
| EndingWith | findByFirstnameEndingWith | … where x.firstname like ?1 (parameter bound with prepended %) |
| Containing | findByFirstnameContaining | … where x.firstname like ?1 (parameter bound wrapped in %) |
| OrderBy | findByAgeOrderByLastnameDesc | … where x.age = ?1 order by x.lastname desc |
| Not | findByLastnameNot | … where x.lastname <> ?1 |
| In | findByAgeIn(Collection ages) | … where x.age in ?1 |
| NotIn | findByAgeNotIn(Collection age) | … where x.age not in ?1 |
| TRUE | findByActiveTrue() | … where x.active = true |
| FALSE | findByActiveFalse() | … where x.active = false |
| IgnoreCase | findByFirstnameIgnoreCase | … where UPPER(x.firstame) = UPPER(?1) |
