java.sql2 annotation usage

[Douglas Surber]

Lukas,

Thanks for the comments, ideas, feedback.

1) open question. Proposals welcome.

2) the elementSqlTypeName can be any SQL type, not just a user defined type, so “NUMBER” would be fine.

3,4.5) Work in Progress. For the most part the annotations are just placeholders, reminders that we need something to address the particular need. What would be enormously helpful is for you (or anyone) to provide actual Java source files with JavaDoc for these annotation ideas.

6) Converters have not been discussed. At first blush they strike me as something that isn’t strictly necessary and would be better considered in a subsequent release.

7) Will think about it, but see my reply to 3, 4, 5.

8) I like this idea a lot. I’ll take a cut at it and see how much weight it adds. In some ways I like it better than the iterative approach. What would you think of eliminating the iterative model and replacing it with the list model?

Douglas

> On Oct 18, 2017, at 4:22 AM, Lance Andersen <lance.andersen at oracle.com> wrote:
> 
> Hi Lukas,
> 
> 
> Thank you for the feedback.  Let me think about this more and get back to you after I have a chance to digest it a bit more and have a conversation with Douglas..
> 
> Best
> Lance
>> On Oct 18, 2017, at 4:22 AM, Lukas Eder <lukas.eder at gmail.com> wrote:
>> 
>> 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
> 
> <http://oracle.com/us/design/oracle-email-sig-198324.gif>
> <http://oracle.com/us/design/oracle-email-sig-198324.gif> <http://oracle.com/us/design/oracle-email-sig-198324.gif>
> <http://oracle.com/us/design/oracle-email-sig-198324.gif>Lance Andersen| Principal Member of Technical Staff | +1.781.442.2037
> Oracle Java Engineering 
> 1 Network Drive 
> Burlington, MA 01803
> Lance.Andersen at oracle.com <mailto:Lance.Andersen at oracle.com>
> 
> 
>