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?

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. — Mark Rotteveel
– Mark Rotteveel, Commented May 3, 2013 at 11:21
@Mark Rotteveel, mind to post your comment as an answer, so I could accept it? — aviad
– aviad, Commented May 3, 2013 at 11:28

Mark Rotteveel · Accepted Answer · 2019-09-24 06:51:05Z

5

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.

edited Sep 24, 2019 at 6:51

answered May 3, 2013 at 11:44

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Rotteveel Over a year ago

@valijon You're suggested edit is not appropriate. You're correct that the class is called java.math.BigInteger, but this is a literal quote from the JDBC 4.1 specification, which has this error.

Jasper · Accepted Answer · 2017-07-19 12:05:36Z

I had the same problem, using Types.BIGINT as a refinement works. I used it in combination with BatchPreparedStatementSetter, this is simple and very readable:

 @Transactional
public void saveStuff(List<Foo> foos) {
    int batchSize = foos.size();

    String sql = "INSERT INTO db.foo " +
            "(sting_id, a_big_integer, bar, ..etc ) " +
            "VALUES (?, ?, ?, ..etc )";

        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                FeedEntry feedEntry = feedEntries.get(i);
                int index = 1;
                ps.setString(index++, foo.id());
                ps.setObject(index++, foo.getTheBigInteger(), Types.BIGINT);
                ps.setString(index++, foo.bar())
                //etc
            }

            @Override
            public int getBatchSize() {
                return foos.size();
            }
        });

}

Nikola Loncar · Accepted Answer · 2017-11-02 14:09:23Z

0

You can use java.math.BigDecimal for conversion of BigInt. The below code should work:

public List<PersonInfo> select(String id) {

    BigDecimal bigDecId = new BigDecimal(id);
    JdbcTemplate select = new JdbcTemplate(dataSource);
    return select
        .query("SELECT * FROM PE.SUPPLIER_INPUT_DATA WHERE ID = ?",
                new Object[] { bigDecId },
                new PersonRowMapper());
}

edited Nov 2, 2017 at 14:09

Nikola Loncar

2,7111 gold badge30 silver badges39 bronze badges

answered Nov 2, 2017 at 13:54

Shirish Kota

312 bronze badges

Comments

Mircea Stanciu · Accepted Answer · 2018-06-05 21:38:21Z

0

I had the same problem and I updated the driver from 9.3 to 42.2.2. Then worked fine

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>${postgresql.version}</version>
    </dependency>

answered Jun 5, 2018 at 21:38

Mircea Stanciu

3,7633 gold badges37 silver badges39 bronze badges

Collectives™ on Stack Overflow

How to pass BigInteger from java to Postgres?

4 Answers 4

Original answer

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Original answer

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related