Implement collection type extensions

[beikov]

In SQL 99 the array type was added which is very convenient especially when indexes are possible. There are various databases that have support for collection types in one or another way. We should take a look into that and try to implement an abstraction for that.

• PostgreSQL has full native support for array types
• DB2 has XML support which can be used to emulate arrays. Not very storage space friendly but one can definitely afford the space when already being able to pay for DB2 licenses
• MSSQL has XML support too
• Oracle has native support for array types
• MySQL has XML support too
• H2 has native support for array types

Regarding JDBC Support

• DB2 doesn't support selecting multisets: https://www.google.at/url?sa=t&source=web&rct=j&url=https://www.ibm.com/support/knowledgecenter/SSGU8G_11.70.0/com.ibm.jccids.doc/src/tpc/imjcc_rjvjdata.htm&ved=0ahUKEwjogbrH9dLSAhXLIsAKHV5UBeoQFggaMAA&usg=AFQjCNEg33fieC26t1dNgjBJkZNB6qwH9w&sig2=3XtfgNJGmX8SSV1EDVK9kw but this seems to suggest it does https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_tjv00002.html
• Oracle supports reading through the array type: http://forums.devx.com/showthread.php?156112-How-to-read-a-oracle-multiset-in-java
• PostgreSQL jdbc does not implement this yet according to this: http://stackoverflow.com/questions/32016829/postgresql-array-agg-of-multiple-columns-with-jdbc
• The SQL Server JDBC driver apparently also does not support that as can be seen here: https://github.com/Microsoft/mssql-jdbc/blob/55f0b7f40611c0985ea6122ecd3fef0f4bea16ca/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerResultSet.java#L2627

Related issues

• java.sql.Struct type support pgjdbc/pgjdbc#381
• Support for Arrays of Composite Types pgjdbc/pgjdbc#63
• Resultsets of arrays of composite types unsupported: Method org.postgresql.jdbc3.Jdbc3Array.getArrayImpl(long,int,Map) is not yet implemented pgjdbc/pgjdbc#24

Also see: https://github.com/mopano/hibernate-array-type

[jwgmeligmeyling]

A PR for an array datatypes module for Hibernate has been proposed hibernate/hibernate-orm#1499 . Personally I use the array custom types provided by vladmihalcea/hibernate-types. Both implementations rely on Connection#createArrayOf(String typeName, Object[] elements). Whilst Oracle supports native array types, based on the comments it seems this method is not implemented in their JDBC driver. I am uncertain about H2.

I am also uncertain whether we should emulate array support for databases that do not. It makes this feature drastically more complicated. Not only do we have to emulate the collection storage, but also any of the collection array functions. We'd also lose valuable type information that databases with native array implementations will have.

I actually toyed around with pgjdbc/pgjdbc#381 a little myself. PostgreSQL's array_agg can return a record array, which could benefit from proper struct handling at the driver level. I wanted to use this in combination with array_agg to obtain record arrays and fetch multiple plural non-set associations in this way (while also eliminating redundant data in the cross product). The feature is unfortunately difficult to implement. Primarily because the struct is basically received as a string value from the DB and type info for the struct members would have to be retrieved separately as far as I remember. Given the little interest in the past few years, I doubt whether this will ever be implemented.

[beikov]

The main purpose of emulating this via XML or JSON would be to reduce the amount of data that needs to be fetched when wanting to fetch collections. I don't know if we can make that happen for hibernate native collections, but the potential benefits for entity views are huge.

I would like to use that as default fetch strategy for collections as it should be the most efficient way of fetching nested data. It avoids cross products and also avoids separate queries. We can make use of more efficient pagination as we don't have to handle cardinality/cross product due to collections and generally should be able to implement more efficient collection filtering due to filters being pushed into the collection aggregation.

Array support in postgresql is nice to support because it has some nice filter/index performance benefits to it, so that's something I definitely want to add as a separate feature.
Type information is definitely something we have to be able to retain for arrays, as accessing elements should produce validly typed nodes. For that, I think we need a special array type registry to also be able to produce a valid array type when aggregating individual values.