2

I am trying to get Oracle SQL Data into Java JBDC.

What is the Java convert data type equivalent of Number(38) in Oracle? Is it BigInteger or Long?

create table Customers
(
    Customer_id                   NUMBER(38)
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
mattsmith5
  • 540
  • 4
  • 29
  • 67

1 Answers1

5

long is not enough. It only have 19 decimals:

Maximum value = 2^63-1 = 9,223,372,036,854,775,807.

You need to use BigInteger. BigDecimal should also be fine.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
talex
  • 17,973
  • 3
  • 29
  • 66
  • Or `BigDecimal`? – andrewJames Aug 22 '21 at 19:47
  • I don't see BigInteger in the result set mapper below, rs, would BigDecimal work also? List customerIdList = namedParameterJdbcTemplate.query(GET_CUSTOMER_ID_QUERY, (rs, rowNum) -> rs.get.. , related question here, https://stackoverflow.com/questions/68884237/how-to-query-for-a-listinteger-in-jdbctemplate – mattsmith5 Aug 22 '21 at 19:48
  • cc @andrewjames comment above – mattsmith5 Aug 22 '21 at 19:52
  • 1
    @andrewjames you can, but AFAIR if you don't specify number of digits after decimal point it is considered 0. – talex Aug 22 '21 at 19:53
  • 1
    @talex Yes, the scale (2nd argument) defaults to 0, so `NUMBER( 38 )` is effectively a whole number (no fraction). See [documentation](https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT313). – Basil Bourque Aug 22 '21 at 19:57
  • I corrected your number from 18 digits to 19, per my calculations of [`Long.MAX_VALUE`](https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/lang/Long.html#MAX_VALUE). Am I right? – Basil Bourque Aug 22 '21 at 20:05
  • @BasilBourque Yes, 18 whole. If you use 19 you won't be able to convert all possible values from `long` to JDBC. If you use 18 you can't convert from JDBC to `long`. Chose wisely :) – talex Aug 22 '21 at 20:10
  • I shouldn't even use long in the first place, just having a row mapping issue, seems getBigDecimal is utilized in resultset. , get BigInteger isn't ? https://stackoverflow.com/questions/68884237/how-to-query-for-a-listinteger-in-jdbctemplate – mattsmith5 Aug 22 '21 at 20:21
  • @mattsmith5 If supported, it is `getObject(, BigInteger.class)`. Unfortunately, the JDBC specification is not very clear on `BigInteger` support, so this may vary between JDBC drivers. The specific `getXXX` methods are a hold-over from older, pre-generics, design decisions and are no longer added for newer types. – Mark Rotteveel Aug 23 '21 at 13:49