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;
-
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 ofstudent.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 ofstudent.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 '!'
-
when the problem is solve by
JpaSpecificationExecutor
, so I think this may can solve also in querydsl. At last I find thetemplate
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<> 类型操作
(吐槽一下,为什么不支持markdown格式编辑器)