CODEWITHSUNDEEP
javaspringuuidjdbctemplate
Gary Greenberg
Gary Greenberg

Reputation: 576

How to return generated UUID primary key

I am working with mySQL database and the table has a primary key in UUID format, generated by the database engine. I am using Spring framework JdbcTemplate (https://docs.spring.io/spring/docs/current/javadoc-api/index.html?org/springframework/jdbc/core/JdbcTemplate.html) to perform all operation. But on inserts I do need to return primary key to the caller. JdbcTemplate has method public int update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder) but it returns integer keys only. KeyHolder interface also allow only numeric keys. Does someone know how to overcome this deficiency? Should I use something other than JdbcTemplate? It is not desirable, but possible. Give me a hint, please.

Upvotes: 3

Views: 3536

Answers (3)

Jay Ehsaniara
Jay Ehsaniara

Reputation: 1529

One way to do that is to create UUID in your java in advance and then pass it on to (SQL/DB), so in this case, you already know whats going to be your Primary key. since in theory UUID is unique there should be no collision.

UUID uuid = UUID.randomUUID();
jdbcTemplate.update("INSERT INTO users ( user_uuid , first_name , last_name) VALUES (?,?,?)",
                "admin", uuid, user.getFirstName(), user.getLastName());
//uuid: you already have the PrimeryKey in advance

Upvotes: 0

SimpleG
SimpleG

Reputation: 11

This works for me!

public String save(SysUser sysUser) {
    String SQL = "INSERT INTO sys_user(id,account_number,telephone,email,real_name) VALUES (?,?,?,?,?);";
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(connection -> {
        PreparedStatement preparedStatement = connection.prepareStatement(SQL, new String[]{"id"});
        preparedStatement.setString(1, sysUser.getId());
        preparedStatement.setString(2, sysUser.getAccountNumber());
        preparedStatement.setString(3, sysUser.getTelephone());
        preparedStatement.setString(4, sysUser.getEmail());
        preparedStatement.setString(5, sysUser.getRealName());
        return preparedStatement;
    }, keyHolder);
    return String.valueOf(keyHolder.getKeyList().get(0).get("id"));
}

Upvotes: 1

Sully
Sully

Reputation: 14943

You can use it this way to get a non numeric key

GeneratedKeyHolder gendKey = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
    @Override
    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
        PreparedStatement stmt = con.prepareStatement("INSERT INTO MY_TABLE(col1, col2) VALUES (?, ?) ", Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, "val1");
        stmt.setString(2, "val2");
        return stmt;
    }
}, gendKey);

// If multiple rows updated, use getKeys()
String uuid = (String) gendKey.getKey().get("UUID-COL-NAME");

Upvotes: 0

Related Questions