How to insert UUID into RAW(16) column

Question

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.

imho u have to pass something like byte[] to JDBC driver to allow storage of datatype RAW. — ibre5041, Jun 25 '18 at 12:58
@ibre5041, I can write it down as bytes but I need to make DbSetup working. Hibernate works fine — Serge Iroshnikov, Jun 25 '18 at 13:04

Marmite Bomber · Answer 1 · 2018-06-25T14:28:22.857

11

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();
  }

edited Jun 25 '18 at 14:28

answered Jun 25 '18 at 14:16

Marmite Bomber

19,886
4
26
53

2

@Serge you are welcome; anyway from my experience, I'd use / allow to use hash codes as PKs only if there are two good reasons, why `sequence` can't be used and that hash code brings a real advantage against a numerical PK. Good luck! – Marmite Bomber Jun 25 '18 at 15:32
I am using it due to necessity of merging data from intermediate servers on the main one. I think this way is better than maintaining separate range of sequence values for each server. – Serge Iroshnikov Jun 26 '18 at 13:29
I am using jbdcTemplate, not preparedStatement/Con, what do I do then? I need to somehow setBytes(2, after calling as Bytes function, if anyone knows, would be great ! – mattsmith5 Aug 23 '21 at 03:35
see question here, https://stackoverflow.com/questions/68895766/how-to-insert-uuid-into-raw16-column-with-jdbc-template – mattsmith5 Aug 23 '21 at 16:34

score 4 · Answer 2 · answered Jun 25 '18 at 13:32

4

Oracle has no real UUID datatype and dealing with RAW(16) is really a PITA.

What we do, is to pass the UUID as a string to a SQL statement that uses hextoraw():

String sql = "insert into foo (id) values (hextoraw(?))";
PreparedStatement pstmt = connection.prepareStatement(sql);
UUID uid = UUID.randomUUID();
pstmt.setString(1, uid.toString().replaceAll("-", ""));

answered Jun 25 '18 at 13:32

in testing, it does not following this page conversion https://robobunny.com/cgi-bin/guid – mattsmith5 Aug 23 '21 at 20:07
yeah, i was trying to test with oracle, etc – mattsmith5 Aug 23 '21 at 20:10
any idea how to get just the string value? https://stackoverflow.com/questions/68886687/java-guid-convert-into-oracle-raw16-string – mattsmith5 Aug 23 '21 at 20:39

score 2 · Answer 3 · answered Jun 10 '19 at 12:00

getJdbcTemplate().update("INSERT INTO abc(abc_id, abc_uuid, "
                                       + "VALUES (?, ?)",
                                          abcId, uuidToBytes(abcUuid))

Here's a helper method to converet UUID type to bytes.

private byte[] uuidToBytes(final UUID uuid) {
        if (Objects.isNull(uuid)) {
            return null;
        }

        final byte[] uuidAsBytes = new byte[16];

        ByteBuffer.wrap(uuidAsBytes)
                  .order(ByteOrder.BIG_ENDIAN)
                  .putLong(uuid.getMostSignificantBits())
                  .putLong(uuid.getLeastSignificantBits());

        return uuidAsBytes;
    }

you need one more line, see above answer def uuid = UUID.randomUUID() stmt.setBytes(2,asBytes(uuid)) — mattsmith5, Aug 23 '21 at 03:34
this is incorrect, needs set bytes method (2, added after testing — mattsmith5, Aug 23 '21 at 16:03

jccampanero · Answer 4 · 2021-08-27T22:32:19.717

JdbcTemplate provides different methods for performing a DML operations like insert. Please, consider for instance update.

@MarmiteBomber provides in his/her answer all the necessary information for performing what you need, please, only wrap the code appropriately in the different artifacts defined by Spring.

For example, you can use PreparedStatementCreator, something like:

jdbcTemplate.update(new PreparedStatementCreator() {
  @Override
  public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
    PreparedStatement ps = con.prepareStatement("insert into TAB_UUID (id, uuid) values (?,?)");
    ps.setInt(1,1);
    UUID uuid = UUID.randomUUID();
    ps.setBytes(2,asBytes(uuid));
    return ps;
  }
});

The code can be simplified using lambdas to:

jdbcTemplate.update(con -> {
  PreparedStatement ps = con.prepareStatement("insert into TAB_UUID (id, uuid) values (?,?)");
  ps.setInt(1,1);
  UUID uuid = UUID.randomUUID();
  ps.setBytes(2,asBytes(uuid));
  return ps;
});

If you prefer, you can use PreparedStatementSetter instead:

jdbcTemplate.update("insert into TAB_UUID (id, uuid) values (?,?)", new PreparedStatementSetter() {
  @Override
  public void setValues(PreparedStatement ps) throws SQLException {
    ps.setInt(1,1);
    UUID uuid = UUID.randomUUID();
    ps.setBytes(2, asBytes(uuid));
  }
});

Again, the code can be simplified with lambdas:

jdbcTemplate.update("insert into TAB_UUID (id, uuid) values (?,?)", ps -> {
  ps.setInt(1,1);
  UUID uuid = UUID.randomUUID();
  ps.setBytes(2, asBytes(uuid));
});

In both examples you explicitly invoke setBytes in the underlying prepared statement and use the asBytes method from the Marmite answer.

How to insert UUID into RAW(16) column

4 Answers4