Generating beans to hold result sets from SQL SELECT statements

You can generate a bean to hold the result set that the statement returns. You can use an SQL SELECT statement that is in any string literal in a Java class that contains a valid SQL SELECT statement, as long as the Java class appears in a Java project that supports pureQuery.

Procedure

To generate a bean to hold the result set from a SELECT statement:

  1. In the Java editor, position the text cursor within the statement, right-click the statement, and then select Data Access Development > Generate pureQuery Code. The Generate pureQuery Code from an SQL Statement wizard opens.
  2. Complete the steps of the wizard.
  3. Click Finish to generate the bean.

Results

If you do not use AS clauses in SQL queries that produce joins or unions, or that contain calculated columns, result sets might contain columns with non-unique names. Whether the workbench is able to resolve this problem when generating a bean to represent result sets 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.

Feedback