springboot 之jpa高級查詢操作

Posted on by

springboot的jpa可以根據方法名自動解析sql 非常方便, 隻需要在 dao接口中定義方法即可;

下面是一個 demo

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層調用:

@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;
 }

具體的關鍵字，使用方法和生產成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操作數據庫,對jpa熟悉的朋友應該很快就能理解,springboot使用原生jpa的關鍵是引入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;

動態引入entitymanger , 之後就能正常使用瞭;

createNativeQuery是操作原生mysql方法;支持跨表查詢;

jpa的事務 直接使用註解Transactional 參數rollbackon表示回滾條件, 這個註解一搬加在service層; 註意getSingleResult 如果查不到數據會報錯;

@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實現多表聯查;

@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;
 }

這是一個聯查user 和address的例子, 返回的結果是個List<Object[]> 項目中一般封裝成vo 類,或者List<Map<String,Object>> 的形式

github項目地址 https://github.com/Christain1993/SpringBootIntegration

補充：springBootJpa的復雜查詢

分頁

/**
   * 條件查詢+分頁
   * @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);
  }
  
  /**
   * 動態條件構建
   * @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")+"%"));
        }
        // 案件編號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()]));
      }
    };
  }

or查詢

想實現這樣的效果

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);

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：