java.sql2 annotation usage

[Lukas Eder]

Hello,

I have a couple of minor issues with the proposed usage of annotations in
the new java.sql2 package - apart from the fact that I personally don't
really like annotations - but that is an unrelated, different discussion :)

1) ResultSet type parameters (e.g. Oracle SYS_REFCURSOR or DB2 / PostgreSQL
/ SQL Server table valued functions). The key trait of these parameters is
the fact that record types are unknown at compile time, and thus cannot be
represented with any of the existing annotations, I suspect - or is the
expectation for this to be supported through @SqlArray + @SqlColumns?

2) @SqlArray is currently designed for "array of struct" but does it also
work for "array of scalar types"? In Oracle, this would correspond to the
distinction between e.g. TABLE OF OBJECT and TABLE OF NUMBER. In
PostgreSQL, it corresponds to RECORD[] or MY_COMPOSITE_TYPE[] vs. INT[].

3) @SqlColumns works rather differently from @SqlStruct + @Field. I suggest
offering the same API for both, e.g. @SqlColumn (which corresponds to
@Field). Nevertheless, the @SqlColumns constructor annotation is certainly
useful, so I suggest also adding @Fields

4) Bikeshedding names: Renaming @Field to @SqlField might be appropriate to
align the name with the other 4 annotations currently present.

5) While the Oracle database distinguishes between "structs" and "table
records" in some areas, databases like PostgreSQL don't. A "struct" is
simply a nested record, like any other top level record. So, I wonder if
the @Field vs @SqlColumns distinction is really necessary. We could replace
@SqlStruct by @SqlRecord and use @SqlColumn(s) instead of @Field.

6) Have there been any discussions about adding converter annotations,
which could be used with @SqlColumn(s) and @Field? The @AttributeConverter
annotation in JPA is rather powerful for this purpose. jOOQ's Converter SPI
is also one of the most used features, as people always have preferences
for custom data types that go beyond the simple JDBC types. For example, it
would be great if someone was using e.g. a custom data type for IP
addresses, they could use their IPType in the @SqlColumn or @Field
annotated members, and JDBC would automatically convert those types.

7) Another interesting JPA annotation is the group of @Embeddable /
@Embedded annotations, which would make perfect sense also in a JDBC
context. Would this be an interesting addition?

8) Would it be worth thinking about being able to bind a
List<AnnotatedParameterType> in one go to a BatchCountOperation? The
example from the JavaOne 2017 CON1491 talk (page 21) loops over the list
explicitly and binds them 1:1:

BatchCountOperation batch = conn.batchCountOperation(sql);
list.forEach((elem) -> {
    batch.countOperation()
            .set("elem_", elem)
            .submit();
});
batch.submit();

It would be cool if this could be simplified to something like

ParameterizedBatchCountOperation batch = conn.batchCountOperation(sql);
batch.setAll("elem_", list);
batch.submit();

9) Has there been any discussion about potentially supporting this
annotation-based approach also for DynamicMultiOperation? For instance, the
results could be mapped to this class automatically:

@SqlResults
class MultiResults {
  @SqlResult(1)
  List<MyRecord> records;

  @SqlResult(2)
  int updateCount;

  @SqlResult(3)
  Throwable error;

  @SqlResult(4)
  List<MyOtherRecord> otherRecords;
}

That's it for now.
Thanks,
Lukas