micronaut-projects / micronaut-projects/micronaut-data
More flexible control over statements in JdbcOperations
- Dominant language
- Java
- Stars
- 482
- Forks
- 229
- Avg merge
- 1d 7h
- Merged PRs (30d)
- 32
Description
While trying to work around issue #79, I decided to overload my repository's `save` method and just write the the SQL out directly. This mostly worked, but I bumped into what I believe is a bug / opportunity for improvement: easy support for getting the generated ID back.
Specifically, this is what my `save` method looks like:
```java
public S save(@NonNull @Valid @NotNull S book) {
final String sql = "insert into books (title, data) values (?, ?)";
return jdbcOperations.prepareStatement(sql, statement -> {
statement.setString(1, book.getTitle());
try {
PGobject json = new PGobject();
json.setType("jsonb");
json.setValue(objectMapper.writeValueAsString(book.getData()));
statement.setObject(2, json);
statement.execute();
// TODO: this isn't actually working yet, because we can't pass Statement.RETURN_GENERATED_KEYS through up above (limitation of Predator wrapper)
ResultSet generatedKeys = statement.getGeneratedKeys();
if (generatedKeys.next()) {
book.setId(generatedKeys.getLong(1));
}
return book;
} catch (JsonProcessingException e) {
throw new RuntimeException(e);
}
});
}
```
Unfortunately, the call to statement.getGeneratedKeys() won't, at least with the standard Postgres JDBC driver, get me back what I want unless I passed `Statement.RETURN_GENERATED_KEYS` into the `connection.prepareStatement()` call, which I can't do because `JdbcOperations` doesn't expose that API:
https://github.com/micronaut-projects/micronaut-data/blob/23075f01c2aa26ed23311c634e16abf6bcc239a0/data-jdbc/src/main/java/io/micronaut/data/jdbc/operations/DefaultJdbcRepositoryOperations.java#L574
As such, it means I'm hamstrung and need to issue a second query, which is a bummer. So this is a request to consider expanding the JdbcOperations API in some way to give me a tiny bit more control to do stuff like this. Thanks!
Contributor guide
Research direction
Start with data-jdbc/src/main/java/io/micronaut/data/jdbc/operations/DefaultJdbcRepositoryOperations.java around line 574 and read the JdbcOperations API used by the prepareStatement callback. Define the smallest API change that lets callers request generated keys, then verify that the PostgreSQL JDBC driver returns the inserted ID through the existing callback flow.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, postgresql, sql
- Domain
- backend-api-design, database
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100