org.hsqldb.HsqlException:数据异常:强制转换的字符值 [英] org.hsqldb.HsqlException: data exception: invalid character value for cast

问题描述

我在HSQL数据库中有一个表，该表中有一个identity(integer)列.我想支持使用任意字符串(可能是非数字)对列进行查询.但是，HSQL JDBC驱动程序尝试将查询参数转换为整数并引发异常. Oracle驱动程序似乎支持这种情况.

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驱动程序中更改此行为?

Any ideas to alter this behavior in the hsql driver?

org.hsqldb:hsqldb:2.3.0

org.hsqldb:hsqldb:2.3.0

表格:

CREATE TABLE some_table(id IDENTITY NOT NULL);

查询:

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);

例外:

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$2.setValues(SomeDao.java:39)
    at org.springframework.jdbc.core.JdbcTemplate$1.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

推荐答案

问题来自尝试将数字列与非数字字符串进行比较.在比较之前，该字符串将转换为数字，并导致引发强制转换异常.

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.

一些解决方法:

• 将字符串更改为长整数.作为非hsql数据源，这实际上不是一个选择 需要支持任意的字符串ID.

• Change the String to a long. This isn't really an option as non-hsql datasources need to support arbitrary string ids.

Dao可以检查String是否为数字，然后再将 询问.这是可以接受的，但我希望不必这样做.

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.

这篇关于org.hsqldb.HsqlException:数据异常:强制转换的字符值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！