JPA动态查询的两种姿势

TianRanDai
625 阅读4分钟

源码

Specifications

实战代码

  1. 在编写Repository 时 继承 JpaSpecificationExecutor 接口便有了动态查询的能力
package com.fine.dynamicsql.repository;

import com.fine.dynamicsql.domian.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

/**
 * userRepository
 *
 * @author finefine
 */
public interface UserRepository extends JpaRepository<User,Long> , JpaSpecificationExecutor<User> {

}
  1. Repository.findAll 方法中传入 Specification，主要实现toPredicate的判断逻辑，这里我使用了lambda简写。root.get() 传入数据库标的具体字段，与传入的参数形成比较条件。
package com.fine.dynamicsql.service.impl;

import com.fine.dynamicsql.domian.User;
import com.fine.dynamicsql.repository.UserRepository;
import com.fine.dynamicsql.service.UserService;
import com.fine.dynamicsql.vo.UserSearchVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.persistence.criteria.Predicate;
import java.util.ArrayList;
import java.util.List;

/**
 * userserviceimpl
 *
 * @author finefine
 */
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserRepository userRepository;

    @Override
    public List<User> searchUser(UserSearchVO user) {

        return userRepository.findAll((root, query, criteriaBuilder) -> {
            List<Predicate> predicateList = new ArrayList<>();
            if (!StringUtils.isEmpty(user.getUsername())) {
                predicateList.add(criteriaBuilder.like(root.get("username").as(String.class),user.getUsername()));
            }
            if (user.getAge() != null&&user.getAge()>0) {
                predicateList.add(criteriaBuilder.equal(root.get("age").as(Integer.class),user.getAge()));
            }

            //and 表示数组中的所有条件都要满足 ，相当于sql 的 where a=1 and b =2
            return criteriaBuilder.and(predicateList.toArray(new Predicate[predicateList.size()]));

        });
    }

}

如果不熟悉lambda，可以使用匿名内部类的方式：

package com.fine.dynamicsql.service.impl;

import com.fine.dynamicsql.domian.User;
import com.fine.dynamicsql.repository.UserRepository;
import com.fine.dynamicsql.service.UserService;
import com.fine.dynamicsql.vo.UserSearchVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.ArrayList;
import java.util.List;

/**
 * userserviceimpl
 *
 * @author finefine
 */
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserRepository userRepository;

    @Override
    public List<User> searchUser(UserSearchVO user) {

//        return userRepository.findAll((root, query, criteriaBuilder) -> {
//            List<Predicate> predicateList = new ArrayList<>();
//            if (!StringUtils.isEmpty(user.getUsername())) {
//                predicateList.add(criteriaBuilder.like(root.get("username").as(String.class),user.getUsername()));
//            }
//            if (user.getAge() != null&&user.getAge()>0) {
//                predicateList.add(criteriaBuilder.equal(root.get("age").as(Integer.class),user.getAge()));
//            }
//
//            //and 表示数组中的所有条件都要满足 ，相当于sql 的 where a=1 and b =2
//            return criteriaBuilder.and(predicateList.toArray(new Predicate[predicateList.size()]));
//
//        });

        return userRepository.findAll(new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicateList = new ArrayList<>();
                if (!StringUtils.isEmpty(user.getUsername())) {
                    predicateList.add(criteriaBuilder.like(root.get("username").as(String.class), user.getUsername()));
                }
                if (user.getAge() != null && user.getAge() > 0) {
                    predicateList.add(criteriaBuilder.equal(root.get("age").as(Integer.class), user.getAge()));
                }

                //and 表示数组中的所有条件都要满足 ，相当于sql 的 where a=1 and b =2
                return criteriaBuilder.and(predicateList.toArray(new Predicate[predicateList.size()]));
            }
        });
    }

}

测试

  1. 数据库插入了一条数据

    image-20190427214808465

  2. 编写单元测试

    1. 查询参数 age:10 , username:张三 。 预期结果是查询size 为0

      package com.fine.dynamicsql.service;

import com.fine.dynamicsql.vo.UserSearchVO;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

/**
 * UserServiceTest
 *
 * @author finefine
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserServiceTest {

    @Autowired
    private UserService userService;

    @Test
    public void search() {
        UserSearchVO userSearchVO = new UserSearchVO();
        userSearchVO.setAge(10);
        userSearchVO.setUsername("张三");

        Assert.assertEquals(0,userService.searchUser(userSearchVO).size());
    }
}

      测试通过

      image.png
      输出的sql
      image.png

    2. 查询参数 age:10 , username:null 。 预期结果是查询size 为1。

      package com.fine.dynamicsql.service;

import com.fine.dynamicsql.vo.UserSearchVO;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

/**
 * UserServiceTest
 *
 * @author finefine
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserServiceTest {

    @Autowired
    private UserService userService;

    @Test
    public void search() {
        UserSearchVO userSearchVO = new UserSearchVO();
        userSearchVO.setAge(10);
//        userSearchVO.setUsername("张三");

        Assert.assertEquals(1,userService.searchUser(userSearchVO).size());
    }
}

      测试通过

image.png
输出sql
image.png

  1. 两次的测试中，输出的sql中where 条件不一样，第二个测试中，没有username=？这个条件，这就是toPredicate 方法内部动态条件产生的动态sql。

Querydsl

Querydsl 是一种类型安全的查询方式，不仅支持sql，还支持nosql

使用方式

  1. pom 中引入依赖

    		<dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
            <version>4.2.1</version>
            <scope>provided</scope>
        </dependency>

  2. 配置注解处理器，Querydsl 需要根据domian 动态生成QClass(实体类Q前缀)

    			<plugin>
                <groupId>com.mysema.maven</groupId>
                <artifactId>apt-maven-plugin</artifactId>
                <version>1.1.3</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>process</goal>
                        </goals>
                        <configuration>
                            <outputDirectory>target/generated-			sources</outputDirectory>
                            	<processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
                        </configuration>
                    </execution>
                </executions>
                <dependencies>
                    <dependency>
                        <groupId>com.querydsl</groupId>
                        <artifactId>querydsl-apt</artifactId>
                        <version>${querydsl.version}</version>
                    </dependency>
                </dependencies>
            </plugin>

  3. 终端执行 mvn clean compile ，会动态生成类，如下图：

image.png

实战代码

1 . Repository 继承 QuerydslPredicateExecutor

package com.fine.dynamicsql.repository;

import com.fine.dynamicsql.domian.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;

/**
 * user repository
 *
 * @author finefine
 */
public interface UserRepository extends JpaRepository<User,Long>
        , JpaSpecificationExecutor<User>
        , QuerydslPredicateExecutor<User> {

}
  1. serviceimpl
package com.fine.dynamicsql.service.impl;

import com.fine.dynamicsql.domian.QUser;
import com.fine.dynamicsql.domian.User;
import com.fine.dynamicsql.repository.UserRepository;
import com.fine.dynamicsql.service.UserService;
import com.fine.dynamicsql.vo.UserSearchVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.ArrayList;
import java.util.List;
import java.util.Spliterator;
import java.util.stream.Collectors;
import java.util.stream.StreamSupport;

/**
 * userserviceimpl
 *
 * @author finefine
 */
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserRepository userRepository;

    @Override
    public List<User> searchUser(UserSearchVO user) {

//        return userRepository.findAll((root, query, criteriaBuilder) -> {
//            List<Predicate> predicateList = new ArrayList<>();
//            if (!StringUtils.isEmpty(user.getUsername())) {
//                predicateList.add(criteriaBuilder.like(root.get("username").as(String.class),user.getUsername()));
//            }
//            if (user.getAge() != null&&user.getAge()>0) {
//                predicateList.add(criteriaBuilder.equal(root.get("age").as(Integer.class),user.getAge()));
//            }
//
//            //and 表示数组中的所有条件都要满足 ，相当于sql 的 where a=1 and b =2
//            return criteriaBuilder.and(predicateList.toArray(new Predicate[predicateList.size()]));
//
//        });

        return userRepository.findAll(new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicateList = new ArrayList<>();
                if (!StringUtils.isEmpty(user.getUsername())) {
                    predicateList.add(criteriaBuilder.like(root.get("username").as(String.class), user.getUsername()));
                }
                if (user.getAge() != null && user.getAge() > 0) {
                    predicateList.add(criteriaBuilder.equal(root.get("age").as(Integer.class), user.getAge()));
                }

                //and 表示数组中的所有条件都要满足 ，相当于sql 的 where a=1 and b =2
                return criteriaBuilder.and(predicateList.toArray(new Predicate[predicateList.size()]));
            }
        });
    }

    @Override
    public List<User> searchUserDsl(UserSearchVO user) {
        QUser qUser = QUser.user;
        //使用 StreamSupport.stream 包装成stream 再转成List
         return StreamSupport
                .stream(userRepository
                        .findAll(qUser.age.eq(user.getAge())
                                .and(qUser.username.like(user.getUsername())))
                        .spliterator(), false)
                .collect(Collectors.toList());
    }

}

测试

  1. 数据库任有一条数据

    image-20190427223051859

  2. 编写单元测试

    1. 查询参数 age:10 , username:null 。 预期结果是查询size 为1。

      package com.fine.dynamicsql.service;

import com.fine.dynamicsql.vo.UserSearchVO;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

/**
 * UserServiceTest
 *
 * @author finefine
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserServiceTest {

    @Autowired
    private UserService userService;

    @Test
    public void search() {
        UserSearchVO userSearchVO = new UserSearchVO();
        userSearchVO.setAge(10);
//        userSearchVO.setUsername("张三");

        Assert.assertEquals(1,userService.searchUser(userSearchVO).size());
    }

    @Test
    public void searchUserDsl() {
        UserSearchVO userSearchVO = new UserSearchVO();
        userSearchVO.setAge(10);
//        userSearchVO.setUsername("张三");

        Assert.assertEquals(1,userService.searchUser(userSearchVO).size());
    }
}

      测试通过

image.png
sql 输出
image.png

  1. 查询参数 age:10 , username:张三 。 预期结果是查询size 为0

    package com.fine.dynamicsql.service;

import com.fine.dynamicsql.vo.UserSearchVO;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

/**
 * UserServiceTest
 *
 * @author finefine
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserServiceTest {

    @Autowired
    private UserService userService;

    @Test
    public void search() {
        UserSearchVO userSearchVO = new UserSearchVO();
        userSearchVO.setAge(10);
//        userSearchVO.setUsername("张三");

        Assert.assertEquals(1,userService.searchUser(userSearchVO).size());
    }

    @Test
    public void searchUserDsl() {
        UserSearchVO userSearchVO = new UserSearchVO();
        userSearchVO.setAge(10);
        userSearchVO.setUsername("张三");

        Assert.assertEquals(0,userService.searchUser(userSearchVO).size());
    }
}

    测试通过

    image.png

    输出sql

image.png

  1. 可以 看到输出的sql 查询条件随着输入条件的变化而变化。

总结

使用 Specifications 非常简单，实现toPredicate 即可，无需引入第三方库。

使用QueryDsl 比较麻烦，但是它的查询功能更加丰富。

avatar
文章
阅读
粉丝