ADBA feedback

[Lukas Eder]

Indeed, once client code works *directly* with JDBC, I agree that
index-based access is a big pain. But most people have worked around these
limitations by using either some third party tool (simple wrappers, like
Spring's JdbcTemplate or JDBI, Apache DbUtils), or they wrote some mapper /
query builder like jOOQ that abstracts over the queries themselves such
that an actual object model would hide the concrete column indexes.

I've always seen JDBC as a lower level API that abstracts over the network
protocol, but that still needs additional abstraction on top of it from
client code. From that perspective, it's OK for the lower level API to be
lower level and thus make index based access first class citizens. I do
appreciate your effort to include the higher level convenience in the API
itself, of course, but I don't think the two API "parts" really have to
compete with one another. They're for different target audiences:

- Name based: For programmers writing business logic directly with JDBC /
ADBA
- Index based: For programmers writing generic infrastructure logic on top
of JDBC / ADBA

Having said so, given that JDBC already does offer name based access, why
wasn't that being used in those two JDBC apps you've maintained?

2018-04-30 18:25 GMT+02:00 Douglas Surber <douglas.surber at oracle.com>:

> I have maintained two JDBC apps. One uses column indexes and the other
> column names. Maintenance on the one that uses indexes is a nightmare.
> Everyone that works on it spends an inordinate amount of time counting and
> recounting columns trying to get the indexes right. Every time a query or
> table changes there is a lot of breakage because the column indexes shift.
> Fixing that is painful. Using names these problems do not occur.
>
> I understand your arguments for column indexes. My experience has been
> that they are problematic. I’d like to find a solution that addresses the
> use cases you describe and at the same time addresses the maintenance
> problems I have seen with using indexes.
>
> The following is not strictly a serious proposal. It is a attempt to
> inspire alternate solutions.
>
>   /**
>    * Return the value of the column identified by name at the specified
> index. If both name and
>    * index are specified then the column at the specified index must be
> identified by name. Either
>    * but not both may be null in which case only the non-null value is
> used.
>    */
>   public <T> T get(String name, int index, Class<T> type) throws
> IllegalArgumentException;
>
> If there are several similar columns in sequence, then an off-by-one error
> may be difficult to detect. The redundant specification of the column makes
> it easier to detect such errors. While it allows runtime detection of
> errors, it doesn’t eliminate column counting and index shifting.
>
> With respect to case, I’d say the spec should be silent. Vendors can do
> whatever is appropriate for their database. For example Oracle Database
> would likely ignore case but would support case-sensitive double quoted
> identifiers, “\”lower case identifier with spaces\”’.
>
> Douglas
>
> > On Apr 30, 2018, at 1:59 AM, Mark Rotteveel <mark at lawinegevaar.nl>
> wrote:
> >
> > On 30-4-2018 10:35, Lukas Eder wrote:
> >> 2018-04-30 10:28 GMT+02:00 Mark Rotteveel <mark at lawinegevaar.nl
> <mailto:mark at lawinegevaar.nl>>:
> >>    Retrieval by column label in JDBC is case insensitive by
> >>    specification, and I think that ADBA should maintain that. That is
> >>    irrespective if the label was originally case sensitive or not.
> >> Interesting, thanks I wasn't aware of that (you see, I've never really
> used the API) :)
> >
> > Not sure if you're joking or not :|
> >
> >> So, in the event of a query like this:
> >> SELECT 1 AS "A", 2 AS "a"
> >> In JDBC, the index-based API is the only one that works, despite the
> query being correct SQL, producing two explicitly distinct column names
> (even if that's an edge case).
> >
> > Yes, and in JDBC that is also in the specification :):
> >
> > """
> > Column names used as input to getter methods are case insensitive. When
> a getter method is called with a column name and several columns have the
> same name, the value of the first matching column will be returned. The
> column name option is designed to be used when column names are used in the
> SQL query that generated the result set. For columns that are NOT
> explicitly named in the query, it is best to use column numbers. If column
> names are used, the programmer should take care to guarantee that they
> uniquely refer to the intended columns, which can be assured with the SQL
> AS clause.
> > """
> >
> > --
> > Mark Rotteveel
>
>