You can generate pureQuery code from a single SQL statement.
The statement can be an SQL string in Java™ code,
an SQL script that contains a single SQL statement, or part of an
SQL script.
About this task
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 also can work with scripts
that are open in the SQL and XQuery editor.
You can use this
process to generate pureQuery code only for a valid SELECT, INSERT,
UPDATE, DELETE, or CALL statement. If a JDBC prepareStatement() method
of the SQL statement results in errors, a message box displays the
error message that is returned.
If you want to generate code
from more than one SQL statement, see Generating pureQuery code from multiple SQL statements.
Procedure
To generate pureQuery code from a single SQL statement:
- Open the Generate pureQuery Code from an SQL
Statement wizard by following one of these sets of steps:
Option |
Description |
For an SQL string in Java code: |
- Switch to the Java perspective.
- Open the .java file that contains the SQL string in the Java editor.
- In the editor, position the text cursor within the StringLiteral
of a SELECT, INSERT, UPDATE, DELETE, or CALL statement, right-click,
and then select Generate pureQuery Code.
If the Generate pureQuery Code from a Table wizard
opens instead, either the text cursor is not positioned in a StringLiteral
or the text cursor is not positioned in one of the specific types
of SQL statements.
|
For an SQL statement in an SQL script that is open in the
SQL and XQuery editor: |
- In the editor, select an entire SELECT, INSERT, UPDATE, DELETE,
or CALL statement.
- Right-click the selected statement, and then select Generate
pureQuery Code.
|
For an SQL statement in an SQL script in a pureQuery-enabled Java project: |
- Switch to the Java perspective.
- Right-click the .sql file that contains the single SQL SELECT,
INSERT, UPDATE, DELETE, or CALL statement, and then select Generate
pureQuery Code.
|
For an SQL statement 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 single SQL SELECT, INSERT,
UPDATE, DELETE, or CALL statement, and then select Generate
pureQuery Code.
|
If the wizard does not open and a message box is displayed,
review the error message to determine why the SQL statement is not
valid.
- 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.