< Previous | Next >

Lesson 4: Working in the SQL Outline view

When you added pureQuery support to your Java™ project in a previous lesson, a tab for the SQL Outline view appeared at the bottom of the screen. In this lesson, you will learn to use the SQL Outline view to find, test, and tune the SQL statements that are in your pureQuery_test project.
Normally, when you work with SQL statements, you need to scroll through files to find them. When you scroll through a long interface, you can easily become lost. The interface that you use in this tutorial is a long interface with multiple overloaded methods, each of which has multiple variants. Using the SQL Outline view can simplify the multiple SQL statements in your files.

To work with the SQL Outline view:

  1. Double-click the SQL Outline tab at the bottom of the screen. The SQL Outline view opens. The SQL Outline view shows the schemas that you are working with. In this case, it shows GOSALES because GOSALES contains the INVENTORY_LEVELS table.

    The view consists of three pages, which are shown at the bottom of the screen.

    The Database, Java, and Database packages pages
    Each page organizes the SQL statements in your project in different ways. In this lesson, you will learn about the Database and Java pages. In a later lesson, you will learn about the Database packages page.

  2. Expand the schema to see the INVENTORY_LEVELS table, then expand that table. The view lists all of the SQL statements in the interfaces in your Java project that reference the table. Because you have only one interface and all of its SQL statements refer to this table, all of the SQL statements in the interface are listed here.

    The Database page of the SQL Outline view groups SQL statements under database objects that themselves are grouped under database schemas. Notice that each statement is expandable.

  3. Expand the first statement in the list. You see the name of your Java project and the names of the columns that the statement references. If you expand the pureQuery_test project name, you see the package, interface, and name of the method that is annotated with this statement. You also see the line number at which the method is located in the interface.
    Figure 1. The schema, table, and first SQL statement expanded
    Viewing the project in the Database view

    The hierarchy in this Database view is:

    Database schema

         Table

              SQL statement

                   Java project

                        Method that runs the SQL statement

                   Columns in the SQL statement

  4. Click on the Java tab at the bottom of the view. The hierarchy in this Java view is:

    Java project

         Java package

              Interface

                   Method

                        SQL statement

                             Database schema

                                  Table

                                       Column

  5. Open the source file that contains the SELECT statement that retrieves all rows from the INVENTORY_LEVELS table. Right-click the statement on any page in the SQL Outline view and select Find in Source.

    The correct file opens in the Inventory_levelsData tab, and pureQuery places the cursor on the line that contains the SELECT statement.

  6. Filter the statements that appear in the SQL Outline view. For this tutorial, you can probably work with the SQL statements in the SQL Outline view easily without filtering them, but suppose that you have six interfaces and a large number of other Java files in your project. You might want to keep a good number of the Java files open for editing at the same time. In cases like this, the filter option is more useful.

    The SQL Outline view provides a filter that you can use to see only the statements that you want to see.

    1. Open the filter by right-clicking anywhere in the SQL Outline view and selecting Filter. The Filter window opens.
    2. To see only the SELECT statements that reference the INVENTORY_LEVELS table, specify GOSALES.INVENTORY_LEVELS in the Database object name field and select the SELECT check box.

      Although your user ID is the default schema for your Java project, the SQL Outline view shows SQL statements in all Java projects that the Package Explorer lists. So, you must specify the schema in the filter.

      When you click OK, the SQL Outline view should show only four SELECT statements per the criteria that you specified.

  7. Explore the other options in the SQL Outline view. When you right-clicked the statement in step 5, you probably noticed other options on the pop-up menu. The options in the table are described in the following table. To access the menu, right-click an SQL statement in the SQL Outline view.
    Option Description
    Find in Source This option opens for editing the Java file in which the statement appears. The cursor is positioned at the beginning of the line at which the statement is located.
    Run SQL This option runs the statement and shows the result in the SQL Results view.
    Show in SQL Editor This option opens the statement for editing in the SQL Editor.
    Export SQL to File This option saves the statement in a file.
    Compare If you select two statements, this option opens them in the Compare editor, where you can see how they differ from each other.
    Retrieve EXPLAIN Data This option runs the EXPLAIN utility. EXPLAIN is a monitoring tool in Visual Explain that produces information about the estimated cost of executing an SQL SELECT, INSERT, UPDATE, or DELETE statement.
    Open Visual Explain This option opens the Collect Explain Data wizard to collect information that Visual Explain requires to show a diagram of the access plan for the statement. After you complete the Collect Explain Data wizard, the Access Plan Diagram view opens to show a diagram of the access plan for the statement. For more information about Visual Explain, see Diagramming access plans with Visual Explain.
    Get Query Tuner Report Generates recommendations for tuning the statement, if Optim™Query Tuner is installed on your system.
    Open Query Tuner Opens the statement in Optim Query Tuner, if Optim Query Tuner is installed on your system.
    Generate pureQuery Code For SELECT statements, this option generates an interface that contains a method that runs the statement. This option also generates an implementation of the interface. You can choose whether to generate a bean that can hold query results or to use an existing bean.

    For DELETE, INSERT, and UPDATE statements, this option generates an interface that contains a method that runs the statement and an implementation of that interface.

    Find in pureQueryXML You can use this option with pureQuery's client optimization, which this tutorial does not cover.
    Filter Opens the Filter window, which you can use to show only the SQL statements that you want to see.
    Show SQL in Table or Show SQL in Tree Toggles the display of the SQL statements in a table or in a tree.
    Show Data This option shows performance data or EXPLAIN data for the statement. In the next lesson, you will learn how to use the SQL Outline view to gather data about the performance of the SQL statements in your project.
< Previous | Next >

Feedback