[Idea] UserType support like in Hibernate

[timowest]

Currently the literal type support of Querydsl SQL is limited to the types directly supported by the JDBC API.

This makes using JodaTime types or literal types from other APIs difficult.

A solution to this limitation could be the introduction of a UserType functionality like in Hibernate. The UserType would be responsible for the the serialization and derialization to a Statement / ResultSet.

What do you think? I will sketch this approach in this thread. Please comment!

Here is the Hibernate UserType signature for reference :


public interface UserType {

        /**
         * Return the SQL type codes for the columns mapped by this type. The
         * codes are defined on <tt>java.sql.Types</tt>.
         */
        public int[] sqlTypes();

        /**
         * The class returned by <tt>nullSafeGet()</tt>.
         */
        public Class returnedClass();

        /**
         * Compare two instances of the class mapped by this type for persistence "equality".
         * Equality of the persistent state.
         */
        public boolean equals(Object x, Object y) throws HibernateException;

        /**
         * Get a hashcode for the instance, consistent with persistence "equality"
         */
        public int hashCode(Object x) throws HibernateException;

        /**
         * Retrieve an instance of the mapped class from a JDBC resultset. Implementors
         * should handle possibility of null values.
         */
        public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException;

        /**
         * Write an instance of the mapped class to a prepared statement. Implementors
         * should handle possibility of null values. A multi-column type should be written
         * to parameters starting from <tt>index</tt>.
         */
        public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException;

        /**
         * Return a deep copy of the persistent state, stopping at entities and at
         * collections. It is not necessary to copy immutable objects, or null
         * values, in which case it is safe to simply return the argument.
         */
        public Object deepCopy(Object value) throws HibernateException;

        /**
         * Are objects of this type mutable?
         */
        public boolean isMutable();

        /**
         * Transform the object into its cacheable representation. At the very least this
         * method should perform a deep copy if the type is mutable. That may not be enough
         * for some implementations, however; for example, associations must be cached as
         * identifier values. (optional operation)
         */
        public Serializable disassemble(Object value) throws HibernateException;

        /**
         * Reconstruct an object from the cacheable representation. At the very least this
         * method should perform a deep copy if the type is mutable. (optional operation)
         */
        public Object assemble(Serializable cached, Object owner) throws HibernateException;

        /**
         * During merge, replace the existing (target) value in the entity we are merging to
         * with a new (original) value from the detached entity we are merging. For immutable
         * objects, or null values, it is safe to simply return the first parameter. For
         * mutable objects, it is safe to return a copy of the first parameter. For objects
         * with component values, it might make sense to recursively replace component values.
         */
        public Object replace(Object original, Object target, Object owner) throws HibernateException;
}

For Querydsl SQL a subset like the following should be enough :


public interface  Type<T> {

        int[] sqlTypes();

        Class<T> returnedClass();

        @Nullable
        T getValue(ResultSet rs, int startIndex) throws SQLException;

        void setValue(PreparedStatement st,  int startIndex, @Nullable T value) throws SQLException;

}


I am not yet sure how to support multi-column types though.

luisfpg

This would be nice, as it would hide serialization details to a single class, and not spread across the application code.
For example, in Cyclos, we use a lot of custom types, like IntegerRange, DatePeriod, TimeInterval, FileSize, Dimensions... All these examples are 2 columns, either 2 values or a value and an enum.
Enum support, btw, is something which would also be supported by user types? On Cyclos3, we used an UserType for persisting enums, as Hibernate didn't support them directly.
Also, how would the configuration be done to inform Querydsl about user types?
I'm not sure, as Hibernate uses domain annotation or xml, but for Querydsl SQL, domain classes are not taken into account, just the DB, or beans will be generated from DB tables.
But how would the customization be done?

timowest

Enum support, btw, is something which would also be supported by user types?

Yes, Enums could be supported via the ordinal or the enum name.

Also, how would the configuration be done to inform Querydsl about user types?

Maybe via an UserTypes class. I don't want to throw this into SQLTemplates, since it is not SQL-serialization related.


UserTypes userTypes = new UserTypes();
userTypes.set(DateTime.class, new DateTimeType());
SQLQuery query = new SQLQueryImpl(conn, templates, userTypes);

Alternatively we could bundle SQLTemplates and UserTypes into a Configuration class :


Configuration configuration = new Configuration();
UserTypes userTypes = new UserTypes();
userTypes.set(DateTime.class, new DateTimeType());
configuration.setUserTypes(userTypes);
configuration.setTemplates(new H2Templates());
SQLQuery query = new SQLQueryImpl(conn, configuration);

I believe the Configuration approach scales better.

luisfpg

I believe the Configuration approach scales better.

Yes, even allowing something like configuration.setType(class, type).
I wouldn't bind the "UserType" name, as standard types could be used, for example, if someone wants to force a number to be mapped as Long, or BigDecimal.
Don't know if you are planning to refactor the internals to always use a Type instance, even for standard types (I haven't looked at the Querydsl SQL code, actually, maybe it is already similar to that).

But, still on configuration, the next open issue is how to generate Q-types with custom types?
The first idea which comes to my mind is something like:

MetaDataExporter exporter = new MetaDataExporter(...);
TableConfiguration table1 = new TableConfiguration("table1");
table1.setType(new SingleColumnType() "column1");
table1.setType(new MultiColumnType(), "column2", "column3", "column4");
exporter.add(table1);
TableConfiguration table2 = new TableConfiguration("table2");
table2.setType(new EnumNameType(MyEnum.class), "column1");
table2.setType(new EnumOrdinalType(MyOtherEnum.class), "column2");
exporter.add(table2);
exporter.export(conn.getMetaData());


This could also validate that the specified type's sqlTypes() result has the same size as the number of passed columns, and also that types are compatible with what is on the database. Also, the table / column names could be checked against the database at generation time...

Remember, this is just my first idea...

timowest

Don't know if you are planning to refactor the internals to always use a Type instance, even for standard types (I haven't looked at the Querydsl SQL code, actually, maybe it is already similar to that).

For consistency using types for both internals types mappings and custom types feels simpler. I don't like the Hibernate division into Type and UserType.

This could also validate that the specified type's sqlTypes() result has the same size as the number of passed columns, and also that types are compatible with what is on the database. Also, the table / column names could be checked against the database at generation time...

Remember, this is just my first idea...

I see where this is going. An alternative approach would be

* code your DTOs
* annotate them with @Column, @Table and @Type (?!?) annotations
* use an AnnotationProcessor to create the Q-types

The resulting classes (domains and Q-types) could be used with Querydsl SQL.

This approach would be more consistent with my DTO use cases.

luisfpg

I see where this is going. An alternative approach would be

* code your DTOs
* annotate them with @Column, @Table and @Type (?!?) annotations
* use an AnnotationProcessor to create the Q-types

The resulting classes (domains and Q-types) could be used with Querydsl SQL.

This approach would be more consistent with my DTO use cases.

Even though in no way I oppose to this approach, it is a shift from the current Querydsl SQL direction. Quoting the documentation:

To get started export your schema into Querydsl query types like this :...

The new instructions would be: "To get started, code your DTOs (which should be consistent with your schema), then run the annotation processor to generate Q-types".

Also, it have a disadvantage over Querydsl JPA: instead of hand-coding one set of artifacts (domain, as the database schema can be auto-generated), one has to manually keep 2 in sync: DTO's and db schema.

Just brainstorming.

timowest

The new instructions would be: "To get started, code your DTOs (which should be consistent with your schema), then run the annotation processor to generate Q-types".

It would be an alternative approach.

Also, it have a disadvantage over Querydsl JPA: instead of hand-coding one set of artifacts (domain, as the database schema can be auto-generated), one has to manually keep 2 in sync: DTO's and db schema.

I believe the current approach without the bean generation works if you maintain your DTOs manually without any Querydsl annotations and use them for projections.

Concerning JPA you normally have to maintain entities, DTOs and your schema. You have to maintain Entity <> DTO transformations and have to make sure that your entities stay in sync with your schema. Auto-generation of databases is not an option for any project in production and maintenance.

Since there are already good "real" ORMs out there we could try different approaches with Querydsl SQL

The current approach is

* you have a DB schema
* you create Q-types that reflect the DB structure
* you create annotated Bean types based on DB structure

Alternatively

* you have a set of DTOs with @Querydsl mappings
* you have a DB schema
* you derive the Q-types via an annotation processor (easily derived from the QuerydslAnnotationProcessor)

You could validate your DTOs easily against a database schema. Any custom mappings are easier to maintain in code than in TableConfigurations.

And this case supports use case specific DTOs better. You might not always want all the columns/data out.

The current approach works better as an companion to an ORM if some JPQL queries need to be replaced with SQL queries for performance or other reasons.

I am not completely sure about the second approach though.

For Querydsl SQL I don't have a clear vision yet. I just feel that it should stay a tool for advanced type-safe SQL usage.

I'd prefer to keep the bean generation functionality as simple as possible in the beginning, just @Table + @Column + populate support.

When we use it in real life projects we see what works and what doesn't.

luisfpg

I believe the current approach without the bean generation works if you maintain your DTOs manually without any Querydsl annotations and use them for projections.

Concerning JPA you normally have to maintain entities, DTOs and your schema. You have to maintain Entity <> DTO transformations and have to make sure that your entities stay in sync with your schema. Auto-generation of databases is not an option for any project in production and maintenance.

So, Querydsl SQL clearly has 2 objectives:
* Work together with JPA / Hibernate to take care of native queries. In this case, only queries matter, and custom types could be implemented just as projection transformations (there is the problem you mentioned before of multi-column types, which are quite common).
* Work standalone. In this case, DML statements will be executed through Querydsl, so the custom types should also be used for persisting data. I see this as a nice use case, and actually planning to use it on future projects (that's why I suggested the bean generation). Even without the beans, the point is that custom types should still be used here. There is also the multi-column problem, for example, how to create an SQLInsertClause which uses those custom types.

I'm actually more interested in the solution to the second item, as on the first, the DML is fully performed by the ORM tool.

Just to not missing the course, I think DTOs are the wrong place to put mappings, because it shouldn't be it's responsibility to know which DB columns will be used to fill it's values... That's why my first suggestion would make the Q-types themselves having custom types. But, maybe this is why Hibernate has clearly separated user types from types. For example, maybe user types can't even be used on equals operations, or they may work as small components which have sub-properties (in case of ranges). This is a complicated subject, which I'm sure you'll find a way !

Auto-generation of databases is not an option for any project in production and maintenance

Even if I've already seen people stating the opposite, for several years I've been working on systems where the database is automatically generated by Hibernate. Of course, I'm used to add to the mappings indexes, nullability and, sometimes, even sql types to make the generated schema more decent.

When we use it in real life projects we see what works and what doesn't.

I have to say that I like this approach: learn by live examples.
Another thing is: perhaps I'm too narrow-minded by the way I'm used to work. Maybe it would be nice to add to documentation best practices, and even a demo application, so users would use Querydsl the way it was designed for.

Again, all opinions I'm stating here are merely ideas, because I haven't used Querydsl SQL in practice. Also, I don't want to influence making Querydsl bloater because of ideas which might work someday. My job here is to throw out some ideas to the wind, and, as the Querydsl creator and maintainer, filter out what is useful and what is not. Feel free to pull me down if I'm flying too high

timowest

So, Querydsl SQL clearly has 2 objectives:

Yes. The ORM companion goal is quite clear for me implementation-wise, but the standalone is difficult to describe.

Just to not missing the course, I think DTOs are the wrong place to put mappings, because it shouldn't be it's responsibility to know which DB columns will be used to fill it's values...

That's true. For use case specific DTOs annotations would be ugly.

That's why my first suggestion would make the Q-types themselves having custom types. But, maybe this is why Hibernate has clearly separated user types from types. For example, maybe user types can't even be used on equals operations, or they may work as small components which have sub-properties (in case of ranges). This is a complicated subject, which I'm sure you'll find a way smile!

At least single column user types should be usable for equals expressions etc. But multi-column user types are tricky.

Even if I've already seen people stating the opposite, for several years I've been working on systems where the database is automatically generated by Hibernate. Of course, I'm used to add to the mappings indexes, nullability and, sometimes, even sql types to make the generated schema more decent.

If you maintain an application which is already in production then using alter table scripts and maintain them, for example via DBMaintain, is a more secure option. But for projects where ths database can easily be erased Hibernate managed DDLs are probably fine.

Another thing is: perhaps I'm too narrow-minded by the way I'm used to work. Maybe it would be nice to add to documentation best practices, and even a demo application, so users would use Querydsl the way it was designed for.

I believe for JPA and JDO based usage the reference documentation gives enough guidance. Concerning Querydsl SQL I have not really enough experience with it to state any best practices.

Most of the recent Querydsl SQL changes were driven by McKinley's requests. I started using it about one week for a real project. That's why I want to keep the development of it at a manageable pace, to be able to use the features I add and to reflect on them.

My job here is to throw out some ideas to the wind, and, as the Querydsl creator and maintainer, filter out what is useful and what is not. Feel free to pull me down if I'm flying too high.

Most of your suggestions are quite consistent and well presented. But concerning Querydsl SQL I'd like to keep the pace slow, since I am just beginning to use it. ;)

I am maybe the main architect of Querydsl, but I am not really the main user of any of the modules.

I just refactored the support for builtin types to use the Type signature. I am quite happy with the results.

luisfpg

I think I came with a way to support custom types in Querydsl.
Currently, Querydsl supports mapping custom types using the Configuration, but only for type level.
I'd like to suggest adding to configuration the mapping types to specific columns.
So, for example, a Person has a gender, which is an enum.
Custom types could be used as following:

Configuration config = new Configuration();
config.register(new EnumByNameType(Gender.class), "person", "gender");
// Params are (type, table, column)

The same configuration would need to be passed to the MetaDataExporter, the SQLQuery instances and to DMLClauses.
This would allow, something like:

QPerson p = QPerson.person;
from(p).where(p.gender.eq(Gender.MALE));

What do you think?

timowest

This is syntactically quite nice.

The configuration for the code generation is now in SQLTypeMapping, but could be moved into Configuration.

Here are the configuration forms I came up with :

* Override type mapping for SQL type


configuration.setType(Types.DATE, LocalDate.class);


* Override UserType for certain column


configuration.setType("person", "secureId", new EncryptedString();
configuration.setType( "person", "gender", new EnumByNameType(Gender.class));


* General registration


configuration.register(new StringType());

luisfpg

Seems very nice. I had initially suggested the register with the type first, then the table, then the column thinking that in the future, this could be extended to support multi-column types as the Type interface has the sqlTypes() returning an array, which would allow multiple columns. So, the last argument could be a vararg if this support is added.

However, after thinking a bit, there are other issues involved in supporting such multi-column types, like specifing which class implements the component, and which properties of that class reflect each column. This would be harder to support, and, I think, is out of scope for now (even because complex mappings could always be done through JPA).

So, returning to your last post, yes, that would be great.
However, IMHO, I would move methods for reading from ResultSet and writing to PreparedStatements out of the Configuration (as the concept of configuration is to contain parametrization, not to access the DB). It could still have the register methods, which would delegate to some other specialized class (maybe even the own SQLTypeMapping). The read / write methods would be on that class.

Just my 2¢.

timowest

However, after thinking a bit, there are other issues involved in supporting such multi-column types, like specifing which class implements the component, and which properties of that class reflect each column. This would be harder to support, and, I think, is out of scope for now (even because complex mappings could always be done through JPA).

Yes, multi column support comes later.

However, IMHO, I would move methods for reading from ResultSet and writing to PreparedStatements out of the Configuration (as the concept of configuration is to contain parametrization, not to access the DB). It could still have the register methods, which would delegate to some other specialized class (maybe even the own SQLTypeMapping). The read / write methods would be on that class.

That's ok. The class could be though accessible from the Configuration.

luisfpg

I'm already using the per-column data type and it works great!!!
Thanks a lot!

[Abhishek Chavan]

Hi Timo,
I guess the above post is valid for querydsl-sql. However, can querydsl be used with Hibernate supporting the hibernate User Types. I was working on the spatial query extension and have to do this:

private void setParameters(Map<String, Object> params, Query query) {
        if (params == null) return;
        for (String param : params.keySet()) {
            Object value = params.get(param);
            if (value instanceof Geometry) {
                query.setParameter(param, value, GeometryUserType.TYPE);
            } else {
                query.setParameter(param, value);
            }
        }
    }

Is there some way I can achieve this without changing Serializer? I see HibernateUtil has some types defined but I can't add my type to it without changing the class.

[Timo Westkämper]

Hi.

I guess GeometryUserType is not available in hibernate-core?

I will try to figure something out. You should mention this also in the context of the issue.

Br,
Timo

--
Timo Westkämper
Mysema Oy
+358 (0)40 591 2172
www.mysema.com

[Abhishek Chavan]

Yes. Hibernate Core does not support Spatial Queries, hence GeometryUserType  is not present there.

QueryDSL issue for reference : https://github.com/mysema/querydsl/issues/133