JPQL和元组列表作为SELECT IN语句的参数 [英] JPQL and list of tuples as parameter for SELECT IN statements
问题描述
给出以下表格布局:
CREATE TABLE things (
id BIGINT PRIMARY KEY NOT NULL,
foo BIGINT NOT NULL,
bar BIGINT NOT NULL
);
实体类(科特林):
@Entity
@Table(name = "things")
class Thing(
val foo: Long,
val bar: Long
) : AbstractPersistable<Long>()
还有一个存储库:
interface ThingRepository : JpaRepository<Thing, Long> {
@Query("SELECT t FROM Thing t WHERE t.foo IN ?1")
fun selectByFoos(foos: Iterable<Long>): Iterable<Thing>
@Query("SELECT t FROM Thing t WHERE (t.foo, t.bar) IN ((1, 2), (3, 4))")
fun selectByFoosAndBarsFixed(): Iterable<Thing>
@Query("SELECT t FROM Thing t WHERE (t.foo, t.bar) IN ?1")
fun selectByFoosAndBars(foosAndBars: Iterable<Pair<Long, Long>>): Iterable<Thing>
以下两个调用可以正常工作:
The following two calls work fine:
repo.selectByFoos(listOf(1L, 3L))
repo.selectByFoosAndBarsFixed()
但是，这个不是:
repo.selectByFoosAndBars(listOf(Pair(1L, 2L), Pair(3L, 4L)))
它抛出:
org.springframework.dao.DataIntegrityViolationException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not extract ResultSet
Caused by: org.h2.jdbc.JdbcSQLException: Data conversion error converting "aced00057372000b6b6f746c696e2e50616972fa1b06813de78f780200024c000566697273747400124c6a6176612f6c616e672f4f626a6563743b4c00067365636f6e6471007e000178707372000e6a6176612e6c616e672e4c6f6e673b8be490cc8f23df0200014a000576616c7565787200106a6176612e6c616e672e4e756d62657286ac951d0b94e08b020000787000000000000000017371007e00030000000000000002"; SQL statement:
/* SELECT t FROM Thing t WHERE (t.foo, t.bar) IN ?1 */ select thing0_.id as id1_0_, thing0_.bar as bar2_0_, thing0_.foo as foo3_0_ from things thing0_ where (thing0_.foo , thing0_.bar) in (? , ?) [22018-197]
Caused by: java.lang.NumberFormatException: For input string: "aced00057372000b6b6f746c696e2e50616972fa1b06813de78f780200024c000566697273747400124c6a6176612f6c616e672f4f626a6563743b4c00067365636f6e6471007e000178707372000e6a6176612e6c616e672e4c6f6e673b8be490cc8f23df0200014a000576616c7565787200106a6176612e6c616e672e4e756d62657286ac951d0b94e08b020000787000000000000000017371007e00030000000000000002"
我猜想作为参数传递的列表元素没有正确插入查询中.我该如何纠正?
I guess the elements of the list passed as parameter are not inserted correctly into the query. How can I correct this?
当然，我可以手动构建查询，如下所示:
Sure, I could manually build the query, like so:
@Repository
class SecondThingRepository(private val entityManager: EntityManager) {
fun selectByFoosAndBars(foosAndBars: Iterable<Pair<Long, Long>>): Iterable<Thing> {
val pairsRepr = foosAndBars.joinToString(prefix = "(", postfix = ")") { "(${it.first}, '${it.second}')" }
val query: TypedQuery<Thing> = entityManager.createQuery("SELECT t FROM Thing t WHERE (t.foo, t.bar) IN $pairsRepr", Thing::class.java)
return query.resultList
}
}
但这似乎很好.
推荐答案
首先，要提一个警告:并非所有数据库都支持(t.foo, t.bar) IN ((1, 2), (3, 4))语法.使用它会使您的应用程序不可移植.
First of all, a word of warning: not all databases support the (t.foo, t.bar) IN ((1, 2), (3, 4)) syntax. Using it makes your application non-portable.
我假设List中Pair的数目可以是任意的(如果不能，那么有一个更简单的解决方案，涉及将IN表达式修改为例如IN (?1, ?2, ?3)并更新查询方法接受类型为List的三个参数.我想这不是您要的).
I'm assuming the number of Pairs in the List can be arbitrary (if not, there's a much simpler solution involving the modification of the IN expression to e.g. IN (?1, ?2, ?3) and updating the query method to accept three parameters of type List. I suppose that's not what you're asking for, though).
问题是Hibernate不知道如何将Pair类映射到数据库类型.似乎收集元素的类型解析逻辑与外部"类型的解析逻辑也不相同，因此listOf(listOf(1L, 2L), listOf(3L, 4L))也不起作用.
The problem is that Hibernate does not know how to map the Pair class onto a database type. It also seems that the type resolution logic of collection elements is different from the resolution logic for the 'outer' type, so listOf(listOf(1L, 2L), listOf(3L, 4L)) won't work, either.
解决方案(请注意，这有点麻烦)是引入一个Hibernate的UserType，它能够映射Pair对象，并将此新创建的PairType用作List的元素.
The solution (and it's a bit of a hack, mind you) is to introduce a Hibernate's UserType capable of mapping Pair objects AND to use this newly created PairType for the elements of the List.
首先，将以下类添加到您的项目中:
First of all, add the following class to your project:
/* It is absolutely crucial that this class extend Pair. If the Pair class you're using
happens to be final, you will have to implement a Pair class yourself.
For an explanation of why this is required, have a look at SessionFactory.resolveParameterBindType()
and TypeResolver.heuristicType() methods */
public class PairType extends Pair<Long, Long> implements UserType {
public PairType(Long first, Long second) {
super(first, second);
}
public PairType() {
super(null, null);
}
@Override
public int[] sqlTypes() {
return new int[] {Types.ARRAY};
}
@Override
public Class returnedClass() {
return Pair.class;
}
@Override
public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session)
throws HibernateException, SQLException {
if (Objects.isNull(value)) {
st.setNull(index, Types.ARRAY);
} else {
final Pair pair = (Pair) value;
st.setArray(index, new Array() {
@Override
public Object getArray() throws SQLException {
// TODO Auto-generated method stub
return new Object[] {pair.getFirst(), pair.getSecond()};
}
...
//you can leave the rest of the autogenerated method stubs as they are
});
}
}
@Override
public Object deepCopy(Object value) throws HibernateException {
if (Objects.isNull(value)) {
return null;
}
return Pair.of(((Pair) value).getFirst(), ((Pair) value).getSecond());
}
@Override
public boolean isMutable() {
return false;
}
...
//you can leave the rest of the autogenerated method stubs as they are here as well
}
然后，将您的方法签名修改为:
Then, modify your method signature to:
selectByFoosAndBars(foosAndBars: Iterable<PairType>): Iterable<Thing>
注意:上面的解决方案对于H2数据库是开箱即用的.你的旅费可能会改变.
Note: the above solution worked for me out of the box for the H2 database. Your mileage may vary.
这篇关于JPQL和元组列表作为SELECT IN语句的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
