DA SQL sample (Java)
The DA SQL console sample demonstrates how to use the DA SQL feature of Data Abstract. DA SQL is a technology introduced to Data Abstract that gives clients the full flexibility of SQL queries to describe their data needs while offering a layer of protection. The queries will be run against the middle tier rather than against the back-end database(s), using the table and field names that are defined in the schema rather than those defined in the back-end database.
Getting Started
The DA SQL 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\dasql\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.dasql.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 passes a simple DA SQL request to an instance of TableRequestInfoV6 that requests certain fields from a row where the ClientId matches 20 (Step 2). Then a more complex DA SQL is issued that pulls data from multiple tables to create a "new" table and then that table is printed to the console (Step 3).
Examining the Code
All of the code can be found in the Program.java source file.
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();
return;
}
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("");
System.out.println("");
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.print(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;
}
Simple DA SQL request (Step 2)
A DA SQL statement is simply a normal SQL statement however it uses the table & field names from the schema rather than the ones in the back-end Database. To compose a DA SQL simply put the sql statement in a string, and then pass that string to an instance of TableRequestInfoV6 which defines that data that is required and is then passed to the RemoteDataAdapter to retrieve that data.
The SQL request created here (lDASql) requests the ClientId, ClientName, ClientPhone and ClientAddress fields from the Clients table where the ClientId is equal to 20.
int lClientId = 20;
String lDASql = String
.format("SELECT clientid, clientname, clientphone, clientaddress FROM clients WHERE clientid = %d",
lClientId);
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 "Clients".
DataTable lTable = new DataTable("Clients");
Next a TableRequestInfoV6 object is created which will be used to specify custom parameters for the data request. Passing "-1" to setMaxRecords requests that all of the table's records are returned, 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 "true" needs to be passed to the setIncludeSchema method. Finally to pass the DA SQL statement use the setSql method.
TableRequestInfoV6 lRequestInfoV6 = new TableRequestInfoV6();
lRequestInfoV6.setMaxRecords(-1);
lRequestInfoV6.setIncludeSchema(true);
lRequestInfoV6.setSql(lDASql);
Finally to retrieve the data we pass the TableRequestInfoV6 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.
lDataAdapter.fill(lTable, lRequestInfoV6);
An alternative option to request the data using fillAsync is shown in Working Asynchronously with the Remote Data Adapter.
Using a Complex DA SQL request (Step 3)
DA SQL statements aren't limited to basic requests, you can compose as complex a request as you need by using a multi-line string. Here the statement retrieves a table where the rows are comprised of 3 fields which are assigned different names (Group, Product and Sold) from 3 different tables (OrderDetails, Products and ProductGroups) where the order Type was "2", and the order date was between "2013-01-01" and "2013-12-12" and the data is grouped together and ordered by two columns, first by column "3" (i.e. the amount sold) and then by the "Desc" column.
System.out.println("STEP 3. Complex DASQL ...\n");
String lDASqlTemplate = "SELECT g.Name AS [Group], "
+ "p.Name AS Product, " + "sum(d.Amount) AS Sold "
+ "FROM " + "OrderDetails d " + "INNER JOIN Orders o "
+ "ON o.Id = d.[Order] " + "INNER JOIN Products p "
+ "ON p.Id = d.Product " + "INNER JOIN ProductGroups g "
+ "ON g.Id = p.[Group] " + "WHERE "
+ "o.Type = %s " // Sale
+ "AND o.Date > '%s' " // 2013-01-01
+ "AND o.Date <= '%s' " // 2013-12-12
+ "GROUP BY " + "g.Id, " + "g.Name, " + "p.Name " + "ORDER BY "
+ "3 Desc ";
int lOrderTypeSale = 2;
String lFromDateString = "2013-01-01";
String lToDateString = "2013-12-12";
lDASql = String.format(lDASqlTemplate, lOrderTypeSale, lFromDateString,
lToDateString);
System.out.println(String.format("DASQL:\n %s", lDASql));
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 "Clients".
lTable = new DataTable("OrderDetails");
The TableRequestInfoV6 object from Step 2 is re-used except that the new sql statement is passed to the setSql method. Then as before the fill method is called passing in the DataTable and TableRequestInfoV6 objects, which will retrieve the data and place it in the DataTable object.
lRequestInfoV6.setSql(lDASql);
lDataAdapter.fill(lTable, lRequestInfoV6);
Example Output
This is an example of the output you will see when the sample is run. In Step 2 you can see the single row that matches the simple DA SQL statement where the ClientId had to match 20. Then in Step 3 it retrieves all orders from the "Orders" table issued between "2013-01-01" and "2013-12-12" with order "Type" 2 and pulls data from the "Products" and "ProductGroups" tables.
DA SQL 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. Simple DASQL ...
DASQL: SELECT clientid, clientname, clientphone, clientaddress FROM clients WHERE clientid = 20
Table: Clients (1 rows from 1)
|--------------------------------------------------------------------------|
|ClientId |ClientName |ClientPhone |ClientAddress |
|--------------------------------------------------------------------------|
|20 |Anna H. Kugler |(817) 249-9525 |597 Loving Acre... |
|--------------------------------------------------------------------------|
STEP 3. Complex DASQL ...
DASQL:
SELECT
g.Name AS [Group],
p.Name AS Product,
sum(d.Amount) AS Sold
FROM OrderDetails d
INNER JOIN Orders o ON o.Id = d.[Order]
INNER JOIN Products p ON p.Id = d.Product
INNER JOIN ProductGroups g ON g.Id = p.[Group]
WHERE
o.Type = 2 AND
o.Date > '2013-01-01' AND
o.Date <= '2013-12-12'
GROUP BY g.Id, g.Name, p.Name
ORDER BY 3 Desc
Table: OrderDetails (17 rows from 17)
|------------------------------------------------------|
|Group |Product |Sold |
|------------------------------------------------------|
|RAM |RAM DDR3 4096Mb |4 |
|Monitors |Monitor 22"; Dell |4 |
|Motherboards |Motherboard INT... |2 |
|Hard-Disk Drives |HDD Seagate Bar... |2 |
|CPU |CPU INTEL i5-4670 |2 |
|Cases |Case CM HAF 912... |2 |
|Tablets |Apple A1474 iPa... |2 |
|Solid State Disks |SSD Intel 240 Gb |1 |
|Video |Video Asus NVid... |1 |
|Mouses |Mouse Logitech ... |1 |
|Monitors |Monitor 22"; Asus |1 |
|Notebooks |Apple MacBook A... |1 |
|Notebooks |Notebook 15.6";... |1 |
|Netbooks |Ultrabook 13.3"... |1 |
|Tablets |Tablet 10,1"; A... |1 |
|Players |TRANSCEND T.son... |1 |
|Laser Printers |Printer HP Lase... |1 |
|------------------------------------------------------|
DONE! Please press return to exit.