How to pass BigInteger from java to Postgres?
java, jdbc, postgresql, postgresql-9.2, spring
Solution
Support for `BigInteger` was added in JDBC 4.1 (Java 7), somehow I had missed that when I originally wrote this answer.
Specifically section 3.1 Overview of changes of the JDBC 4.1 specification states:
- Additional Mappings to Table B-4, Mapping from Java Object to JDBC Types [..] Support was also added to map `java.lang.BigInteger`[sic] to JDBC `BIGINT`.
- Additional Mappings to Table B-5, Performed by `setObject` and `setNull` between Java Object Types and Target JDBC Types [..] Allow conversion of `java.lang.BigInteger`[sic] to `CHAR`, `VARCHAR`, `LONGVARCHAR`, and `BIGINT`.
I'm not sure how well this is supported across drivers.
Original answer
The JDBC specification does not include support for `BigInteger`; you either need to use a different datatype (eg `BigDecimal` with scale 0), or find out if the PostgreSQL driver offers some implementation specific way to set a `BigInteger` value.
Problem
I need to pass a BigInteger argument to SQL query. (on Postgres 9.2) I have this code in my DAO: ``` public List<PersonInfo> select(String id) { BigInteger bigIntId = new BigInteger(id); JdbcTemplate select = new JdbcTemplate(dataSource); return select .query("SELECT * FROM PE.SUPPLIER_INPUT_DATA WHERE ID = ?", new Object[] { bigIntId }, new PersonRowMapper()); } ``` I am getting the following exception: ``` {"error":"Error invoking getPersonInfoById.[org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT * FROM PE.SUPPLIER_INPUT_DATA WHERE ID = ?]; nested exception is org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.math.BigInteger. Use setObject() with an explicit Types value to specify the type to use.]"} ``` The id is of type bigint Tried to pass plain String - also throws type exception. Googled the message in the exception - no relevant result. Any ideas?