您好,登錄后才能下訂單哦!
如何在springboot中使用jpa實(shí)現(xiàn)一個(gè)高級(jí)查詢功能?很多新手對(duì)此不是很清楚,為了幫助大家解決這個(gè)難題,下面小編將為大家詳細(xì)講解,有這方面需求的人可以來學(xué)習(xí)下,希望你能有所收獲。
package com.bus365.root.dao; import java.io.Serializable; import java.util.List; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.JpaSpecificationExecutor; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.query.Param; import com.bus365.root.model.User; public interface UserDao extends JpaRepository<User,Long>,JpaSpecificationExecutor<User>,Serializable { User findByName(String name); User findByNameAndAge(String name, Integer age); User findByNameOrAge(String name, Integer age); /*@Query(value = "from User where name = :name") List<User> findbyname(@Param("name") String name);*/ }
下面展示service層調(diào)用:
@Override public User findByName(String name) { User user = userDao.findByName(name); return user; } @Override public User findByNameAndAge(String name, Integer age) { User user = userDao.findByNameAndAge(name,age); return user; } @Override public User findByNameOrAge(String name, Integer age) { User user = userDao.findByNameOrAge(name,age); return user; }
具體的關(guān)鍵字,使用方法和生產(chǎn)成SQL如下表所示
Keyword | Sample | JPQL snippet |
---|---|---|
And | findByLastnameAndFirstname | … where x.lastname = ?1 and x.firstname = ?2 |
Or | findByLastnameOrFirstname | … where x.lastname = ?1 or x.firstname = ?2 |
Is,Equals | 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) |
下面介紹使用java原生的jpa操作數(shù)據(jù)庫(kù),對(duì)jpa熟悉的朋友應(yīng)該很快就能理解,springboot使用原生jpa的關(guān)鍵是引入entitymanger
看一下service層
package com.bus365.root.service.impl; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import org.springframework.stereotype.Service; import com.bus365.root.model.Address; import com.bus365.root.service.AddressService; @Service public class AddressServiceImpl implements AddressService { @PersistenceContext private EntityManager entityManager; public List<Address> listAddress(){ List resultList = entityManager.createNativeQuery("select * from address ", Address.class).getResultList(); return resultList; } }
注意 @PersistenceContext
private EntityManager entityManager;
動(dòng)態(tài)引入entitymanger , 之后就能正常使用了;
createNativeQuery是操作原生mysql方法;支持跨表查詢;
jpa的事務(wù) 直接使用注解Transactional 參數(shù)rollbackon表示回滾條件, 這個(gè)注解一搬加在service層; 注意getSingleResult 如果查不到數(shù)據(jù)會(huì)報(bào)錯(cuò);
@Transactional(rollbackOn= {Exception.class}) public Address getAddressByid(Long id) { Address singleResult = null; try { singleResult = (Address) entityManager .createNativeQuery("select * from address where id = :id", Address.class).setParameter("id", id) .getSingleResult(); } catch (Exception e) { e.printStackTrace(); } return singleResult; }
jpa實(shí)現(xiàn)多表聯(lián)查;
@Transactional public List<Object[]> getUserWithAddrByid(Long id) { List resultList = entityManager.createNativeQuery( "select u.id id,u.age age,u.name name,a.name aname,a.completeaddress addre from user u left join address a on u.addressid = a.id where u.id = :id") .setParameter("id", id).getResultList(); return resultList; }
這是一個(gè)聯(lián)查user 和address的例子, 返回的結(jié)果是個(gè)List<Object[]> 項(xiàng)目中一般封裝成vo 類,或者List<Map<String,Object>> 的形式
github項(xiàng)目地址 https://github.com/Christain1993/SpringBootIntegration
補(bǔ)充:springBootJpa的復(fù)雜查詢
/** * 條件查詢+分頁(yè) * @param whereMap * @param page * @param size * @return */ public Page<CaseManage> findSearch(Map whereMap, int page, int size,Integer createId) { Sort sort = new Sort(Sort.Direction.DESC,"id"); Specification<CaseManage> specification = createSpecification(whereMap,createId); PageRequest pageRequest = new PageRequest(page,size,sort); return caseDao.findAll(specification, pageRequest); } /** * 條件查詢 * @param whereMap * @return */ public List<CaseManage> findSearch(Map whereMap,Integer createId) { Specification<CaseManage> specification = createSpecification(whereMap, createId); return caseDao.findAll(specification); } /** * 動(dòng)態(tài)條件構(gòu)建 * @param searchMap * @return */ private Specification<CaseManage> createSpecification(Map searchMap,Integer createId) { return new Specification<CaseManage>() { @Override public Predicate toPredicate(Root<CaseManage> root, CriteriaQuery<?> query, CriteriaBuilder cb) { List<Predicate> predicateList = new ArrayList<Predicate>(); // 案件名稱 if (searchMap.get("case_name")!=null && !"".equals(searchMap.get("case_name"))) { predicateList.add(cb.like(root.get("case_name").as(String.class), "%"+(String)searchMap.get("case_name")+"%")); } // 案件編號(hào)uuid類型 if (searchMap.get("case_uuid")!=null && !"".equals(searchMap.get("case_uuid"))) { predicateList.add(cb.equal(root.get("case_uuid").as(String.class), (String)searchMap.get("case_uuid"))); } return cb.and( predicateList.toArray(new Predicate[predicateList.size()])); } }; }
想實(shí)現(xiàn)這樣的效果
where (state=1 or state=2)and name='zhangsan'
java代碼
List<Predicate> predicateList = new ArrayList<Predicate>(); Predicate or = cb.or(cb.and(cb.equal(root.get("case_authority").as(String.class), "0")), cb.and(cb.equal(root.get("create_id").as(String.class), String.valueOf(createId)))); predicateList.add(or);
看完上述內(nèi)容是否對(duì)您有幫助呢?如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章,請(qǐng)關(guān)注億速云行業(yè)資訊頻道,感謝您對(duì)億速云的支持。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。