Define collation on a per query basis

Question

We're using Apache Cayenne to integrate an already existing MS SQL Server database with our application (I have no permission to change the database DDL, including the table/schema/database collation).

The database is using a specific collation (croatian) which defines single characters like "nj" and "dž", so when I do a LIKE query:

select * from table where name like '%N%'
I get zero results, on the other hand If i do:
select * from table where name like '%NJ%'
I get multiple results.

Obviously this is simple to fix by adding collate to the end of the query, but I know of no way of doing this with Cayenne. Any way to implement this without dropping the ORM-benefits as a whole?

tl;dr: Is there any way to preprocess queries before going to the database, like so:

query = query + ' collate SQL_Latin1_General_CP1_CI_AS'

score 1 · Accepted Answer · answered May 24 '18 at 11:06

You can try to use custom DB adapter with custom SQL translator that will append required part to all select queries. Can be implemented like this for Cayenne 4.0:

public class CustomSQLServerAdapter extends SQLServerAdapter {

    public CustomSQLServerAdapter(/* all params */) {
        super(/* all params */);
    }

    @Override
    public SelectTranslator getSelectTranslator(SelectQuery<?> query, EntityResolver entityResolver) {
        return new SQLServerSelectTranslator(query, this, entityResolver) {
            @Override
            protected void doTranslate() {
                super.doTranslate();
                sql += " collate SQL_Latin1_General_CP1_CI_AS";
            }
        };
    }
}

To use this adapter you can contribute custom detector for it (see docs) or set it directly in Modeler (data node -> adapter).

Define collation on a per query basis

1 Answers1