如何使用Spring Boot JPA实现复杂的长尾词高级查询操作？

本文共计1329个文字，预计阅读时间需要6分钟。

Spring Boot的JPA可以根据方法名自动解析SQL，非常方便。只需在DAO接口中定义方法，无需编写SQL语句。以下是一个示例：

javapackage com.bus365.root.dao;

import java.io.Serializable;import java.util.List;

import org.springframework.stereotype.Repository;

@Repositorypublic interface MyEntityDao extends Serializable {

List findByName(String name);}

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项目地址 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);

以上为个人经验，希望能给大家一个参考，也希望大家多多支持易盾网络。如有错误或未考虑完全的地方，望不吝赐教。