Java org.hsqldb.HsqlException：数据异常：转换的字符值无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18842454/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
org.hsqldb.HsqlException: data exception: invalid character value for cast
提问by Kyle
I have a table in an HSQL database which has an identity(integer) column. I'd like to support querying against the column using an arbitrary string(potentially non-numeric). However, the HSQL JDBC driver tries to cast the query parameter to an integer and throws an exception. The Oracle driver seems to support this case fine.
我在 HSQL 数据库中有一个表，它有一个标识（整数）列。我想支持使用任意字符串（可能是非数字）对列进行查询。但是，HSQL JDBC 驱动程序尝试将查询参数转换为整数并引发异常。Oracle 驱动程序似乎很好地支持这种情况。
Any ideas to alter this behavior in the hsql driver?
有什么想法可以改变 hsql 驱动程序中的这种行为吗？
org.hsqldb:hsqldb:2.3.0
org.hsqldb:hsqldb:2.3.0
The table:
桌子：
CREATE TABLE some_table(id IDENTITY NOT NULL);
The query:
查询：
final String query = "SELECT * FROM some_table WHERE id=?";
String id = "abc";
jdbcTemplate.query(query, new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setString(1, id);
}
}, someMapper);
The exception:
例外：
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [SELECT * FROM some_table WHERE id=?]; data exception: invalid character value for cast; nested exception is java.sql.SQLDataException: data exception: invalid character value for cast
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:639)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:668)
.
.
.
Caused by: java.sql.SQLDataException: data exception: invalid character value for cast
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.throwError(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.setParameter(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.setString(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:135)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:135)
at com.stackoverflow.SomeDao.setValues(SomeDao.java:39)
at org.springframework.jdbc.core.JdbcTemplate.doInPreparedStatement(JdbcTemplate.java:644)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589)
... 33 more
Caused by: org.hsqldb.HsqlException: data exception: invalid character value for cast
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.Scanner.convertToNumber(Unknown Source)
at org.hsqldb.types.NumberType.convertToType(Unknown Source)
... 40 more
采纳答案by Kyle
The problem comes from attempting to compare a numeric column to a non-numeric string. The string is converted to a number prior to comparison and causes the cast exception to be thrown.
问题来自尝试将数字列与非数字字符串进行比较。字符串在比较之前被转换为数字并导致抛出强制转换异常。
Some work arounds:
一些解决方法：
Change the String to a long. This isn't really an option as non-hsql datasources need to support arbitrary string ids.
The dao could check that the String is numeric and only then make the query. This is acceptable, but I was hoping not to have to do this.
将字符串更改为长字符串。这不是一个真正的选项，因为非 hsql 数据源需要支持任意字符串 ID。
dao 可以检查字符串是否为数字，然后才进行查询。这是可以接受的，但我希望不必这样做。
