Increase productivity in Java database development with new IBM pureQuery tools, Part 1: Overview of pureQuery tools

10 unique scenarios, 10x productivity improvement

Whether you're a Java™ developer or always wanted to be one, you have come to the right place. Discover how IBM® pureQuery tools make Java programming with SQL more productive than ever before. Code SQL and Java applications in the Java editor, and be equally productive in both. Never leave your Java editor, and build error-free Java applications and SQL. Generate a simple data access layer with significantly less code than JDBC. Improve your application performance with static SQL, and use SQL to access database and in-memory collections.

Sonali Surange (ssurange@us.ibm.com), pureQuery Tools Lead, EMC

Sonali Surange photoSonali Surange leads IBM's pureQuery tools in the IBM's Data Server Tooling development organization at the IBM Silicon Valley Lab in San Jose, California. Prior to joining IBM's pureQuery, Sonali led the Visual Studio .Net tools effort for IBM Data Servers and IBM's SOA effort on .NET. Previously, Sonali led Informix SOA solutions and was lead developer on Object Relational mapping tools.


developerWorks Contributing author
        level

06 September 2007

Also available in Chinese

Introduction

In this series of articles, learn how Java application developers cut down on SQL coding errors when programming in Java language, and build sample high-performing Java applications and tests without writing a single line of code. Customize SQL inside Java applications to take the drudgery out of SQL programming using pureQuery tools such as SQL content assist, SQL validation, SQL execution, SQL open definition, and more.

What's New in Data Studio Developer

Data Studio Developer keeps adding new functionality so fast we can't keep up! Catch up with the latest by reading the "What's New in Data Studio Developer" series: Version 1.2 and Version 2.1 are both covered in detail.

This series will quickly get you up to speed on using pureQuery tools:

  • Use pureQuery productivity tools to rapidly build database applications, and customize SQL inside the Java perspective using pureQuery productivity tools
  • Make database application development easier than it has ever been using a single API to work with databases or in-memory data sources
  • Build applications with static SQL without any extra work
  • Use pureQuery design patterns in your applications and more

pureQuery tools and technology is available with in IBM Viper Developer V9.5.


Article objectives

Discover key pureQuery tools features, namely the SQL editor integration with Java technology and the pureQuery code generation. Create and customize sample pureQuery applications, and use pureQuery tools to re-use and migrate existing assets, such as SQL or beans, to create pureQuery applications without writing any code.


IBM pureQuery

pureQuery is a new, high-performance Java data access platform focused on simplifying the tasks of developing and managing applications that access data.

The benefits of using pureQuery extend throughout the development, deployment, management, and governance stages of the application life cycle.

pureQuery provides access to data in databases and in-memory Java objects with its tools, APIs, and runtime environment.


Why pureQuery tools

Drawbacks of existing solutions

Today, products such as Hibernate, DALI, and others allow you to build applications to access databases. However, these products depend on proprietary query languages such as HQL/ JPA's JQL and others, forcing you to learn another query language.

Such solutions hide the actual native SQL from the developers and DBAs since these queries transform themselves into the native query languages at runtime. An inherent problem with such solutions is that developers have less visibility to the efficiency of the generated SQL, thus making problem determination more difficult. Resulting applications built using such products are rigidly tied to a single vendor query language. Furthermore, often times the proprietary query languages are not sophisticated enough to handle more complex scenarios and queries.

All existing Eclipse-based products to date provide limited or no integration with the Java editor to help construct standard SQL inside Java applications. Tools such as Hibernate, DALI, and others provide primitive integration between the Java editor and their proprietary languages during application development. The drawback with these tools is that the integration is available only when coding with a proprietary query language.

Solutions in pureQuery

pureQuery solves these problems by allowing you to use the native database query language, standard SQL, to construct high-performing, simple database access layers and applications. You can use standard SQL not just for databases, but also for in-memory collections. pureQuery gives developers full control over the SQL in their application. The sophisticated SQL integration inside Java technology takes away the drudgery behind developing SQL inside Java applications.

pureQuery tools auto-generate the data access layer with suggestions for create, retrieve, update, and delete statements. Developers can focus on adding business logic and customizing auto-generated code using the highly flexible pureQuery tools.

This article provides an overview of pureQuery tools functionality and how you can improve your productivity, resulting in less cost in application development and maintenance.


How do pureQuery tools help?

IBM pureQuery provides several unique and patent-pending technology and tools for developing SQL inside Java applications. For the first time, you are able to use the same or better productivity features for SQL as you are used to when coding in Java language.

pureQuery enhancements are available in the standard Java editor in Eclipse, so you do not have to move to a new editor or learn a new tool.


pureQuery capabilities

With IBM pureQuery tools, you can:

  • Bridge the gap between data and Java technology, working seamlessly within the Eclipse environment
  • Rapidly develop or customize the SQL inside Java applications to your needs using the highly sophisticated SQL editor integration inside the Java editor
  • Rapidly build sample Java applications to access IBM databases without writing a single line of code
  • Auto-generate test applications and JUnits for the generated code
  • Have flexibility in application development to build pureQuery applications starting from database, SQL, or Java beans with highly integrated, rich, and easy-to-use tools
  • Rapidly build pureQuery applications to work with tables, views, aliases, or procedures
  • Easily use pureQuery APIs to build your applications with much less code in comparison with JDBC
  • Build applications to query in-memory collections and databases using one single API
  • Easily build DB2 applications for static SQL to greatly improve performance
  • Work with XML JPA format to keep all SQL in one location outside of your Java files
  • Build pureQuery applications on all IBM databases

This series will drill down into the extensive functionality set provided by each of these capabilities with the help of samples.


Platform support

IBM databases -– IBM DB2® for Linux®, UNIX® and Windows®, zSeries®, and iSeries®, as well as IBM Informix® Dynamic Server


Eclipse environment

  • Developer Workbench 9.5 open beta
  • Future upcoming support for shell sharing with IBM Ration® Application Developer and Rational Software Architect

Database explorer integration – Bridging the gap between data and Java technology

Typically, developers writing database applications in the Java language do not have access to the various connections defined in the database explorer view of the data perspective. The developer has to juggle between database tools and the Java programming tools to write their database queries and related code.

pureQuery tools provide seamless integration between the tools for Java technology and database.

Highlights – Add pureQuery support to a Java project

  • Inside the Java perspective, you can open the database explorer view from the data perspective.
Figure 1. Open database explorer in Java perspective
Open database explorer in Java perspective
  • Using pureQuery tools, you can now associate a Java project with a connection from the database explorer. To do so, add pureQuery support to a Java project.
    • To add pureQuery support, right-click on the Java project, and select Add pureQuery support.
    • Select an existing connection from the database explorer or create a new one.
Figure 2. Add pureQuery support
Add pureQuery support

Highlights – Database explorer connection customization recommendations

Customize the database explorer connection for improved productivity when using pureQuery:

  • Persist database password and chose to auto-reconnect on startup. pureQuery tools will use these settings to provide ease of use:
    1. Go to Windows > Preferences, and select Data.
    2. Select Password information > Persistence scope, and keep "Automatically reconnect connections on startup" checked. pureQuery SQL integration with Java will now use auto-reconnect connections as needed.
  • Work with offline, disconnected model of the database: It is often desirable to design applications with an offline model, especially when live remote servers may not always be available or may be slow to use at application design time. This feature can be useful for remote zSeries and iSeries servers with large number of objects:
    1. Right-click on database explorer connection to save connection offline. pureQuery SQL integration with Java technology will now work on disconnected model.
  • Schema or object filters on connections: pureQuery tools will use these settings when proposing content-assist lists, validation, and other SQL integration features. Setting these values allows you to work with schemas and objects of interest, and can boost performance when working with zSeries and iSeries servers that contain large number of objects.
  • Optionally set current schema. By default, the value will be the same as the user ID on the connection. pureQuery tools will use this schema to qualify all unqualified SQL.
    1. When adding pureQuery support, add the current schema value. (Alternately, modify the pureQuery settings under project properties to set the current schema value.)

SQL editor integration inside Java editor – Code SQL alongside Java language

pureQuery's unique and patent-pending features for SQL integration with Java technology provide developers a tremendous boost in productivity when developing SQL inside Java applications. You can create new SQL or customize existing SQL using pureQuery tools.

Highlights of this integration include:

Ability to use SQL semantic and syntactic validation for SQL inside Java editor

SQL errors are reported while typing SQL inside the Java editor:

  • Errors in queries embedded inside your Java program are shown the same way Java language errors are shown:
Figure 3. SQL Error inside Java program
SQL Error inside Java program
  • View SQL errors in the problems pane.
  • View error markers on Java line numbers with invalid SQL.
  • View error squiggles (or default eclipse preference for errors) under invalid SQL in your Java program.
  • Hover on errors to get detailed information.
  • Errors in SQL keywords (syntactic) and schema, table, column, or other database names (semantic) errors are detected.
  • Detect multiple errors in an SQL statement.
Figure 4. SQL error in problems pane inside Java program
SQL error in problems pane inside Java program

Ability to use SQL semantic and syntactic content assist for SQL inside Java editor

Press the Ctrl key and space bar (or your default content assist keystroke) to get SQL content assist and never manually type SQL again inside Java editor:

  • Discover and select SQL keywords.
  • Discover and select from a list of schemas based on the database associated with the project. Any schema filters associated with the connection will affect the list of items shown.
  • Discover and select from the list of table and column names.
  • Use content assist to discover columns for table aliases.
  • Content-assist proposals show database types and primary keys.
  • Easy-to-distinguish icons for SQL and database schema-related proposals.
Figure 5. SQL content assist inside Java program
SQL content assist inside Java program
  • SQL content assist integrated with bean properties for pureQuery syntax

This article discusses the pureQuery bean syntax in the "Build pureQuery applications – Rapid application development" section of this article.

Figure 6. Java bean content assist for SQL pureQuery syntax
Java bean content assist for SQL pureQuery syntax

Ability to run SQL inside Java program

Run SQL with parameters at design time in Java program without writing any test application:

  1. Right-click on SQL inside Java program, provide parameters for dynamic parameters or pureQuery bean syntax. pureQuery tools remember bean variable parameter values for the IDE session.
  • View results in the data output pane.
  • View messages and parameter values for input and output in the data output pane.
Figure 7. Run SQL inside Java program
Run SQL inside Java program

Ability to provide SQL syntax highlighting

Distinguishing your SQL inside the Java program by highlighting it makes looking at complex programs easy:

  1. Highlight SQL in your Java program.
  2. Select your favorite display option using pureQuery customization for this option. By default, the SQL is surrounded with a light yellow box:
Figure 8. SQL highlighting inside Java program –- light yellow box around SQL
SQL highlighting inside Java program

Open definition in database explorer for SQL inside Java program

Analogous to Java language open declaration, see definition of tables and columns in an SQL inside Java program:

  • View one or more tables that the SQL represents in database explorer, when positioned on the SQL.
  • View column for a select statement in database explorer, when positioned on the column.
Figure 9. Show in database explorer
Show in database explorer

SQL template support in Java program

Re-use favorite templates from the SQL editor to generate commonly needed queries inside Java program:

  • View and select favorite SQL templates when using SQL content assist in Java.
  • Customize SQL templates using SQL editor template editing functionality.
Figure 10. SQL templates inside Java
SQL templates inside Java

Visual explain in Java program

View explain plans for SQL inside Java program.

Use pureQuery's editor integration in your program

pureQuery's SQL integration is available in the standard Java editor in Eclipse.

By default, the SQL editing functionality is enabled on strings that are used within pureQuery APIs.

In addition, you can use it on any strings of your choice and in your applications containing SQL.

Enable pureQuery editor support in existing or new Java projects with SQL

To leverage run SQL, show in database explorer, and generate SQL bean functionality (refer to the "Build pureQuery applications – Rapid application development" section for the SQL bean functionality) on any string in your project, follow these simple steps:

  1. Add pureQuery support to your Java project. This associates a connection with your Java project.
  2. Ensure your previously opened Java files are reopened after adding pureQuery support to see the enabled pureQuery right-click menus.

To leverage SQL content assist, SQL validation, and SQL templates functionality on any strings of your choice in your application, additionally follow the steps below:

  1. Annotate the declaration of the string on which SQL editing is to be enabled using the @Sql command.
  2. Import the definition of @Sql using keystroke combination Ctrl+Shift+O (or your default keystroke). The definition from pureQuery runtime will be imported:

    import com.ibm.pdq.annotation.Sql; @Sql String mySQL = "select * from ssurange.department"

Any assignment to the string mySQL now benefits from pureQuery editor features.


Build pureQuery applications – Rapid application development

Using pureQuery tools, you can build pureQuery starter applications without writing any code. You can then productively customize the SQL in the generated applications, or customize or add new pureQuery APIs to these applications to satisfy your application development requirements.

When to use what – pureQuery tools features

  1. Have database and want to generate data access code?
    Use pureQuery bottom up scenario from database.
  2. Have SQL, and want to re-use it and generate data access code?
    Use pureQuery bottom up scenario from SQL.
  3. Have database and Java beans, and want to map beans and bean fields to database tables and columns to subsequently generate data access code?

    Use pureQuery meet-in-the-middle scenario.
  4. Have database and mapped Java beans, and generate data access code?
    Use pureQuery bottom up from beans scenario.
  5. Have beans and want to generate database schema?
    Use top down scenario.
  6. Prefer to have all bean annotations and application SQL in one XML file?
    Use pureQuery XML scenario.
  7. Need data access code and better performance, but do not need more development work?
    Use pureQuery static SQL scenario.
  8. Need to work with database and in-memory data, and do not want to learn new APIs?
    Use pureQuery query over collections support.

pureQuery bottom up scenario from database

Application developers typically start out developing an application by:

  1. Starting with an example and customizing it to their needs
  2. Using tools to generate stubs and filling in the data access code manually, or
  3. Hand-coding the whole application

pureQuery tools provide a productivity boost towards all of the above scenarios.

pureQuery code generation

Using pureQuery tools, you can generate pureQuery data access code for tables, views, and procedures.

pureQuery tools provide suggestions for create, retrieve, update, and delete SQL statements, demonstrating the ease of use towards developing using pureQuery.

You can generate pureQuery inline-method style- or annotated-method style-based applications:

  • Use inline-method style to create applications with SQL inside your application for similar look and feel as JDBC.
  • Use annotated-method style to keep your SQL associated with the interface and separated from your application using the interface.

You can easily customize the application by adding new pureQuery methods, interfaces, and SQL, or changing existing methods, interfaces, and SQL

Additionally, you can auto-generate sample programs or JUnit test cases that use these generated APIs and SQL, and run them without writing a single line of code.

pureQuery bottom up scenario from database – Tables, views

pureQuery provides the ability to generate pureQuery code to access and manipulate data from tables, views, and nicknames. pureQuery APIs use beans to hold the result of a select statement. Using pureQuery APIs, you can provide input parameter values for select, insert, update, and delete statements using bean instances. This greatly simplifies the data access code. pureQuery tools generate code for all the beans, the SQL statements, and the pureQuery APIs that use them.

  • Right-click on a table in the database explorer, and select Generate pureQuery code.
Figure 11. Generate pureQuery code from a table
Generate pureQuery code from a table

Highlights of bean generation

You can auto-generate bean representing table or view. If you need to comply with Java coding standards:

  1. Change Java bean name, change bean field names as required, and get mapping to database automatically generated
  2. Generate public fields or protected fields with public getter, setters
  3. Provide super classes as applicable
  4. If you need custom Java type mapping, you can simply change default type mappings for bean fields in wizard, as shown in Figure 12. Standard JDBC type conversion rules will apply at application execution time.
Figure 12. Bean generation options
Figure 12. Bean generation options

Highlights of pureQuery data access code generation

pureQuery syntax with variables representing bean fields greatly reduces lines of code and complexity for select, update, insert, and delete statements, where multiple parameters can be represented using one bean parameter.

pureQuery provides great ease of use by providing query APIs that return results as iterators of beans holding the result, eliminating manual result set handling to fetch data of interest.

  • You can use pureQuery's queryFirst API to easily get one row instead of all rows.
  • The generated code has suggestions for using pureQuery APIs for select, insert, update, and delete statements. Primary keys are used in the generated SQL to construct where clauses where applicable. You can also generate code for tables with auto-generated keys.
  • You can generate inline-method style or annotated-method style based on your application requirements.

If you need to comply with SQL coding standards, you can:

  • Use preferences to use * or all columns in selects
  • Use preferences to generate only a few of the suggested method suggestions when using the annotated-method style

Additionally, you can merge new methods into existing annotated-method style interfaces.

Figure 13. Code generation preferences
Code generation preferences
Listing 1. Generated code –- Bean (Refer to comments in code for code analysis)
/**************************************************************
 *A bean that represents the PROJECT table.
 *
 **************************************************************/
import com.ibm.pdq.annotation.Table;
import com.ibm.pdq.annotation.Id;
import com.ibm.pdq.annotation.Column;
import java.math.BigDecimal;
import java.sql.Date;

//  Auto generated mapping for bean to table name
@Table(name="PROJECT", schema="SSURANGE")
public class MyProject  {

// Class variables
@Id
public String projno;
// Auto generated mapping for field to column name
@Column(name="PROJNAME") public String myProjname;
public String deptno;
public String respemp;
public BigDecimal prstaff;
public Date prstdate;
public Date prendate;
public String majproj;
 

}
Listing 2. Generated code -- Sample Inline-method style
// create and use standard jdbc connection for conn
Data db = DataFactory.getData (conn);
db.setAutoCommit(false);
// execute the select statement and return results in an iterator of MyProject bean
// MyProject bean represents the result
Iterator<MyProject> getMyProjects = db.queryIterator ("select DEPTNO, MAJPROJ,
PROJNAME, PRENDATE, PROJNO, PRSTAFF, PRSTDATE, RESPEMP from SSURANGE.PROJECT",
MyProject.class);
// get the bean representing the first row to use to test update, delete and insert.
// The changes are rolled back if there is any error
MyProject bean = null;
if (getMyProjects.hasNext()) {
bean = getMyProjects.next();
} else {
SampleUtil.printLn("Result set is empty.")
db.rollback();
return;
}

// Suggestion for query using primary key and bean parameter. This API returns one
// bean.
// Notice that the parameter information is provided via a bean instance.
// pureQuery also supports standard dynamic parameters  ( ie ?)
MyProject getMyProject = db.queryFirst ("select DEPTNO, MAJPROJ, PROJNAME, PRENDATE,
PROJNO, PRSTAFF, PRSTDATE, RESPEMP from SSURANGE.PROJECT where PROJNO = :projno",
MyProject.class, bean);
SampleUtil.printClass(getMyProject);

// Suggestion for query update statement. Provide all values to set in bean fields!
db.update("update SSURANGE.PROJECT set DEPTNO = :deptno, MAJPROJ = :majproj,
PROJNAME = :myProjname, PRENDATE = :prendate, PROJNO = :projno, PRSTAFF = :prstaff,
PRSTDATE = :prstdate, RESPEMP = :respemp where PROJNO = :projno", bean);
getMyProjects = db.queryIterator ("select * from SSURANGE.PROJECT", MyProject.class);
SampleUtil.printLn("Results for update (bean)");
SampleUtil.printAll(getMyProjects);

// Suggestion for query delete statement. Provide all values to set in bean fields!
db.update("delete from SSURANGE.PROJECT where PROJNO = :projno", bean);
getMyProjects = db.queryIterator ("select * from SSURANGE.PROJECT", MyProject.class);
SampleUtil.printLn("Results for - delete (bean)");
SampleUtil.printAll(getMyProjects);

// Suggestion for query insert statement. Provide all values to set in bean fields!

db.update("insert into SSURANGE.PROJECT (DEPTNO, MAJPROJ, PROJNAME, PRENDATE,
PROJNO, PRSTAFF, PRSTDATE, RESPEMP) values( :deptno, :majproj, :myProjname,
:prendate, :projno, :prstaff, :prstdate, :respemp)", bean);
getMyProjects = db.queryIterator ("select * from SSURANGE.PROJECT", MyProject.class);
SampleUtil.printLn("Results for - insert (bean)");
SampleUtil.printAll(getMyProjects);

pureQuery bottom up scenario from database - Procedures

pureQuery tools provide the ability to generate pureQuery code to call a procedure. pureQuery APIs use beans to represent parameters and beans to hold the result set(s). pureQuery tools generate code for all the beans, the call statement, and pureQuery APIs to execute the call statement.

To generate code for a call statement of a procedure, simply right-click on a procedure in the database explorer, and select Generate pureQuery Code. Then follow the wizard steps.

pureQuery tools generate beans to hold parameter values, if any parameters exist (input, output, or in out). pureQuery APIs use the bean to accept input parameters or return values for input and in out parameters.

You can optionally generate beans for each result set returned. pureQuery procedure code generation wizard provides options to run the procedure to discover and propose the result bean shape. You can customize the bean to have super classes, public or protected fields with public getters and setters, or change the default Java type mapping.

Note: A future release will allow you to define the result set shapes without running the procedure.

Figure 14. Result set bean generation
Result set bean generation

Highlights of pureQuery data access code generation

Refer to Listing 3 (pureQuery with result set beans) for an example of pureQuery APIs returning results of a call statement in result beans.

Refer to Listing 6 (pureQuery with raw JDBC resultsets) for an example of pureQuery APIs returning results as raw JDBC result sets. Note that you can work with raw JDBC result sets if you choose not to run the procedure to discover result set bean shape(s).

Inline-method or annotated-method style programs can be generated.

Sample Inline-method style:

Listing 3. pureQuery with result set beans
// Get jdbc connection
Data db = DataFactory.getData (conn);

// Initialize parameter bean
Procedure1Param parms = new Procedure1Param();
// set parameter values from arguments to the program.
setParms(parms, args);

// Call the procedure using pureQuery API
// Note that all parameters are represented in the call statement
// Note that parameter bean is passed in as a parameter
StoredProcedureResult spResult =
db.call("Call SSURANGE.PROCEDURE1( :param1, :param2 )", parms );

// Retrieve first result set into the corresponding result bean
Iterator<ResultBean1> resultSet1 = spResult.getIterator(ResultBean.class);

// Print results
SampleUtil.printAll(resultSet1);

// Retrieve second result set into the corresponding result bean
Iterator<ResultBean2> resultSet2 = spResult.getIterator(ResultBean.class);

// Print results
SampleUtil.printAll(resultSet2);
Listing 4. Auto-generated parameter bean
public class Procedure1Param  {

// Class variables
public String param1;
public String param2;

}
Listing 5. Auto-generated result set bean to hold the first result set
public class ResultBean1  {

// Class variables
public String routinename;
public String routineschema;
}

Similar bean is generated to hold the second result set:

Listing 6. pureQuery with raw JDBC resultsets
// If you choose not to run the procedure to discover the result set shape used to
// define the result bean, pureQuery APIs allow you to work with the JDBC result set

// Get jdbc connection
Data db = DataFactory.getData (conn);

// Initialize parameter bean
Procedure1Param parms = new Procedure1Param();
setParms(parms, args);

// call the procedure using pureQuery API
StoredProcedureResult spResult =
db.call("Call SSURANGE.PROCEDURE1( :param1, :param2 )", parms );

// Process the result sets
boolean results = spResult.hasResultSets();
while ( results){
ResultSet rs = spResult.hasResultSet();
SampleUtil.dumpResultSet(rs);
Results = spResult.moveToNext();
}

pureQuery bottom up from SQL

pureQuery tools make it easy to re-use existing assets, such as SQL, towards building pureQuery applications. Using this feature, you can migrate your existing JDBC or other database applications containing SQL easily.

pureQuery tools provide flexible and easy integration within the Java editor to achieve this. You can create a bean to hold the results of an SQL statement and use simple pureQuery APIs to execute your SQL and return results.

Using pureQuery tools, you can follow the simple steps listed below:

  1. Add pureQuery support to your Java project
  2. Open/ re-open the Java file containing the SQL, right-click on the SQL, and select Generate SQL bean from the pureQuery assist menu, which generates a bean to hold the results of the SQL. Your auto-generated pureQuery application can now be customized to use the beans as results.
Listing 7. Generated code - Sample inline-method style
// change the generated code to represent your SQL and beans.
Iterator<MyBean> getMyProjects = db.queryIterator ("your new SQL", MyBean.class);
Figure 15. Generate bean from SQL
Generate bean from SQL

pureQuery meet-in-the-middle

Re-use and migrate existing beans and database schema to pureQuery

pureQuery tools make it easy to re-use existing beans and database schemas to build pureQuery applications. You can easily map beans and fields to database tables and columns using the flexible and easy-to-use content assist on pureQuery annotations for table, schema, and columns.

Once the mapping is complete, you can view the mapped table or column using the "show in database explorer" feature to ensure your mapping is correct and complete.

pureQuery tools provide an easy way to map existing beans to database schema definition. With a combination of pureQuery annotations, content assist within the annotations and Java quick fixes, you can do the mapping with no coding.

Follow these simple steps:

  1. Add pureQuery support to your project.
  2. Open the Java bean.
  3. Map the bean to a table by adding the @Table annotation on the class.
  • The annotations are defined in com.ibm.pdq.annotation package and you can use Ctrl + shift + O keystroke, or your default keystroke, to import the required import statements into the bean file.
  • Use Ctrl + space on the name attribute to get content assist for tables and schemas. Similarly, use @Column annotation to map a bean field to a database column. Use Ctrl + space on the name attribute to get column content assist.
Figure 16. Schema content assist
Figure 16. Schema content assist
Figure 17. Table content assist
Table content assist
Figure 18. Column content assist
Column content assist

View mapped beans in database explorer

You can open the definition of the table or column that your bean or field represents in the database explorer:

  1. Place you cursor on any bean field.
  2. Using the pureQuery assist menu, select Show in Database Explorer. The column definition representing the bean field will be shown in the Database Explorer.

    Alternately, placing the cursor anywhere in the bean file and using this menu will open the table representing this bean in the Database Explorer.
Figure 19. Show beans in database explorer
Figure 19. Show beans in database explorer

pureQuery bottom up from beans scenario

Using pureQuery tools, you can re-use and migrate beans mapped to the database to generate pureQuery data access layer.

Generate pureQuery code from mapped beans using generate pureQuery code action. You can create all pureQuery styles and sample applications or JUnit test cases, similar to the bottom up scenario.

Figure 20. Generate pureQuery code from mapped beans
Generate pureQuery code from mapped beans

pureQuery top down scenario

Re-use existing beans and generate relational schema

You can get SQL script proposal to create the database object that represents a bean using pureQuery tools. The DDL script representing the bean is shown in the console window, which you can then modify and run in your SQL editor.

Easy integration within Java editor is available to achieve this.

  1. Right-click on bean, and select Generate DDL.
  2. View proposed DDL in the console window.

pureQuery XML scenario

Export application information into an XML format, which is a subset of the XML JPA specification. You can use pureQuery tools to export the SQL in your annotated method style program and the bean to database mapping information. As a result, you can manage this information in one XML.

You can set the pureQuery generator property to point to your XML file. The implementation for the annotated method interface now uses SQL and bean mappings from your XML.

Easy integration with the Java editor.

  1. Right-click on bean or interface, and select Generate XML.
  2. Right-click on the Java project, select pureQuery, and in the generator section, add:
    • xmlFile – yourFile within the eclipse workspace

pureQuery static SQL scenario

Boost application performance with no extra work

Static SQL provides a performance boost when running your applications.

pureQuery makes working with Static SQL extremely easy. The choice to run statically or dynamically is now made at execution time and without affecting the application development experience. As a result, developer do not need to learn new tools or programming paradigms for their applications to reap the benefits of static SQL.

pureQuery's static SQL integration provides a great productivity boost over existing SQLJ solutions.

To see static SQL with pureQuery in action, view the attached screen capture video, pureQuery_staticSQL.zip, (see Downloads).

To bind an application, right-click on the Java project, and select Bind pureQuery Application menu.

Messages on the console will indicate that the bind process succeeded. The connection associated with the project is used to perform the bind.

To bind your application to a different database, simply modify the connection associated with the project, and bind the application again using the above step. Right-click on the project, select Properties, and under pureQuery, change the associated connection.

Figure 21. Bind pureQuery application
Bind pureQuery application

To unbind, clean the project using the top level menu Project > Clean.

Static SQL is supported only for DB2 databases. Static SQL is also supported only for annotated-method style. Future releases will provide static SQL integration into inline-method style.

Copy the pdq.properties file attached with this article (see Downloads) into the source folder of your project. By default this is the src folder. The pdq.properties file is also included in Listing 8 below.

At runtime, you have a choice to run the application statically or dynamically.

  • To run statically, keep pdq.executionMode=STATIC
  • To run dynamically, remove pdq.executionMode=STATIC
Listing 8. Pdq.properties file
#Below data zero properties can be overridden by supplying the system properties
as input to the JVM:
#
#-Dpdq.config.file=filename //a file on file system.
#or
#-Dpdq.config.resource=filename //a file on classpath
(default is /com/ibm/zero/data/runtime/dzero.properties)
#or
#-DIndividualPropertyName=Value
#
#If both pdq.config.file and pdq.config.resource are specified,
pdq.config.file has precedence.


#Name of datazero log file. Can be absolute or relative path.
pdq.log.file.name=c:\\temp\\pdq.log

#detail level of messages written to the log file.
#Supported Levels are those supported by java.util.logging APIs:
# OFF - Logging turned off.
# SEVERE - Least detail, only severe problems are logged.
# WARNING
# INFO
# CONFIG
# FINE
# FINER
# FINEST - very detailed log.
# ALL - All levels will be logged.
#
pdq.log.file.level=ALL

#detail level of messages written to the console.
pdq.log.console.level=OFF

#Switch to buffer logs in memory and only write them on error.
#When non-zero, this will avoid the performance overhead of disk I/O of
#writing excessive logs, but will still provide some history around
#the vicinity of error conditions.
#Could be set to 1000 to buffer 1000 log records for example.
pdq.log.lazy.buffer=0

#Data Zero runtime execution log. STATIC or DYNAMIC.
#STATIC is only supported by DB2 databases.
pdq.executionMode=STATIC

pureQuery Query over collections

pureQuery now allows you to use standard SQL to query information sources such as in-memory collections, making your code simple and consistent when working with varied data sources.

In Listing 9, below, notice how employees are retrieved from the database and the results are joined with departments from a collection in memory.

Sample pureQuery code to join results of a database query with an in-memory collection are shown in Listing 10.

Listing 9. Inline-method style
// get jdbc connection

// get employees from the database
Datadb = DataFactory.getData (conn);
Iterator<Employee> getEmployees = db.queryIterator ("select BIRTHDATE, BONUS, COMM,
EDLEVEL, EMPNO, FIRSTNME, HIREDATE, JOB, LASTNAME, MIDINIT, PHONENO, SALARY, SEX,
WORKDEPT from SSURANGE.EMPLOYEE", Employee.class);

// create in memory array of departments
ArrayList depts = new ArrayList();
Department d = new Department();
d.deptno = "A00";
d.deptname = "SPIFFY COMPUTER SERVICE DIV.";
depts.add(d);

   Data qocData = DataFactory.getData();
   
// call the pureQuery API to join employees from the database with in-memory
// collection of departments
// The first parameter is the SQL like API to use for join
// The second parameter represents bean class to hold the result
// The third parameter defines the Employee bean
// The fourth parameter defines the Department class
   Iterator<InMemResult> result = qocData.queryIterator (
    "select e.FIRSTNME, d.deptname from ?1.com.ss.Employee as e, 
    ?2.com.ss.Department as d where e.workdept=d.deptno",
   InMemResult.class, getEmployees, depts );
   // print result
   SampleUtil.printAll(result);
Listing 10. Bean holding the result
package com.inmem;
    

/**************************************************************
 *A bean that represents the result of join between in-memory collection of
 * departments with employees from the database
 *
 **************************************************************/
import com.ibm.pdq.annotation.Table;
import com.ibm.pdq.annotation.Id;
import java.sql.Date;
import java.math.BigDecimal;



public class InMemResult  {

// Class variables
@Id
public String deptname;
public String firstnme;


}

Stay tuned for more details in a future article in this series.


10x productivity improvement

Before the existence of pureQuery tools, developers had to juggle between different tools for Java technology and SQL to accomplish their database application development tasks. This process is time consuming and error prone, thus resulting in high application development cost.

This article has looked at the highlights of pureQuery tools, discussing more than 10 unique features that greatly improve developer productivity and provide SQL integration into Java technology. This article also looked at pureQuery tools for rapid database application development.

In summary, this article discussed the following features:

  1. Add pureQuery support
  2. SQL validation
  3. SQL content assist
  4. Run SQL
  5. SQL syntax highlighting and open definition in database explorer
  6. pureQuery bottom up scenario from database
  7. pureQuery bottom up scenario from SQL
  8. pureQuery meet-in-the-middle scenario
  9. pureQuery static SQL scenario
  10. pureQuery query over collections scenario

The remaining articles in this series drill down into each feature and look at how each feature in turn provides 10x productivity improvement.


Summary and a peek into the future

pureQuery can completely turn your Java application development experience for databases from drudgery to highly productive.

Table 1 summarizes the feature support per pureQuery style :

Table 1. Feature support per pureQuery style
FeatureAnnotated-method styleInline-method style
pureQuery static SQLYesNo
pureQuery XML scenarioYesNo
Code generation merge newYesNo
All other featuresYesYes

Table 2 summarizes the feature support per database platform:

Table 2. Feature support per database platform
FeatureDB2IDSzSeriesiSeries
pureQuery static SQL YesNoYesYes
All other featuresYesYesYesYes

In the next article in this series, learn in depth about how pureQuery validates SQL and see various examples of how pureQuery reports SQL errors as you type inside the Java editor.

Then stay tuned for the following Part 3, which focuses on pureQuery rapid application development.


Acknowledgements

The pureQuery team in Silicon Valley Lab, San Jose, California

Technology, architecture, and leadership from Stephen Brodsky

Leadership from Steven Sit, Dinesh Nirmal, Audrey Wang, and Swaminathan Gounder

Tools development – Becky Nin, Azadeh Ahadian

Tools quality assurance – Shahneela Rahman, Mark Hager, and Mi Wan

Information Development – Robert Heath, Ellen Livingood

Runtime – Bill Bireley, Anshul Dawra, Daya Vivek, Suavi Demir, Jaijeet Chakravorty, Delmar Blevins, Christopher M Ferrar, Heather Lamb, and Christina Davis

Runtime Quality Assurance – Brandon Wirick, Victoria Rabern

Special thanks to Audrey, Robert, Becky, Mario Ds Briggs, and Rafael Coss for reviewing this article.


Downloads

DescriptionNameSize
SQL with pureQuery screen capture videoJLinQ_staticSQL.zip7MB
Sample pdq.properties filepdq.zip1KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Java technology, Open source
ArticleID=253736
ArticleTitle=Increase productivity in Java database development with new IBM pureQuery tools, Part 1: Overview of pureQuery tools
publish-date=09062007