From multiple SQL statements, you can generate
data access objects or inline methods that use pureQuery code. The
statements can be in SQL scripts in the workbench or they can be in
script files in the file system.
About this task
Depending on your license configuration, this functionality
might not be available. You can work with SQL scripts that are in
pureQuery-enabled Java™ projects
in the Java perspective and
in data development projects in the SQL and Routine Development perspective.
You can work with SQL scripts that are open in the SQL and XQuery
editor and you also can import SQL statements that are in script files.
An
SQL script can contain one or more SQL statements. The number of SQL
statements that a script contains or the number of statements that
you select determines the wizard that you use to generate pureQuery
code. For scripts that contain a single SQL statement and for scripts
in which you select a single SQL statement, see Generating pureQuery code from single SQL statements.
You
can use this process to generate pureQuery code only for valid SELECT,
INSERT, UPDATE, DELETE, or CALL statements. If a JDBC prepareStatement() method
of an SQL statement results in errors, a message box displays the
error message that is returned.
Procedure
To generate pureQuery code from multiple SQL statements:
- Open the Generate pureQuery Code from SQL Statements wizard
by performing one of the following sets of steps:
Option |
Description |
For SQL statements that are in an SQL script that is open
in the SQL and XQuery editor: |
- Optional: If you want to generate pureQuery code for only some
of the SQL statements in the script, select each of the statements
in its entirety. If you do not select any statements, pureQuery code
is generated for all of the SQL statements.
- Right-click the selected SQL statements or, if you did not select
any statements, right-click anywhere in the editor, and then select Generate
pureQuery Code.
|
For SQL statements that are in an SQL script in a pureQuery-enabled Java project: |
- Switch to the Java perspective.
- Right-click the .sql file in the project that contains the SQL
statements, and then select Generate pureQuery Code.
|
For SQL statements that are in an SQL script in a data development
project: |
- Switch to the SQL and Routine Development perspective.
- Expand the SQL Scripts folder in the project.
- Right-click the script that contains the SQL statements, and then
select Generate pureQuery Code.
|
For SQL statements that are in a script file in the file
system: |
- Select .
- In the New wizard, expand the Data node,
select pureQuery Annotated-method Interface,
and then click Next.
|
- Specify information on the pureQuery Code Generation page
and then click Next. If you
opened the wizard from an SQL script in the work bench, the Statements list
on the SQL Statements page is populated with the SQL statements in
the script.
- To import SQL statements from a script file in the file
system, follow these steps:
- Specify the terminator that the SQL statements in the
file use.
- Click Import, and then select
the script file.
- Optional: Change the name of a bean or a method
in the list, by double-clicking the name that you want to change,
and then entering the new name. If you change the name
of a bean, the default name of its corresponding method is updated
with the new bean name.
- Optional: If you have an SQL SELECT statement,
review and edit the bean information to generate by clicking the SELECT
statement in the list, and then clicking Details.
- Optional: If you have an SQL CALL statement,
review and edit the custom call handler to use in the generated method
by clicking the CALL statement in the list, and then clicking Details.
- Complete the steps of the wizard. To see information
about the fields in the wizard, press F1.
- Click Finish to generate the files
that you specified.
Results
When you generate pureQuery
code, the workbench creates a bean that you can use to access your
database. For example, you can generate code from an UPDATE statement
or a SELECT statement. When you generate code from a SELECT statement,
the workbench creates a bean that you can use to hold query results.
If you do not use AS clauses in SELECT statements that produce joins
or unions, or that contain calculated columns, query results might
contain columns with non-unique names. Whether the workbench is able
to resolve this problem when generating a bean to represent query
results depends on the type of database that your application uses.
- DB2® for Linux®, UNIX®,
and Windows®; DB2 for z/OS®; Informix® Dynamic Server: The
workbench uses the @Column annotation on properties that map to columns
that have the same name as one or more other columns.
For
example, suppose that your application runs the following simple query:
select a.col1, b.col1 from a, b where a.id=b.id;
The
set() methods
for the corresponding properties in the beans that hold the query
results need
@Column annotations that give the
name of the table in which the two
id columns appear:
public class JoinExample{
private int a_id;
private int b_id;
@Column (name="id", table="a")
public void setA_id (int a_id)
{
this.a_id = a_id;
}
public int getA_id ()
{
return a_id;
}
@Column (name="id", table="b")
public void setB_id (int b_id)
{
this.b_id = b_id;
}
public int getB_id ()
{
return b_id;
}
}
- Oracle: The workbench does not use the table attribute
of the @Column annotation to map problematic columns to properties
in the bean. You must use either of these ways to map them:
- Use the AS clause in the SELECT statement to assign unique names
to columns in query results.
- Use a RowHandler that can work with columns in query results by
their index numbers, rather than by their names. See The RowHandler <ROW> interface.