pureQuery with IDS
pureQuery provides a high-performance data access platform that makes
it easier to develop, optimize, secure, and manage data access.
pureQuery consists of:
- Java application programming interfaces (APIs) built for ease of
use and for simplifying the use of best practices for enhanced
database performance when using Java
- A runtime that provides optimized and secure database access
- An Eclipse-based integrated database development environment, Data
Studio Developer, for enhancing development productivity
pureQuery offers many advantages, including:
- Bridging the gap between data and Java technology by harnessing
the power of SQL within an easy-to-use Java data access
platform
- Improving problem isolation and resolution capabilities by
correlating Java data access with problem application code
- Deploying applications to query in-memory collections and
databases using a single API
- Reducing or preventing SQL injection risk for Java database
applications
- Mapping relational data to Java objects using pureQuery beans
- Facilitating and encouraging use of Java development best
practices
For more information on pureQuery, see
Resources.
Creating a
pureQuery application with IDS
In this section, learn how to generate a pureQuery data access layer
and test code from existing tables. This section also provides a
sample java application so you can try it out.
- Switch to the Java perspective and create a new Java project
titled "pureQueryTutorial".
- Go to the Data Perspective's Data Source Explorer view.
Optionally, you can add the Data Source Explorer View in the Java
perspective, instead of switching between the Data and Java
perspectives.
- Pick up your IDS connection, navigate to the table
product, right-click, and select Generate pureQuery
code.
Figure 16.
Data Source Explorer view
- Browse to select an existing source folder under pureQueryTutorial
project.
-
Enter pureQueryCode for package
name.
Notice that in Figure 16, "Generate
annotated-method interface for table" is selected. pureQuery
offers two different method styles: inline-style and
annotated method style.
pureQuery inline-style
- Includes a complete set of Java methods for executing
queries and update operations
- The methods take an SQL statement and its parameters as
input and return results
pureQuery annotated method style
- Provides data accessor and update methods
- The methods are declared in a user-created Java interface
using annotations that express the specific query or
update operations in standard SQL
- Automatically creates implementation of specified methods
using Java annotated class definitions
- Separate data access declaration and associated SQL from
application's business logic
For more information about the different styles, see the
Write
high performance Java data access applications article series.
This tutorial uses the annotated method style. Select
Next.
Figure 17.
"Generate pureQuery code from a table" wizard
- The next screen (Figure 18) allows you to
generate test classes. Select both annotated method and
inline-style test classes so you can view both to see the
differences. Optionally, select Include connection information
in test, then select Next.
Figure 18.
"Generate pureQuery code from a table" wizard, part
2
- The next screen allows you to map the columns to the bean field.
Use the drop-down menu option to select Timestamp as the
Field Type for both introduction_date and discontinued_date
columns, and click on Next.
Figure 19.
"Generate pureQuery code from a table" wizard, part
3
- Select Generate all SQL statements, then select
Finish.
Figure 20.
"Generate pureQuery code from a table" wizard, part
4
As the wizard completes, several classes are created:
- Product.java: A java file containing a one to one mapping
from the data in the Product table to the Java
object.
- ProductData.java: An interface containing the abstraction
of the data access layer for the querying of data or data
manipulation.
- ProductDataImpl.java: An implementation class of the
interface created above
- ProductDataTest.java: A test class demonstrating
annotated-method style
- ProductInlineSample.java: A test class demonstrating
inline-style
Figure 21.
Classes created for Product
This creates two classes for you in the package "querypackage" called
Product.java and ProductData.java. You do not need to modify any of
the generated files. The code should run as it is.
To read information from the Product table, you can run
ProductDataTest.java or ProductInlineSample.java.
You can also write a sample application similar to the following (using
your own connection URL where "xxx" is the username you are connecting
as and "yyy" is the password). The file is named GetProduct.java (code
details shown in Listing 2).
To run GetProduct.java, right-click the file, and click on Run as a
Java Application.
Listing 2. Retrieving data
package pureQuery.example;
import java.sql.*;
import java.util.Iterator;
import pureQueryCode.*;
import com.ibm.pdq.runtime.Data;
import com.ibm.pdq.runtime.factory.DataFactory;
public class GetProduct {
public static void main(String[] args)
{
try
{
Class.forName ("com.ibm.db2.jcc.DB2Driver");
Connection con = DriverManager.getConnection("jdbc:ids://" +
"localhost:9089/gsdb:" +
"DELIMIDENT=y;","xxx","yyy");
ProductData prodData =
DataFactory.getData( ProductData.class, con );
Product p;
// Uses the default SQL - select * from product
Iterator < Product > prodList = prodData.getProducts();
while (prodList.hasNext()){
p = prodList.next();
System.out.println("The product number is "
+ p.getBase_product_number() +
" and its image is titled " + p.getProduct_image() +
" and size code of: " + p.getProduct_size_code());
}
System.out.println("New List");
//Customizing the query executed
Data db = DataFactory.getData(con);
prodList = db.queryIterator("select * from gosales.product " +
"where product_number <4000",Product.class);
while (prodList.hasNext()){
p = prodList.next();
System.out.println("Product num " + p.getProduct_number()
+ "'s base product number is:"
+ p.getBase_product_number()
+ "is introduced on " + p.getIntroduction_date());
}
}
catch(Exception e)
{
System.out.println(e.getMessage());
}
}
}
|
The above example shows how easy it is for Java developers to create a
simple database application without having to write any SQL. You can
also see that the query can be customized by giving an explicit query
with the queryIterator method.
Heterogeneous
batch updates with IDS
In the release of Data Studio Developer 2.1, IBM Data Studio pureQuery
Runtime, Version 2.1 is included. pureQuery introduces Informix
support for heterogeneous batch updates, where a batch operation can
include several API calls and even span across multiple data access
objects. With heterogeneous batch operation, several tables can be
updated in a single network call. Heterogeneous batching is only
supported by IBM Data Server Driver for JDBC and SQLJ.
Copy and paste the code from Listing 3 to take
advantage of heterogeneous batch updates. The file is named
HeteroBatch.java. Again, you need to edit "xxx" for username and "yyy"
for password.
Listing 3. Heterogeneous batch code
package pureQuery.example;
import java.util.*;
import java.sql.*;
import com.ibm.pdq.runtime.*;
import com.ibm.pdq.runtime.factory.DataFactory;
public class HeteroBatch {
public static void main(String[] args) {
try
{
String url = "jdbc:ids://localhost:9089/gsdb:" +
"traceFile=C:\\temp\\myjcctrace.txt;traceLevel=-1;";
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
Connection con = DriverManager.getConnection(url,"xxx","yyy");
Data db = DataFactory.getData(con);
String strSQL1 = "UPDATE GOSALESCT.CUST_ORD_DETL"
+ " SET PROD_QTY = PROD_QTY + 1"
+ " WHERE ORD_DETL_CODE = 1089";
String strSQL2 = "UPDATE GOSALESCT.CUST_ORD"
+ " SET ORD_NBR_OF_ITEMS = ORD_NBR_OF_ITEMS + 1"
+ " WHERE ORD_NBR = 100012";
Map<String, Object> quantity = db.queryFirst(
" SELECT ORD_NBR_OF_ITEMS"
+ " FROM GOSALESCT.CUST_ORD"
+ " WHERE ORD_NBR = ?", 100012);
System.out
.println("QUANTITY OF ITEMS in CUST_ORD BEFORE UPDATE : "
+ quantity.get("ord_nbr_of_items"));
quantity = db.queryFirst("SELECT PROD_QTY"
+ " FROM GOSALESCT.CUST_ORD_DETL"
+ " WHERE ORD_DETL_CODE = ?", 1089);
System.out
.println("QUANTITY OF PRODUCT in CUST_ORD_DETL BEFORE UPDATE : "
+ quantity.get("prod_qty"));
((Data) db).startBatch(HeterogeneousBatchKind.
heterogeneousModify__);
Object[] empty = new Object[0];
db.update(strSQL1, empty);
db.update(strSQL2, empty);
((Data) db).endBatch();
db.commit();
quantity = db.queryFirst("SELECT ORD_NBR_OF_ITEMS"
+ " FROM GOSALESCT.CUST_ORD"
+ " WHERE ORD_NBR = ?", 100012);
System.out
.println("QUANTITY OF PRODUCT in CUST_ORD AFTER UPDATE : "
+ quantity.get("ord_nbr_of_items"));
quantity = db.queryFirst("SELECT PROD_QTY"
+ " FROM GOSALESCT.CUST_ORD_DETL"
+ " WHERE ORD_DETL_CODE = ?", 1089);
System.out
.println("QUANTITY OF ITEMS in CUST_ORD_DETL AFTER UPDATE : "
+ quantity.get("prod_qty"));
}
catch (SQLException e)
{
System.out.println("SQL Error in the application: " +
e.getErrorCode() + " " +
e.getMessage());
}
catch (Exception e)
{
System.out.println("Error in the application: " +
e.getMessage());
}
}
}
|
In the above code, both tables CUST_ORD and CUST_ORD_DETL are updated
in a heterogeneous batch.
Besides using UPDATE statements in
heterogeneous batch, you can use INSERT and
DELETE statements. Parameters are also
supported. For more information on using parameters in a heterogeneous
batch, go to the "Batch heterogeneous updates with parameter" section
of the Integrated Data Management Information Center (see
Resources).
Also, notice that the queryFirst method is
used. It is used to return the first row from the resultset. This
method should be used only if you wish to retrieve one row from the
resultset.
SQL Capture
with pureQuery
While developing your data access application, you can also view the
performance of the queries executed. In order to do this, you need to
run the application with pureQuery and enable SQL capturing.
If the SQL capturing is not enabled, right-click on the Java project
and select Properties.
Figure 22. Editing
pureQuery support
Select pureQuery on the left side. Check the box next to "Enable
SQL capturing and binding for JDBC applications", and click on
OK.
Figure 23. "Add
pureQuery support" wizard
(Click here to see a larger
image of Figure 23.)
Now you need to run HeteroBatch in pureQuery. Right-click on
HeteroBatch.java, and select Run Configurations...
under the "Run As" option.
Figure 24. Run
configuration
In the "Run Configurations" wizard, you need to expand pureQuery on the
left column, click on HeteroBatch, and run.
Figure 25. "Run
Configurations" wizard
(Click here to see a larger
image of Figure 25.)
Open pureQuery Outline and click on the Toggle Profile icon on
the upper right corner.
Expand the tables to see the performance results.
Figure 26. pureQuery
performance results
(Click here to see a larger
image of Figure 26.)
You will be able to see results in metrics, such as number of times
run, total time, maximum time, average time, and minimum time.
You can also view and edit the captured SQL statements. In the Package
Explorer, locate and open capture.pdqxml.
Figure 27.
capture.pdqxml location
Select an SQL statement and right-click on it to edit the
statement.
Figure 28. Editing an
SQL statement in capture.pdqxml
A window pops up asking you to edit the schema and path. Select a
schema and click on OK.
Figure 29. Editing
the schema and path
The new SQL statement will appear below the original one. Edit
UPDATE
GOSALESCT.CUST_ORD_DETL SET PROD_QTY = PROD_QTY + 1 WHERE
ORD_DETL_CODE = 1089
by replacing 1 with 3.
Figure 30. Editing an
SQL statement
Click on Save.
You now need to turn off the capture mode and set the
enableDynamicSQLReplacement property to
true in pdq.properties, which is located under pureQueryTutorial
project. (See Figure 31.)
Figure 31. Setting
enableDynamicSQLReplacement property to
true and the updated result
(Click here to see a larger
image of Figure 31.)
Click on Save again and rerun the application. Notice that the
PROD_QTY column is incremented by 3, instead of by 1.
This feature is useful if you want to edit an SQL statement to improve
performance, but cannot change the application code. This is typical
in the case of third-party applications or for applications where you
do not have the source code. You can view the performance metrics
again in order to determine if there is any performance change. For
more information on adding alternate SQL statements to pureQueryXML
files, see the Integrated Data Management Information Center (see
Resources).
pureQuery documentation can also be found in the Integrated Data
Management Information Center (see
Resources).
You can also check out a four-part video series on the new features in
Data Studio Developer 2.1, which talks about identifying SQL
statements that take the longest, changing SQL statements without
touching the code, improving productivity and collaboration among
developers and DBAs, enhanced pureQuery Outline, and eliminating SQL
injection risks (see Resources).
| 
