Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to insert UUID into RAW(16) column

I have the RAW(16) PK column in Oracle, and trying to insert into it using JDBC:

        PreparedStatement stmt = connection.prepareStatement("insert into COUNTRY (id, state, version, code, name, nationality, issuing_entity, country) values (?, ?, ?, ?, ?, ?, ?, ?)");
        UUID id = UUID.randomUUID();
        stmt.setObject(1, id, Types.BINARY);

However, I am getting an exception:

java.sql.SQLException: Invalid column type
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8494)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:7995)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8559)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:225)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setObject(HikariProxyPreparedStatement.java)
at rw.gov.dgie.framework.test.AbstractTestCaseWithDB.tryToInsertCountry(AbstractTestCaseWithDB.java:78)
at rw.gov.dgie.framework.test.AbstractTestCaseWithDB.dbSetup(AbstractTestCaseWithDB.java:62)
at test.rw.gov.dgie.bms.terr.service.TestCountryService.init(TestCountryService.java:37)

I am getting the same exception when trying to use DbSetup for inserting test data.

Is there a way to make JDBC insert UUIDs into RAW(16) column?

I am using Oracle JDBC 12.2.0.1.0.

like image 947
Serge Iroshnikov Avatar asked Jun 25 '18 12:06

Serge Iroshnikov


People also ask

What is datatype of UUID in SQL?

The UUID data type is considered a subtype of the STRING data type, because UUID values are displayed in their canonical textual format and, in general, behave the same as string values in the various SQL operators and expressions.

What is Oracle UUID?

A class that represents an immutable universally unique identifier (UUID). A UUID represents a 128-bit value.

Does Oracle have a GUID data type?

SYS_GUID () function in oracle database can be defined as a built-in function in PL/SQL which is used to generate and return a global unique identifier (GUID) (RAW value) of size 16 bytes for each row of the table and it does not accept any argument in the function, it generates GUID which are supposed to be unique ...


1 Answers

You must convert the UUID to a byte array. See the method asBytes how to do it.

After it the binding is a s simple as using setBytes.

Example

def stmt = con.prepareStatement("insert into TAB_UUID (id, uuid) values (?,?)") 
// bind
stmt.setInt(1,1)
def uuid = UUID.randomUUID()
stmt.setBytes(2,asBytes(uuid)) 
def rowCount = stmt.executeUpdate()

Here just for case the link doesn't work the conversion method UUID to byte array

  public static byte[] asBytes(UUID uuid) {
    ByteBuffer bb = ByteBuffer.wrap(new byte[16]);
    bb.putLong(uuid.getMostSignificantBits());
    bb.putLong(uuid.getLeastSignificantBits());
    return bb.array();
  }
like image 87
Marmite Bomber Avatar answered Sep 18 '22 02:09

Marmite Bomber