< Previous | Next >

Lesson 2: Adding a method to the InventoryLevelsData interface

In this lesson, you will write a method that returns the value of the QUANTITY_SHIPPED column in the INVENTORY_LEVELS table.
The SQL statements that pureQuery generates are all helpful, but you also want statements that are tailored for the applications that you plan to write. For example, one of your applications will need a SELECT statement to determine how many units of a certain product were shipped during a certain month of a certain year. The application will not need to return any values other than the number of units shipped.

With pureQuery, it is easy to add the declaration of this new method to the interface, then PureQuery automatically regenerates the implementation class so that it implements the method.

To add a new method to the interface:

  1. Go to the InventoryLevelsData.java tab.
  2. Below the second getInventoryLevels() method (the one that takes individual fields), add a few blank lines to the file and then type the following lines:
    	// Select QUANTITY_SHIPPED by parameters
    	@Select(sql = "SELECT  FROM ")
    Tip: Don't worry about the error markers in the editor. They will go away when you are done declaring the method.
  3. Add the GSDB schema to the code:
    1. In the code that you just typed, click to place the cursor just before the second quotation mark. Then, while holding the CTRL key, press the SPACE bar. A small window appears just below the current line. This window lists proposed SQL elements for the statement.
    2. In the small window, scroll down until you find the list of schemas in the GSDB database and double-click the GOSALES schema.
  4. Add the INVENTORY_LEVELS table to the code:
    1. In the code, type a period after the schema and press CTRL and SPACE together again to see a small window with the list of the tables that are in that schema.
    2. Use the down arrow key to move down to the INVENTORY_LEVELS table and press Enter.
  5. Complete the SQL statement. You want to select the value of the QUANTITY_SHIPPED column. You also want to add the name of the column quickly and without mistyping it.
    1. Click to place the cursor in between the two spaces after the SELECT keyword. While holding the CTRL key, press the SPACE bar. Another window appears below the current line. This window lists the columns that are in the INVENTORY_LEVELS table.
    2. Move down to the QUANTITY_SHIPPED column by pressing the down arrow key, and press Enter.
    3. Write the WHERE clause so that, like the previous SELECT statement, it includes the four primary key columns.
  6. Format the SQL statement so that it fits the screen. In the previous steps, you probably scrolled so far to the right while writing the statement that you cannot see the rest of the code.
    Figure 1. The end of the SELECT statement
    The end of the SELECT statement

    Fortunately, you do not need to scroll that far to the right every time you want to read the SELECT statement. To format the SQL:

    1. Click to place the cursor in the statement. Right-click the statement and select pureQuery > Format SQL.
    2. Scroll back all the way to the left. Notice that the SQL statement is now neatly formatted on multiple rows.
  7. Write the method so that it returns an integer with the value of QUANTITY_SHIPPED and takes individual fields as parameters.
    int getQuantityShipped(short inventoryYear, short inventoryMonth,
    				int warehouseBranchCode, int productNumber);
  8. Test the SELECT statement. You realize that you wrote the method without first testing the SELECT statement. pureQuery lets you test it from the Java editor. To test the statement, click to place the cursor in the statement, then right-click the statement and select pureQuery > Run SQL.

    Because the statement uses parameter markers in the WHERE clause, you need to tell pureQuery which values to use.

    The Specify Host Variable Values window opens.
  9. In the Specify Host Variable Values window, type the following values:
    • 2007
    • 1
    • 7
    • 10110
    The SQL Results view opens.
  10. In the SQL Results view, select the first row and then select the Result1 tab to see the result.
    The result of the SELECT statement

    You can see from the Results view that the SELECT statement runs without error.

When you save the interface, pureQuery automatically regenerates the InventoryLevels_DataImpl.java file so that it implements the new method.
< Previous | Next >

Feedback