jdbc prepared statement with oracle NUMBER type

Question

I have a java application which sets up a jdbc connection to an Orcale database. I am attempting to insert data into the database but am confused when it comes to the oracle NUMBER type. I have three columns in my table which are of these types respectively.

NUMBER(38,0)
NUMBER(20,0)
NUMBER(16,0)

My first question is what type of java type should I put the data in, in order to use it in a prepared statement.

My second question is what set operation can I use in a prepared statement in order to insert the data.

Lets just assume we are working with NUMBER(38,0). Would I set the java type to a BigInteger? If I had an integer 1 would it be

 BigInteger one = new BigInteger(1);

Then in my preparedStatement it would be

 PreparedStatement pstmt = conn.prepareStatement("INSERT INTO TABLE(bigInt) VALUES(?)");
 pstmt.setLong(1, one);

This seems to not work, so I assume that this is not correct. Any help would be appreciated.

Spotty

did you try setBigDecimal() docs.oracle.com/javase/6/docs/api/java/sql/…, java.math.BigDecimal) – Radiography

@BhavikShah I tried setBigDecimal, it is still giving me ORA-00911: invalid character. I am even building and printing out the sql insert statement and then pasting it into sql developer and it works fine. It is only failing within the java. – Spotty

I followed a few SO questions similar to this..no answers unfortunately :( ..one answer however suggested to store it as string..does this work for you? – Radiography

@Spotty - is that your actual statement, or have you edited it? Just wondering if you've left a semicolon at the end of the command string - which would work in SQL Developer but throw ORA-00911 through JDBC. – Carolacarolan

I figured out the issue.. I am embarrased to say that in my preparedStatment string I added a semicolon at the end conn.prepareStatement("INSERT INTO TABLE(bigInt) VALUES(?);"); Sorry.. thank you for the help – Spotty

Answer 1

setLong() cannot take a BigInteger. If you truly have values exceeding the range of long in your database, then you may need to use setBigDecimal(), as there is no setBigInteger(), and your variable would have to be of type BigDecimal. If long encompasses the range of values in your database, then just use long and setLong().

Quarta

Answer 2

you can try this way:

 oracle.sql.NUMBER numberValue = new oracle.sql.NUMBER(bigIntegerValue);
 cs.setObject(id, numberValue, OracleTypes.NUMBER);

where bigIntegerValue is an instance of java.math.BigInteger, it works for me

Abfarad

wont it be performance issue as it type casts the bigIntger to Number – Vertebrate