Spring Data Jpa 的簡單查詢多表查詢HQL，SQL ，動態查詢， QueryDsl ，自定義查詢筆記
基本知識：
- Repository：僅僅只是一個標識，沒有任何方法，方便Spring自動掃描識別
- CrudRepository：繼承Repository，實現一組CRUD相關方法
- PagingAndStortingRepository：繼承CrudRepository，實現一組分頁排序相關方法
- JpaRepository：繼承PagingAndStortingRepository，QueryByExampleExecutor 實現一組JPA規範方法
下面是一個AppUser 的 Dao介面，除了簡單的findBy 介面查詢外，還有@Query的查詢，查詢返回一個自定義的結果Useinfo，UserInfo 從2個表中獲取，從AppUser 中獲取 phone ，name 等資訊，從 HearUrl 表中獲取userid 對應的頭像地址；
@Repository
public interface AppUserRepository extends JpaRepository<AppUser, Long>{
AppUser findByUserid(String userid);
//根據電話和客戶名查詢
AppUser findByPhoneAndCustomName(String phone, String customName);
//分頁查詢
Page<AppUser> findByCustomName(String customName,Pageable pageable);
//查詢2個表的指定欄位，組成新的物件UserInfo
@Query(value = "select new com.cxx.beans.UserInfo(u.userid, u.phone,u.name, u.address, u.info,head.headUrl) "
+ "from AppUser u, HeadUrl head where u.userid = head.userid and"
+ " u.userid = :userid")
UserInfo findUserInfoByUserid(@Param("userid") String userid);
//查詢2個表具有同一個userid 的內容，並組成新的物件
@Query(value = "select new com.cxx.beans.UserInfo(u.userid, u.phone,u.name, u.address, u.info,head.headUrl) "
+ "from AppUser u, HeadUrl head where u.userid = head.userid")
List<UserInfo> findUserInfo();
@Query(value = "select u.userid, u.phone,u.name, u.address, u.info,head.headUrl from AppUser u, HeadUrl head"
+ " where u.userid = head.userid and"
+ " u.userid = :userid",nativeQuery = true)
List<Object[]> findUserInfos2(@Param("userid") String userid);
//刪除使用者，根據name 和city 刪除全部匹配的使用者
@Modifying
@Transactional
@Query(value="delete from AppUser where name =?1 and city= ?2")
void deleteUser(String name,String city);
//去重查詢，查詢city 列表
@Query(value="select distinct city from AppUser")
List<String> getCityList();
}
public class UserInfo implements Serializable{
private String userid;
private String phone;
private String name;
private String address;
private String info;
private String headUrl;
public UserInfo()
{
}
public UserInfo(String userid, String phone, String name, String address, String info, String headUrl) {
super();
this.userid = userid;
this.phone = phone;
this.name = name;
this.address = address;
this.info = info;
this.headUrl = headUrl;
}
...
}
findUserInfo 返回的資料結構
{
"userid": "e4d6f4eef48c4408985b65f6997891bb",
"phone": "13760234541",
"name": "張三",
"address": null,
"info": null,
"headUrl": "http://cbc-huanan-image.oss-cn-shenzhen.aliyuncs.com/15337983334421.png"
}
findUserinfos2 返回的資料結構：
[
[
"e4d6f4eef48c4408985b65f6997891bb",
"13760234541",
"張三",
null,
null,
"http://cbc-huanan-image.oss-cn-shenzhen.aliyuncs.com/15337983334421.png"
],
[ ... ]
]
查詢到多個去重後，比如我需要查包含密碼資訊的房間和包含指紋資訊的房間，查詢結果再去重，得到所有的房間號
//set去重
Set set = new HashSet();
List<String> listNew=new ArrayList<>();
set.addAll(list1);
set.addAll(list2);
listNew.addAll(set);
更正：
我想從 A表(AppUser)中獲取一些欄位（name ，等）， 再根據B表（HeadUrl）中獲取一些欄位(headUrl)，組成查詢結果，上面的這個方法，在有一個表中不存在相應的記錄是無法得到想要的結果 @Query(value = "select new com.cxx.beans.UserInfo(u.userid, u.phone,u.name, u.address, u.info,head.headUrl) "
+ "from AppUser u, HeadUrl head where u.userid = head.userid and"
+ " u.userid = :userid")
UserInfo findUserInfoByUserid(@Param("userid") String userid);
正確的SQL 查詢語句如下：需要用LeftJoin 方式，這樣當HeadUrl 中查詢內容為空時也能正確返回。
所以正確的查詢方法如下，但這個返回的是Object陣列，需要根據這個陣列內容，再手動轉成我們需要的物件
@Query(select u.userid, u.phone,u.name, u.address, u.info, head.headUrl from AppUser u LEFT JOIN HeadUrl head USING(userid) where u.userid = :userid",nativeQuery =true)
List<Object[]> findFriendsByUserid(@Param("userid")String userid);
Spring JPA方法名解析步驟
- 在進行方法名解析時會先去掉多餘的字首，比如find、findBy、read、readBy、get、getBy，然後對剩下部分進行解析，並且如果方法最後一個引數時 Sort 或 Pageable 型別，也會提取相關資訊
- 比如：findByNameLikeAndAgeGreaterThan
- 剔除findBy
- 判斷nameLikeAndAgeGreaterThan（根據POJO規範，首字母變小寫）是否為返回物件 User 的一個屬性，如果是，則根據該屬性進行查詢，如果沒有該屬性，則進行下一步
- 從右往左擷取第一個大寫字母開頭的字串（此處為Than），然後檢查剩下的字串是否為 User 的一個屬性，如果是，則根據該屬性進行查詢，否則重複第2步，直到找出 name 為 User 的屬性
- 從已擷取的部分後面開始，重新第 1 步開始（剔除條件語句），迴圈，直到把方法名處理完畢
- 通過獲取的操作、條件和屬性、帶入引數值，生成查詢
| 關鍵字 | 例子 | 對應的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) |
關聯外來鍵查詢
- 假設有兩張表 address與 user
- address表中有一個 user_id 的外來鍵列對應著 user 表的主鍵
- 若查詢條件為 id 為 1 的 address且同時對應 id 為 2 的使用者，如下列 SQL 語句：
SELECT a.* FROM address a JOIN user u ON a.user_id = u.id WHERE a.id = 1 AND u.id = 2
- 則使用 _ 來連線關聯實體屬性查詢，上述 SQL 對應的 JPA 方法名為
Address findTopByIdAndUser_Id(int addressId,int userId);
限制查詢結果數量
public interface PersonRepository extends JpaRepository<Person,Longs>{
//獲得符合查詢條件的前30條資料
List<Person>findTop30ByName(String name);
}
通過@query編寫建立查詢
- 可以通過@Query（若使用nativesql屬性，則使用原生的sql語句）註解來建立查詢（引數也可用 ?1 ?2 代替，則不需@Param），如：
@Query("select * from User u where u.name like :first and u.age>:age")
List<User> findByNameLikeAndAgeGreaterThan(@Param("first")String firstName,@Param("age")Integer age);
@Modifying
@Transactional
@Query("update User u set u.name = ?1 where u.id = ?2")
public int increaseSalary(String name, int id);
@Modifying
@Transactional
@Query(value="delete from UserShips where userid =?1 and houseNo= ?2")
void deleteByUseridAndHouseNo(String userid ,String houseNo);
- 更新、刪除操作需要加上@Transactional @Modifying
複雜查詢
多表聯合查詢，動態查詢可以用QueryDsl 方式，也可以用Specification 方式來做。以下是動態查詢的一個例項：
下面是人員Bean 的關鍵資料：
public class Person {
@Id
@GeneratedValue(generator="system-uuid")
private String id;
private String name;
....
@ManyToOne
private House house;
@ManyToOne
//@JsonIgnore
private Building building;
@ManyToMany(cascade = CascadeType.PERSIST,fetch=FetchType.EAGER)
private Set<GateDevice> gate_device;
private String phone;
Dao:
@Repository
public interface PersonDao extends JpaRepository<Person, String>,JpaSpecificationExecutor<Person>,QuerydslPredicateExecutor<Person>
{
Person findByName(String name);
List<Person> findByBuildingId(String id);
List<Person> findByIdIn(Collection<String> ids);
//根據person ID 查詢 person的概要資訊
@Query(value="SELECT p.id as pid,"
+ "p.name as name, "
+ "p.sex as sex,"
+ "p.card_id as card_id,"
+ "file.url as url,"
+ "p.enable as enable,"
+ "p.permission_status,"
+ "h.name AS room_name,"
+ "b.name AS building_name "
+ "FROM Person p "
+ "LEFT JOIN FileUrl file ON p.id=file.owner.id "
+ "LEFT JOIN House h ON p.house.id = h.id "
+ "LEFT JOIN Building b ON p.building.id = b.id "
+ "WHERE p.id= ?1 "
)
List<Object[]> findAllInfo(String id);
/*
* personinfo 的建構函式 和QueryDsl 的自動建構函式有衝突（好像是）
*
*
*
//根據person id 查詢獲取person 的概要資訊 personinfo
@Query(value="SELECT new com.cbest.cb025.entity.PersonInfo("
+ "p.id,"
+ "p.name,"
+ "p.card_id,"
+ "h.name,"
+ "b.name, "
+ "p.sex,"
+ "p.permission_status,"
+ "p.enable"
+ ") "
+ "FROM Person p "
+ "LEFT JOIN House h ON p.house.id = h.id "
+ "LEFT JOIN Building b ON p.building.id = b.id "
+ "WHERE p.id= ?1 GROUP BY p.id"
)
PersonInfo findOnePersonInfo(String id);
@Query(value="SELECT new com.cbest.cb025.entity.PersonInfo("
+ "p.id,"
+ "p.name,"
+ "p.card_id,"
+ "h.name,"
+ "b.name, "
+ "p.sex,"
+ "p.permission_status,"
+ "p.enable"
+ ") "
+ "FROM Person p "
+ "LEFT JOIN House h ON p.house.id = h.id "
+ "LEFT JOIN Building b ON p.building.id = b.id "
+ "WHERE p.id in (?1)"
)
List<PersonInfo> findPersonInfoByPersonIds(String[] id);
*/
查詢資料：
Specification<Person> s = new Specification<Person>() {
@Override
public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
Predicate p = cb.conjunction();
p.getExpressions()
.add(cb.equal(root.get("building").get("address").get("owner").get("company"),company));
if (!StringUtils.isEmpty(req.getProvince())) {
p.getExpressions().add(cb.like(root.get("building").get("address").get("province"),
"%" + req.getProvince() + "%"));
}
// 根據city 查詢
if (!StringUtils.isEmpty(req.getCity())) {
p.getExpressions()
.add(cb.like(root.get("building").get("address").get("city"), "%" + req.getCity() + "%"));
}
if (!StringUtils.isEmpty(req.getDistrict())) {
p.getExpressions().add(cb.like(root.get("building").get("address").get("district"),
"%" + req.getDistrict() + "%"));
}
if (!StringUtils.isEmpty(req.getStreet())) {
p.getExpressions().add(
cb.like(root.get("building").get("address").get("street"), "%" + req.getStreet() + "%"));
}
if (!StringUtils.isEmpty(req.getVillage())) {
p.getExpressions().add(
cb.like(root.get("building").get("address").get("name"), "%" + req.getVillage() + "%"));
}
if (!StringUtils.isEmpty(req.getApartment())) {
p.getExpressions().add(cb.like(root.get("building").get("name"), "%" + req.getApartment() + "%"));
}
。。。
if (!StringUtils.isEmpty(req.getKeyword()))
{
Join<Person, House> person_room = root.join("house", JoinType.LEFT); // 這裡必須要用JOIN 的形式，否則在house 外來鍵為空時查詢不到資料
/* p.getExpressions().add(cb.and(cb.isNotNull(root.get("house")),cb.like(root.get("house").get("name"), "%" + req.getRoom() + "%")));*/
p.getExpressions().add(
cb.or(
cb.like(root.get("phone"), "%" + req.getKeyword() + "%"),
cb.like(root.get("name"), "%" + req.getKeyword() + "%"),
cb.like(root.get("card_id"), "%" + req.getKeyword() + "%"),
cb.like(root.get("building").get("address").get("name"), "%" + req.getKeyword() + "%"),
cb.like(root.get("building").get("name"), "%" + req.getKeyword() + "%"),
cb.like(person_room.get("name"),"%" + req.getKeyword() + "%")
)
);
}
return p;
}
};
if (req.getSize() == 0) {
List<Person> devices = personDao.findAll(s);
return ResponseFactory.getBaseResponse(devices);
}
Sort sort = new Sort(Sort.Direction.ASC, "name");
Pageable pageable = PageRequest.of(req.getPage(), req.getSize(), sort);
Page<Person> devices = personDao.findAll(s, pageable);
上面就是動態查詢的人員列表的重點程式碼；特別注意的是，Person 中有個 外來鍵 House 是可以為Null 的； 這種情況下就必須選用Join 的方式來建立查詢表示式，而上面的查詢例子中 Building 因為不為空，所以可以用 直接建立表示式的形式。
2.多表聯合查詢，返回自定義分頁資料：
人員檔案資訊
@Entity
@EntityListeners(AuditingEntityListener.class)
@GenericGenerator(name="system-uuid",strategy = "uuid")
public class FileUrl {
@Id
@GeneratedValue(generator="system-uuid")
private String id;
private String url;
@ManyToOne(optional = true,cascade = CascadeType.PERSIST)
@JsonIgnore
private Person owner;
....
查詢結果Bean 定義：
@Data
@Builder
public class PersonInfo {
private String pid;
private String name;
private String card_id;
private String room_name;
private String building_name;
private String start_time;
private String end_time;
private int sex;
private int permission_status;
private boolean enable;
private Set<String> urls = new HashSet<String>(); // 怎麼查詢出這個資料？ 一直沒找到直接的方法。
public String getPid() {
return pid;
}
public void setPid(String pid) {
this.pid = pid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCard_id() {
return card_id;
}
public void setCard_id(String card_id) {
this.card_id = card_id;
}
public String getRoom_name() {
return room_name;
}
public void setRoom_name(String room_name) {
this.room_name = room_name;
}
public String getBuilding_name() {
return building_name;
}
public void setBuilding_name(String building_name) {
this.building_name = building_name;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
public int getPermission_status() {
return permission_status;
}
public void setPermission_status(int permission_status) {
this.permission_status = permission_status;
}
public boolean isEnable() {
return enable;
}
public void setEnable(boolean enable) {
this.enable = enable;
}
public PersonInfo(String pid, String name, String card_id, String room_name, String building_name,
String start_time, String end_time, int sex, int permission_status, boolean enable,Set<String> urls) {
super();
this.pid = pid;
this.name = name;
this.card_id = card_id;
this.room_name = room_name;
this.building_name = building_name;
this.start_time = start_time;
this.end_time = end_time;
this.sex = sex;
this.permission_status = permission_status;
this.enable = enable;
this.urls = urls;
}
public static PersonInfo transform(Tuple tuple) {
QPerson person = QPerson.person;
QHouse house = QHouse.house;
QBuilding building = QBuilding.building;
PersonInfo pinfo = new PersonInfo(
tuple.get(person.id),
tuple.get(person.name),
tuple.get(person.card_id),
tuple.get(house.name),
tuple.get(building.name),
tuple.get(person.start_time),
tuple.get(person.end_time),
tuple.get(person.sex),
tuple.get(person.permission_status),
tuple.get(person.enable),
new HashSet<String>()
);
return pinfo;
}
...
QueryDsl 方式分頁查詢獲取資料： 沒有一次性查詢獲得檔案地址的陣列，因為研究了好久還是不會，有人可以留言幫指導下嗎？
public PageResult<PersonInfo> findPageablePersonInfoByDeviceId(String device_id,Pageable pageable) {
logger.info("findPageablePersonInfoByDeviceId id = " + device_id + " pageSize = " + pageable.getPageSize() + ",pageNumber = " + pageable.getPageNumber());
long time = System.currentTimeMillis();
QPerson person = QPerson.person;
QHouse house = QHouse.house;
QBuilding building = QBuilding.building;
QFileUrl fileUrl = QFileUrl.fileUrl;
String[] ids = personDao.findPersonIdsByDevicId(device_id);
logger.info("findPageablePersonInfoByDeviceId: findPersonIdsByDevicId costTime = " + (System.currentTimeMillis() - time));
QueryResults<Tuple> queryResults = jpaQueryFactory
.select(
person.id,
person.name,
house.name,
building.name,
person.card_id,
person.start_time,
person.end_time,
person.sex,
person.permission_status,
person.enable
)
.from(person)
.leftJoin(house).on(house.id.eq(person.house.id))
.leftJoin(building).on(person.building.id.eq(building.id))
.where(person.id.in(ids))
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetchResults()
;
Set<PersonInfo> info = queryResults.getResults().stream().map(PersonInfo::transform
)
.collect(Collectors.toSet());
Set<String> pids = new HashSet<String>();
for(PersonInfo p : info)
{
pids.add(p.getPid());
}
List<Tuple> res = jpaQueryFactory
.select(
person.id,
fileUrl.url
)
.from(fileUrl)
.leftJoin(person).on(fileUrl.owner.id.eq(person.id))
.where(person.id.in(pids))
.fetch();
for(PersonInfo p : info)
{
for (Tuple row : res) {
if(row.get(person.id).equals(p.getPid()))
p.addUrl(row.get(fileUrl.url));
}
}
int totalPage = 0;
if(queryResults.getTotal()%pageable.getPageSize() == 0)
totalPage = (int) (queryResults.getTotal()/pageable.getPageSize());
else
totalPage = (int) (queryResults.getTotal()/pageable.getPageSize()) + 1;
PageResult<PersonInfo> result = new PageResult<PersonInfo>(info,
totalPage,
(int)queryResults.getTotal(),
(pageable.getPageNumber() == totalPage -1) ? true :false,
pageable.getPageNumber(),
(pageable.getPageNumber() == 0) ? true :false,
info.size());
logger.info("findPageablePersonInfoByDeviceId: findTotal costTime = " + (System.currentTimeMillis() - time));
return result;
}
用到的複雜查詢大概就是這些了。
還有一種情況，就是一對多的聯合查詢處理，可以用SetJoin 這樣的方式
Specification<Person> s = new Specification<Person>() {
@Override
public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
Predicate p = cb.conjunction();
SetJoin<Person, GateDevice> join = root.joinSet("gate_device", JoinType.LEFT);
p.getExpressions().add(cb.equal(join.get("sn"), req.getDevice_sn()));
return p;
}
};
相關文章
- jpa動態查詢與多表聯合查詢
- SQL查詢的：子查詢和多表查詢SQL
- Spring data jpa 多表查詢（三:多對多關係動態條件查詢）Spring
- spring data jpa查詢Spring
- Hibernate hql 多表查詢
- MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]MySql筆記
- Spring Data JPA 實現多表關聯查詢Spring
- JPA多表關聯查詢
- MYSQL學習筆記26: 多表查詢|子查詢MySql筆記
- 查詢(2)--動態查詢
- JPA的多表複雜查詢
- Spring-Data-JPA criteria 查詢Spring
- Spring Data JPA + QueryDSL實現CRUD和複雜查詢案例Spring
- 多表查詢
- JPA 之 多表聯合查詢
- Spring Data Jpa 複雜查詢總結 (多表關聯 以及 自定義分頁 )Spring
- Spring JPA 定義查詢方法Spring
- MySQL - 資料查詢 - 簡單查詢MySql
- Hibernate通常是三種：hql查詢，QBC查詢和QBE查詢：
- 查詢(1)--靜態查詢
- #查詢演算法#【1】簡單查詢：順序、折半查詢演算法
- Hql查詢語句
- 【MySQL】多表查詢MySql
- Django 多表查詢Django
- 04多表查詢
- mysql多表查詢MySql
- MySQL 多表查詢MySql
- SQL SERVER 動態查詢SQLServer
- 在HQL select查詢語句中自定義查詢結果返回的資料型別資料型別
- MYSQL學習筆記24: 多表查詢(聯合查詢,Union, Union All)MySql筆記
- Spring Data JPA 實現聯表查詢Spring
- 【SQL查詢】集合查詢之INTERSECTSQL
- 簡單的查詢
- SQL面試題 三（單表、多表查詢）SQL面試題
- 關聯查詢子查詢效率簡單比照
- Spring JPA 聯表查詢Spring
- 如何使用Hibernate/JPA的JPQL/HQL查詢提取？
- hibernate中hql查詢