Table of Contents
Introduction
QueryJ is an Ant-based tool that generates a complete set of Java classes following the DAO pattern, to access and manipulate data stored on any JDBC-compliant (and some non-compliant as well) database.
Besides that, QueryJ is becoming a framework to allow generating files to suit any particular requirements, from an already-deployed relational database.
Architecture
Download
Currently, QueryJ is available from the Subversion repository, but you can access a pre-packaged jar file from ACM-SL's Maven repository. You're encouraged to get the sources directly.
Once you have a Subversion client installed, just do a checkout:
svn co http://svn.acm-sl.org/queryj/trunk
After doing so, you'll need to build the project from its sources, and for that task QueryJ uses Maven, Ant and Maven AntDep. Install and configure Maven (add http://maven.acm-sl.org to your list of Maven repositories) and Ant, and copy maven-antdep.jar to the lib folder of your Ant installation.
Up to this point, run the following command in the trunk folder of your QueryJ checkout:
./build.sh rebuild
Under dist/build you'll get the QueryJ jar ready to be used.
Usage
Declaring the Ant task
As said before, QueryJ is executed from within Ant. Therefore, it includes an Ant task, which has to be declared in your build.xml:
<!--
Declares QueryJ task.
@return the correctly declared queryj task.
-->
<target name="declare.queryj.task">
<description>Declares QueryJ task</description>
<!--
Defines the <i>QueryJ</i> task.
-->
<taskdef
name="queryj"
classname="org.acmsl.queryj.tools.QueryJTask"
classpathref="queryj.classpath"/>
</target>
In order to let Ant find the previous class, you'll need to include queryj.jar in the path reference queryj.classpath above. It's up to you to define such setting, or even to avoid defining it and place queryj.jar in your ANT_HOME/lib. There're some dependencies that need to be present in such path (or copied to ANT_HOME/lib folder). If you performed a checkout and executed maven antdep they are already there, under queryj/trunk/dependencies/lib/java and queryj/trunk/dependencies/lib/java/runtime. They are described in the Maven descritor (queryj/trunk/src/toolconf/maven/project.xml).
Configuring QueryJ
Once the taskdef executes sucessfully, you can proceed configuring QueryJ:
<!--
Executes QueryJ.
@params see queryj.properties.
@return the result of executing QueryJ.
-->
<target
name="queryj"
depends="declare.queryj.task"
description="Executes QueryJ">
<!--
Executes QueryJ defining all its attributes and
content.
-->
<property file="queryj.properties"/>
<mkdir dir="${queryj.outputdir}"/>
<queryj
driver="${queryj.driver}"
url="${queryj.url}"
username="${queryj.username}"
password="${queryj.password}"
catalog="${queryj.catalog}"
schema="${queryj.schema}"
repository="${queryj.repository}"
package="${queryj.package}"
classpathref="queryj.classpath"
outputdir="${queryj.outputdir}"
extractFunctions="${queryj.extractfunctions}"
extractProcedures="${queryj.extractprocedures}"
jndiDataSource="${queryj.jndi}"
generateMockDAOImplementation="${queryj.generate.mock}"
generateXMLDAOImplementation="${queryj.generate.xml}"
headerfile="${queryj.header.file}"
customSqlModel="${queryj.custom.sql}"
sqlXmlFile="${queryj.sql.xml.file}"
allowEmptyRepositoryDAO="${queryj.allow.empty.repository.dao}"
implementMarkerInterfaces="${queryj.implement.markers}">
<externally-managed-fields>
<field
name="''column-name''"
table-name="''table-name''"
keyword="''sequence-name''.nextval"
retrieval-query="select ''sequence-name''.nextval from dual"/>
</externally-managed-fields>
</queryj>
</target>
QueryJ properties
The above script expects some settings to be defined in an external file, queryj.properties (see source:queryj/trunk/conf/properties/queryj.properties.sample)
# Defines the JDBC driver to use for executing QueryJ queryj.driver=oracle.jdbc.driver.OracleDriver # Defines the JDBC url to use for executing QueryJ queryj.url=jdbc:oracle:thin:@''XXX.XXX.XXX.XXX:YYYY:ZZZ'' # Defines the JDBC username to use for executing QueryJ queryj.username=''username'' # Defines the JDBC password to use for executing QueryJ queryj.password=''password'' # Defines the database catalog to use for executing QueryJ. queryj.catalog=''catalog'' # Defines the database schema to use for executing QueryJ. queryj.schema=''schema'' # Defines the table repository name. queryj.repository=''repository'' # Specifies the parent package the generated sources belong. queryj.package=com.mycompany.myproject # Where should QueryJ generate the output? queryj.outputdir=src # Specifies whether to extract database functions. queryj.extractfunctions=false # Specifies whether to extract stored procedures. queryj.extractprocedures=false # The JNDI path the generated sources will try to get the # DataSource (Database connection) from. queryj.jndi=java:comp/env/jdbc/''myproject'' # Whether to generate an alternate Mock implementation. queryj.generate.mock=false # Whether to generate an alternate XML implementation. queryj.generate.xml=false # The copyright header for the generated sources. queryj.header.file=header.txt # The format of the custom SQL queryj.custom.sql=xml # The XML file for the custom SQL. queryj.sql.xml.file=custom-sql.xml # Whether to allow empty repository (global) DAO. queryj.allow.empty.repository.dao=true # Whether to make the generated sources implement marker # interfaces or not. queryj.implement.markers=true
Sequences and keywords
You can define your custom sequences or keywords inside the <queryj> element in the Ant script.
If your model has a table whose primary key consist of just one column, taking its values from a sequence, then
<externally-managed-fields>
<field
name="column-name"
table-name="table-name"
keyword="sequence-name.nextval"
retrieval-query="select sequence-name.nextval from dual"/>
</externally-managed-fields>
Custom SQL
<?xml version="1.0"?>
<!DOCTYPE sql-list
[
<!ELEMENT sql-list (sql+,parameter-list,result-list,property-list,flag-list)>
<!ELEMENT sql (description,value,parameter-ref,result-ref?,connection-flags-ref?,statement-flags-ref?,resultset-flags-ref?)>
<!ATTLIST sql
id ID #REQUIRED
dao CDATA #IMPLIED
repositoryScope (true) #IMPLIED
name CDATA #REQUIRED
type (select | insert | update | delete | select-for-update) #REQUIRED
implementation (mysql | oracle | postgres | odbc | all-jdbc | mock | xml | all) #REQUIRED
validate (true | false) #REQUIRED>
<!ELEMENT description (#PCDATA)>
<!ELEMENT value (#PCDATA)>
<!ELEMENT parameter-ref EMPTY>
<!ATTLIST parameter-ref
id IDREF #REQUIRED>
<!ELEMENT result-ref EMPTY>
<!ATTLIST result-ref
id IDREF #REQUIRED>
<!ELEMENT connection-flags-ref EMPTY>
<!ATTLIST connection-flags-ref
id IDREF #REQUIRED>
<!ELEMENT statement-flags-ref EMPTY>
<!ATTLIST statement-flags-ref
id IDREF #REQUIRED>
<!ELEMENT resultset-flags-ref EMPTY>
<!ATTLIST resultset-flags-ref
id IDREF #REQUIRED>
<!ELEMENT parameter-list (parameter)*>
<!ELEMENT parameter EMPTY>
<!ATTLIST parameter
id ID #REQUIRED
index CDATA #REQUIRED
type CDATA #REQUIRED
name CDATA #REQUIRED
validation-value CDATA #REQUIRED>
<!ELEMENT result-list (result)*>
<!ELEMENT result (property-ref)*>
<!ATTLIST result
id ID #REQUIRED
class CDATA #IMPLIED
matches (none | single | multiple) #REQUIRED
provider CDATA #IMPLIED>
<!ELEMENT property-ref EMPTY>
<!ATTLIST property-ref
id IDREF #REQUIRED>
<!ELEMENT property-list (property)*>
<!ELEMENT property EMPTY>
<!ATTLIST property
id ID #REQUIRED
column_name CDATA #REQUIRED
index CDATA #IMPLIED
name CDATA #IMPLIED
type CDATA #REQUIRED>
<!ELEMENT flag-list (connection-flags*,statement-flags*,resultset-flags*)>
<!ELEMENT connection-flags EMPTY>
<!ATTLIST connection-flags
id ID #REQUIRED
transactionisolation ( TRANSACTION_NONE
| TRANSACTION_READ_COMMITTED
| TRANSACTION_READ_UNCOMMITTED
| TRANSACTION_REPEATABLE_READ
| TRANSACTION_SERIALIZABLE ) #REQUIRED>
<!ELEMENT statement-flags EMPTY>
<!ATTLIST statement-flags
id ID #REQUIRED
autogeneratedkeys (NO_GENERATED_KEYS | RETURN_GENERATED_KEYS) #IMPLIED
fetchsize CDATA #IMPLIED
maxfieldsize CDATA #IMPLIED
maxrows CDATA #IMPLIED
querytimeout CDATA #IMPLIED
fetchdirection (FETCH_FORWARD | FETCH_REVERSE | FETCH_UNKNOWN) #IMPLIED
escapeprocessing (true | false) #IMPLIED
moreresults (CLOSE_CURRENT_RESULT | KEEP_CURRENT_RESULT | CLOSE_ALL_RESULTS) #IMPLIED
cursorname CDATA #IMPLIED>
<!ELEMENT resultset-flags EMPTY>
<!ATTLIST resultset-flags
id ID #REQUIRED
type (TYPE_FORWARD_ONLY | TYPE_SCROLL_INSENSITIVE | TYPE_SCROLL_SENSITIVE) #IMPLIED
concurrency (CONCUR_READ_ONLY | CONCUR_UPDATABLE) #IMPLIED
holdability (HOLD_CURSORS_OVER_COMMIT | CLOSE_CURSORS_AT_COMMIT) #IMPLIED>
<!ENTITY sql SYSTEM "queries.xml">
<!ENTITY parameter-list SYSTEM "parameters.xml">
<!ENTITY result-list SYSTEM "results.xml">
<!ENTITY property-list SYSTEM "properties.xml">
]>
<sql-list>
<!-- Check queries.xml for the <sql> elements -->
&sql;
<!-- Check parameters.xml for the <parameter> elements -->
¶meter-list;
<!-- Check results.xml for the <result> elements -->
&result-list;
<!-- Check properties.xml for the <property> elements -->
&property-list;
<flag-list>
<connection-flags
id="connection-flags.find-by-primary-key"
transactionisolation="TRANSACTION_READ_COMMITTED"/>
<connection-flags
id="connection-flags.default"
transactionisolation="TRANSACTION_NONE"/>
<connection-flags
id="connection-flag-transaction-none"
transactionisolation="TRANSACTION_NONE"/>
<connection-flags
id="connection-flag-transaction-read-committed"
transactionisolation="TRANSACTION_READ_COMMITTED"/>
<connection-flags
id="connection-flag-transaction-read-uncommitted"
transactionisolation="TRANSACTION_READ_UNCOMMITTED"/>
<connection-flags
id="connection-flag-transaction-repeatable-read"
transactionisolation="TRANSACTION_REPEATABLE_READ"/>
<connection-flags
id="connection-flag-transaction-serializable"
transactionisolation="TRANSACTION_TRANSACTION_SERIALIZABLE"/>
<statement-flags
id="statement-flags.default"
autogeneratedkeys="NO_GENERATED_KEYS"/>
<statement-flags
id="find-customer-for-update-statement-flags"
autogeneratedkeys="NO_GENERATED_KEYS"
fetchsize="10"
maxfieldsize="1000"
maxrows="10"
querytimeout="30"
fetchdirection="FETCH_FORWARD"
escapeprocessing="false"
moreresults="CLOSE_ALL_RESULTS"/>
<resultset-flags
id="resultset-flags.default"
type="TYPE_FORWARD_ONLY"
concurrency="CONCUR_UPDATABLE"
holdability="CLOSE_CURSORS_AT_COMMIT"/>
<resultset-flags
id="find-customer-for-update-resultset-flags"
type="TYPE_FORWARD_ONLY"
concurrency="CONCUR_UPDATABLE"
holdability="CLOSE_CURSORS_AT_COMMIT"/>
</flag-list>
</sql-list>
queries.xml
This file contains any specific SQL queries your application uses, such the sample below.
<sql
id="select.active.customers"
name="select-recent-customers-with-paid-purchases"
dao="customer"
type="select"
implementation="all"
validate="true">
<description>Retrieves all customers with one or more paid purchases, registered after a given date</description>
<value><![CDATA[
select
c.id,
c.name,
c.registration_date
from
customer c,
purchase p
where
c.id = p.id
and p.paid = 1
and c.registration_date > ?
]]></value>
<parameter-ref id="date.parameter"/>
<result-ref id="multiple.customer.result"/>
</sql>
parameters.xml
<parameter-list>
<parameter
id="date.parameter"
name="registration_date"
index="1"
type="Date">
<parameter
id="customer-id.parameter"
name="customer_id"
index="1"
type="long"
validation-value="33"/>
</parameter-list>
results.xml
<result-list>
<result
id="single.customer.result"
class="''com.mycompany.myproject.vo.Customer''"
matches="single"/>
<result
id="multiple.customer.result"
class="''com.mycompany.myproject.vo.Customer''"
matches="multiple"/>
<result
id="multiple.id-and-date.result"
class="''com.mycompany.myproject.vo.IdDatePairVO"
matches="multiple">
<property-ref id="customer-id.property"/>
<property-ref id="registration-date.property"/>
</result>
</result-list>
properties.xml
<property-list>
<property
id="customer-id.property"
column_name="customer_id"
type="long"/>
<property
id="registration-date.property"
column_name="registration_date"
type="Date"/>
</property-list>
Static entities exported as Java constants
QueryJ allows exporting static content found in a table to the generated sources as Java constants. Such feature is useful to avoid unneeded database access and prevent code to refer to such values by their primary keys instead of by their logical meaning. This doesn't mean the table cannot be accessed like any other: it's just that its values when QueryJ executes appear as immutable constants in the code.
QueryJ uses the comments for the table to provide this functionality. It a table's comment contain @static name, then all contents of the table are fetched, and for each one a constant is created in the DAO interface for that table, whose name is based on the value of the attribute specified after the static keyword.
Generated code
API
Accessing the relational model gets easy:
import packageName.dao.RepositoryNameDataAccessManager;
import packageName.dao.TableNameDAO;
import packageName.vo.TableName;
RepositoryNameDataAcessManager manager = RepositoryNameDataAccessManager.getInstance();
TableNameDAO dao = manager.getTableNameDAO();
TableName valueObject = dao.findByPrimaryKey(15);
Standard operations
Each DAO comes with at least some standard operations to manipulate the data:
- findByPrimaryKey
- insert
- update
- delete
Custom queries
Every custom query appears as a new method, whose name is based on the name attribute of the <sql> element, expecting the set of arguments defined as parameter references in such element, and, in such case, returning a value determined by the associated <result>.
So, for the example select-recent-customers-with-paid-purchases above, QueryJ generates a method in CustomerDAO such as:
public Customer selectRecentCustomersWithPaidPurchases(final Date registration_date);
And implements it inside the actual DAO implementation packageName.dao.rdb.vendor.VendorCustomerDAO, using the SQL provided.
Extending QueryJ
Migration model
Roadmap
