micronaut-projects / micronaut-projects/micronaut-data

More flexible control over statements in JdbcOperations

Open
#80 7 comments 0 reactions 0 assignees View on GitHub
info: workaround available type: enhancement
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.