一、前言

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 = ?

下面简单罗列方法命名规则:

关键词DemoJPQL 语句片段
AndfindByLastnameAndFirstname… where x.lastname = ?1 and x.firstname = ?2
OrfindByLastnameOrFirstname… where x.lastname = ?1 or x.firstname = ?2
Is,EqualsfindByFirstname,findByFirstnameIs,findByFirstnameEquals… where x.firstname = ?1
BetweenfindByStartDateBetween… where x.startDate between ?1 and ?2
LessThanfindByAgeLessThan… where x.age < ?1
LessThanEqualfindByAgeLessThanEqual… where x.age ?? ?1
GreaterThanfindByAgeGreaterThan… where x.age > ?1
GreaterThanEqualfindByAgeGreaterThanEqual… where x.age >= ?1
AfterfindByStartDateAfter… where x.startDate > ?1
BeforefindByStartDateBefore… where x.startDate < ?1
IsNullfindByAgeIsNull… where x.age is null
IsNotNull,NotNullfindByAge(Is)NotNull… where x.age not null
LikefindByFirstnameLike… where x.firstname like ?1
NotLikefindByFirstnameNotLike… where x.firstname not like ?1
StartingWithfindByFirstnameStartingWith… where x.firstname like ?1 (parameter bound with appended %)
EndingWithfindByFirstnameEndingWith… where x.firstname like ?1 (parameter bound with prepended %)
ContainingfindByFirstnameContaining… where x.firstname like ?1 (parameter bound wrapped in %)
OrderByfindByAgeOrderByLastnameDesc… where x.age = ?1 order by x.lastname desc
NotfindByLastnameNot… where x.lastname <> ?1
InfindByAgeIn(Collection ages)… where x.age in ?1
NotInfindByAgeNotIn(Collection age)… where x.age not in ?1
TRUEfindByActiveTrue()… where x.active = true
FALSEfindByActiveFalse()… where x.active = false
IgnoreCasefindByFirstnameIgnoreCase… where UPPER(x.firstame) = UPPER(?1)