java.sql.SQLException: Bad value for type BigDecimal

Hibernate ORM
1

I’m using SQLite + Hibernate in my JAVA app. I’m getting an error java.sql.SQLException: Bad value for type BigDecimal while executing a SQL query using hibernate’s NativeQueryImpl class. Below is my code which I’m using

  public List<Map<String, Object>> executeNativeQuery(String query) {
    NativeQueryImpl nativeQuery = (NativeQueryImpl) entityManager.createNativeQuery(query);
    nativeQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
    return nativeQuery.getResultList();
  }

I’m calling executeNativeQuery function by passing a SQL query and getting the error at the line return nativeQuery.getResultList();. Looks like somewhere while extracting the result hibernate is trying to extract String value as BigDecimal.

Exception log

java.sql.SQLException: Bad value for type BigDecimal : An elegant piece with blue and white Zircon stones studded like a scintillating star-shaped evil eye to ward off the negative vibes. Available in Silver and Rose Gold Plating. 92.5 Sterling Silver AAA Cubic Zirconia Handcrafted and Electroplated with Rhodium for strength and luster. Consciously crafted with non-allergic materials, thus making it hypoallergenic. 92.5 Sterling Silver Authenticity Certificate included. All our jewellery is handcrafted and therefore variations may occur.\n\tat org.sqlite.jdbc3.JDBC3ResultSet.getBigDecimal(JDBC3ResultSet.java:190)\n\tat org.sqlite.jdbc3.JDBC3ResultSet.getBigDecimal(JDBC3ResultSet.java:199)\n\tat com.zaxxer.hikari.pool.HikariProxyResultSet.getBigDecimal(HikariProxyResultSet.java)

Hibernate Version : 5.4.25.Final

SQLite JDBC driver

implementation("org.xerial:sqlite-jdbc:3.36.0.3")

SQLite Dialect

implementation("com.github.gwenn:sqlite-dialect:0.1.0")

And one more thing this is not failing for all queries of the same kind. It would be really helpful if I get any help here

2

Since we don’t officially support SQLite and I doubt that the SQLite JDBC or dialect developers are active here, I fear this might not be the right place to ask this question.
OTOH, this looks like a JDBC driver issue to me, as Hibernate is asking the ResultSet about the types of the result columns and then uses these to fetch values. So the JDBC driver reports java.sql.Types.NUMERIC or java.sql.Types.DECIMAL for that column and Hibernate uses ResultSet#getBigDecimal due to that, but apparently the data is a String.

I don’t know how SQLite works to be honest, but I thought it is “schemaless”, in which case the only “sane” type code for it to return would be java.sql.Types.VARCHAR.
If you save heterogenous data, it might try to be smart and detect the type somehow, but seems it fails to do so reliably.

Either way, I think you should ask that question in the issue trackers of the JDBC driver project or the Dialect project

1 Like
3

Thank you for the quick response :pray: