postgresql - 如何使用 spring batch 和 postgres 在不明确的列上指定 `order by`？

Question

我正在扩展一个 JdbcPagingItemReader 来执行按列排序的查询，但它不起作用，似乎 spring batch 和 postgres 创建了不兼容的约束。

本质上，查询(使用 SqlPagingQueryProviderFactoryBean 创建)是:

select ta.my_id as i
from table_a ta join table_b tb on ta.my_id = tb.my_id 
where ta.unrelated_field = 42
order by i;

但这在结果的第二页上失败了，因为 PostgresPagingQueryProvider 修改了它 here (从 here 调用)到 where (ta.unrelated_field = 42) AND ((i > the_last_value_in_the_previous_page))，并且 postgres 不允许放置列别名 (i)在 where 子句中。

我尝试了其他方法来指定排序键，但由于以下限制，我看不到任何方法如何工作:

• Postgres 不允许在 where 子句中使用别名，例如 i。
• Postgres 不允许 order by 子句中有歧义的列，例如 my_id，因为它可以引用 2 个表中的列。
• JdbcPagingItemReader 假定 here select 子句中的排序键和列的调用完全相同
• 对 ResultSet.getObject 的调用会将键与列名列表进行比较，没有表限定符，因此 getObject("ta.my_id") 将不起作用。

那么，我怎样才能让我的初始查询起作用呢？我错过了什么？或者这是 Spring 批处理中的错误？我无法重命名 my_id 以使其在每个表中都不同。

编辑

This question面临完全相同的问题，但在他的情况下，他可以使用不合格的列名，这在我的情况下不起作用，因为该列出现在多个表中。

更多详情:

为了提供实际的代码和错误消息，下面是我在 order by 子句中使用别名时遇到的实际错误(示例中的 i 是 中的code>ss_id，my_id为subsnp_id，代码为here ):

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT distinct sub.subsnp_id AS ss_id,loc.snp_id AS rs_id,hgvs.hgvs_c AS hgvs_c_string,hgvs.start_c+1 AS hgvs_c_start,hgvs.stop_c+1 AS hgvs_c_stop,hgvs.ref_allele_c AS reference_c,hgvs.hgvs_t AS hgvs_t_string,hgvs.start_t+1 AS hgvs_t_start,hgvs.stop_t+1 AS hgvs_t_stop,hgvs.ref_allele_t AS reference_t,hgvs.var_allele AS alternate,obsvariation.pattern AS alleles,ctg.contig_name AS contig_name,loc.asn_from +1 AS contig_start,loc.asn_to +1 AS contig_end,loc.loc_type AS loc_type,ctg.contig_chr AS chromosome,loc.phys_pos_from + 1 AS chromosome_start,loc.phys_pos_from + 1 + loc.asn_to - loc.asn_from AS chromosome_end,batch.loc_batch_id_upp AS batch_name,CASE    WHEN hgvs.orient_c = 2 THEN -1 ELSE 1 END AS hgvs_c_orientation,CASE    WHEN hgvs.orient_t = 2 THEN -1 ELSE 1 END AS hgvs_t_orientation,CASE    WHEN loc.orientation = 1 THEN -1 ELSE 1 END AS snp_orientation,CASE    WHEN ctg.orient = 1 THEN -1 ELSE 1 END AS contig_orientation,CASE    WHEN link.substrand_reversed_flag = 1 THEN -1 ELSE 1 END AS subsnp_orientation FROM b150_snpcontigloc loc JOIN b150_contiginfo ctg ON ctg.ctg_id = loc.ctg_id JOIN snpsubsnplink link ON loc.snp_id = link.snp_id JOIN subsnp sub ON link.subsnp_id = sub.subsnp_id JOIN batch on sub.batch_id = batch.batch_id JOIN b150_snphgvslink hgvs ON hgvs.snp_link = loc.snp_id JOIN dbsnp_shared.obsvariation ON obsvariation.var_id = sub.variation_id WHERE (batch.batch_id = ? AND ctg.group_term IN (?) AND ctg.group_label LIKE ?) AND ((ss_id > ?)) ORDER BY ss_id ASC LIMIT 100]; nested exception is org.postgresql.util.PSQLException: ERROR: column "ss_id" does not exist
  Position: 1472
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:684) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:711) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:761) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:192) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:199) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
    at org.springframework.batch.item.database.JdbcPagingItemReader.doReadPage(JdbcPagingItemReader.java:218) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar!/:3.0.8.RELEASE]
    at org.springframework.batch.item.database.AbstractPagingItemReader.doRead(AbstractPagingItemReader.java:108) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar!/:3.0.8.RELEASE]
    at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.read(AbstractItemCountingItemStreamItemReader.java:88) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar!/:3.0.8.RELEASE]
    at uk.ac.ebi.eva.dbsnpimporter.io.readers.SubSnpCoreFieldsReader.read(SubSnpCoreFieldsReader.java:131) ~[classes!/:0.2-SNAPSHOT]
    ... 59 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: column "ss_id" does not exist
  Position: 1472
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:116) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:692) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
    ... 68 common frames omitted

Answer 1

我们遇到了这个问题，如果您在选择中为完全限定的列名称设置别名，它将起作用。您的分析是正确的，如果您执行 select ta.my_id as "ta.my_id" 那么您需要的值将在结果集中返回，并且可以在 where 和 order by 中引用相同的排序键条款。