Nice one, the following shows that you can get the com.mendix.systemwideinterfaces.IMendixObject ORM entities from the id field in the table
// This file was generated by Mendix Modeler.
//
// WARNING: Only the following code will be retained when actions are regenerated:
// - the import list
// - the code between BEGIN USER CODE and END USER CODE
// - the code between BEGIN EXTRA CODE and END EXTRA CODE
// Other code you write will be lost the next time you deploy the project.
// Special characters, e.g., é, ö, à, etc. are supported in comments.
package sql.actions;
import com.mendix.systemwideinterfaces.core.IContext;
import com.mendix.webui.CustomJavaAction;
/**
* description:
* takes sql query and attempts to populate pre-declared list of entity
* parameters:
* str_sql:
* sql statement, e.g. 'SELECT * from "public"."system$filedocument" LIMIT 8'
* required
* str_lst:
* pre-declared list of some entity type
* required
* str_entitytype [todo]:
* safety for entity type checking
* optional
* idx_astack
* [reverse] call stack index
* optional
* default: 1
*/
public class ja_sql2imendixobject extends CustomJavaAction<java.lang.Boolean>
{
private java.lang.String str_sql;
private java.lang.String str_lst;
private java.lang.String str_entitytype;
private java.lang.Long idx_astack;
public ja_sql2imendixobject(IContext context, java.lang.String str_sql, java.lang.String str_lst, java.lang.String str_entitytype, java.lang.Long idx_astack)
{
super(context);
this.str_sql = str_sql;
this.str_lst = str_lst;
this.str_entitytype = str_entitytype;
this.idx_astack = idx_astack;
}
@java.lang.Override
public java.lang.Boolean executeAction() throws Exception
{
// BEGIN USER CODE
if(str_sql!=null&&str_lst!=null){
java.util.Stack<com.mendix.core.actionmanagement.CoreAction<?>> astack=getContext().getActionStack();
java.lang.reflect.Method method;//reflect private api
try{
if(idx_astack==null)idx_astack=new Long(1);//default 1
method=astack.get(astack.size()-1-idx_astack.intValue()).getClass().getMethod("getVariable",java.lang.String.class);//note reverse
java.util.List<com.mendix.systemwideinterfaces.core.IMendixObject> lst=(java.util.ArrayList<com.mendix.systemwideinterfaces.core.IMendixObject> )method.invoke(astack.get(astack.size()-1-idx_astack.intValue()),str_lst);//note reverse
com.mendix.core.Core.dataStorage().executeWithConnection(
connection->{
java.util.List<com.mendix.systemwideinterfaces.core.IMendixObject> arr_obj=new java.util.ArrayList<com.mendix.systemwideinterfaces.core.IMendixObject>();
try{
java.sql.PreparedStatement stmt=connection.prepareStatement(str_sql);
java.sql.ResultSet rset=stmt.executeQuery();
java.sql.ResultSetMetaData rmd=rset.getMetaData();
while(rset.next()){
try{//todo: check orm type
long id=rset.getLong("id");
lst.add(
com.mendix.core.Core.retrieveId(
this.getContext(),
com.mendix.core.Core.createMendixIdentifier(id)
)
);//manip caller
}catch(Exception e) {
com.mendix.core.Core.getLogger(this.toString()).error(e);
}
}
}catch(Exception e) {
throw new com.mendix.systemwideinterfaces.MendixRuntimeException(e);
}
return arr_obj;
}
);
}catch(Exception e){
com.mendix.core.Core.getLogger(this.toString()).error(e);
}
return true;
}else{
throw new com.mendix.systemwideinterfaces.MendixRuntimeException("str_sql/str_lst NULL");
}
// END USER CODE
}
/**
* Returns a string representation of this action
*/
@java.lang.Override
public java.lang.String toString()
{
return "ja_sql2imendixobject";
}
// BEGIN EXTRA CODE
// END EXTRA CODE
}
Also fakes return any type by pushing it into the caller so you can populate anything
I tried the following code (from: https://github.com/ako/QueryApiBlogPost)
List<IMendixObject> resultList = null;
resultList = Core.dataStorage().executeWithConnection(connection ->
{
List<IMendixObject> result = new ArrayList<IMendixObject>();
try {
PreparedStatement stmt = connection.prepareStatement(this.Sql);
ResultSet rset = stmt.executeQuery();
ResultSetMetaData rmd = rset.getMetaData();
int colCount = rmd.getColumnCount();
while(rset.next()){
IMendixObject obj = Core.instantiate(getContext(),this.ResultEntity);
result.add(obj);
for(int colIdx=1; colIdx <= colCount ; colIdx++){
String colName = rmd.getColumnName(colIdx);
obj.setValue(getContext(),colName,rset.getObject(colIdx));
}
}
} catch (SQLException e) {
throw new MendixRuntimeException(e);
}
return result;
});
return resultList;
Above code uses a non-persistable entity to show specific results. To begin (and to learn) I only want to execute this query:
select * from "PUBLIC"."testproject$client"
limit 10
What must I change in the Java code to make this work? The goal is to get a list of clients that I can use in a Microflow (return as list in my JavaAction).