It's weekend, so...

I'm aware that the String Template JEP is still in the early phase. But I'm excited about the future it will bring. That is, not a mere convenient String.format(), but something far more powerful that can be used to create injection-safe higher-level objects.

Hypothetically, I can imagine JDBC API being changed to accept StringTemplate, safely:

List<String> userIds = ...;
UserStatus = ...;
try (var connection = DriverManager.getConnection(...)) {
  var results = connection.query(
      // Evaluates to a StringTemplate
      // parameters passed through PreparedStatement
      """
      SELECT UserId, BirthDate, Email from Users
      WHERE UserId IN (\{userIds}) AND status = \{userStatus}
      """);
}

We would be able to create dynamic SQL almost as if they were the golden gold days' static SQL. And the SQL will be 100% injection-proof.

That's all good. What remains unclear to me though, is what to do with the results?

The JDBC ResultSet API is weakly typed, and needs the programmer to call results.getString("UserId"), results.getDate("BirthDay").toLocalDate() etc.

Honestly, the lack of static type safety doesn't bother me much. With or without static type safety, for any non-trivial SQL, I wouldn't trust the correctness of the SQL just because it compiles and all the types match. I will want to run the SQL against a hermetic DB in a functional test anyways, and verify that given the right input, it returns the right output. And when I do run it, the column name mismatch error is the easiest to detect.

But the ergonomics is still poor. Without a standard way to extract information out of ResultSet, I bet people will come up with weird ways to plumb these data, some are testable, and some not so much. And people may then just give up the testing because "it's too hard".

This seems a nice fit for named parameters. Java currently doesn't have it, but found this old thread where u/pron98 gave a nice "speculation". Guess what? 3 years later, it seems we are really really close. :-)

So imagine if I could define a record for this query:

record UserData(String userId, LocalDate birthDate, String email) {}

And then if JDBC supports binding with named parameters out of box, the above code would be super easy to extract data out of the ResultSet:

List<String> userIds = ...;
UserStatus = ...;
try (var connection = DriverManager.getConnection(...)) {
  List<UserData> userDataList = connection.query(
      """
      SELECT UserId, BirthDate, Email from Users
      WHERE UserId IN (\{userIds}) AND status = \{userStatus}
      """,
      UserData.class);
}

An alternative syntax could use lambda:

List<String> userIds = ...;
UserStatus = ...;
try (var connection = DriverManager.getConnection(...)) {
  List<UserData> userDataList = connection.query(
      """
      SELECT UserId, BirthDate, Email from Users
      WHERE UserId IN (\{userIds}) AND status = \{userStatus}
      """,
     (String userId, LocalDate birthDate, String email) ->
         new UserData() with {
             .userId = userId, .birthDate = birthDate, .email = email});
}

But:

1. It's verbose

2. The SQL can select 12 columns. Are we really gonna create things like Function12<A, B, C, ..., K, L> ?

And did I say I don't care much about static type safety? Well, I take it back partially. Here, if compiler can help me check that the 3 columns match in name with the proeprties in the UserData class, that'd at least help prevent regression through refactoring (someone renames the property without knowing it breaks the SQL).

I don't know of a precedent in the JDK that does such thing - to derive static type information from a compile-time string constant. But I suppose whatever we do, it'd be useful if JDK provides a standard API that parses SQL string template into a SQL AST. Then libraries, frameworks will have access to the SQL metadata like the column names being returned.

If a compile-time plugin like ErrorProne parses out the column names, it would be able to perform compile-time checking between the SQL and the record; whereas if the columns are determined at runtime (passed in as a List<String>), it will at least use reflection to construct the record.

So maybe it's time to discuss such things beyond the JEP? I mean, SQL is listed as a main use case behind the design. So might as well plan out for the complete programmer journey where writing the SQL is the first half of the journey?

Forgot to mention: I'm focused on SQL-first approach where you have a SQL and then try to operate it in Java code. There are of course O-R frameworks like JPA, Hibernate that are model-first but I haven't needed that kind of practice yet so I dunno.

What are your thoughts?