Dynamic Where sample (Java)

The Dynamic Where console sample demonstrates how to use the Dynamic Where feature of Data Abstract which provides a means to dynamically change an SQL statement's WHERE clause that will be sent to the server.

Getting Started

The Dynamic Where sample is installed in C:\Users\Public\Documents\RemObjects Samples\Data Abstract for Java\Java\Console\.

To build it you can either build & execute it from the command line or make use of the provided Eclipse .classpath and .project files.:

If using the command line remember that you will need to explicitly include the two jar files provided by the Data Abstract for Java package in the classpath. So for example to build and run this sample you'll need:

  • Build: javac -cp "C:/Program Files/RemObjects Software/Data Abstract for Java/Bin/com.remobjects.dataabstract.jar";"C:/Program Files/RemObjects Software/Data Abstract for Java/Bin/com.remobjects.sdk.jar";src src\com\remobjects\dataabstract\samples\dynamicwhere\Program.java
  • Execute: java -cp "C:/Program Files/RemObjects Software/Data Abstract for Java/Bin/com.remobjects.dataabstract.jar";"C:/Program Files/RemObjects Software/Data Abstract for Java/Bin/com.remobjects.sdk.jar";src com.remobjects.dataabstract.samples.dynamicwhere.Program

Like all the samples provided with Data Abstract for Java you will need to be running the Relativity Server with the DASamples Domain and the Simple Schema available.

Lastly this sample makes use of a convenience class (DataTableExtension) which is provided with Data Abstract for Java. It provides a single public method LogToString that takes a DataTable and converts its contents into a String in a pretty fashion that could be logged to a console or into a file. Please note that this class extension is intended for test purposes only.

Running the Sample

When run the sample sets up a connection to an instance of Relativity Server and logs in (Step 1). It then creates a WhereExpression which will look for "Role" fields that contain the string "Manage" (Step 2). Finally a more complex expression is created that will find rows where the "Role" field contains the string "Manager" and whose name starts with "J", or for users whose "Id" matches 20, 22 or 24 (Step 3)

Examining the Code

All of the code can be found in the Program.java source file.

The sample uses the Users schema table which has a number of fields, however we are only interested in the following four:

  • Id is the primary key and use an AutoInc data type.
  • Name is a string of up to 50 characters and is required for each new row
  • Role is also a string of up to 50 characters, the field is optional.
  • Type is a LargeInt, the field is optional.

Setting up a Connection & Logging in (Step 1)

There are three stages to setting up a connection to an instance of Relativity Server.

In the first stage we initialize the underlying services that we will use to establish the connection to the server. The BinMessage is a message type that will be used to encode the data as a message. The HttpClientChannel is the communications channel over which the data will be sent, here we are establishing that it uses "HTTP" and specifying the address of the server. Finally an instance of Bin2DataStreamer is created which will handle the encoding and decoding of the data packets being transmitted.

The second stage is to configure a RemoteDataAdapter object that we interact with to communicate with the server. After its created you pass it the three objects we created previously and set the DataServiceName and LoginServiceName. At this point we have a working connection but we will be unable to manipulate the schema data until we log in. Attempting to do so will cause an ROException exception to be thrown with the message "Session could not be found.".

The final stage passes a special login string (lLoginString) to the login method to establish a login session. The login method returns a boolean which will be true if the login attempt was successful or false if it failed. The login string is comprised of 4 parts:

  • Username the username to attempt to login with
  • Password the password to use
  • Domain the name of the domain that the schema is located in
  • Schema the name of the schema we wish to use

For example, the login string from this sample is "User Id=simple;Password=simple;Domain=DASamples;Schema=Simple"

NOTE If there is a problem attempting to communicate with the server, or if the schema or domain name is incorrect then an ROException will be thrown and should be handled appropriately.

BinMessage lMessage = new BinMessage();
HttpClientChannel lChannel = new HttpClientChannel();
try {
    lChannel.setTargetUrl(new URI("http://localhost:7099/bin"));
} catch (URISyntaxException e1) {
    e1.printStackTrace();
}

System.out.println(String.format("Target URL is %s", lChannel
        .getTargetUrl().toString()));
System.out.println("RO SDK layer is configured.");

Bin2DataStreamer lDataStreamer = new Bin2DataStreamer();
RemoteDataAdapter lDataAdapter = new RemoteDataAdapter();
lDataAdapter.setDataStreamer(lDataStreamer);
lDataAdapter.setMessage(lMessage);
lDataAdapter.setClientChannel(lChannel);
lDataAdapter.setDataServiceName("DataService");
lDataAdapter.setLoginServiceName("LoginService");

System.out.println("RO DataAbstract layer is configured.");

System.out.println("STEP 1: Login to DataService");
String RelativityConnectionStringTemplate = "User Id=\"%s\";Password=\"%s\";Domain=\"%s\";Schema=\"%s\"";
String lLoginString = String.format(RelativityConnectionStringTemplate,
        "simple", "simple", "DASamples", "Simple");
System.out.println(String.format("Login string is %s", lLoginString));
Boolean lLogged = lDataAdapter.login(lLoginString);

if (lLogged) {
    System.out.println("Login has been successful. Going further...");
} else {
    System.out
            .println("Cannot login. Please check your user name  and password. Exiting...");
    return;
}

To use Dynamic Select we first need to create a TableRequestInfoV5 object that means we can specify custom parameters for the data request. We pass "-1" to setMaxRecords to request all of the table's records, if a positive value is passed then the number of records is limited to that. To request the schema to be returned in the results we pass "true" to setIncludeSchema.

To specify which fields we are interested in we need to create a StringArray object and add a String object per field we are interested in. Here we pass an empty StringArray to the setDynamicSelectFieldNames method and then loop through a normal array of Strings of adding each String to the StringArray which is retrieved using getDynamicSelectFieldNames.

Finally we tell the RemoteDataAdapter to use Dynamic Select by passing "true" to setDynamicSelect.

TableRequestInfoV5 lRequestInfo = new TableRequestInfoV5();
lRequestInfo.setMaxRecords(-1); // no limits - return all records
lRequestInfo.setIncludeSchema(true); // update schema in the result data
                                     // table to match recieved data
                                     // (not necessarily).
lDataAdapter.setDynamicSelect(true);
lRequestInfo.setDynamicSelectFieldNames(new StringArray(new String[] {
        "Id", "Name", "Type", "Role" }));

Building & Issuing a Dynamic Where expression (Step 2)

To build the simple Dynamic Where expression we need, we start with a BinaryExpression which is the main type of expression used to form WHERE clauses. We next create a FieldExpression and pass the name of the field we are interested in to it, here "Role". Next we create a ConstantExpression passing in the string we are interested in matching, along with a "%" before and after the string to indicate that we want to find the string anywhere in a string, and the DataType. Finally we pass in a BinaryOperator which denotes how the operands of the BinaryExpression are applied.

The next step is create a DataTable which will hold the results from the server. The name passed to the constructor should be the name of the table you wish to retrieve data from; here it is "Users"

To pass the WhereExpression to the TableRequestInfoV5 use the setWhereClause, though you need to convert the WhereExpression to an XML string using toXmlString and passing the result to the constructor of the XmlType class.

Finally to retrieve the data we pass the TableRequestInfoV5 and DataTable to the fill method of RemoteDataAdapter which executes in a synchronous fashion delaying further processing until the request completes. Once the data has been retrieved it is printed to the console.

System.out.println("STEP 2. Using Simple Condition ...");
System.out.println("Select Managers");

WhereExpression lCondition = new BinaryExpression(new FieldExpression(
        "Role"), new ConstantExpression("%Manager%", DataType.String),
        BinaryOperator.Like);

System.out.println(String.format("Condition: %s",
        lCondition.toSqlString()));

DataTable lTable = new DataTable("Users");
lRequestInfo.setWhereClause(new XmlType(lCondition.toXmlString()));
lDataAdapter.fill(lTable, lRequestInfo);

Building & Issuing a complex Dynamic Where expression (Step 3)

In Step 3 the sample builds a much more complicated WHERE clause which attempts to find "Managers" whose name starts with "J", or users whose "Id" is either 20, 22 or 24. To achieve it, two separate BinaryExpressions which will be passed to a top level BinaryExpression which uses an "OR" BinaryOperator to match either the first OR the second BinaryExpression.

The first BinaryExpression is composed of two other BinaryExpressions. The first creates a FieldExpression with the field name "Name" and a ConstantExpression with the string "J%" which indicates that we want any string that starts with a "J" and the DataType. The second is identical to the one in Step 2 which will match any "Role" fields which contain the string "Manager". Lastly we pass in an "AND" BinaryOperator which denotes how the operands of the two BinaryExpression are applied.

The next BinaryExpression is comprised of a FieldExpression with the field name "Id" and a ListExpression which is comprised of an array of 3 ConstantExpressions which will attempt to match the numbers 20, 22 and 24. Lastly an "In" BinaryOperator is used to indicate that we want to see if the left operator is IN the right.

System.out.println("STEP 3. Using Complex Condition ...");
System.out
        .println("Select Managers with name started with 'J' OR users with Id 20, 22, 24");

lCondition = new BinaryExpression(
        new BinaryExpression(new BinaryExpression(new FieldExpression(
                "Name"), new ConstantExpression("J%", DataType.String),
                BinaryOperator.Like), new BinaryExpression(
                new FieldExpression("Role"), new ConstantExpression(
                        "%Manager%", DataType.String),
                BinaryOperator.Like), BinaryOperator.And),
        new BinaryExpression(
                new FieldExpression("Id"),
                new ListExpression(new WhereExpression[] {
                        new ConstantExpression(20, DataType.Integer),
                        new ConstantExpression(22, DataType.Integer),
                        new ConstantExpression(24, DataType.Integer) }),
                BinaryOperator.In), BinaryOperator.Or);

Example Output

This is an example of the output you will see when the sample is run. In Step 2 you can see there are 9 rows where the "Role" field contains the word "Manager". Then in Step 3 you can see that there are a total of 5 rows, 2 of which where the "Role" field contains the word "Manager" and the "Name" field begins with "J" and 3 rows where the "Id" matches the values 20, 22 and 24.

Dynamic Where sample has been started.
Target URL is http://localhost:7099/bin
RO SDK layer is configured.
RO DataAbstract layer is configured.
STEP 1: Login to DataService
Login string is User Id="simple";Password="simple";Domain="DASamples";Schema="Simple"
Login has been successful. Going further...


STEP 2. Using Simple Condition ...
Select Managers
Condition: (Role like "%Manager%")

Table: Users (9 rows from 9)
|----------------------------------------------------------------|
|Id            |Name               |Type     |Role               |
|----------------------------------------------------------------|
|2             |Gale Dalton        |1        |Income Manager     |
|3             |Matthew Decker     |1        |Income Manager     |
|4             |Joseph Henson234   |1        |Income Manager     |
|5             |Jennifer Kent      |1        |Sales Manager      |
|6             |Sandy Manning      |1        |Sales Manager      |
|7             |Glenn Cunningham   |1        |Sales Manager      |
|8             |Marcy Collins      |1        |Sales Manager      |
|9             |Kirsten Rosario    |1        |Sales Manager      |
|10            |Merle Frank        |1        |Sales Manager      |
|----------------------------------------------------------------|



STEP 3. Using Complex Condition ...
Select Managers with name started with 'J' OR users with Id 20, 22, 24
Condition: (((Name like "J%") and (Role like "%Manager%")) or (Id in [20, 22, 24]))

Table: Users (5 rows from 5)
|----------------------------------------------------------------|
|Id            |Name               |Type     |Role               |
|----------------------------------------------------------------|
|4             |Joseph Henson234   |1        |Income Manager     |
|5             |Jennifer Kent      |1        |Sales Manager      |
|20            |Anna H. Kugler ... |3        |Guest              |
|22            |Kenny S. Lay       |3        |Guest              |
|24            |Lillie R. Schro... |3        |Guest              |
|----------------------------------------------------------------|



DONE! Please press return to exit.