How to pass BigInteger from java to Postgres?

Question

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?

Answer 1

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.