Home

 

Creating a Java stored procedure

To create a stored procedure using the Stored Procedure wizard, do these steps:

In the Data Project Explorer view, expand the RAD7DataDevelopment project, right-click the Stored Procedures folder, and select New Æ Stored Procedure. The New Stored Procedure wizard opens.

In the Name and Language page:

RAD75DataDevelopment is preselected.

Type AddCredit for the Name.

Select Java as the language.

Because we are developing a stored procedure against Derby database, Java is the only option for the Language. If you develop stored procedures against a DB2 database, you will see two options: Java and SQL.

Type itso.bank.data as the package name.

Select Dynamic SQL using JDBC (Figure | 1-18).

Click Next.

Figure 11-18 Create stored procedure: Name and Language

In the SQL Statements page, click Create SQL. This action launches the New SQL Statement wizard that guides you through the creation of an SQL statement.

In the first page of the New SQL Statement wizard, keep the defaults to create a SELECT statement using the wizard, and click Next.

We go through several tabs to create the SQL statement:

In the Tables tab, in the Available Tables list, expand the ITSO schema, select ITSO.ACCOUNT, ITSO.ACCOUNT_CUSTOMER, and ITSO.CUSTOMER, and click > to move the three tables to the Selected Tables list.

Select the Columns tab, expand the CUSTOMER table and select FIRST_NAME and LAST_NAME. Expand the ACCOUNT table and select ID and BALANCE. Click > to move the columns to the Selected Columns list.

Select the Joins tab, drag the cursor from SSN (CUSTOMER) to CUSTOMER_SSN (ACCOUNT_CUSTOMER), and from ID (ACCOUNT) to ACCOUNT_ID (ACCOUNT_CUSTOMER) to create two joins (Figure | 1-19).

Figure 11-19 Create stored procedure: Joins

Select the Conditions tab. In the first row click the cell under Column and select CUSTOMER.LASTNAME. In the same row select LIKE as the Operator and type :PARTIALNAME as the Value (Figure | 1-20). Click Next.

Figure 11-20 Create stored procedure: Conditions

In the Change the SQL Statement page, review the generated SQL statement and click Finish to close the New SQL Statement wizard.

Back in the New Stored Procedure wizard, select One for the Result set and click Next.

In the Parameters page, leave the settings as default and click Next.

In the Deploy Options page of the wizard, clear Deploy on Finish. We will deploy the stored procedure in later steps. Click Next.

In the Code Fragments page of the wizard, click Next.

Review the selections on the Summary page of the wizard and click Finish.

The stored procedure opens in the routine editor. Select the Configuration tab in the routine editor. In the Java section, click ADDCRETID.java. The generated file opens and is shown in Example | 1-2.

Example 11-2 AddCredit.java

package itso.bank.data;

import java.sql.*; // JDBC classes

public class ADDCREDIT {

	public static void aDDCREDIT(java.lang.String PARTIALNAME, 

										ResultSet[] rs1)

			throws SQLException, Exception {

		// Get connection to the database

		Connection con = DriverManager.getConnection

											("jdbc:default:connection");

		PreparedStatement stmt = null;

		boolean bFlag;

		String sql;

		sql = "SELECT ITSO.CUSTOMER.FIRST_NAME, ITSO.CUSTOMER.LAST_NAME,

															ITSO.ACCOUNT.BALANCE"

			+ "   FROM"

			+ "        ITSO.ACCOUNT JOIN ITSO.CUSTOMER JOIN

							ITSO.ACCOUNT_CUSTOMER ON ITSO.CUSTOMER.SSN =

							ITSO.ACCOUNT_CUSTOMER.CUSTOMER_SSN ON ITSO.ACCOUNT.ID

								= ITSO.ACCOUNT_CUSTOMER.ACCOUNT_ID"

				+ "   WHERE ITSO.CUSTOMER.LAST_NAME LIKE  ?";

		stmt = con.prepareStatement(sql);

		stmt.setString(1, PARTIALNAME);

		bFlag = stmt.execute();

		rs1[0] = stmt.getResultSet();

	}

}

We give $100 credit to the selected accounts. Add the following code under the rs1[0] = stmt.getResultSet() statement (Example | 1-3):

Example 11-3 Snippet to give $100 credit to each account

		String sql2 = "UPDATE ITSO.ACCOUNT  SET BALANCE = (BALANCE + 100)"

			+ " WHERE ID IN " + 

				"(SELECT ITSO.ACCOUNT.ID FROM ITSO.ACCOUNT"

				+ " JOIN ITSO.ACCOUNT_CUSTOMER"

				+ " ON ITSO.ACCOUNT.ID = ITSO.ACCOUNT_CUSTOMER.ACCOUNT_ID"

				+ " JOIN ITSO.CUSTOMER ON ITSO.ACCOUNT_CUSTOMER.CUSTOMER_SSN =" 

				+ " ITSO.CUSTOMER.SSN" 

			+ " WHERE ITSO.CUSTOMER.LAST_NAME LIKE  ?)";

		stmt = con.prepareStatement(sql2);

		stmt.setString( 1, PARTIALNAME );

		stmt.executeUpdate();

The modified AddCredit.java can also be found in:

C:\7672code\database\samples

ibm.com/redbooks