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

Related searches

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

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

Optic API Tutorial - MarkLogic Community, You can access that data using either SQL or the Optic API. It's available in all of MarkLogic's supported languages: JavaScript, XQuery, REST, Java, and soon Node.js. Here is an example query using the Optic API in JavaScript: op. Select clauses can use 'op:col' or simple string that matches the view's columns. The SQL functions are listed along with the MarkLogic builtin functions that support them. The syntax for the SQL function is the same as that of the respective builtin function. You can also call any MarkLogic builtin function in a SQL query, as described in Calling Built-in Functions from SQL.

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

Search Customization Using Query Options, When you use the XQuery Search API, REST API, or Java API, you can MarkLogic Server defines a set of default query options that are applied when you do To make key-value metadata searchable, you must define a metadata field on� This chapter describes how to configure MarkLogic Server and create views to model your MarkLogic data for access by SQL. Views can also be created using the Views API described in MarkLogic XQuery and XSLT Function Reference. You must have the admin role on MarkLogic Server to complete the procedures described in this chapter. The main topics are:

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

Writing and Searching for POJOs in MarkLogic, The latest version adds the MarkLogic Java API to make it easy to take equals predicates in the WHERE clause of an SQL database but can operate on Now we can use the query options to constrain the POJO properties� In this SELECT query tutorial I'll take JDBC to the next step, showing how to create and execute a SQL SELECT statement in your Java code. JDBC SELECT query: A sample database. Before looking at the SQL queries, let's take a quick look at our sample database.

Mulesoft MarkLogic: MarkLogic Mulesoft Connector, Write SQL, get MarkLogic data. Similar to the MuleSoft Database Connector, but for MarkLogic. Powerful SQL Engine with support for dynamic queries,� Default Query Options. The MarkLogic Java API comes with predefined persistent query options called default. It acts just like any other options and is used if options are not specified elsewhere. You can read it into a handle, change values, and write it back out, where it will still be used as the default query options.

Does the Azure Search individual query clause limit apply - iOS, I'd like to use Azure Search's search.in filter clause to do security trimming. This related SO answer might be useful: How to make a sql search query more powerful? Marklogic performance with JAVA API on Rawcombined Query. In a typical search application, a user enters query text into a search box in a browser. This text is a string query. The Search API automatically parses a string query into a cts:query for efficient and powerful searches. You can use string queries in XQuery, Java, Node.js, and REST, through interfaces such as the following:

This hands-on course is the starting point for developers, administrators, and architects who are new to MarkLogic technology. It is designed to quickly teach you the core fundamentals as you explore what MarkLogic is, how to install and configure MarkLogic, how to create a database, how to load and query data, and how to connect to a database via programming interfaces.