Using longitude and latitude in SQL query with custom Java

0
Hi, I want to use this kind of query (from https://gist.github.com/robotmay/851250) in a microflow: # Using an example table of 'addresses' with a latitude and longitude column # Replace #LONGITUDE#, #LATITUDE#, and #DISTANCE_IN_MILES# with your search values SELECT addresses.*, (ACOS( SIN(RADIANS(#LATITUDE#)) * SIN(RADIANS(addresses.latitude)) + COS(RADIANS(#LATITUDE#)) * COS(RADIANS(addresses.latitude)) * COS(RADIANS(addresses.longitude) - RADIANS(#LONGITUDE#)) ) * 3963.1676) AS distance FROM addresses WHERE (((ACOS( SIN(RADIANS(#LATITUDE#)) * SIN(RADIANS(addresses.latitude)) + COS(RADIANS(#LATITUDE#)) * COS(RADIANS(addresses.latitude)) * COS(RADIANS(addresses.longitude) - RADIANS(#LONGITUDE#)) ) * 3963.1676) <= #DISTANCE#) OR (addresses.latitude = #LATITUDE# AND addresses.longitude = #LONGITUDE#)) But this is not possible with XPath. Is there a simple way to get the results of this query in a microflow? I think the only way to do this is a java-action? I want to loop through the results and use it in a listview. 
asked
2 answers
1

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

 

answered
1

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).

answered