Level: Intermediate
Azadeh Ahadian (azadeh@us.ibm.com), Eclipse Application Development Tools , IBM
16 Aug 2007
The project code named Java™ Language Integrated Query (JLINQ) gives database application developers an easy, GUI-based means to significantly increase productivity in both the design and implementation phases. This is accomplished through user-initiated automatic transformation of relational data into Java objects for access and manipulation of data. In turn, these objects can be seamlessly utilized in a natural object-oriented (OO) programming paradigm to write the business logic and the underlying code. JLINQ's functionality in effect eliminates traditional JDBC programming by integrating the query language with Java itself.
Introduction
The objectization of relational data access
Most businesses are built on information management systems whose main underlying infrastructures are based on data stored in one or more relational databases. These systems consist of software programs whose sole purpose is information processing and are implemented in managed languages such as Java.
As an OO-based language characterized by ease of use for encapsulation, inheritance and polymorphism, Java is used on the one hand to implement the computational and logical aspects of information processing programs, and on the other hand to access and manipulate relational data. Java defines information in terms of objects, while relational databases use rows. Objects are unique instances of the class to which they belong, and hence they are physically distinct from one another. Similarly, relational database rows are also unique, so long as they are identified by primary key values. On the other hand, objects do stand alone and continue to exist so long as they are still referenced by one or more other objects. Rows however exist as elements of tables, and are eliminated as soon as they are removed.
Therefore, while objects and rows are similar in many aspects, the intrinsic value of objects to modular programming in the interest of scalability, extensibility, and efficient code maintenance has been the basis for design fundamentals to abstract the relational row layer through objectization. We define objectization as the process of bringing OO's fundamental concepts of encapsulation, inheritance, and polymorphism to an otherwise intensive instructional, in-line data query practice performed using SQL. In short, objectization is the process of creating seamless objects with methods capable of performing database operations (including create, replace, update and delete), in a pure OO paradigm.
The most popular way to objectize to programmatically access and manipulate relational data has been through special APIs and wrappers that provide one or more SQL statements written as text strings. The entire relational data processing is based on these encapsulated SQL statements, and they are significant portions of the program logic. However these SQL statements are not transparent to the Java language and therefore are unable to benefit from compile-time optimization and semantic and syntax verification -- not to mention being left out of design-time features such as content assist and IntelliSense that collectively provide tremendous time saving assistance in the underlying design and implementation process.
Hence, one of the main challenges to increasing programmer efficiency and productivity has been the transformation of relational data into objects that can be used seamlessly, in a natural OO programming fashion, for writing the underlying code. This process has required having a good understanding of how to sequence the data access API calls, and having a good technical understanding of the underlying data access programming model (for example, JDBC), in addition to having good coding skills and the ability to break down the software (and in particular its data access layer) into modularized segments.
The project code named Java Language Integrated Query (JLINQ) gives database application developers an easy, GUI-based means to significantly increase productivity in both the design and implementation phases. This is accomplished through user-initiated automatic transformation of relational data into Java objects for access and manipulation of data. In turn, these objects can be seamlessly utilized in a natural OO programming paradigm to write the business logic and the underlying code. JLINQ's functionality in effect eliminates traditional JDBC programming by integrating the query language with Java.
This article provides an overview of JLINQ and includes a step-by-step scenario showing how JLINQ can significantly increase programmer productivity.
JLINQ: What is it?
JLINQ is an Eclipse-based, pluggable component that seamlessly integrates with the Eclipse Integrated Development Environment (IDE). It automatically provides the design and runtime means for managing relational data as objects. The design provides the means for the developer to specify a database connection (using for example DB2®), thus enabling the Eclipse Java project to be aware of the underlying database artifacts (such as tables, stored procedures, and so on). Once the connection and the auto discovery of all database artifacts are completed, users can intuitively, through the GUI , transform any one of the underlying relational table entities into a Java object. This is accomplished through auto generation of the relevant SQL statements and auto creation of the parent Java objects that encapsulate those statements. The generated Java objects and the contained SQL statements can be further customized in a natural Eclipse IDE programming environment with full integrated SQL and Java editing capabilities.
The tight integration of SQL inside the Java editor provides the required programming productivity increase and the same seamless design time experience and ease of use for SQL coding as writing Java code. The editor integration includes fully supported semantic and syntactic validation through design time compilation, content assist, IntelliSense, syntax colorization, and dynamic and selective SQL execution inside Java where errors are marked in an identical manner as Java errors in the error pane.
Furthermore the tightly integrated hand shaking that takes place between design and runtime makes it possible to run SQL statements at design time. taking into account any host variables and parameters used, to preview result sets, and to auto-rollback any side effects that the execution of SQL statements may cause.
Additionally performance analysis of selected SQL statements at design time can be performed through the launch of Visual Explain, thus providing the graphical representation of the optimizer implementation of a query request. The query request is broken down into individual components with icons representing each unique component. Visual Explain also includes information on the database objects considered and chosen by the query optimizer. Visual Explain's detailed representation of the query implementation makes it easier to understand where the greatest cost is being incurred, all at design time, from inside the integrated SQL-Eclipse Java editor.
JLINQ objectization process
The JLINQ system provides three distinct ways to auto-transform the relational access layer into an object based model.
- Database-driven objectization (DDO):
The most common usage of JLINQ is to select a table and to generate the objects for data access and manipulation, otherwise referred to as CRUD (create, replace, update, delete). This is where JLINQ generates the OO-based classes and interfaces required to perform the CRUD operations on the selected tables. It also generates a special class that can be used to unit test the objects created. We will discuss this more in detail in the section A quick tour.
- Query-driven objectization (QDO):
It is often the case that the process of building an application originates from having a set of database query statements (such as SQL, XQuery, or a combination of both). This is when object hierarchies need to be created based on existing query statements, which in turn can be used to build the underlying application. The JLINQ system, through a simple point-and-click selection of a query statement and in an intuitive GUI manner via a pop-up context menu, can easily be asked to generate the relevant object class hierarchies. The system in essence utilizes the metadata obtained as a result of parsing the query statements (and if applicable through metadata it maintains from its underlying database connection) to auto-generate the required object classes.
- Object-relational mapping (ORM):
There are instances when a bean class that is meant to represent a relational table is available, but the actual link between that class and the desired table in the database does not exist. JLINQ provides an intuitive mechanism through its rich content assist infrastructure to perform a one-to-one mapping of the class name to a given table name residing in the underlying database, as well as the ability to map the class's protected (or public) variables to that table's columns.
There are also instances when a given bean class with all the required relational mapping meta-data exists, but the underlying physical tables to which it is mapped are missing. JLINQ provides the intuitive UI means to derive and to generate the required DDL statements from the bean class for the creation of the required underlying database artifacts (that is, tables and columns).
A quick tour
The following sections examine the most common usage of JLINQ through the DDO process, followed by a tour of the QDO and ORM processes as described earlier in the section JLINQ objectization process. Finally an overview of JLINQ's tight integration with the Eclipse Java Editor and the added-value features it offers will be provided. This will include the hand-shaking between the design and runtime for test running of SQL statements.
Before you try the scenarios below, make sure you have installed the following:
- DB2 for Linux®, UNIX®, and Windows® Version 8.0 or later and the SAMPLE database that comes with the installation
- Developer Workbench Version 9.5
- JLINQ plug-in Version 1.0
Links to download DB2 and IBM Viper Developer V9.5 Open Beta (source for the Developer Workbench and the JLINQ plug-in) are located in the Resources section of this article.
A database-driven objectization (DDO) case scenario
In this section, we examine a simple employee personnel scenario where the goal is to programmatically, in an OO paradigm, access and manipulate the employee data.
In the following steps, you create a JLINQ-aware Java project that transforms the Employee table in the DB2 SAMPLE database into an object model that in turn can be used in a natural, object-oriented fashion in any Java application.
The design pattern for the development of database applications using JLINQ consists of the following basic steps:
Step 1. Create a Java Project in the Eclipse IDE
When creating the Java project, make sure the source and bin folders are created. Name the project "Personnel." .
Step 2. Make your Personnel Java project JLINQ enabled
- Select with your right mouse button the Personnel Java project and from the menu select Add JLINQ Support.
Figure 1. JLINQ enable your Eclipse Java project
This launches the Add JLINQ Support wizard that you use to enable your Java project for JLINQ.
- The first page of the wizard consists of general options. You can leave the default values as they are. Most importantly, make sure the checkbox next to Import required JLINQ JAR files into project is checked. This is because JLINQ has its own specific design and runtime JAR files that must be included with your Java project.
Figure 2. Add JLINQ Support wizard: JLINQ general option page
Click Next to go to the next page of the Add JLINQ Support wizard.
- Next you create the database connection. This page displays all discovered database connections. Here you select one from the list. If there are no existing connections, the wizard will guide you to create a new connection. For this example, select the SAMPLE database, where the Employee table resides.
Figure 3. Add JLINQ Support wizard: JLINQ database connection page
- Click on the Finish button to dismiss the wizard and to JLINQ-enable the Personnel Java project.
Step 3. Transform the relational artifacts into Java objects and auto-generate the JLINQ code
Once the database connection is made, the Java project becomes database aware. This will result in the automatic display of all database artifacts for that particular connection in the Database Explorer tree view of the IDE, as shown in Figure 4.
Figure 4. Database explorer populated with all artifacts from the SAMPLE database
The power of JLINQ is in its ability to auto-generate the required code in order to transform any one of the underlying relational database table artifacts into Java objects. This is simply done by selecting the desired database artifact and launching the JLINQ Code Generation wizard.
Follow these steps to transform the relational artifacts into Java objects and auto-generate the JLINQ code:
- For this example, select the Employee table node from the database explorer tree view, and click the the right mouse button on that node, as shown in Figure 5.
Figure 5. Transform the Employee table into a Java object: Auto-generate the JLINQ code
- From the pop-up menu, select the Generate the JLINQ code option. This launches the Generate JLINQ Code wizard, shown in Figure 6.
Figure 6. JLINQ Code Generation Wizard
- Specify the Java Bean General options.
The first page of the Generate JLINQ Code for a Table wizard, shown in Figure 6, consists of attributes that are meant to describe a bean class representing the Employee table. You can leave the default values as they are. Lets examine a few of the attributes:
- Specify how to define the generated bean variables.
In the second page of the wizard (shown in Figure 7), you can optionally specify whether to generate public or protected fields and with or without public accessor methods.
Figure 7. JLINQ Code Generation for defining the bean fields
You can also optionally change the name of beans variable names (which by default are set to be the same as the columns of the Employee table). Note that a special annotation is generated for every variable name that is different from its respective mapping column name. The annotation will be of the form:
@COLUMN[name=<name of the table column>]
|
and is preceded by the variable declaration. For example in this particular case change the name of the Bean field name firstnme to firstnme_new. The annotation that is generated and preceded to the declaration in the java file will be:
@COLUMN[name=FIRSTNME] protected string firstnme_new
|
The same would take place for every other variable name that is different from the table column name (see Figure 9).
For this example make sure the Protected Fields with Public accessor methods is checked. Optionally you can also change the variable type name to be different than its mapping columns data type.
- Specify different flavors of SQL statements to generate.
In the final page of the wizard (see Figure 8), you can optionally select different flavors of SQL statement(s) to be generated. For this example select Generate all SQL statements.
Figure 8. Different flavors of SQL statements to be generated
- Click Finish to generate the JLINQ code.
Upon clicking on the Finish button, the wizard generates a utility package in addition to the following five main files that complete the DDO process as expressed earlier in the section entitled JLINQ objectization process. Let's examine what the generated artifacts include:
 |
A query-driven objectization (QDO) case scenario
Given one or more SQL statements, JLINQ provides an intuitive means to auto create the object hierarchies for the relational data access layer. The following step-by-step walk through will illustrate the power of JLINQ in creating object models given only the SQL statements.
- Open the same Eclipse Java Personnel project as created in the earlier section on the DDO case scenario.
- Add a new Java file class to the project and include the following SQL statement in the Main method:
SELECT BIRTHDATE, BONUS, COMM, EDLEVEL, EMPNO, FIRSTNME, HIREDATE, JOB, LASTNAME,
MIDINIT, PHONENO, SALARY, SEX, WORKDEPT from EMPLOYEE
|
- Point and right mouse button click anywhere in the SQL statement, and from the pop-up context menu select the JLINQ Assist -> Generate SQL Bean option. This will result in the launch of Create Bean for a SQL Result Set wizard .
Figure 15. Create a bean from an SQL statement
- On the first page specify a name for the Bean class to be generated. In this case enter "MySQLEmployee" and click on the Next button.
Figure 16. Specify the Bean Class name to be generate from SQL statement
- In the second page of the wizard you can specify how to define the bean fields. For this example you can leave the default values as they are. Optionally you can also change the variable type name to be different than its mapping columns data type (See Figure 17).
Figure 17. Specify the bean fields
- Click on the Finish button. This will generate the bean file, MySQLEmployee.Java.
To create the interface and the default implementation file, you can use JLINQ's ORM process, shown in the next section.
An object-relational mapping (ORM) case scenario
Given a Java bean class, JLINQ provides an intuitive mechanism to map the members of that class to any given relational entity in the underlying database table.
Note that the design time mapping needs to be done according to the table schema and rules-of-the-game . For example even though at design time a variable of data type double can be mapped to a table column of type XML, the run time consequences are unpredictable and hence resulting in errors. The following walk though example illustrates this mapping.
- Open the same Eclipse Java Personnel project as created in the DDO example.
- To illustrate the ORM feature, you can either use an existing bean or a Java user-defined class which is the representative of a table in the database. In this example we use an existing bean, so delete all generated Java files except MyEmployee.Java.
Figure 18. Mapping a bean to a relational table
- With MyEmployee.Java class opened in the editor, add the following annotation at the top of the class name:
@Table (name = EMPLOYEE, Schema = DB2ADMIN)
|
This annotation performs the actual mapping between the class name and the table in the underlying database.
- Add the following annotation at the top of every field/variable for which you wish the mapping to take place. In this example we only map BIRTHDAY and FIRSTNME fields, hence add
@COLUMN (name = BIRTHDAY)
|
at the top of the BIRTHDAY variable name, and
@COLUMN (name = FIRSTNME)
|
at the top of FIRSTNME variable name (See Figure 19).
Figure 19. Annotation creates the mapping between host variables and table columns
- Now point and right mouse button click anywhere inside the MyEmployee class in the editor, and from the pop-up context menu select the JLINQ Assist -> Generate JLINQ CODE option. This will result in the launch of the Generate JLINQ Code for a Table wizard as described earlier, which will ultimately generate the JLINQ related code (including interface files, test classes, implementation code, and so on.).
Note that since the Java bean class already exists, the wizard's Bean Field page is omitted from being presented to the user.
JLINQ's content assist and tight integration with the Java editor
One of the most powerful features that JLINQ offers is the seamless integration between SQL and Java in one single editor. This tight integration provides a consistent set of design time features for both Java and SQL, including code assist, syntax validation, and colorization, in addition to semantic validation of the SQL statements.
Code assist provides:
|
Design and runtime hand-shaking
One of the most advantageous features of JLINQ's design time functionality resides in its ability to dynamically run any given SQL statement from within the editor. This unique feature makes it possible for dynamic execution of SQL statements at design time with rollback capabilities.
To see this feature while in the Java Editor, simply point-and-click with right mouse button down on any one of the generated SQL statements. From the context menu select JLINQ Assist -> Run SQL (see Figure 26). This will execute the SQL statement and the result set will be displayed in the Data View of the Eclipse IDE (see Figure 27).
Figure 26. Point-and-click selection to run the SQL at design time
Figure 27. Result of the SQL execution at design time displayed in Data View
Conclusion
In an era where data and its efficient accessibility are the driving force behind the evolution of database-centric business applications into pure services delivered over the Web (or otherwise), and in the interest of increase productivity for rapid application development and maintenance, the need to have an object-oriented infrastructural framework has never been greater. IBM's JLINQ, through a seamless intuitive objectization process of relational access, provides a state-of-the-art, pluggable solution to the Eclipse IDE environment for optimizing developers' productivity and performance.
Resources
Learn
Get products and technologies
- Download a free trial version of DB2 Enterprise 9.
- Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.
- Download the IBM Viper Developer V9.5 Open Beta to obtain the JLINQ plug-in and the version of the DB2 Developer Workbench required for this article.
About the author
 |
|
|
|
Azadeh Ahadian is a software developer in the Information Management tool organization at IBM’s Silicon Valley Lab in San Jose, California. She has a master's degree with over eight years of industry experience in software application and tools development. Azadeh has been a key contributor and a member of the JLINQ design time team.
|
凡是有该标志的文章，都是该blog博主Caoer（草儿）原创，凡是索引、收藏
、转载请注明来处和原文作者。非常感谢。