SpringDataJpa动态Sql
前言
案例github地址（如果有用点个star呗） https://github.com/chenxiban/BlogCaseSet.git
Spring Data JPA大大的简化了我们持久层的开发，但是实际应用中，我们还是需要动态查询的。 比如，前端有多
个条件，这些条件很多都是可选的，那么后端的SQL，就应该是可以定制的，在使用Hibernate的时候，可以通过
判断条件来拼接SQL(HQL)，当然，Spring Data JPA在简化我们开发的同时，也是提供了相应的支持。所以，动态 SQL常用于多条件的查询。
如何使用动态Sql
在查询某些数据的过程中，有可能只需要某些字段，而不是全部字段，这就需要我们使用动态sql，在使用的过程
中需要我们的Repository接口继承JpaSpecificationExecutor接口，然后查询的时候，传入动态查询参数，分页参
数等即可，同样的在创建项目的过程中属性文件，pom.xml文件，项目主类等通用代码将省略步子书写，具体步骤
如下：
编写实体类
在com.cyj.springboot.entity下编写Cat实体类，代码如下
package com.cyj.springboot.entity;
import java.io.Serializable;
import java.sql.Timestamp;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OrderBy;
import javax.persistence.Table;
import javax.persistence.Transient;
@Entity // HQL 使用,默认类名
@Table(name="cattb") //数据库原生SQL使用,默认表名
public class Cat implements Serializable{
@Id //实体类的主键
@GeneratedValue //自动增长列
@OrderBy //数据加载顺序
@Column(columnDefinition="int unsigned NOT NULL comment '备注:猫自动增长主键' ")
private Integer id;
@Column(length=20) //字符长度20
private String name;
@Column(columnDefinition="char(1) comment '备注:猫姓名' ")
private String sex;
@Column(columnDefinition="int unsigned DEFAULT 0 comment '备注:猫年龄' ")
private Integer age;
private Date birthday;
// @CreationTimestamp@UpdateTimestamp //插入,修改时自动维护时间戳
@Column(columnDefinition="TIMESTAMP",nullable=false,updatable=false,insertable=false)
private Timestamp updateTime;
@Transient //临时参数,不映射到数据库表字段
private String catParam;
//----------------------------- 以下是构造方法 ------------------------
public Cat(String name, String sex, Integer age,String catParam) {
super();
this.name = name;
this.sex = sex;
this.age = age;
this.birthday = new Date();
this.catParam = catParam;
}
public Cat(String name, String sex, Integer age, Date birthday, String catParam) {
super();
this.name = name;
this.sex = sex;
this.age = age;
this.birthday = birthday;
this.catParam = catParam;
}
public Cat() {
super();
}
//----------------------------- 以下是Getter和setter方法 ------------------------
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public Timestamp getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Timestamp updateTime) {
this.updateTime = updateTime;
}
public String getCatParam() {
return catParam;
}
public void setCatParam(String catParam) {
this.catParam = catParam;
}
//----------------------------- 以下是重写的toString方法 ------------------------
@Override
public String toString() {
return "Cat [id=" + id + ", name=" + name + ", sex=" + sex + ", age=" + age + ", birthday=" + birthday
+ ", updateTime=" + updateTime + ", catParam=" + catParam + "]";
}
}**package com.cyj.springboot.entity;
import java.io.Serializable;
import java.sql.Timestamp;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OrderBy;
import javax.persistence.Table;
import javax.persistence.Transient;
@Entity // HQL 使用,默认类名
@Table(name = "cattb") // 数据库原生SQL使用,默认表名
public class Cat implements Serializable {
// Cat实体的属性: id, name, sex, age, birthday, updateTime
@Id // 实体类的主键
@GeneratedValue // 自动增长列
@OrderBy // 数据加载顺序
@Column(columnDefinition = "int unsigned NOT NULL comment '备注:猫自动增长主键' ")
private Integer id;
@Column(length = 20) // 字符长度20
private String name;
@Column(columnDefinition = "char(1) comment '备注:猫姓名' ")
private String sex;
@Column(columnDefinition = "int unsigned DEFAULT 0 comment '备注:猫年龄' ")
private Integer age;
private Date birthday;
// @CreationTimestamp@UpdateTimestamp //插入,修改时自动维护时间戳
@Column(columnDefinition = "TIMESTAMP", nullable = false, updatable = false, insertable = false)
private Timestamp updateTime;
@Transient // 临时参数,不映射到数据库表字段
private String catParam;
// ----------------------------- 以下是构造方法 ------------------------
public Cat(String name, String sex, Integer age, String catParam) {
super();
this.name = name;
this.sex = sex;
this.age = age;
this.birthday = new Date();
this.catParam = catParam;
}
public Cat(String name, String sex, Integer age, Date birthday, String catParam) {
super();
this.name = name;
this.sex = sex;
this.age = age;
this.birthday = birthday;
this.catParam = catParam;
}
public Cat() {
super();
}
// ----------------------------- 以下是Getter和setter方法 ------------------------
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public Timestamp getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Timestamp updateTime) {
this.updateTime = updateTime;
}
public String getCatParam() {
return catParam;
}
public void setCatParam(String catParam) {
this.catParam = catParam;
}
// ----------------------------- 以下是重写的toString方法 ------------------------
@Override
public String toString() {
return "Cat [id=" + id + ", name=" + name + ", sex=" + sex + ", age=" + age + ", birthday=" + birthday
+ ", updateTime=" + updateTime + ", catParam=" + catParam + "]";
}
}
编写dao层
在com.cyj.springboot.dao下编写CatRepository，使其继承JpaSpecificationExecutor接口就可以使用
Specification进行动态SQL查询了,举例代码如下：
package com.cyj.springboot.dao;
import java.util.List;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;
import com.cyj.springboot.entity.Cat;
public interface CatRepository extends JpaRepository<Cat, Integer>, JpaSpecificationExecutor<Cat> {
// ----------------------------
// 只要继承了JpaRepository,就获得了所有的增删改查技能.你见或者不见,基本增删改查都在这里
// ---------------------------
// 示例内容在 com.cyj.springboot.ServiceImpl.CatServiceImpl
// ----------------------------以上是JpaRepository已经实现好的基本增删改查------------------
// ----------------------------以下是自定义条件查询--------------------------------------
// Cat实体的属性: id, name, sex, age, birthday, updateTime,catParam
// And --- 等价于 SQL 中的 and 关键字，比如 findByAgeAndSex(int Age,char sex)；
public List<Cat> findByNameAndSex(String name, String sex);
// Or --- 等价于 SQL 中的 or 关键字，比如 findByAgeOrSex(int Age,char sex)；
public List<Cat> findByNameOrSex(String name, String sex);
// Between --- 等价于 SQL 中的 between 关键字，比如 findByAgeBetween(int min, int max)；
public List<Cat> findByAgeBetween(int min, int max);
// LessThan --- 等价于 SQL 中的 "<"，比如 findByAgeLessThan(int max)；
public List<Cat> findByAgeLessThan(int max);
// GreaterThan --- 等价于 SQL 中的">"，比如 findByAgeGreaterThan(int min)；
public List<Cat> findByAgeGreaterThan(int min);
// IsNull --- 等价于 SQL 中的 "is null"，比如 findByNameIsNull()；
public List<Cat> findByNameIsNull();
// IsNotNull --- 等价于 SQL 中的 "is not null"，比如 findByNameIsNotNull()；
public List<Cat> findByNameIsNotNull();
// NotNull --- 与 IsNotNull 等价；
public List<Cat> findByNameNotNull();
// Like --- 等价于 SQL 中的 "like"，比如 findByNameLike(String name);
public List<Cat> findByNameLike(String name);
// NotLike --- 等价于 SQL 中的 "not like"，比如 findByNameNotLike(String name)；
public List<Cat> findByNameNotLike(String name);
// OrderBy --- 等价于 SQL 中的 "OrderBy"，比如 findByNameNotNullCatByAgeAsc()；
public List<Cat> findByNameNotNullOrderByAgeAsc();
// Not --- 等价于 SQL 中的 "！ ="，比如 findByNameNot(String name)；
public List<Cat> findByNameNot(String name);
// In --- 等价于 SQL 中的 "in"，比如 findByNameIN(String name);
public List<Cat> findByNameIn(String name);
// NotIn --- 等价于 SQL 中的 "not in"，比如 findByNameNotIN(String name);
public List<Cat> findByNameNotIn(String name);
// And --- 等价于 SQL 中的 and 关键字，比如 findByAgeAndSex(int Age,char sex)；
public List<Cat> findByAgeAndSex(int Age, char sex);
// Or --- 等价于 SQL 中的 or 关键字，比如 findByAgeOrSex(int Age,char sex)；
public List<Cat> findByAgeOrSex(int Age, char sex);
// StartingWith findByNameStartingWith ... where x.name like ?1(parameter bound
// with appended %)
public List<Cat> findByNameStartingWith(String name);
// EndingWith findByNameEndingWith ... where x.name like ?1(parameter bound with
// prepended %)
public List<Cat> findByNameEndingWith(String name);
// Containing findByNameContaining ... where x.name like ?1(parameter bound
// wrapped in %)
public List<Cat> findByNameContaining(String name);
// OrderBy findByAgeOrderByName ... where x.age = ?1 order by x.name desc
public List<Cat> findByNameOrderByAge(String name, Integer age);
// True findByActiveTrue ... where x.avtive = true
// public List<Cat> findByActiveTrue();
// Flase findByActiveFalse ... where x.active = false
// public List<Cat> findByActiveFalse();
// Like --- 等价于 SQL 中的 "like"，比如 findByNameLike(String name);
public List<Cat> findByNameLike(String name, Sort sort);
public Page<Cat> findByNameLike(String name, Pageable pageable);
// ******************** []HQL 方式 ] 序号参数*******************
// Cat实体的属性: id, name, sex, age, birthday, updateTime
// 以HQL方式获取数据
// 前面介绍的获取数据的方式都没有使用到任何的HQL语句，那些方法已经可以满足很多需求，也有时候会觉得方法名太长不太方便，下面介绍一下使用Hql方式获取数据：
// 在ICatService中加入 ：
@Query("FROM Cat c WHERE c.name=?1 AND c.sex IS NOT NULL")
List<Cat> findAll(String name);
/*
* 测试方法
*
* @Test public void testQuery() { List<Cat> list = CatService.findAll("Cat3");
* System.out.println(list.size()); }
*/
// 修改数据
// 在ICatService接口中写一个修改的方法，只要涉及修改或删除数据的操作都需要加上注释@Modifying和@Transcational（Transcational是org.springframework.transaction.annotation包中的不要导错了）
@Query("UPDATE Cat c SET c.age=?2 WHERE c.id=?1")
@Modifying
@Transactional
void updatePwd(Integer id, Integer age);
/*
* 测试方法
*
* @Test public void testUpdate() { CatService.updatePwd(1, 100); }
*/
// 删除数据
// 在ICatService接口中的方法：
@Query("DELETE FROM Cat c WHERE c.name=?1")
@Modifying
@Transactional
void deleteByCatName(String name);
/*
* 测试方法
*
* @Test public void testDelete() { CatService.deleteByCatName("Cat4"); }
*/
// 在上面的操作方式中参数传递都是以一种有序的方式传递的，另外还有一种更为直观的[命名参数]方式来传递参数，下面举个例子说明：
// ******************** []HQL 方式 ] 序号参数*******************
// 注意： 在参数传中参数前加注释@Param并指定名称，在@Query中使用:名称的方式来传递参数。
// 在接口ICatService中添加方法：
@Query("UPDATE Cat c SET c.sex= :sex WHERE c.id = :id")
@Modifying
@Transactional
void updateEmail(@Param("id") Integer id, @Param("sex") String sex);
/*
* 测试方法
*
* @Test public void testUpdate2() { CatService.updateCat(1, "女"); }
*/
@Query(" SELECT MAX(c.age) FROM Cat c ")
public Long maxAge();
@Query("select c from Cat c where c.name like %:name% ")
List<Cat> queryByname(@Param(value = "name") String name);
// **************************一些复杂查询[原生的SQl]***********************
// 一些比较复杂的关联查询要怎么实现呢，JPA的处理方法是：利用[原生的SQl]命令来实现那些复杂的关联查询，下面就来看下案例。
// 通过设置 nativeQuery = true 来设置开启使用数据库原生SQL语句
// 利用原生的SQL进行查询操作
@Query(value = "select c.* from ordertb o ,cattb u where o.uid=u.id and u.name=?1", nativeQuery = true)
@Modifying
public List<Cat> findCatByName(String name);
// 利用原生的SQL进行删除操作
@Query(value = "delete from cattb where id=?1 ", nativeQuery = true)
@Modifying
public void deleteCatById(int id);
// 利用原生的SQL进行删除操作
@Query(value = "delete from cattb where uid=?1 ", nativeQuery = true)
@Modifying
public void deleteCatByUId(int uid);
// 利用原生的SQL进行修改操作
@Query(value = "update cattb set name=?1 where id=?2 ", nativeQuery = true)
@Modifying
public void updateCatName(String name, int id);
// 利用原生的SQL进行插入操作
@Query(value = "insert into cattb(name,uid) value(?1,?2)", nativeQuery = true)
@Modifying
public void insertCat(String name, int uid);
@Query(value = " SELECT * FROM cattb WHERE NAME LIKE %:name% ", nativeQuery = true)
List<Cat> queryBynameSQL(@Param(value = "name") String name);
// *******************JPA分页*******************************
// JPA是怎么实现分页的效果，其实JPA脱胎于hibernate，所以本身就对分页功能有很好的支持。
// 实现分页功能
Page<Cat> findByNameNot(String name, Pageable pageable);
// @RequestMapping(value = "/params")
// @ResponseBody
/*
* public String getEntryByParams(String name, Integer page,Integer size) { Sort
* sort = new Sort(Sort.Direction.DESC, "id"); Pageable pageable = new
* PageRequest(page, size, sort); Page<Cat>
* pages=CatDao.findByNameNot(name,pageable); Iterator<Cat> it=pages.iterator();
* while(it.hasNext()){ System.out.println("value:"+((Cat)it.next()).getId()); }
* return "success...login...."; }
*/
/*
* 上面的代码一个是在dao层中的，一个是在controller中的。
*
* dao层中添加一个返回值为Page，参数值为Pageable。controller层中通过实例化Pageable这个类，然后调用dao层这个分页方法。
*
* 通过这些步骤就可以轻轻松松的实现分页的效果啦，看起来是不是特别方便。
*/
// 最后在给大家介绍一下JPA是如何实现事务操作的。其实因为SpringBoot中已经对事务做了很好的封装了，使用起来特别方便。下面看一下案例：
/*
* @RequestMapping("/saveCat")
*
* @ResponseBody
*
* @Transactional() public String saveCat(){ Cat o1=new Cat("11",2); Cat o2=new
* Cat("22",2); Cat o3=new Cat("33",2); Cat o4=new Cat("44",2); CatDao.save(o1);
* CatDao.save(o2); CatDao.save(o3); CatDao.save(o4); return
* "successfull....saveCat......"; }
*/
/*
* 只要在方法的上面加上@Transaction 这个注解就可以轻轻松松的实现事务的操作了，是不是特别方便啊。 不过这里有几点需要注意的是：
*
* 1.这个注解实现的事务管理器是默认的，如果不想要默认是事务管理器，可以自己进行添加，我这里就不多介绍了。
*
* 2.事务的隔离级别也是可以自己设置的。
*
* 3.事务的传播行为也是可以自己设置的。
*/
}
编写查询条件实体类
在com.ysd.springboot.entitysearch下，编写CatSearch实体类，代码如下
package com.cyj.springboot.entitysearch;
import java.util.Date;
import org.springframework.format.annotation.DateTimeFormat;
/**
*
* @Description: 查询条件实体类
* @ClassName: CatSearch.java
* @author ChenYongJia
* @Date 2017年11月18日 下午11:49:24
* @Email 867647213@qq.com
*/
public class CatSearch {
// Cat实体的属性: id, name, sex, age, birthday, updateTime
// CatSearch实体的属性: name, sex, minAge, maxAge, startBirthday, endBirthday,
// startUpdateTime, endUpdateTime
private String name;
private String sex;
private Integer minAge;
private Integer maxAge;
@DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME, pattern = "yyyy-MM-dd HH:mm:ss")
private Date startBirthday;
@DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME, pattern = "yyyy-MM-dd HH:mm:ss")
private Date endBirthday;
@DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME, pattern = "yyyy-MM-dd HH:mm:ss")
private Date startUpdateTime;
@DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME, pattern = "yyyy-MM-dd HH:mm:ss")
private Date endUpdateTime;
// ----------------------------- 以下是构造方法 ------------------------
public CatSearch() {
super();
}
public CatSearch(String name, String sex, Integer minAge, Integer maxAge, Date startBirthday, Date endBirthday,
Date startUpdateTime, Date endUpdateTime) {
super();
this.name = name;
this.sex = sex;
this.minAge = minAge;
this.maxAge = maxAge;
this.startBirthday = startBirthday;
this.endBirthday = endBirthday;
this.startUpdateTime = startUpdateTime;
this.endUpdateTime = endUpdateTime;
}
public CatSearch(Date startBirthday, Date endBirthday) {
super();
this.startBirthday = startBirthday;
this.endBirthday = endBirthday;
}
public CatSearch(Integer minAge, Integer maxAge) {
super();
this.minAge = minAge;
this.maxAge = maxAge;
}
public CatSearch(String name, String sex) {
super();
this.name = name;
this.sex = sex;
}
public CatSearch(String name) {
super();
this.name = name;
}
// ----------------------------- 以下是Getter和setter方法 ------------------------
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Integer getMinAge() {
return minAge;
}
public void setMinAge(Integer minAge) {
this.minAge = minAge;
}
public Integer getMaxAge() {
return maxAge;
}
public void setMaxAge(Integer maxAge) {
this.maxAge = maxAge;
}
public Date getStartBirthday() {
return startBirthday;
}
public void setStartBirthday(Date startBirthday) {
this.startBirthday = startBirthday;
}
public Date getEndBirthday() {
return endBirthday;
}
public void setEndBirthday(Date endBirthday) {
this.endBirthday = endBirthday;
}
public Date getStartUpdateTime() {
return startUpdateTime;
}
public void setStartUpdateTime(Date startUpdateTime) {
this.startUpdateTime = startUpdateTime;
}
public Date getEndUpdateTime() {
return endUpdateTime;
}
public void setEndUpdateTime(Date endUpdateTime) {
this.endUpdateTime = endUpdateTime;
}
// ----------------------------- 以下是重写的toString方法 ------------------------
@Override
public String toString() {
return "CatSearch [name=" + name + ", sex=" + sex + ", minAge=" + minAge + ", maxAge=" + maxAge
+ ", startBirthday=" + startBirthday + ", endBirthday=" + endBirthday + ", startUpdateTime="
+ startUpdateTime + ", endUpdateTime=" + endUpdateTime + "]";
}
}
编写业务service接口
在com.cyj.springboot.service下，编写CatService接口，代码如下：
package com.cyj.springboot.service;
import java.util.List;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import com.cyj.springboot.entity.Cat;
import com.cyj.springboot.entitysearch.CatSearch;
public interface CatService {
public Cat insert(Cat cat);
public List<Cat> insert(List<Cat> list);
public void delete(Integer id);
public void delete(Cat cat);
public void delete(List<Cat> list);
public void deleteAll();
public Cat update(Cat cat);
public List<Cat> update(List<Cat> list);
public Boolean exists(Integer id);
public Long count();
public Cat queryById(Integer id);
public List<Cat> queryAll();
public List<Cat> queryByNameLike(String name);
public List<Cat> queryByName(String name);
public List<Cat> queryByNameSQL(String name);
public Long queryMaxAge();
public List<Cat> queryAllSort(String fieldName);// 排序
public Page<Cat> queryAllPage(Integer page, Integer size);// 分页,排序
public List<Cat> queryNameLikeAllSort(String nameLike, String fieldName);// 带条件的排序
public Page<Cat> queryNameLikeAllPage(String nameLike, Integer page, Integer size);// 带条件的分页
// ----------------------------使用JpaRepository,Specification动态SQL查询------------------
public List<Cat> queryByDynamicSQL(CatSearch catSearch);
public List<Cat> queryByDynamicSQL02(CatSearch catSearch);
public Page<Cat> queryByDynamicSQLPage(CatSearch catSearch, Integer page, Integer size);// 动态查询条件的排序分页
}
编写业务实现类
在com.cyj.springboot.ServiceImpl下，编写业务实现类，代码如下
package com.cyj.springboot.ServiceImpl;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import com.cyj.springboot.dao.CatRepository;
import com.cyj.springboot.entity.Cat;
import com.cyj.springboot.entitysearch.CatSearch;
import com.cyj.springboot.service.CatService;
@Service
public class CatServiceImpl implements CatService{
@Autowired
private CatRepository repository;
@Override
public Cat insert(Cat cat) {
return repository.save(cat);
}
@Override
public List<Cat> insert(List<Cat> list) {
return repository.save(list);
}
@Override
public void delete(Integer id) {
repository.delete(id);
}
@Override
public void delete(Cat cat) {
repository.delete(cat);
}
@Override
public void delete(List<Cat> list) {
repository.delete(list);
}
@Override
public void deleteAll() {
repository.deleteAll();
}
@Override
public Cat update(Cat cat) {
return repository.save(cat);
}
@Override
public List<Cat> update(List<Cat> list){
return repository.save(list);
}
@Override
public Boolean exists(Integer id){
return repository.exists(id);
}
@Override
public Long count() {
return repository.count();
}
@Override
public Cat queryById(Integer id) {
return repository.findOne(id);
}
@Override
public List<Cat> queryAll(){
return repository.findAll();
}
@Override
public List<Cat> queryAllSort(String fieldName){//排序
// Sort sort = new Sort(Sort.Direction.DESC, "id");
// Pageable pageable = new PageRequest(page, size, sort);
Sort sort = new Sort(Sort.Direction.DESC, fieldName,"id");
return repository.findAll(sort);
}
@Override
public Page<Cat> queryAllPage(Integer page,Integer size){//分页
Sort sort = new Sort(Sort.Direction.ASC, "id");
Pageable pageable = new PageRequest(page, size, sort);
return repository.findAll(pageable);
}
//----------------------------以上是JpaRepository已经实现好的基本增删改查------------------
@Override
public Long queryMaxAge() {
return repository.maxAge();
}
@Override
public List<Cat> queryByNameLike(String name){
return repository.findByNameLike("%"+name+"%");
}
@Override
public List<Cat> queryByName(String name){
return repository.queryByname(name);
}
@Override
public List<Cat> queryByNameSQL(String name){
return repository.queryBynameSQL(name);
}
@Override
public List<Cat> queryNameLikeAllSort(String nameLike,String fieldName){//带条件的排序
// Sort sort = new Sort(Sort.Direction.DESC, "id");
// Pageable pageable = new PageRequest(page, size, sort);
Sort sort = new Sort(Sort.Direction.DESC, fieldName,"id");
return repository.findByNameLike("%"+nameLike+"%", sort);
}
@Override
public Page<Cat> queryNameLikeAllPage(String nameLike,Integer page,Integer size){//带条件的分页
Sort sort = new Sort(Sort.Direction.ASC, "id");
Pageable pageable = new PageRequest(page, size, sort);
return repository.findByNameLike("%"+nameLike+"%", pageable);
}
//----------------------------使用JpaRepository,Specification动态SQL查询------------------
@Override
public List<Cat> queryByDynamicSQL(CatSearch catSearch){
return repository.findAll(this.getWhereClause(catSearch));
}
@Override
public List<Cat> queryByDynamicSQL02(CatSearch catSearch){
return repository.findAll(this.getWhereClause02(catSearch));
}
@Override
public Page<Cat> queryByDynamicSQLPage(CatSearch catSearch,Integer page,Integer size){//动态查询条件的排序分页
Sort sort = new Sort(Sort.Direction.ASC, "id");
Pageable pageable = new PageRequest(page, size, sort);
return repository.findAll(this.getWhereClause(catSearch),pageable);
}
//Cat实体的属性: id, name, sex, age, birthday, updateTime
//Cat实体的属性: id, name, sex, age, birthday, updateTime
//CatSearch实体的属性: name, sex, minAge, maxAge, startBirthday, endBirthday, startUpdateTime, endUpdateTime
/**
* 查询条件动态组装
* 动态生成where语句
* 匿名内部类形式
* @param catSearch
* @return
*/
private Specification<Cat> getWhereClause(final CatSearch catSearch){
return new Specification<Cat>() {
@Override
public Predicate toPredicate(Root<Cat> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
Predicate predicate = cb.conjunction();//动态SQL表达式
List<Expression<Boolean>> exList = predicate.getExpressions();//动态SQL表达式集合
if( catSearch.getName() != null && !"".equals(catSearch.getName()) ){
exList.add(cb.like(root.<String>get("name"), "%"+catSearch.getName()+"%"));
}
if( catSearch.getSex() != null && !"".equals(catSearch.getSex()) ){
exList.add(cb.equal(root.get("sex").as(String.class), catSearch.getSex()));
}
if( catSearch.getMinAge() != null ){
exList.add(cb.greaterThanOrEqualTo(root.<Integer>get("age"), catSearch.getMinAge()));
}
if( catSearch.getMaxAge() != null ){
exList.add(cb.lessThanOrEqualTo(root.get("age").as(Integer.class), catSearch.getMaxAge()));
}
if( catSearch.getStartBirthday() != null ){
exList.add(cb.greaterThanOrEqualTo(root.<Date>get("birthday"), catSearch.getStartBirthday()));//大于等于起始日期
}
if( catSearch.getEndBirthday() != null ){
exList.add(cb.lessThanOrEqualTo(root.get("birthday").as(Date.class), catSearch.getEndBirthday()));//小于等于截止日期
}
return predicate;
}
};
}
/**
* 查询条件动态组装
* 动态生成where语句
* 匿名内部类形式
* @param catSearch
* @return
*/
private Specification<Cat> getWhereClause02(final CatSearch catSearch){
return new Specification<Cat>() {
@Override
public Predicate toPredicate(Root<Cat> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
List<Predicate> pList = new ArrayList<Predicate>();
if( catSearch.getName() != null && !"".equals(catSearch.getName()) ){
pList.add(cb.like(root.<String>get("name"), "%"+catSearch.getName()+"%"));
}
if( catSearch.getSex() != null && !"".equals(catSearch.getSex()) ){
pList.add(cb.equal(root.get("sex").as(String.class), catSearch.getSex()));
}
if( catSearch.getMinAge() != null ){
pList.add(cb.greaterThanOrEqualTo(root.<Integer>get("age"), catSearch.getMinAge()));
}
if( catSearch.getMaxAge() != null ){
pList.add(cb.lessThanOrEqualTo(root.get("age").as(Integer.class), catSearch.getMaxAge()));
}
if( catSearch.getStartBirthday() != null ){
pList.add(cb.greaterThanOrEqualTo(root.<Date>get("birthday"), catSearch.getStartBirthday()));//大于等于起始日期
}
if( catSearch.getEndBirthday() != null ){
pList.add(cb.lessThanOrEqualTo(root.get("birthday").as(Date.class), catSearch.getEndBirthday()));//小于等于截止日期
}
Predicate[] pre = new Predicate[pList.size()];
return query.where(pList.toArray(pre)).getRestriction();
}
};
}
/**
* 查询条件动态组装
* 匿名内部类形式
*/
private Specification<Cat> queryCatByDynamicWhere(final String name,final String sex,final Integer age,final Date startDate,final Date endDate){
return new Specification<Cat>() {
@Override
public Predicate toPredicate(Root<Cat> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
List<Predicate> predicateList = new ArrayList<Predicate>();
Predicate predicate = criteriaBuilder.conjunction();
List<Expression<Boolean>> expressionList = predicate.getExpressions();//动态SQL表达式集合
if( name != null && !"".equals(name) ){
expressionList.add(
criteriaBuilder.and(root.<String>get("name").in(name))
);
// predicateList.add(criteriaBuilder.and(root.<String>get("name").in(name)));
}
if( sex != null && !"".equals(sex) ){
expressionList.add(
criteriaBuilder.and(root.<String>get("sex").in(sex))
);
}
if( age != null){
expressionList.add(
criteriaBuilder.and(root.<String>get("age").in(age))
);
}
if( startDate != null && endDate != null ){
predicate.getExpressions().add(
criteriaBuilder.between(root.<Date>get("birthday"), startDate, endDate)
);
}
// return criteriaBuilder.and(predicateList.toArray(new Predicate[predicateList.size()]));
// return criteriaQuery.where(predicateList.toArray(new Predicate[predicateList.size()])).getRestriction();
return predicate;
}
};
}
}
在上面的实体类中，编写动态sql条件Specification，需要一个
org.springframework.data.jpa.domain.Specification接口的实现类。
CriteriaBuilder创建CriteriaQuery CriteriaQuery指定要查询的表，得到Root，Root代表要查询的表
CriteriaBuilder创建条件Predicate，Predicate相对于SQL的where条件，多个Predicate可以进行与、或操作
编写controller层
在com.cyj.springboot.controller下编写CatController代码，代码如下：
package com.cyj.springboot.controller;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.cyj.springboot.entity.Cat;
import com.cyj.springboot.entitysearch.CatSearch;
import com.cyj.springboot.service.CatService;
/**
* SpringMVC控制器
*
* @Description: 子模块
* @ClassName: CityRestController.java
* @author ChenYongJia
* @Date 2017-10-4 下午8:04:34
* @Email 867647213@qq.com
*/
@RestController
public class CatController {
@Autowired
private CatService service;
// 默认静态资源目录:http://localhost:8080/springbootjpasimple.sql
/**
* 测试项目环境是否正常 http://localhost:8080/index
*
* @return
*/
@RequestMapping("/index")
public Cat index() {
Cat cat = new Cat("张三", "男", 18, "预留参数");
return cat;
}
/**
* 新增 http://localhost:8080/insertOne
*
* @return
*/
@RequestMapping("/insertOne")
public Cat add() {
Cat cat = new Cat("张三", "男", 18, "预留参数");
return service.insert(cat);
}
/**
* 排序 http://localhost:8080/orderBy
*
* @return
*/
@RequestMapping("/orderBy")
public List<Cat> orderBy() {
return service.queryAllSort("age");
}
/**
* 条件模糊查询:[JPA关键字形式] http://localhost:8080/nameLike?name=m
* http://localhost:8080/nameLike?name=七
*
* @return
*/
@RequestMapping("/nameLike")
public List<Cat> nameLike(String name) {
return service.queryByNameLike(name);
}
/**
* 条件模糊查询:[HQL形式] http://localhost:8080/queryName?name=m
*
* @return
*/
@RequestMapping("/queryName")
public List<Cat> queryName(String name) {
return service.queryByName(name);
}
/**
* 条件模糊查询:[原生SQL形式] http://localhost:8080/queryNameSQL?name=m
*
* @return
*/
@RequestMapping("/queryNameSQL")
public List<Cat> queryNameSQL(String name) {
return service.queryByNameSQL(name);
}
/**
* http://localhost:8080/queryMaxAge
*
* @return
*/
@RequestMapping("/queryMaxAge")
public Long queryMaxAge() {
return service.queryMaxAge();
}
/**
* 分页查询 http://localhost:8080/queryPage
*
* @return
*/
@RequestMapping("/queryPage")
public Object queryPage() {
Page<Cat> page = null;
page = service.queryAllPage(1, 3);// 第2页,每页3条;第几页从零开始,每页显示几条.
System.out.println("queryPage page=>" + page);
Long total = page.getTotalElements();
List<Cat> list = page.getContent();
Map<String, Object> map = new HashMap<>();
map.put("total", total);
map.put("rows", list);
return map;
}
/**
* 带条件的排序查询 http://localhost:8080/nameLikeorderBy?name=m
*
* @return
*/
@RequestMapping("/nameLikeorderBy")
public List<Cat> nameLikeorderBy(String name) {
return service.queryNameLikeAllSort(name, "age");
}
/**
* 带条件的分页查询 http://localhost:8080/queryNameLikePage?name=m
*
* @return
*/
@RequestMapping("/queryNameLikePage")
public Object queryNameLikePage(String name) {
Page<Cat> page = null;
page = service.queryNameLikeAllPage(name, 0, 3);// 第1页,每页3条;第几页从零开始,每页显示几条.
System.out.println("page=>" + page);
Long total = page.getTotalElements();
List<Cat> list = page.getContent();
Map<String, Object> map = new HashMap<>();
map.put("total", total);
map.put("rows", list);
return map;
}
// ----------------------------使用JpaRepository,Specification动态SQL查询------------------
/**
* 动态条件查询 http://localhost:8080/queryDynamic?name=田
* http://localhost:8080/queryDynamic?minAge=50&maxAge=100
* http://localhost:8080/queryDynamic?minAge=100&maxAge=200&startBirthday=2017-11-20
* 00:00:00&endBirthday=2017-11-20 23:59:59
*
* @return
*/
@RequestMapping("/queryDynamic")
public List<Cat> queryDynamic(CatSearch search) {
List<Cat> list = service.queryByDynamicSQL(search);
System.out.println("动态查询 list=>" + list);
return list;
}
}
请自行下载源码进行测试。（静态sql文件在static文件夹下）
最后
-
更多参考精彩博文请看这里：《陈永佳的博客》
-
喜欢博主的小伙伴可以加个关注、点个赞哦，持续更新嘿嘿！
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
