springboot 之jpa高级查询操作

springboot 之jpa高级查询操作

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,JpaSpecificationExecutor,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 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

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

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

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

补充：springBootJpa的复杂查询

分页

/**

* 条件查询+分页

* @param whereMap

* @param page

* @param size

* @return

*/

public Page findSearch(Map whereMap, int page, int size,Integer createId) {

Sort sort = new Sort(Sort.Direction.DESC,"id");

Specification specification = createSpecification(whereMap,createId);

PageRequest pageRequest = new PageRequest(page,size,sort);

return caseDao.findAll(specification, pageRequest);

}

/**

* 条件查询

* @param whereMap

* @return

*/

public List findSearch(Map whereMap,Integer createId) {

Specification specification = createSpecification(whereMap, createId);

return caseDao.findAll(specification);

}

/**

* 动态条件构建

* @param searchMap

* @return

*/

private Specification createSpecification(Map searchMap,Integer createId) {

return new Specification() {

@Override

public Predicate toPredicate(Root root, CriteriaQuery> query, CriteriaBuilder cb) {

List predicateList = new ArrayList();

// 案件名称

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类型

ihttp://f (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 predicateList = new ArrayList();

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。