javamarklogicmarklogic-9marklogic-optic-api

How write SQL Query in Marklogic JAVA API using WHERE IN clause?


I am using Marklogic Optic Java API as follows :-

ModifyPlan plan = p.fromView(SchemaName, ViewName)
              .where(p.eq(p.col("COL1"), strColValue))
              .select(colSeq);

Now I have list of data & I need use "WHERE IN" clause for this query.

select * from table where col in ('val1', 'val2'.....,'valn');

Could anyone please tell me how to do this using marklogic Java API.

Thanks & Regards,

Renuka Patil


Solution

  • One way to use SQL on Marklogic DB using Java is JDBC.You can perform following steps:

    1. Step 1:

    Download JDBC driver from: https://jdbc.postgresql.org/download/postgresql-42.1.4.jar & Reference it in your Java Project.

    1. Step 2:

    Setup ODBC server on Marklogic DB as described in : https://docs.marklogic.com/guide/admin/odbc (Remember to select your correct DB in this step & for this example, change Auth type to Basic)

    1. Step 3:

    Sample Java code can be as follow:

    try {
                Connection conn1 = DriverManager.getConnection("jdbc:postgresql://MYHOST:PORT/?preferQueryMode=simple","USER","PWD");
                Statement stmt = conn1.createStatement();
                String sqlstmt = "select SCHEMA.VIEW.COLUMN1, VSCHEMA.VIEW.COLUMN2, SCHEMA.VIEW.COLUMN3 from SCHEMA.VIEW where SCHEMA.VIEW.COLUMN4 in ('VAL1', 'VAL2', 'VAL3')";
                ResultSet rs = stmt.executeQuery(sqlstmt);
                while(rs.next()){ 
                    String c1= rs.getString("COLUMN1"); 
                    String c2= rs.getString("COLUMN2");
                    System.out.println("COL1:"+c1);
                    System.out.println("COL2:"+c2);
                }
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    

    Note:

    1. The code shown above is just to show the possibility of connection & not industrialized version. Hence should be refactored while using as per coding gudelines like removing hardcoding & use of binded queries, etc
    2. Port Number is one which was used for ODBC server setup in Marklogic.

    Hope it helps you :-)