如何使用select子句中的参数化列映射iBATIS查询?

I want to have a method that finds a certain value from a column of a particular table in the database, where the name of the column is passed in as a parameter. So the Java method would have the following signature:

我希望有一个方法，该方法可以从数据库中特定表的列中查找某个值，在该列的名称作为参数传入。因此，Java方法将具有以下签名:

public Integer getFoo(String column) throws DataAccessException;

My attempted mapping for this query is the following:

我对这个查询的映射尝试如下:

<select id="getFoo" parameterClass="java.lang.String" resultClass="java.lang.Integer">
    select min($column$) from daily_statistics where $column$ &gt; 0
</select>

This fails in an interesting way. If I call this method once, it works. But if I call it twice with different column names, the second call fails with the following stack trace:

这是以一种有趣的方式失败的。如果我调用这个方法一次，它就会工作。但是如果我用不同的列名调用它两次，第二次调用将失败，其堆栈跟踪如下:

Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in com/company/project/dao/ibatis/maps/FooBar.xml.  
--- The error occurred while applying a result map.  
--- Check the getSomething-AutoResultMap.  
--- Check the result mapping for the 'MIN(FIRST_COLUMN)' property.  
--- Cause: java.sql.SQLException: Invalid column name
Caused by: java.sql.SQLException: Invalid column name
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:181)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:100)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:561)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:536)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:97)
at org.springframework.orm.ibatis.SqlMapClientTemplate$1.doInSqlMapClient(SqlMapClientTemplate.java:273)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)
... 21 more

Note that 'FIRST_COLUMN' represents the name of the first column, even though the error occurs on the second call, never on the first call.

注意，“FIRST_COLUMN”表示第一列的名称，尽管错误发生在第二个调用上，而不是在第一个调用上。

I have discovered that the following mapping will not give errors, even when called multiple times:

我发现，下面的映射不会给出错误，即使调用多次:

<select id="getFoo" parameterClass="java.lang.String" resultClass="java.lang.Integer">
    select min(ANY_COLUMN) from daily_statistics where $column$ &gt; 0
</select>

So it seems that the problem is related to the use of a parametrized column in the select clause.

因此，问题似乎与select子句中参数化列的使用有关。

1 个解决方案

#1

Use an alias in the SQL query. That should solve the problem of mapping the result back to java.

在SQL查询中使用别名。这将解决将结果映射回java的问题。