Oracle ADF Essentials/ Oracle ADF with MYSQL

Using Oracle ADF along with MYSQL can be a tricky since it was originally designed to work with Oracle Database. Like we understand that there are some trivial differences to certain operators across both engines.

Consider that you are using a view criteria that uses 'Contains' or 'Begins With' conditions for the bind variables.  MySQL doesn't support the pipe operator for concatenation out of the box.

The issue and solution is articulated here. One solution is to SET sql_mode='PIPES_AS_CONCAT';. This will fix the engine itself.
If you have additional constraints or dependent queries due to which you can't change the engine, here's another solution.

Let's take a User ViewObject with a user search view criteria, looks like the screenshot below

Create a Custom Implentation that extends the ViewObjectImpl class and overrides buildQuery method.

Let's dive into the code real quick

public class CustomVOImpl extends ViewObjectImpl {
    public CustomVOImpl(String string, ViewDefImpl viewDefImpl) {
        super(string, viewDefImpl);
    }

    public CustomVOImpl() {
        super();
    }
   
    @Override
     protected String buildQuery(int noUserParams, boolean forRowCount)
     {
       String query = super.buildQuery(noUserParams, forRowCount);
       if (query!=null) {
         query = query.replace( "( ? || '%')", " (CONCAT( ?, '%' ) )");
         query = query.replace( "('%' || ? || '%')", " (CONCAT('%', ?, '%' ) )");
       }
       //System.out.println("Updated query"+query);
       return query;
     }

     @Override
     protected String buildQuery(int noUserParams, boolean forRowCount, String selClause, String fromClause, String whereClause, int subQueryLevel)
     {
       String query = super.buildQuery(noUserParams, forRowCount, selClause, fromClause, whereClause, subQueryLevel);
       if (query!=null) {
           query = query.replace( "( ? || '%')", " (CONCAT( ?, '%' ) )");
           query = query.replace( "('%' || ? || '%')", " (CONCAT('%', ?, '%' ) )");
       }
        // System.out.println("Updated query"+query);
       return query;
     }
}
To explain in detail, query.replace( "( ? || '%')", " (CONCAT( ?, '%' ) )"). The '?' is where our bind parameter plugs itself in. Now replacing pipe operator with MySQL concat looks like (CONCAT( ?, '%' ) ). This concatenates bind parameter with a trailing '%' wildcard character.
Likewise to '%' || ? || '%' translates to (CONCAT('%', ?, '%' ) ).

Finally don't forget to incorporate this change in your ViewObject.!

 I did not find anything else that got funny with ADF Essentials + MySQL with Integrated WLS. Had to do some additional configs on Glassfish which I will explain it in a different blog post.