10

I want to insert a Big integer value using prepared statement, i have one string variable called xid (41527820021925053) 

    preparedStatement = conn.prepareStatement(sql);
    preparedStatement.setObject(1,XOBJ);
    preparedStatement.setObject(2,YOBJ);
    preparedStatement.setBigInteger(3, xid);
    preparedStatement.setInt(4, 23);
    preparedStatement.executeUpdate();
    preparedStatement.close();

I am new to this how to achieve this.

Nat
  • 3,587
  • 20
  • 22
  • 1
    Are you actually talking about `java.math.BigInteger`? – Mark Rotteveel Nov 02 '16 at 10:18
  • 1
    *FYI:* According to [this comment by OP](http://stackoverflow.com/questions/40376198#comment68009412_40377394), this question is about the **PostgreSQL `bigint` data type**, not about the Java `BigInteger` type. – Andreas Nov 02 '16 at 11:40

3 Answers3

20

PreparedStatement doesn't have a setBigInteger() method.

Use one of these methods:

UPDATE

With the following comment made by OP, the second option above (now highlighted) is the correct option to use, since PostgreSQL bigint is the same a Java long.

guys i am using postgres & it has bigint ["UniqueIdGenerator"()] data-type,which is a 17 digit big integer.

Andreas
  • 154,647
  • 11
  • 152
  • 247
1

If you are talking about an integer or long value in a String, then you should be able to just use setString(idx, yourValue), setObject(idx, yourValue), or setObject(idx, yourValue, java.sql.Types.BIGINT) the driver should convert this to the target type.

If you are talking about a java.math.BigInteger, then a JDBC 4.1 (or higher) compliant driver should allow you to set a BigInteger value to a BIGINT, CHAR, VARCHAR or LONGVARCHAR column using setObject(idx, yourBigInteger), or setObject(idx, yourBigInteger, targetType) where targetType is for example java.sql.Types.BIGINT or java.sql.Types.VARCHAR.

However, be aware that not all drivers implement this support.

See JDBC 4.1 specification section 3.1 Overview of changes, table B-4 Mapping from Java Object Types to JDBC Types, table B-5 Conversions Performed by setObject and setNull Between Java Object Types and Target JDBC Types. Or alternatively, JDBC 4.2 specification, but then only tables B-4 and B-5.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • 1
    A SQL data type that can take 20+ digits for an integer value is likely defined a `NUMERIC(n, 0)` (or `DECIMAL` and/or without `0` argument or whatever the RDBMS dialect calls it), which is unlikely to be a JDBC type of `BIGINT`. As such, `setObject()` with a `BigInteger` value is fairly useless for anything other than text columns. If they at least had defined that `BigInteger` should map to `DECIMAL` / `NUMERIC`, the support for `BigInteger` would have been useful. – Andreas Nov 02 '16 at 10:41
  • @Andreas That may be, but JDBC 4.1 **defines this mapping**, so drivers **must** support it. But it is a good point, I'll bring it up in the JDBC Expert Group. – Mark Rotteveel Nov 02 '16 at 11:14
  • 1
    Yeah, I think `BigInteger` should map to all the same JDBC types as `BigDecimal`. I mean, if `BigDecimal` can map to `TINYINT`, why can't `BigInteger`? Especially given that `BigInteger` is *logically* a `BigDecimal` restricted to a scale of 0. – Andreas Nov 02 '16 at 11:24
  • guys i am using postgres & it has bigint ["UniqueIdGenerator"()] data-type,which is a 17 digit big integer. –  Nov 02 '16 at 11:26
  • @Andreas Yes, I was also a bit surprised about that omission when I recently implemented support for this in Jaybird. – Mark Rotteveel Nov 02 '16 at 11:26
  • 1
    @Helper 17 digits fit in a long, so you don't even need to use `BigInteger` or a string. – Mark Rotteveel Nov 02 '16 at 11:27
  • That depends on our database structure, I can't do anything. i need to accomplish my task, thats it. –  Nov 02 '16 at 11:28
  • @MarkRotteveel So you implemented all the mappings of course, right? ;-) The JDBC spec just lists the minimum required mapping support. – Andreas Nov 02 '16 at 11:28
  • @Andreas I didn't, yet, but good point :). I reopened that improvement ticket. – Mark Rotteveel Nov 02 '16 at 11:31
  • 3
    @Helper You seem confused. The PostgreSQL data type [`bigint`](https://www.postgresql.org/docs/current/static/datatype-numeric.html) is *exactly* the same as a Java `long`, i.e. an 8-byte [two's-compliment](https://en.wikipedia.org/wiki/Two%27s_complement) integer value. So, if your question is about the PostgreSQL `bigint` type, and not about the Java `BigInteger` type, which is what it appears to be about, then please edit the question to clarify it, e.g. say the exact data type name and mention the RDBMS vendor / dialect. – Andreas Nov 02 '16 at 11:34
  • Yes you are 100% correct i have not tested for long thanks @Andreas –  Nov 02 '16 at 11:51
0

You can try 

    preparedStatement = conn.prepareStatement(sql);
    preparedStatement.setObject(1,XOBJ);
    preparedStatement.setObject(2,YOBJ);
    preparedStatement.setBigDecimal(3, BigDecimal.valueOf(Long.parseLong(xid))); //or you can try below
    preparedStatement.setBigDecimal(3, new BigDecimal(xid)); //both are correct
    preparedStatement.setInt(4, 23);
    preparedStatement.executeUpdate();
    preparedStatement.close();
deadpool
  • 436
  • 2
  • 10
  • 24
  • 3
    If you use `parseLong()`, why not call `setLong()`? If you want to use `setBigDecimal()`, use `new BigDecimal(xid)`. As the answer is right now, it's wrong. – Andreas Nov 02 '16 at 09:21
  • Both are only correct *if* the value of `xid` is limited to the range of values supported by `long`. Question doesn't specify if that is the case (example value is not an indicator of extreme value). – Andreas Nov 02 '16 at 11:36
  • 1
    @helper mentioned that it was a 17 digit big number right and he has a string variable called xid. – deadpool Nov 02 '16 at 11:38
  • 1
    You didn't know that when you wrote that "both are correct", so as far as you *knew*, they weren't not both correct. Now that you do know, they do both give the correct result for all input values, but the "correct" answer would be to use `setLong()`. If you always had in mind that value would fit in a `long`, then that should have been your answer from the beginning. – Andreas Nov 02 '16 at 11:52