repository相关
# 自定义 SQL 查询
# hql方式查询
@Query("select u from User u")
Page<User> findALL(Pageable pageable);
1
2
2
# 原生sql查询
参数从1开始算起、还可以使用 @Param 来支持。
分页参数放在最后
@Query("select * from user u where u.nick_name = ?1", nativeQuery = true)
Page<User> findByNickName(String nickName, Pageable pageable);
@Transactional(timeout = 10)
@Modifying
@Query("update User set userName = ?1 where id = ?2")
int modifyById(String userName, Long id);
@Transactional
@Modifying
@Query("delete from User where id = ?1")
void deleteById(Long id);
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# 分页
@Test
public void testPageQuery() {
int page=1,size=2;
Sort sort = new Sort(Sort.Direction.DESC, “id”);
Pageable pageable = PageRequest.of(page, size, sort);
userRepository.findALL(pageable);
userRepository.findByNickName(“aa”, pageable);
}
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 限制查询
应用场景：只返回前面X条数据
User findFirstByOrderByLastnameAsc();
User findTopByOrderByAgeDesc();
Page<User> queryFirst10ByLastname(String lastname, Pageable pageable);
List<User> findFirst10ByLastname(String lastname, Sort sort);
List<User> findTop10ByLastname(String lastname, Pageable pageable);
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 多表查询
多表查询在JPA 中有两种实现方式，第一种是利用 Hibernate 的级联查询来实现，第二种是创建一个结果集的接口来接收连表查询后的结果
public interface UserInfo {
String getUserName();
String getEmail();
String getAddress();
String getHobby();
}
1
2
3
4
5
6
2
3
4
5
6
@Query("select u.userName as userName, u.email as email, d.introduction as introduction , d.hobby as hobby from User u , UserDetail d " + "where u.id=d.userId and d.hobby = ?1 ")
List<UserInfo> findUserInfo(String hobby);
1
2
2
# 复杂条件查询
JpaSpecificationExecutor 是 JPA 2.0 提供的 Criteria API 的使用封装，可以用于动态生成 Query 来满足我们业务中的各种复杂场景。
- Root root，代表了可以查询和操作的实体对象的根，开一个通过 get(“属性名”) 来获取对应的值。
- CriteriaQuery query，代表一个 specific 的顶层查询对象，它包含着查询的各个部分，比如 select 、from、where、group by、order by 等。
- CriteriaBuilder cb，来构建 CritiaQuery 的构建器对象，其实就相当于条件或者是条件组合，并以 Predicate 的形式返回。
@Service
public class UserDetailServiceImpl implements UserDetailService{
@Resource
private UserDetailRepository userDetailRepository;
// detailParam为前端传过来的对象
@Override
public Page<UserDetail> findByCondition(UserDetailParam detailParam, Pageable pageable){
return userDetailRepository.findAll((root, query, cb) -> {
List<Predicate> predicates = new ArrayList<Predicate>();
//equal 示例
if (!StringUtils.isNullOrEmpty(detailParam.getIntroduction())){
predicates.add(cb.equal(root.get("introduction"),detailParam.getIntroduction()));
}
//like 示例
if (!StringUtils.isNullOrEmpty(detailParam.getRealName())){
predicates.add(cb.like(root.get("realName"),"%"+detailParam.getRealName()+"%"));
}
//between 示例
if (detailParam.getMinAge()!=null && detailParam.getMaxAge()!=null) {
Predicate agePredicate = cb.between(root.get("age"), detailParam.getMinAge(), detailParam.getMaxAge());
predicates.add(agePredicate);
}
//greaterThan 大于等于示例
if (detailParam.getMinAge()!=null){
predicates.add(cb.greaterThan(root.get("age"),detailParam.getMinAge()));
}
return query.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
}, pageable);
}
}
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
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
问题？
前端哪些值的问题、 值的判断问题（相等、模糊查询、in等等）、数据权限的问题（）
# 1、定义注解
@DataPermission @Query
/**
* 查询注解，交由处理
* @author Zheng Jie
* @date 2019-6-4 13:52:30
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Query {
// Dong ZhaoYang 2017/8/7 基本对象的属性名
String propName() default "";
// Dong ZhaoYang 2017/8/7 查询方式
Type type() default Type.EQUAL;
/**
* 连接查询的属性名，如User类中的dept
*/
String joinName() default "";
/**
* 默认左连接
*/
Join join() default Join.LEFT;
/**
* 多字段模糊搜索，仅支持String类型字段，多个用逗号隔开, 如@Query(blurry = "email,username")
*/
String blurry() default "";
enum Type {
// jie 2019/6/4 相等
EQUAL
// Dong ZhaoYang 2017/8/7 大于等于
, GREATER_THAN
// Dong ZhaoYang 2017/8/7 小于等于
, LESS_THAN
// Dong ZhaoYang 2017/8/7 中模糊查询
, INNER_LIKE
// Dong ZhaoYang 2017/8/7 左模糊查询
, LEFT_LIKE
// Dong ZhaoYang 2017/8/7 右模糊查询
, RIGHT_LIKE
// Dong ZhaoYang 2017/8/7 小于
, LESS_THAN_NQ
// jie 2019/6/4 包含
, IN
// 不包含
, NOT_IN
// 不等于
,NOT_EQUAL
// between
,BETWEEN
// 不为空
,NOT_NULL
// 为空
,IS_NULL
}
/**
* @author Zheng Jie
* 适用于简单连接查询，复杂的请自定义该注解，或者使用sql查询
*/
enum Join {
/** jie 2019-6-4 13:18:30 */
LEFT, RIGHT, INNER
}
}
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
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
# 2、定义查询条件的类
@Data
@DataPermission(fieldName = "id")
public class DeptQueryCriteria{
@Query(type = Query.Type.INNER_LIKE)
private String name;
@Query
private Boolean enabled;
@Query
private Long pid;
@Query(type = Query.Type.IS_NULL, propName = "pid")
private Boolean pidIsNull;
@Query(type = Query.Type.BETWEEN)
private List<Timestamp> createTime;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 3、写service
# 4、工具类
/**
* 参考文章: https://www.jianshu.com/p/d9a99b95d094
* @author Zheng Jie
* @date 2019-6-4 14:59:48
*/
@Slf4j
@SuppressWarnings({"unchecked","all"})
public class QueryHelp {
/**
* 构造查询条件
* root ：Root接口，代表查询的根对象，可以通过root获取实体中的属性
* query ：代表一个顶层查询对象，用来自定义查询
* cb ：用来构建查询，此对象里有很多条件方法
* @param <R>
* @param <Q>
* @param root
* @param query
* @param cb
* @return
*/
public static <R, Q> Predicate getPredicate(Root<R> root, Q query, CriteriaBuilder cb) {
List<Predicate> list = new ArrayList<>();
if(query == null){
return cb.and(list.toArray(new Predicate[0]));
}
// 数据权限验证
DataPermission permission = query.getClass().getAnnotation(DataPermission.class);
if(permission != null){
// 获取数据权限
List<Long> dataScopes = SecurityUtils.getCurrentUserDataScope();
if(CollectionUtil.isNotEmpty(dataScopes)){
if(StringUtils.isNotBlank(permission.joinName()) && StringUtils.isNotBlank(permission.fieldName())) {
Join join = root.join(permission.joinName(), JoinType.LEFT);
list.add(getExpression(permission.fieldName(),join, root).in(dataScopes));
} else if (StringUtils.isBlank(permission.joinName()) && StringUtils.isNotBlank(permission.fieldName())) {
list.add(getExpression(permission.fieldName(),null, root).in(dataScopes));
}
}
}
try {
List<Field> fields = getAllFields(query.getClass(), new ArrayList<>());
for (Field field : fields) {
boolean accessible = field.isAccessible();
// 设置对象的访问权限，保证对private的属性的访
field.setAccessible(true);
Query q = field.getAnnotation(Query.class);
if (q != null) {
String propName = q.propName();
String joinName = q.joinName();
String blurry = q.blurry();
String attributeName = isBlank(propName) ? field.getName() : propName;
Class<?> fieldType = field.getType();
Object val = field.get(query);
if (ObjectUtil.isNull(val) || "".equals(val)) {
continue;
}
Join join = null;
// 模糊多字段
if (ObjectUtil.isNotEmpty(blurry)) {
String[] blurrys = blurry.split(",");
List<Predicate> orPredicate = new ArrayList<>();
for (String s : blurrys) {
orPredicate.add(cb.like(root.get(s)
.as(String.class), "%" + val.toString() + "%"));
}
Predicate[] p = new Predicate[orPredicate.size()];
list.add(cb.or(orPredicate.toArray(p)));
continue;
}
if (ObjectUtil.isNotEmpty(joinName)) {
String[] joinNames = joinName.split(">");
for (String name : joinNames) {
switch (q.join()) {
case LEFT:
if(ObjectUtil.isNotNull(join) && ObjectUtil.isNotNull(val)){
join = join.join(name, JoinType.LEFT);
} else {
join = root.join(name, JoinType.LEFT);
}
break;
case RIGHT:
if(ObjectUtil.isNotNull(join) && ObjectUtil.isNotNull(val)){
join = join.join(name, JoinType.RIGHT);
} else {
join = root.join(name, JoinType.RIGHT);
}
break;
case INNER:
if(ObjectUtil.isNotNull(join) && ObjectUtil.isNotNull(val)){
join = join.join(name, JoinType.INNER);
} else {
join = root.join(name, JoinType.INNER);
}
break;
default: break;
}
}
}
switch (q.type()) {
case EQUAL:
list.add(cb.equal(getExpression(attributeName,join,root)
.as((Class<? extends Comparable>) fieldType),val));
break;
case GREATER_THAN:
list.add(cb.greaterThanOrEqualTo(getExpression(attributeName,join,root)
.as((Class<? extends Comparable>) fieldType), (Comparable) val));
break;
case LESS_THAN:
list.add(cb.lessThanOrEqualTo(getExpression(attributeName,join,root)
.as((Class<? extends Comparable>) fieldType), (Comparable) val));
break;
case LESS_THAN_NQ:
list.add(cb.lessThan(getExpression(attributeName,join,root)
.as((Class<? extends Comparable>) fieldType), (Comparable) val));
break;
case INNER_LIKE:
list.add(cb.like(getExpression(attributeName,join,root)
.as(String.class), "%" + val.toString() + "%"));
break;
case LEFT_LIKE:
list.add(cb.like(getExpression(attributeName,join,root)
.as(String.class), "%" + val.toString()));
break;
case RIGHT_LIKE:
list.add(cb.like(getExpression(attributeName,join,root)
.as(String.class), val.toString() + "%"));
break;
case IN:
if (CollUtil.isNotEmpty((Collection<Object>)val)) {
list.add(getExpression(attributeName,join,root).in((Collection<Object>) val));
}
break;
case NOT_IN:
if (CollUtil.isNotEmpty((Collection<Object>)val)) {
list.add(getExpression(attributeName,join,root).in((Collection<Object>) val).not());
}
break;
case NOT_EQUAL:
list.add(cb.notEqual(getExpression(attributeName,join,root), val));
break;
case NOT_NULL:
list.add(cb.isNotNull(getExpression(attributeName,join,root)));
break;
case IS_NULL:
list.add(cb.isNull(getExpression(attributeName,join,root)));
break;
case BETWEEN:
List<Object> between = new ArrayList<>((List<Object>)val);
list.add(cb.between(getExpression(attributeName, join, root).as((Class<? extends Comparable>) between.get(0).getClass()),
(Comparable) between.get(0), (Comparable) between.get(1)));
break;
default: break;
}
}
field.setAccessible(accessible);
}
} catch (Exception e) {
log.error(e.getMessage(), e);
}
int size = list.size();
return cb.and(list.toArray(new Predicate[size]));
}
@SuppressWarnings("unchecked")
private static <T, R> Expression<T> getExpression(String attributeName, Join join, Root<R> root) {
if (ObjectUtil.isNotEmpty(join)) {
return join.get(attributeName);
} else {
return root.get(attributeName);
}
}
private static boolean isBlank(final CharSequence cs) {
int strLen;
if (cs == null || (strLen = cs.length()) == 0) {
return true;
}
for (int i = 0; i < strLen; i++) {
if (!Character.isWhitespace(cs.charAt(i))) {
return false;
}
}
return true;
}
public static List<Field> getAllFields(Class clazz, List<Field> fields) {
if (clazz != null) {
fields.addAll(Arrays.asList(clazz.getDeclaredFields()));
getAllFields(clazz.getSuperclass(), fields);
}
return fields;
}
}
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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
参考文章 ：