Allow custom SQL for columns when using CRUD

[agentgt]

Often times in JDBC when inserting or updating for databases like Postgresql with more advance types it is easiest to perform a cast of the parameter. That is delegate the data conversion to the database.

For example lets assume Postgresql database column 'b' with INET datatype (usually used for IP addresses):

-- generated sql
INSERT INTO some_table(a,b) VALUES ( ?, ?::inet );

Then with Java

// java
preparedStatement.setString(2, "127.0.0.1")

For the PreparedStatementMapper (or whatever its name) this isn't a problem because you write the insert statement.

However for the Crud SQL generation it is not customizable.

The code that would need to be customized to allow someone to do this is essentially allow custom ColumnMeta in the CrudFactory. aka ColumnMeta provider of sorts.

Anyway the above would be a worthwhile feature for us as the CRUD SQL insert generation is useful for large tables.

[arnaudroger]

that would be nice also I wonder if there is a way from the metadata to do that automatically for the case of inet

[arnaudroger]

so was just trying with inet there, and there is a work around there

JdbcMapperFactory
        .newInstance()
        .addColumnProperty("b", 
            new IndexedSetterProperty(
                (ps,  t, i) -> ps.setObject(i, t, Types.OTHER)
            )
        )
        .crud(FooString.class, long.class)
        .table(c, "issue_649");

if you defined the an IndexedSetterProperty that will call setObject with a Types.OTHER the jdbc driver will do the conversion.

[agentgt]

Oh I guess Types.Other works now! A really long time ago (postgres 9.2) I had issues going that route.

BTW just a minor critique on ergonomics: it is a shame addColumnProperty doesn't have that much type information. I had to go look into the code to see what was expected. I'm guessing that is because of backwards compatibility or the method is too overloaded?

Maybe add additional methods like addColumnPropertySetter. Even a marker interface would be better than just Object (ie have Getter/SetterProperty implement some no method interface).

I am still exploring SFM and learning it so I might not fully grasp the API but I still would rather have greater control over SQL generation even if the above does appear to work. Otherwise like many of these tools we (my team) just end up going completely custom (create or are own libraries) or go low level (bypass the library).

[arnaudroger]

Yep the customisation need more doc or helper method... and the effect not that clear...

Also agree on the sql, crud was done as an experiment that ended up working pretty well, I don’t think I touched for a while so feed back are more than welcome.

You can imagine I don’t use all of it every day.