You can write XPath and OQL queries in Java. See the methods Core. retrieveXPathQuery and Core.retrieveOQLDataTable
Unfortunaltey, because you do not know the type of objects you can not return them. I am not sure what your use case is. If you are certain that their type will be some specific entity then it is possible. If you need to work for any typ of objects you will need some workaround.
There are some examples here how to use Xpath and OQL in a java action: https://github.com/ako/QueryApiBlogPost.
You can use an entity parameter, part of the connector kit improvements, to specify the type of objects in the list. See this example: https://github.com/ako/QueryApiBlogPost#retrieve-objects-using-oql
I did this many times. Especially an SQL 'IN'-statement is lacking in Mendix. For this I have used the approach:
After this, retrieve all the Mendix identifiers from the IDataTable and put them in a list. Then, feed that list to the Mendix Core API function:
// Retrieve the data from the database IDataTable table = Core.retrieveOQLDataTable(getContext(), OqlQuery);
List<IMendixObject> objects = RetrieveObjectsByIDs(ListIDs); return objects;
to retrieve the correct objects agreeing with the IN statement.
To sum it up, it involves some Java, but is not too hard to build.
So if want to write OQL query in .java file using Core.retrieveOQLDataTable method, what will be the syntax? i keep getting parse errors in the code.
"select Alias.ColName as ColName
+ " FROM \"Module$EntityName\" as Alias where Alias.Colname= 'value'";
I have tried Module.EntityName a well. Nothing works
Our test on this for Mendix6 and 7 resulted in:
1) Advanced SQL -- OK
2) Advanced OQL -- OK
3) Advanced XPath -- OK
4) CreateDateRangeList -- NG because of an error GENERATE_SERIES of PostgreSQL