# CustomerDB.jcx Sample

This topic inludes the source code for the CustomerDB.jcx Sample.

## Sample Location

This sample is located in the following directory in your WebLogic Workshop installation:

BEA_HOME/weblogic81/samples/workshop/SamplesApp/WebServices/database/customer_db/

## Sample Source Code

`001 package database.customer_db; 002 003 import com.bea.control.DatabaseControl; 004 import java.sql.SQLException; 005 006 007 /** 008 *<p> A sample Database Control that demonstrates use of 009 * CREATE TABLE, DROP TABLE, INSERT, UPDATE, and SELECT 010 * SQL statements.</p> 011 * 012 * <p>This Database Control uses the JDBC Data Source 013 * "cgSampleSataSource" which is configured automatically 014 * when WebLogic Workshop is installed.</p> 015 * 016 * <p>The data source is configured with the JNDI 017 * name "cgSampleDataSource".</p> 018 * 019 * @jc:connection data-source-jndi-name="cgSampleDataSource" 020 */ 021 022 public interface CustomerDB extends com.bea.control.ControlExtension, DatabaseControl 023 { 024 025 /** 026 * <p>Demonstrates SQL's 'CREATE TABLE' statement using a multi-line @jc:sql tag.</p> 027 * 028 * <p>Note that database column names match the member names in the Customer class 029 * with the exception of case.</p> 030 * 031 * <p>The CUSTOMER table is created and populated when WebLogic Workshop is 032 * installed. If this method is called when the table already exists, a 033 * harmless SQLException will be thrown. This method is here to 034 * illustrate that CREATE TABLE statements, like any SQL statement, can be 035 * issued by a Database control method.</p> 036 * 037 * @return Creation or failure reason 038 * 039 * @jc:sql statement:: 040 * CREATE TABLE customer ( 041 * custid INT 042 * CONSTRAINT customer_pk PRIMARY KEY, 043 * name VARCHAR(40), 044 * address VARCHAR(40), 045 * city VARCHAR(40), 046 * state CHAR(2), 047 * zip VARCHAR(11), 048 * area_code CHAR(3), 049 * phone CHAR(9)) 050 * :: 051 */ 052 public void createCustomerTable() throws SQLException; 053 054 /** 055 * <p>Demonstrates SQL DROP TABLE statement.</p> 056 * 057 * <p>The CUSTOMER table is created when WebLogic Workshop is installed and 058 * should probably not be deleted. If this method is called when the table 059 * does not exist, a harmless SQLException will be thrown. This 060 * method is here to illustrate that DROP TABLE statements, like any SQL 061 * statement, can be issued by a Database control method.</p> 062 * 063 * @return Creation or failure reason 064 * 065 * @jc:sql statement="DROP TABLE customer" 066 */ 067 public void dropCustomerTable() throws SQLException; 068 069 /** 070 * <p>Demonstrates the throwing of a SQL exception.</p> 071 * 072 * <p>An intentionally malformed SQL statement is attempted.</p> 073 * 074 * @jc:sql statement="CREATE TABLE badTable missingParen INT" 075 */ 076 public void badSQL() throws java.sql.SQLException; 077 078 /** 079 * <p>Demonstrates SQL INSERT statement using a multi-line @jc:sql tag.</p> 080 * 081 * @return The number of rows inserted. 082 * 083 * @param custid The key of the customer record. 084 * @param name The new name for the customer record (40 characters). 085 * @param address The new street address for the customer record (40 characters). 086 * @param city The new city for the customer record (40 characters). 087 * @param state The new 2 letter state code for the customer record. 088 * @param zip The new 9 digit zip code (xxxxx-xxxx) for the customer record. 089 * @param area_code The new 3 digit area code for the customer record. 090 * @param phone The new 7 digit phone number (xxx-xxxx) for the customer record. 091 * 092 * @jc:sql statement:: 093 * INSERT INTO customer (custid,name,address,city,state,zip,area_code,phone) 094 * VALUES ({custid},{name},{address},{city},{state},{zip},{area_code},{phone}) 095 * :: 096 */ 097 public int insertCustomer(int custid, String name, String address, String city, String state, String zip, String area_code, String phone); 098 099 /** 100 * <p>Demonstrates SQL INSERT statement using a multi-line @jc:sql tag with 101 * substitutions from an object.</p> 102 * 103 * @return The number of rows inserted. 104 * 105 * @param cust A Customer object whose member values will become the field values 106 * of the record in the database. 107 * 108 * @jc:sql statement:: 109 * INSERT INTO customer (custid,name,address,city,state,zip,area_code,phone) 110 * VALUES ({cust.custid},{cust.name},{cust.address},{cust.city},{cust.state}, 111 * {cust.zip},{cust.area_code},{cust.phone}) 112 * :: 113 */ 114 public int insertCustomerObject(Customer cust); 115 116 /** 117 * <p>Demonstrates SQL DELETE statement using a multi-line @jc:sql tag.</p> 118 * 119 * @return The number of rows deleted. 120 * 121 * @param custid The key of the customer record to delete. 122 * 123 * @jc:sql statement:: 124 * DELETE FROM customer WHERE custid={custid} 125 * :: 126 */ 127 public int deleteCustomer(int custid); 128 129 /** 130 * <p>Demonstrates a single-result SELECT query returning a <tt>Customer</tt> object.</p> 131 * 132 * @return A <tt>Customer</tt> object. Note that database column names map to the class's 133 * member names (case-insensitive). 134 * 135 * @param custid The key of the customer record. 136 * 137 * @jc:sql statement="SELECT custid, name, address, city, state, zip, area_code, phone FROM customer WHERE custid = {key}" 138 */ 139 public Customer findCustomerByID(int key); 140 141 /** 142 * <p>Demonstrates a single-row SELECT query returning a HashMap.</p> 143 * 144 * @return A HashMap containing the customer record. Note that the keys to the 145 * HashMap are the names of the database columns (upper-case names). 146 * 147 * @param custid The key of the customer record. 148 * 149 * @jc:sql statement="SELECT * FROM customer WHERE custid = {key}" 150 */ 151 public java.util.HashMap findCustomerHashByID(int key); 152 153 /** 154 * <p>Demonstrates a single-row SELECT query returning a single column value.</p> 155 * 156 * @return The 'CITY' column of the customer record. 157 * 158 * @param custid The key of the customer record. 159 * 160 * @jc:sql statement="SELECT city FROM customer WHERE custid = {key}" 161 */ 162 public String findCustomerCityByID(int key); 163 164 /** 165 * <p>Demonstrates a multiple-row SELECT query returning an Iterator.</p> 166 * 167 * @return A java.util.Iterator containing a 168 * <tt>CustomerDBControl.Customer</tt> object for 169 * each row in the result set. 170 * 171 * @jc:sql statement="SELECT * FROM customer" 172 * iterator-element-type="database.customer_db.Customer" 173 */ 174 public java.util.Iterator findAllCustomersIterator(); 175 176 /** 177 * <p>Demonstrates a multiple-result SELECT query returning an array.</p> 178 * 179 * @return An array containing a <tt>CustomerDBControl.Customer</tt> 180 * object for each row in the result set. 181 * 182 * @jc:sql statement="SELECT * FROM customer" 183 * array-max-length=100 184 */ 185 public Customer[] findAllCustomersArray(); 186 187 /** 188 * <p>Demonstrates SQL UPDATE statement.</p> 189 * 190 * <p>The return type for methods that use INSERT, UPDATE, or DELETE 191 * must either be 'int' or 'void'.</p> 192 * 193 * @return The number of records updated. 194 * 195 * @param custid The key of the customer record. 196 * @param address The new street address for the customer record. 197 * 198 * @jc:sql statement="UPDATE customer SET address = {address} WHERE custid={custid}" 199 */ 200 public int changeAddress(int custid, String address); 201 202 /** 203 * @jc:sql statement="SELECT name FROM customer" 204 * array-max-length=100 205 */ 206 public String [] getAllCustomerNames(); 207 208 /** 209 * <p>Demonstrates a multiple-row SELECT query returning a ResultSet.</p> 210 * 211 * @return A java.sql.ResultSet containing all customer records. 212 * 213 * @jc:sql statement="SELECT * FROM customer" 214 */ 215 public java.sql.ResultSet findAllCustomersResultSet(); 216 217 /** 218 * <p>Demonstrates how to invoke an internal database function. 219 * The function called is: in(row, rowIDs). 220 * 221 * @jc:sql statement="SELECT * FROM customer WHERE {sql:fn in(custid,{customerIDs})}" 222 */ 223 Customer[] callInternalFunction(Integer[] customerIDs); 224 }`