jpa 动态查询条件 数组_SpringBoot SpringDataJPA 动态查询、多条件查询

Spring-data - JPA用的还是比较多的，之前在公司也一直在用，因为方便。下面我们来整理一下如何配置。

pom.xml

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

4.0.0

springdatajpademo

springdatajpademo

1.0-SNAPSHOT

UTF-8

zh_CN

1.7

1.7

4.1.9.RELEASE

1.9.2.RELEASE

5.1.1.Final

5.1.32

4.0.7

1.2.7

9.1.0.v20131115

2.7.7

data-manager-server

com.goldmsg.res.Bootstrap

org.springframework

spring-context

${org.springframework.version}

org.springframework

spring-test

${org.springframework.version}

org.springframework.data

spring-data-jpa

${org.springframework.data.version}

org.hibernate

hibernate-core

${org.hibernate.version}

org.hibernate

hibernate-entitymanager

${org.hibernate.version}

mysql

mysql-connector-java

${mysql.connector.version}

com.alibaba

druid

1.0.15

userInfo.java

import javax.persistence.*;

/**

* Created with IntelliJ IDEA.

* User: lu

* Date: 17-3-30

* Time: 下午5:57

* To change this template use File | Settings | File Templates.

*/

@Entity

@Table(name="obj_user")

@NamedQuery(name="UserInfo.findAll", query="SELECT o FROM UserInfo o")

public class UserInfo {

private int userId;

private String name;

private int age;

private long high;

@javax.persistence.Id

@Column(name="user_id")

public int getUserId() {

return userId;

}

public void setUserId(int userId) {

this.userId = userId;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public int getAge() {

return age;

}

public void setAge(int age) {

this.age = age;

}

public long getHigh() {

return high;

}

public void setHigh(long high) {

this.high = high;

}

}

UserInfoDao.java

public interface UserInfoDao extends PagingAndSortingRepository, JpaSpecificationExecutor {

}

UserInfoSpecDao.java

import com.springdatajpademo.pojo.UserInfo;

import com.springdatajpademo.pojo.UserInfo_;

import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.CriteriaBuilder;

import javax.persistence.criteria.CriteriaQuery;

import javax.persistence.criteria.Predicate;

import javax.persistence.criteria.Root;

/**

* Created with IntelliJ IDEA.

* User: lu

* Date: 17-3-30

* Time: 下午8:05

* To change this template use File | Settings | File Templates.

*/

public class UserInfoDaoSpec {

public static Specification getSpec(final String name,final int age,final int high) {

return new Specification() {

@Override

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

Predicate p1 = null;

if(name!=null) {

Predicate p2 = cb.equal(root.get(UserInfo_.name),name);

if(p1 != null) {

p1 = cb.and(p1,p2);

} else {

p1 = p2;

}

}

if(age!=0) {

Predicate p2 = cb.equal(root.get(UserInfo_.age), age);

if(p1 != null) {

p1 = cb.and(p1,p2);

} else {

p1 = p2;

}

}

if(high!=0) {

Predicate p2 = cb.equal(root.get(UserInfo_.high), high);

if(p1 != null) {

p1 = cb.and(p1,p2);

} else {

p1 = p2;

}

}

return p1;

}

};

}

}

UserInfoExtendDao.java

import com.springdatajpademo.pojo.UserInfo;

import com.springdatajpademo.pojo.UserInfo_;

import com.springdatajpademo.pojo.UserStat;

import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;

import javax.persistence.PersistenceContext;

import javax.persistence.Tuple;

import javax.persistence.criteria.*;

import java.util.List;

/**

* Created with IntelliJ IDEA.

* User: lu

* Date: 17-3-31

* Time: 上午10:32

* To change this template use File | Settings | File Templates.

*/

@Repository

public class UserInfoExtendDao {

@PersistenceContext(unitName = "springJpa")

EntityManager em;

public List getUserInfo(String name,int age,int high) {

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaQuery query = cb.createQuery(UserInfo.class);

//from

Root root = query.from(UserInfo.class);

//where

Predicate p1 = null;

if(name!=null) {

Predicate p2 = cb.equal(root.get(UserInfo_.name),name);

if(p1 != null) {

p1 = cb.and(p1,p2);

} else {

p1 = p2;

}

}

if(age!=0) {

Predicate p2 = cb.equal(root.get(UserInfo_.age), age);

if(p1 != null) {

p1 = cb.and(p1,p2);

} else {

p1 = p2;

}

}

if(high!=0) {

Predicate p2 = cb.equal(root.get(UserInfo_.high), high);

if(p1 != null) {

p1 = cb.and(p1,p2);

} else {

p1 = p2;

}

}

query.where(p1);

List userInfos = em.createQuery(query).getResultList();

return userInfos;

}

public UserStat getUserStat(String name) {

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaQuery query = cb.createTupleQuery();

//from

Root root = query.from(UserInfo.class);

//select

Selection countUser;

Selection sumHigh;

countUser = cb.count(root.get(UserInfo_.userId));

sumHigh = cb.sum(root.get(UserInfo_.high));

CompoundSelection selection = cb.tuple(countUser, sumHigh);

//where

Predicate predicate = cb.equal(root.get(UserInfo_.name), name);

//query

CriteriaQuery criteriaQuery = query.select(selection).where(predicate);

Tuple tuple = em.createQuery(criteriaQuery).getSingleResult();

UserStat userStat = new UserStat();

userStat.setCountUser(tuple.get(countUser));

userStat.setSumHigh(tuple.get(sumHigh));

return userStat;

}

}

Application.java

import com.springdatajpademo.pojo.UserInfo;

import com.springdatajpademo.pojo.UserStat;

import com.springdatajpademo.repository.UserInfoDao;

import com.springdatajpademo.repository.UserInfoDaoSpec;

import com.springdatajpademo.repository.UserInfoExtendDao;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;

public class Application {

public static void main(String[] args) {

ClassPathXmlApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");

Application application = new Application ();

application .testJpa1(applicationContext);

application .testJpa2(applicationContext);

}

public void testJpa1(ClassPathXmlApplicationContext applicationContext) {

UserInfoExtendDao userInfoExtendDao = (UserInfoExtendDao)applicationContext.getBean("userInfoExtendDao");

List userInfos = userInfoExtendDao.getUserInfo("张三",16,165);

printUserInfo(userInfos);

UserStat userStat = userInfoExtendDao.getUserStat("张三");

System.out.println(userStat.getCountUser());

System.out.println(userStat.getSumHigh());

}

public void testJpa2(ClassPathXmlApplicationContext applicationContext) {

UserInfoDao userInfoDao = (UserInfoDao)applicationContext.getBean("userInfoDao");

{

//三个条件:"张三",16,165

System.out.println("\n三个条件:张三,16,165");

List userInfos = userInfoDao.findAll(UserInfoDaoSpec.getSpec("张三",16,165));

printUserInfo(userInfos);

}

{

//两个条件:"张三",16,这里假设赋值0为未赋值

System.out.println("\n两个条件:张三,16");

List userInfos = userInfoDao.findAll(UserInfoDaoSpec.getSpec("张三",16,0));

printUserInfo(userInfos);

}

{

//两个条件:"张三",16,这里假设赋值0为未赋值

System.out.println("\n一个条件:张三");

List userInfos = userInfoDao.findAll(UserInfoDaoSpec.getSpec("张三",0,0));

printUserInfo(userInfos);

}

}

private void printUserInfo(List userInfos) {

if(userInfos!=null) {

for(UserInfo userInfo : userInfos) {

System.out.println("userId:"+userInfo.getUserId()+" name:"+userInfo.getName());

}

}

}

}

以上配置来自网络，有问题可以在下面评论，技术问题可以私聊我。

QQ技术交流群：213365178