QueryDSL Predicate for use with JPARepository where field is a JSON String converted using an AttributeConverter to a List<Object>

• 类似问题参考 https://github.com/querydsl/querydsl/issues/2652
• https://stackoverflow.com/questions/69106849/querydsl-predicate-for-use-with-jparepository-where-field-is-a-json-string-conve/72040938#72040938

Firstly, my English is pool. So if I have some error, ignore it.

I find one way to solve this problem, main ideas is use mysql function cast(xx as char) to cheat hibrenate. Below is my base info. My code is work for company, so I make an example.

// StudentRepo.java
public interface StudentRepo<Student, Long> extends JpaRepository<Student, Long>,  QuerydslPredicateExecutor<Student>, JpaSpecificationExecutor<Student> {
}

// Student.java
@Data
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode(of = "id")
@Entity
@Builder
@Table(name = "student")
public class Student {
    @Convert(converter = ClassIdsConvert.class)
    private List<String> classIds;
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
}

// ClassIdsConvert.java
public class ClassIdsConvert implements AttributeConverter<List<String>, String> {
    @Override
    public String convertToDatabaseColumn(List<String> ips) {
        // classid23,classid24,classid25
        return String.join(",", ips);
    }
    @Override
    public List<String> convertToEntityAttribute(String dbData) {
        if (StringUtils.isEmpty(dbData)) {
            return null;
        } else {
            return Stream.of(dbData.split(",")).collect(Collectors.toList());
        }
    }
}

my db is below

id	classIds	name	address
1	2,3,4,11	join	北京市
2	2,31,14,11	hell	福建省
3	2,12,22,33	work	福建省
4	1,4,5,6	ouy	广东省
5	11,31,34,22	yup	上海市

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL,
  `classIds` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

1. Use JpaSpecificationExecutor solve the problem

Specification<Student> specification = (root, query, criteriaBuilder) -> {
    String classId = "classid24"
    String classIdStr = StringUtils.wrap(classId, "%");
    var predicate = criteriaBuilder.like(root.get("classIds").as(String.class), classIdStr);
    return criteriaBuilder.or(predicate);
};
var students = studentRepo.findAll(specification);
log.info(new Gson().toJson(students))

attention the code root.get("classIds").as(String.class)

In my opinion, if not add .as(String.class) , hibnerate will think the type of student.classIds is list. And throw Exception as below.

SQL will like below which can run correct in mysql. But hibnerate cann't work.

org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value [%classid24%] did not match expected type [java.util.List (n/a)]; nested exception is java.lang.IllegalArgumentException: Parameter value [%classid24%] did not match expected type [java.util.List (n/a)]
    
SELECT
	student0_.id AS id1_0_,
	student0_.class_ids AS class_ids2_0_
FROM
	student student0_ 
WHERE
 	student0_.class_ids LIKE '%classid24%' ESCAPE '!'

if you add .as(String.class) , hibnerate will think the type of student.classIds as string. And don't check it at all.

SQL will like below which can run correct in mysql. Also in JPA.

SELECT
	student0_.id AS id1_0_,
	student0_.class_ids AS class_ids2_0_
FROM
	student student0_ 
WHERE
 	cast( student0_.class_ids AS CHAR ) LIKE '%classid24%' ESCAPE '!'

2. when the problem is solve by JpaSpecificationExecutor, so I think this may can solve also in querydsl. At last I find the template idea in querydsl.

String classId = "classid24";
StringTemplate st = Expressions.stringTemplate("cast({0} as string)", qStudent.classIds);
var students = Lists.newArrayList<studentRepo.findAll(st.like(StringUtils.wrap(classId, "%"))));
log.info(new Gson().toJson(students));

it's sql is like below.

SELECT
	student0_.id AS id1_0_,
	student0_.class_ids AS class_ids2_0_
FROM
	student student0_ 
WHERE
 	cast( student0_.class_ids AS CHAR ) LIKE '%classid24%' ESCAPE '!'

大致的想法是用 cast(xx as char) 方法来代替 List<> 类型操作