How to write query in java/Eclipse and send back the list to mendix using java action.

2
Is it possible to write our own query in java for mendix and send back the result.
asked
5 answers
3

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.

 

answered
2

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

answered
1

Hi Amresh,

I did this many times. Especially an SQL 'IN'-statement is lacking in Mendix. For this I have used the approach:

  1. Create a string variable called 'OqlQuery' in which the (dynamic) query string with the 'IN' statement is written.
  2. Create a Java action with the OqlQuery String as an inputparameter.
  3. As an output parameter choose whatever you like (but most likely a list of objects that agree with the IN statement?)
  4. In the Java action, call the Mendix Core API function:  
  5. // Retrieve the data from the database
IDataTable table = Core.retrieveOQLDataTable(getContext(), OqlQuery);
     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: 
    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.

answered
0

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

answered
0

See: https://github.com/ako/QueryApiBlogPost

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

answered