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

Question

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

John Snelson · Accepted Answer · 2018-04-19 09:31:57Z

3

Probably the easiest way to do that is to just pass in a sequence of strings on the right hand side of the join condition:

ModifyPlan plan = p.fromView(SchemaName, ViewName)
    .where(p.eq(p.col("COL1"), p.xs.stringSeq("val1", "val2",...,"valn")))
    .select(colSeq);

answered Apr 19, 2018 at 9:31

John Snelson

9584 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vinayak Singh · Accepted Answer · 2018-04-19 08:11:56Z

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

Step 1:

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

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)

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:

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
Port Number is one which was used for ODBC server setup in Marklogic.

Hope it helps you :-)

AS Mackay · Accepted Answer · 2019-05-18 11:58:42Z

-1

I see p.xs.stringSeq is deprecated. The documentation is preferring us to use as below:

{
    ModifyPlan plan = p.fromView(SchemaName, ViewName)
                       .where(p.eq(p.col("COL1"), 
                               p.seq(p.xs.string("val"),p.xs.string("val2"))))
                       .select(colSeq);
}

I changed from p.xs.stringSeq to p.seq

edited May 18, 2019 at 11:58

AS Mackay

2,8559 gold badges21 silver badges27 bronze badges

answered May 18, 2019 at 10:35

Kesavarapu Chaithu

273 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related