1 联合主键
- 参考博客链接 Spring Data JPA 复合主键
-
SQL脚本
-- 用户表(联合主键) DROP TABLE IF EXISTS `t_user`; CREATE TABLE `t_user` ( `user_name` varchar(10) NOT NULL COMMENT '用户名字', `user_phone` varchar(15) NOT NULL COMMENT '用户电话', `user_age` int DEFAULT NULL COMMENT '用户年龄', `user_address` varchar(20) DEFAULT NULL COMMENT '用户地址', PRIMARY KEY (`user_name`,`user_phone`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO `t_user` VALUES ('张三','18362610001',18,'洛阳'), ('张三','18362610002',20,'北京'), ('李四','18362610003',19,'上海'), ('王五','18362610004',22,'西安'), ('王五','18362610005',28,'郑州');
-
创建普通 maven 工程,引入依赖
<properties> <maven.compiler.source>11</maven.compiler.source> <maven.compiler.target>11</maven.compiler.target> </properties> <dependencies> <!-- spring-boot 测试类依赖 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <version>2.5.6</version> </dependency> <!-- jpa 依赖--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> <version>2.6.0</version> </dependency> <!-- mysql 依赖 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.22</version> </dependency> <!-- lombok 依赖 --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.16</version> </dependency> </dependencies>
-
配置文件
spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver username: root password: root url: jdbc:mysql://192.168.192.134:3306/test?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=false jpa: show-sql: true hibernate: ddl-auto: update
-
项目结构
方法一
- 使用
@IdClass + @Id
注解
-
实体类
package cn.method1; import lombok.Data; import javax.persistence.*; /** * 联合主键法一:使用 @IdClass + @Id 注解 * * @IdClass 注解指定主键的集合类,该类中的所有属性均为主键 * 若是 @IdClass(当前类.class),即当前类的字节码,那么表明当前类中的所有属性均为主键 * 不管法一还是法二,都需要定义一个主键类,因为对应的持久层接口 UserRepository 中形参2 为ID的类型,需要填 UserEntityPk */ @Entity @Table(name = "t_user") @IdClass(UserEntityID.class) @Data public class UserEntity { @Id @Column(name = "user_name") private String userName; @Id @Column(name = "user_phone") private String userPhone; @Column(name = "user_age") private Integer userAge; @Column(name = "user_address") private String userAddress; }
package cn.method1; import lombok.Data; import java.io.Serializable; /** * 主键类 UserEntityPk * <p> * 主键类需要满足以下要求(否则各种报错) * 1. 实现 Serializable 接口 * 2. 有public无参构造方法 * 3. 覆盖equals和hashCode方法(eclipse和IDEA中均可自动生成) * * @Data 注解补充:点进去后看官方解释,如下 * Generates getters for all fields, a useful toString method, and hashCode and equals implementations that check * all non-transient fields. Will also generate setters for all non-final fields, as well as a constructor. * Equivalent to {@code @Getter @Setter @RequiredArgsConstructor @ToString @EqualsAndHashCode}. * 即 @Data 等价于这五个注解 @Getter @Setter @RequiredArgsConstructor @ToString @EqualsAndHashCode */ @Data public class UserEntityID implements Serializable { private String userName; private String userPhone; // @Override // public boolean equals(Object o) { // if (this == o) return true; // if (o == null || getClass() != o.getClass()) return false; // UserEntityPk that = (UserEntityPk) o; // return Objects.equals(userName, that.userName) && Objects.equals(userPhone, that.userPhone); // } // // @Override // public int hashCode() { // return Objects.hash(userName, userPhone); // } }
-
持久层
package cn.method1; import org.springframework.data.jpa.repository.JpaRepository; /** * 联合主键,参数类型填主键类 UserEntityPk * <p> * 补充:好像getBy开头是延迟加载,findBy开头是立即加载 */ public interface UserRepository extends JpaRepository<UserEntity, UserEntityID> { }
-
测试
package test; import cn.App; import cn.method1.UserEntity; import cn.method1.UserRepository; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import java.util.List; @SpringBootTest(classes = App.class) class TestMethod1 { @Autowired private UserRepository userRepository; @Test void getAll() { List<UserEntity> list = userRepository.findAll(); list.forEach(user -> System.out.println(user)); /** * UserEntity(userName=张三, userPhone=18362610001, userAge=18, userAddress=洛阳) * UserEntity(userName=张三, userPhone=18362610002, userAge=20, userAddress=北京) * UserEntity(userName=李四, userPhone=18362610003, userAge=19, userAddress=上海) * UserEntity(userName=王五, userPhone=18362610004, userAge=22, userAddress=西安) * UserEntity(userName=王五, userPhone=18362610005, userAge=28, userAddress=郑州) */ } @Test public void testSave() { UserEntity user = new UserEntity(); user.setUserName("李小四"); user.setUserAge(22); user.setUserPhone("18362611111"); user.setUserAddress("长安"); UserEntity result = userRepository.save(user); System.out.println(result); // UserEntity(userName=李小四, userPhone=18362611111, userAge=22, userAddress=长安) } }
方法二
- 使用
@Embeddable + @EmbeddedId
注解 【推荐该方法】
-
实体类
package cn.method2; import lombok.Data; import javax.persistence.Column; import javax.persistence.EmbeddedId; import javax.persistence.Entity; import javax.persistence.Table; /** * 联合主键法二:使用 @Embeddable + @EmbeddedId 注解 * 不管法一还是法二,都需要定义一个主键类,因为对应的持久层接口 UserRepository 中形参2 为ID的类型,需要填 UserEntityPk * <p> * 注:推荐使用法二(即该方法),因为两种方案都需要单独定义一个主键类, * 法一在定义主键类的情况下还需要在实体类(即本例中的UserEntity)中写入主键的属性 * 而法二则不需要,更符合封装的思想,故感觉法二更优! */ @Entity @Table(name = "t_user") @Data public class UserEntity2 { // @AttributeOverrides(value = { // @AttributeOverride(name = "userName", column = @Column(name = "user_name")), // @AttributeOverride(name = "userPhone", column = @Column(name = "user_phnoe"))} // ) @EmbeddedId private UserEntityID2 ids; @Column(name = "user_age") private Integer userAge; @Column(name = "user_address") private String userAddress; }
package cn.method2; import lombok.Data; import javax.persistence.Column; import javax.persistence.Embeddable; import java.io.Serializable; /** * 主键类 UserEntityPk2 * <p> * 主键类需要满足以下要求(否则各种报错) * 1. 实现 Serializable 接口 * 2. 有public无参构造方法 * 3. 覆盖equals和hashCode方法 */ @Embeddable // 法2主键复合类上需要用到该注解 @Data public class UserEntityID2 implements Serializable { @Column(name = "user_name") private String userName; @Column(name = "user_phone") private String userPhone; }
-
持久层
package cn.method2; import org.springframework.data.jpa.repository.JpaRepository; import java.util.List; public interface UserRepository2 extends JpaRepository<UserEntity2, UserEntityID2> { /** * 下面定义 findBy 语句时,不要再写成下面这两行,因为会映射不到主键中的属性报错 * List<UserEntity2> findByUserName(String username); * List<UserEntity2> getByUserName(String username); * <p> * 正确写法如下,规则为 findBy主键名主键属性And主键名主键属性 * 本例中,主键名为 ids(见实体 UserEntity2 中 @EmbeddedId 注解标注的属性) * 属性名为 userName 和 userPhone (见 UserEntityID2 类中的属性) */ List<UserEntity2> findByIdsUserName(String userName); // 根据一个主键属性查 UserEntity2 findByIdsUserNameAndIdsUserPhone(String userName, String userPhone); // 根据两个主键属性查 List<UserEntity2> findByUserAgeAndUserAddress(Integer age, String address); // 根据普通属性查 }
-
测试类
package test; import cn.App; import cn.method2.UserEntity2; import cn.method2.UserEntityID2; import cn.method2.UserRepository2; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.transaction.annotation.Transactional; import java.util.List; @SpringBootTest(classes = App.class) public class TestMethod2 { @Autowired private UserRepository2 userRepository2; /** * 测试查询所有 * 主要是获取结果,用于下测试下面自定义的findBy方法时、观察是否与预期一致 */ @Test public void testFinAll() { List<UserEntity2> list = userRepository2.findAll(); list.forEach(user -> System.out.println(user)); /** * UserEntity2(ids=UserEntityID2(userName=张三, userPhone=18362610001), userAge=18, userAddress=洛阳) * UserEntity2(ids=UserEntityID2(userName=张三, userPhone=18362610002), userAge=20, userAddress=北京) * UserEntity2(ids=UserEntityID2(userName=李四, userPhone=18362610003), userAge=19, userAddress=上海) * UserEntity2(ids=UserEntityID2(userName=王五, userPhone=18362610004), userAge=22, userAddress=西安) * UserEntity2(ids=UserEntityID2(userName=王五, userPhone=18362610005), userAge=28, userAddress=郑州) */ } /** * 主键使用 @EmbeddedId 注解的情况下 * 测试自定义的 findBy 查询语句 * <p> * 测试1:根据一个主键属性查 */ @Test public void test1() { List<UserEntity2> list = userRepository2.findByIdsUserName("张三"); list.forEach(user -> System.out.println(user)); /** * UserEntity2(ids=UserEntityID2(userName=张三, userPhone=18362610001), userAge=18, userAddress=洛阳) * UserEntity2(ids=UserEntityID2(userName=张三, userPhone=18362610002), userAge=20, userAddress=北京) */ } /** * 测试2:根据两个主键属性查 * <p> * 因为 getById() 是延迟加载,故需要添加 @Transactional 注解,否则报 no session 异常 * 若是增删改的操作,再添加个 @Rollback(value = false), * 因为测试类中加事务注解后当程序运行完会默认回滚,这样就在数据库中观察不到结果了 * * 注:getBy开头是延迟加载,findBy开头是立即加载 */ @Test @Transactional public void test2() { UserEntity2 user1 = userRepository2.findByIdsUserNameAndIdsUserPhone("张三", "18362610001"); System.out.println("user1 = " + user1); UserEntity2 user2 = userRepository2.findByIdsUserNameAndIdsUserPhone("张三", "18362610002"); System.out.println("user2 = " + user2); UserEntityID2 ids = new UserEntityID2(); ids.setUserName("张三"); ids.setUserPhone("18362610002"); UserEntity2 user3 = userRepository2.getById(ids); System.out.println("user3 = " + user3); /** * 结果 * user1 = UserEntity2(ids=UserEntityID2(userName=张三, userPhone=18362610001), userAge=18, userAddress=洛阳) * user2 = UserEntity2(ids=UserEntityID2(userName=张三, userPhone=18362610002), userAge=20, userAddress=北京) * user3 = UserEntity2(ids=UserEntityID2(userName=张三, userPhone=18362610002), userAge=20, userAddress=北京) * * 对比上面的 testFinAll 结果可知,自定义的 findBy查询语句正确! * 且使用 @EmbeddedId 注解下,自带方法 getById() 也可正常使用且结果与预期一致! */ } /** * 测试3:根据普通属性查 */ @Test public void test3() { List<UserEntity2> list = userRepository2.findByUserAgeAndUserAddress(18, "洛阳"); list.forEach(user -> System.out.println(user)); // UserEntity2(ids=UserEntityID2(userName=张三, userPhone=18362610001), userAge=18, userAddress=洛阳) } }
2 动态SQL
2.1 实操
概述
-
JpaSpecificationExecutor
方法列表public interface JpaSpecificationExecutor<T> { // 1. 查询单个对象 Optional<T> findOne(@Nullable Specification<T> var1); // 2. 查询列表 List<T> findAll(@Nullable Specification<T> var1); // 3. 分页查询全部,返回值:分页pageBean(由 spring data jpa 提供) Page<T> findAll(@Nullable Specification<T> var1, Pageable var2); // 4. 查询全部并排序 List<T> findAll(@Nullable Specification<T> var1, Sort var2); // 5. 统计数据条数 long count(@Nullable Specification<T> var1); }
-
Specification
查询条件/** * 定义我们自己的Specification实现类,并实现下面的方法 * * p1 : root是查询的根对象,查询的任何属性都可以从根对象中获取 * p2 : criteriaQuery是顶层查询对象,自定义查询方式(了解即可,一般用不) * p3 : criteriaQuery是查询构造器,封装了很多的查询条件 */ public Predicate toPredicate(Root<ProductEntityVO> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) { }
-
SQL脚本
DROP TABLE IF EXISTS `t_user`; CREATE TABLE `t_user` ( `user_id` int NOT NULL AUTO_INCREMENT, `user_name` varchar(10) NOT NULL COMMENT '用户名字', `user_phone` varchar(15) NOT NULL COMMENT '用户电话', `user_age` int NOT NULL COMMENT '用户年龄', `user_address` varchar(20) NOT NULL COMMENT '用户地址', PRIMARY KEY (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO `t_user` VALUES (1,'张三','18362610001',18,'河南郑州'), (2,'李四','15962610002',20,'河南洛阳'), (3,'李小四','18762610007',20,'河南偃师'), (4,'王五','15162610006',15,'北京'), (5,'李四','18362610008',22,'中国河南');
编码
-
实体类
package cn.dynamicSQL; import lombok.Data; import javax.persistence.*; @Entity @Table(name = "t_user") @Data public class UserEntity3 { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "user_id", nullable = false) private Integer userId; @Column(name = "user_name", nullable = false) private String userName; @Column(name = "user_phone", nullable = false) private String userPhone; @Column(name = "user_age", nullable = false) private Integer userAge; @Column(name = "user_address", nullable = false) private String userAddress; }
-
持久层
package cn.dynamicSQL; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.JpaSpecificationExecutor; // 此时除了要继承 JpaRepository 接口外,还要继承 JpaSpecificationExecutor 接口 public interface UserRepository3 extends JpaRepository<UserEntity3, Integer>, JpaSpecificationExecutor<UserEntity3> { }
-
测试
package test; import cn.App; import cn.dynamicSQL.UserEntity3; import cn.dynamicSQL.UserRepository3; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.data.domain.Page; import org.springframework.data.domain.PageRequest; import org.springframework.data.domain.Pageable; import org.springframework.data.domain.Sort; import org.springframework.data.jpa.domain.Specification; import javax.persistence.criteria.*; import java.util.List; @SpringBootTest(classes = App.class) public class TestDynamicSQL { /** * 注意导入 dynamicSQL 包下的 UserRepository接口 * 另外,记得重新运行SQL脚本,动态SQL的 t_user 表数据与前面的联合主键 t_user 数据不一样 */ @Autowired private UserRepository3 userRepository; /** * 测试1:查询所有 */ @Test public void test1() { List<UserEntity3> list = userRepository.findAll(); list.forEach(user -> System.out.println(user)); /** * UserEntity33(userId=1, userName=张三, userPhone=18362610001, userAge=18, userAddress=河南郑州) * UserEntity33(userId=2, userName=李四, userPhone=15962610002, userAge=20, userAddress=河南洛阳) * UserEntity33(userId=3, userName=李小四, userPhone=18762610007, userAge=20, userAddress=河南偃师) * UserEntity33(userId=4, userName=王五, userPhone=15162610006, userAge=15, userAddress=北京) * UserEntity33(userId=5, userName=李四, userPhone=18362610008, userAge=22, userAddress=中国河南) */ } /** * 测试2:根据条件,动态查询单个对象 * 需求:根据用户名查询,查询客户名为"李四"的客户 */ @Test public void test2() { /** * 匿名内部类,用于自定义查询条件 * 1. 实现 Specification<T> 接口,泛型为查询的对象类型 * 2. 重写 toPredicate 方法,该方法主要用于构造查询条件 * 3. 需要借助 toPredicate 方法中的两个形参, * root 用于获取需要查询的对象属性, * criteriaBuilder 用于构造查询条件,内部封装了很多查询条件(如模糊/精确匹配等等) * 补充:criteria 单词的意思为:标准、条件 * * 注:想要动态查询,需要满足下面两个条件 * (1). 比较的属性名称,在root中可以获取 * (2). 查询方式,都封装在 criteriaBuilder 对象中 */ // Specification<UserEntity3> spec = new Specification<UserEntity3>() { // @Override // public Predicate toPredicate(Root<UserEntity3> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) { // // 1. 获取比较的属性 // Path<Object> userName = root.get("userName"); // /** // * 2. 构造查询条件,精确查询用equal // * p1:需要比较的属性(Path对象) // * p2:当前需要比较的取值 // * // * eg. select * from t_user where user_name = "李四"; // * p1即指 user_name(只不过此时要用属性名而非数据库字段名),p2即指"李四" // */ // Predicate predicate = criteriaBuilder.equal(userName, "李四"); // return predicate; // } // }; // java8 lambda表达式写法,效果与上面等价,但更简练 Specification<UserEntity3> spec = (root, query, criteriaBuilder) -> { Path<Object> userName = root.get("userName"); Predicate predicate = criteriaBuilder.equal(userName, "李四"); return predicate; }; List<UserEntity3> list = userRepository.findAll(spec); list.forEach(user -> System.out.println(user)); /** * UserEntity33(userId=2, userName=李四, userPhone=15962610002, userAge=20, userAddress=河南洛阳) * UserEntity33(userId=5, userName=李四, userPhone=18362610008, userAge=22, userAddress=中国河南) */ } /** * 测试 3 * 需求:根据用户名和地址和年龄查询,查询用户名为"李四"、住址含"河南"、年龄小于等于"25",最后将结果按年龄降序排序 */ @Test public void test3() { // 1. 构造查询条件 Specification<UserEntity3> spec = new Specification<UserEntity3>() { @Override public Predicate toPredicate(Root<UserEntity3> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) { Path<Object> userName = root.get("userName"); Path<Object> userAddress = root.get("userAddress"); Path<Object> userAge = root.get("userAge"); Predicate p1 = criteriaBuilder.equal(userName, "李四"); /** * 注:equal方法可以直接得到path对象(属性),然后进行比较 * like、gt、lt、ge、le等:得到path对象,根据path指定比较的类型参数,再去进行比较 * (原因:上面得到的都为Path<Object>,若是不指定数据类型的字节码,程序不知道是数字还是字符串...) */ Predicate p2 = criteriaBuilder.like(userAddress.as(String.class), "%河南%"); /** * criteriaBuilder.and() 以"与"的形式拼接多个查询条件 * criteriaBuilder.or() 以"或"的形式拼接多个查询条件 * and 、 or 的选择由具体业务而定 */ Predicate p3 = criteriaBuilder.le(userAge.as(Integer.class), 25); Predicate predicate = criteriaBuilder.and(p1, p2, p3); return predicate; } }; /** * 2. 构造排序对象,需要调用构造方法实例化对象 * p1:顺序类型(升序ACS/降序DESC), * p2:排序的属性名 */ Sort sort = Sort.by(Sort.Direction.DESC, "userAge"); List<UserEntity3> list = userRepository.findAll(spec, sort); list.forEach(user -> System.out.println(user)); System.out.println("满足条件的数据共 count = " + userRepository.count(spec)); /** * 控制台sql * SELECT UserEntity30_.user_id AS user_id1_0_, * UserEntity30_.user_address AS user_add2_0_, * UserEntity30_.user_age AS user_age3_0_, * UserEntity30_.user_name AS user_nam4_0_, * UserEntity30_.user_phone AS user_pho5_0_ * FROM t_user UserEntity30_ * WHERE UserEntity30_.user_name=? * AND (UserEntity30_.user_address LIKE ?) * AND UserEntity30_.user_age<=25 * ORDER BY UserEntity30_.user_age desc * * 结果 * UserEntity3(userId=5, userName=李四, userPhone=18362610008, userAge=22, userAddress=中国河南) * UserEntity3(userId=2, userName=李四, userPhone=15962610002, userAge=20, userAddress=河南郑州) * 满足条件的数据共 count = 2 */ } /** * 测试4:分页查询 * <p> * 不带条件的分页 :Page<T> findAll(Pageable var1); * 带有条件的分页 : Page<T> findAll(@Nullable Specification<T> var1, Pageable var2); * Specification为查询条件,Pageable为分页参数(含查询页码、每页查询的条数) * 返回值 Page 是 JPA 为我们封装好的 pageBean 对象,可以获取数据列表(getContent)、总条数(getTotalElements)等信息 * <p> * 注:PageRequest 是 Pageable 接口的实现类 * p1:当前查询的页数(从0)开始 * p2:每页查询的数量 */ @Test public void test4() { Pageable pageable = PageRequest.of(1, 2); Page<UserEntity3> page = userRepository.findAll(pageable); System.out.println("得到数据结果列表:" + page.getContent()); System.out.println("得到总条数:" + page.getTotalElements()); System.out.println("得到总页数:" + page.getTotalPages()); /** * 得到数据结果列表: * [UserEntity3(userId=3, userName=李小四, userPhone=18762610007, userAge=20, userAddress=河南偃师), * UserEntity3(userId=4, userName=王五, userPhone=15162610006, userAge=15, userAddress=北京)] * 得到总条数:5 * 得到总页数:3 */ } }
2.2 样例
样例一
/**
* @Author likf
* @Date 2020/12/14 16:35
* 需求:SearchProductInVO有productStatus、productCategoryId、productTagId、productName、productSellingPrice五个属性
* 然后根据传入的属性是否为空进行动态SQL拼接
* 且将查询结果进行分页和排序
* 注:productTagId 1/2/3分别对应新品/热销/推荐标签,在数据库中为三个字段且均为Boolean类型
*/
@Service
public class MGetAllProductLstServiceImpl extends BaseServiceImpl implements MGetAllProductLstService {
@Autowired
private ProductEntityRepo productEntityRepo;
@Override
public void doCheck(SearchProductInVO searchProductInVO) {
}
@Override
public ResponseVO doLogic(SearchProductInVO inVO, Map<String, String> redisSessionConfig) {
ResponseVO responseInfo = super.getResponseVO();
SearchProductOutVO outVO = new SearchProductOutVO();
Specification<ProductEntityVO> spec = new Specification<ProductEntityVO>() {
@Override
public Predicate toPredicate(Root<ProductEntityVO> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
Path<Object> productStatus = root.get("productStatus");
Path<Object> categoryId = root.get("categoryId");
Path<Object> productNewTag = root.get("productNewTag");
Path<Object> productHotTag = root.get("productHotTag");
Path<Object> productRecommendTag = root.get("productRecommendTag");
Path<Object> productName = root.get("productName");
Path<Object> productSellingPrice = root.get("productSellingPrice");
Path<Object> productFailure = root.get("productFailure");
List<Predicate> predicates = new ArrayList<>();
if (!StringUtils.isEmpty(inVO.getSearchProductVO().getProductStatusId())) {
/**
* 法1:
* 因为上面 productStatus 返回类型为 Path<Object> ,因此此处需要 as(String.class) 进行类型转化
* 若是上面返回类型修改成 Path<String> 则此处不再需要进行类型转换
*
* 法2:直接写成如下代码
* Predicate p1 = criteriaBuilder.equal(root.get("productStatus"), inVO.getSearchProductVO().getProductStatusId());
* 此时不进行类型转换编译不会报错,若是真数据查询时出错的话,再加 as(xxx.class) 进行数据类型转换
*/
Predicate p1 = criteriaBuilder.equal(productStatus.as(String.class), inVO.getSearchProductVO().getProductStatusId());
predicates.add(p1);
}
if (!StringUtils.isEmpty(inVO.getSearchProductVO().getProductCategoryId())) {
Predicate p2 = criteriaBuilder.equal(categoryId, inVO.getSearchProductVO().getProductCategoryId());
predicates.add(p2);
}
if (!StringUtils.isEmpty(inVO.getSearchProductVO().getProductTagId())) {
if (Objects.equals(inVO.getSearchProductVO().getProductTagId(), "02")) {
Predicate p3 = criteriaBuilder.equal(productNewTag, true);
predicates.add(p3);
}
if (Objects.equals(inVO.getSearchProductVO().getProductTagId(), "03")) {
Predicate p4 = criteriaBuilder.equal(productHotTag, true);
predicates.add(p4);
}
if (Objects.equals(inVO.getSearchProductVO().getProductTagId(), "01")) {
Predicate p5 = criteriaBuilder.equal(productRecommendTag, true);
predicates.add(p5);
}
}
if (!StringUtils.isEmpty(inVO.getSearchProductVO().getProductName())) {
Predicate p6 = criteriaBuilder.like(productName.as(String.class), "%" + inVO.getSearchProductVO().getProductName() + "%");
predicates.add(p6);
}
if (!StringUtils.isEmpty(inVO.getSearchProductVO().getProductMinPrice())) {
// 若是 ge() 报错 改成 greaterThanOrEqualTo(),这俩应该都是 大于等于,但是为啥一个编译报错一个不报暂时未知
Predicate p7 = criteriaBuilder.ge(productSellingPrice.as(Double.class), inVO.getSearchProductVO().getProductMinPrice());
predicates.add(p7);
}
if (!StringUtils.isEmpty(inVO.getSearchProductVO().getProductMaxPrice())) {
Predicate p8 = criteriaBuilder.le(productSellingPrice.as(Double.class), inVO.getSearchProductVO().getProductMaxPrice());
predicates.add(p8);
}
// 拼接商品未失效条件
Predicate p9 = criteriaBuilder.equal(productFailure, false);
predicates.add(p9);
return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
}
};
//设置排序和分页
Sort sort = Sort.by(Sort.Direction.ASC, "productId");
Pageable pageable = PageRequest.of(inVO.getPageVO().getCurrentPage(), inVO.getPageVO().getPageSize(), sort);
Page<ProductEntityVO> list = productEntityRepo.findAll(spec, pageable);
if (list.getContent().size() > 0) {
for (ProductEntityVO entity : list.getContent()) {
ProductInfoListVO temp = new ProductInfoListVO();
BeanUtils.copyProperties(entity, temp);
temp.setFailure(entity.getProductFailure());
temp.setProductCategory(entity.getCategoryId());
outVO.getProductInfoListVOList().add(temp);
}
//设置返回的pagevo值
PageVO pageVO = new PageVO();
pageVO.setCurrentPage(inVO.getPageVO().getCurrentPage());
pageVO.setPageSize(list.getSize());
pageVO.setTotal(list.getTotalElements());
outVO.setPageVO(pageVO);
responseInfo.setData(outVO);
} else {
responseInfo.setMessageToHeader(GGMMessageEnums.MSG_0002);
}
return responseInfo;
}
}
样例二:A and ( B or C )
- 参考链接 JPA多条件查询之AND和OR混合查询
/**
* 思路:
* 1. 在List中加入所有 and 条件集合, 然后使用 criteriaBuilder.and() 转化为 andPredicate
* 2. 在List中加入所有 or 条件集合,然后使用 criteriaBuilder.or() 转化为 orPredicate
* 3. 使用 criteriaBuilder.and(andPredicate, orPredicate) ,即实现 A and ( B or C )
* 注:当后面条件为空时,会拼接 and 0 = 1,解决方法看最后一行的返回值!!!
*/
@Override
public ResponseVO doLogic(GetAllProductsInVO inVO, Map<String, String> redisSessionConfig) {
Specification<ProductEntityVO> spec = new Specification<ProductEntityVO>() {
@Override
public Predicate toPredicate(Root<ProductEntityVO> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
// and 条件集合
List<Predicate> andList = new ArrayList<>();
Predicate p1 = criteriaBuilder.equal(root.get("productStatus"), "02");
andList.add(p1);
Predicate p2 = criteriaBuilder.equal(root.get("productFailure"), false);
andList.add(p2);
if (!StringUtils.isEmpty(inVO.getProductsSearchVO().getProductCategoryId())) {
Predicate p3 = criteriaBuilder.equal(root.get("categoryId"), inVO.getProductsSearchVO().getProductCategoryId());
andList.add(p3);
}
Predicate andPredicate = criteriaBuilder.and(andList.toArray(new Predicate[andList.size()]));
// or 条件集合
List<Predicate> orList = new ArrayList<>();
if (!StringUtils.isEmpty(inVO.getProductsSearchVO().getSearchKeyValue())) {
Predicate p8 = criteriaBuilder.like(root.get("productName"), "%" + inVO.getProductsSearchVO().getSearchKeyValue() + "%");
Predicate p9 = criteriaBuilder.like(root.get("productTitle"), "%" + inVO.getProductsSearchVO().getSearchKeyValue() + "%");
orList.add(p8);
orList.add(p9);
}
Predicate orPredicate = criteriaBuilder.or(orList.toArray(new Predicate[orList.size()]));
/**
* 若 orPredicate 表达式的个数等于0 ,则不能进行拼接 criteriaBuilder.and(andPredicate, orPredicate)
* 若是在 orPredicate.getExpressions().size() == 0 的情况下进行拼接,where会出现 and 0=1 造成 false ,
* 导致非预期结果
*/
return orPredicate.getExpressions().size() == 0 ? criteriaBuilder.and(andPredicate) : criteriaBuilder.and(andPredicate, orPredicate);
}
};
Sort order = Sort.by(Sort.Direction.DESC, "createTime");
Pageable pageable = PageRequest.of(inVO.getPageVO().getCurrentPage(), inVO.getPageVO().getPageSize(), order);
Page<ProductEntityVO> list = productInitEntityRepo.findAll(spec, pageable);
// 得到数据后剩下的处理略...
}
样例三:@Query 实现动态SQL
-
即使用MySQL的
if(判断条件,结果为true时执行的sql,结果为false时执行的sql)
函数,若是条件非空进行sql拼接,否则拼接 where 1=1 ,重在理解思想!!!@Query(value = "select * from project_demand where project_id=?1 and if(?2!='',demand_id in (select demand_id from demand_user where user_id=?2),1=1)", nativeQuery = true) List<ProjectDemand> getListByUser(String projectId,String userId);
样例四:手动拼接SQL
// 模拟传入参数的inVO
@Data
public class TestInVO {
private List<String> idList = new ArrayList<>();
private String productName;
private String categoryId;
private Double startPrice;
private Double endPrice;
}
// 模拟从数据库查询所需字段的daoVO
@Data
@AllArgsConstructor
@NoArgsConstructor
public class TestDaoVO3 {
private String productId;
private String productName;
private String categoryId;
private Double productSellingPrice;
private String productPicture;
}
// 测试
RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = GGMServerApplication.class)
public class dynamicTest {
//jpa的数据库操作类
@PersistenceContext
private EntityManager entityManger;
/**
* 注:
* (1). 两个表中不能有相同列,若是有的话使用as命名别名,否则报 NonUniqueDiscoveredSqlAliasException 异常
* (2). sql拼接完之后都加个空格,以免与下一次拼接字母连接在一起,造成sql语法错误
*/
@Test
public void test4() {
TestInVO inVO = new TestInVO();
// inVO.setIdList(Lists.newArrayList("ggm_product_0007", "ggm_product_0008", "ggm_product_0009"));
// inVO.setCategoryId("2");
// inVO.setProductName("小米");
inVO.setStartPrice(500d);
inVO.setEndPrice(700d);
StringBuffer sql = new StringBuffer("select new com.gmall.dao.test.TestDaoVO3(" +
"x.productId, " +
"x.productName, " +
"x.categoryId, " +
"x.productSellingPrice, " +
"y.productPicture) " +
"from ProductEntityVO x " +
"left join ProductPictureEntityVO y " +
"on x.productId = y.productId " +
"where 1 =1 "
);
Map<String, Object> params = new HashMap<>();
if (inVO.getIdList().size() > 0) {
sql.append("and x.productId in (:idList) ");
params.put("idList", inVO.getIdList());
}
if (StringUtils.isNotBlank(inVO.getCategoryId())) {
sql.append("and x.categoryId = :categoryId ");
params.put("categoryId", inVO.getCategoryId());
}
if (StringUtils.isNotBlank(inVO.getProductName())) {
sql.append("and x.productName like :productName ");
params.put("productName", "%" + inVO.getProductName() + "%");
}
if (inVO.getStartPrice() != null && inVO.getEndPrice() != null) {
sql.append("and x.productSellingPrice >= :startPrice and x.productSellingPrice <= :endPrice ");
params.put("startPrice", inVO.getStartPrice());
params.put("endPrice", inVO.getEndPrice());
} else if (inVO.getStartPrice() != null) {
sql.append("and x.productSellingPrice >= :startPrice ");
params.put("startPrice", inVO.getStartPrice());
} else if (inVO.getEndPrice() != null) {
sql.append("and x.productSellingPrice <= :endPrice ");
params.put("endPrice", inVO.getEndPrice());
}
/**
* 进行查询,并将参数传入 sql 中
* entityManger.createNativeQuery() 方法使用原生SQL查询
* entityManger.createQuery() 使用JPQL语法查询
*/
Query query = entityManger.createQuery(sql.toString());
for (String key : params.keySet()) {
query.setParameter(key, params.get(key));
}
List<TestDaoVO3> resultList = query.getResultList();
for (TestDaoVO3 vo3 : resultList) {
System.out.println(vo3);
}
}
}
- 源码链接:blogs-jpa