How to pass BigInteger from java to Postgres?
Solution 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 mapjava.lang.BigInteger
[sic] to JDBCBIGINT
.- Additional Mappings to Table B-5, Performed by
setObject
andsetNull
between Java Object Types and Target JDBC Types
[..]
Allow conversion ofjava.lang.BigInteger
[sic] toCHAR
,VARCHAR
,LONGVARCHAR
, andBIGINT
.
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.
Solution 2
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();
}
});
}
aviad
I make things work... and then I make them smaller and faster
Updated on June 21, 2022Comments
-
aviad almost 2 years
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?
-
Mark Rotteveel over 4 years@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.