Model Programs
Many programming styles can be used to write the data-access portion of a J2ME component. Two programming styles—referred to as Model Aand Model B. The Model A program style is used to execute SQL requests that use the execute() or executeUpdate()methods and don’t return a ResultSet. The Model B program style is used to execute SQL requests that use the executeQuery() method, which returns a ResultSet.
Both program styles are designed to minimize the code clutter in this chapter. Code clutter occurs whenever many lines of code are used in code examples, but only a few lines of code change between examples. In an effort to reduce code clutter, each complete program model is presented at the beginning of the chapter. One or two comments indicate where within the program model to place code segments that are used to illustrate SQL routines throughout the rest of the chapter. These code segments are contained either within a try {} block or within the DownRow() method. The try {} block is where the SQL statement is created and executed. The DownRow() method is where the J2ME application interacts with the ResultSet.
Model A Program
The Model A program, shown in Listing 11-1, is designed to execute queries that do not return a ResultSet. It is organized into the ModelA() constructor and the main(). The main() creates an instance of the ModelA object called sql1, which causes the execution of the constructor.
The constructor begins by creating three String objects. These are url, userId, and password. The url is assigned the URL of the database. In this example, the JDBC/ODBC bridge is used to connect to the CustomerInformation database. Of
course, the URL used in your J2ME application replaces the URL in Model A and will represent the JDBC driver and database that are specific to your J2ME component. Likewise, the database used with your J2ME application may or may not require a user ID and password. If these are required, they will undoubtedly be different from the user ID and password used in Model A. The first try {} block in Model A uses the Class.forName() method to load the JDBC driver into the Java Virtual Machine (JVM). You will probably need to replace the driver used in Model A with the appropriate JDBC driver for your J2ME component. Once the JDBC driver is loaded, the getConnection() method is called to open a connection to the database using the url, userID, and password String objects. The getConnection() method returns a Connection object called database, which is declared above the definition of the constructor. Two catch {} blocks follow the first try {} block and are used to trap exceptions that occur while the driver is loaded and a connection is established with the database. You can change the text of messages shown in Model A that are displayed when an exception is caught to a message that is more conducive to your programming style and J2ME requirements. The second try {} block in Model Acontains a comment. You should replace this try block with the try {} that appears in the section of this chapter that describes the task you want performed by the J2ME component. You’ll notice as you read through this chapter that the try {} block within each section references a Statement object called DataRequest, which is declared as a private member of the ModelA class.
The second try {} block is followed by a catch {} block that traps SQL exceptions that occur during the execution of the query in the second try {} block. Anytime a database connection is closed, you should first check for a null value on
the Connection object. If the connection is no longer valid, the close() method will throw
NullPointerException. import java.sql.*;
public class ModelA
{
private Connection Database;
private Statement DataRequest;
public ModelA ()
{
String url = "jdbc:odbc:CustomerInformation";
String userID = "jim";
String password = "keogh";
try {
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver");
Database = DriverManager.getConnection(url,userID,password);
}
catch (ClassNotFoundException error) {
System.err.println("Unable to load the JDBC/ODBC bridge." + error);
System.exit(1);
}
catch (SQLException error) {
System.err.println("Cannot connect to the database. "+ error);
if(Database != null) {
try {
Database.close();
}
catch(SQLException er){}
}
System.exit(2);
}
try {
// insert example code here
}
catch ( SQLException error ) {System.err.println("SQL error." + error);
if(Database != null) {
try {
Database.close();
}
catch(SQLException er){}
}
System.exit(3);
}if(Database != null) {
try {
Database.close();
}
catch(SQLException er){}
}
}
public static void main ( String args [] )
{
final ModelA sql1 = new ModelA ();
System.exit ( 0 ) }
}
Model B Program
The Model B program, shown in Listing 11-2, is designed for use by J2ME components that retrieve information from a database. Notice that the Model B program is similar to the Model A program in that the constructor definition (except for the name of the constructor) and the main() are identical. Therefore, refer to the previous section for a description of the constructor and main().;
Model B also contains DisplayResults() and DownRow(). DisplayResults() is passed the ResultSet returned in the second try {} block, which is used to move the virtual cursor to the first row of the ResultSet using the next() method (see Chapter 6). The next() returns a boolean value that is assigned to the Records variable. The if statement evaluates the value of Records and displays a “No data returned” message if Records contains a false value indicating that there isn’t any data in the row.
However, a true value causes the do…while loop to execute, which is where a call to DownRow() is made, passing it the ResultSet. DownRow() retrieves data stored in columns of the ResultSet and assigns those values to variables. Each section of this chapter that discusses a task that retrieves data from a database has its own DownRow(). Therefore,
you need to replace the DownRow() in Model B with the DownRow() that is associated with the task that you want performed by the J2ME component.
import java.sql.*;
public class ModelB
{
private Connection Database;
private Statement DataRequest;
private ResultSet Results;
public ModelB ()
{
String url = "jdbc:odbc:CustomerInformation";
String userID = "jim";
String password = "keogh";
try {
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver");
Database = DriverManager.getConnection(url,userID,password);
}
catch (ClassNotFoundException error) {
System.err.println("Unable to load the JDBC/ODBC bridge." + error);
System.exit(1);
}
catch (SQLException error) {
}
System.err.println("Cannot connect to the database." + error);
System.exit(2);
try {
// Enter example code here
}
catch ( SQLException error ){
System.err.println("SQL error." + error);
if(Database != null) {
try {
Database.close();
}
catch(SQLException er){}
}
System.exit(3);
}
if(Database != null) {}
try {
Database.close();
}
catch(SQLException er){}
}
private void DisplayResults (ResultSet DisplayResults)
throws SQLException
{
boolean Records = DisplayResults.next();
if (!Records ) {
System.out.println( "No data returned");
return;
}
try {
do {
DownRow( DisplayResults) ;
} while ( DisplayResults.next() );
}
catch (SQLException error ) {
System.err.println("Data display error." + error);
if(Database != null) {
try {
Database.close();
}
catch(SQLException er){}
}
System.exit(4);
}
}
private void DownRow ( ResultSet DisplayResults )
throws SQLException
{
//Enter new DownRow() code here
}
public static void main ( String args [] )
{
final ModelB sql1 = new ModelB ();
System.exit(0);
}
}
Tables: A database design is used as the basis for building tables and indexes that make up a database. Tables and indexes are created using a query written using SQL. Typically, the database administrator writes and executes the query that creates tables and indexes, although it is possible to have a J2ME application or a Java application execute the same query to create tables and indexes.
Create a Table
You create a table by formatting an SQL query. The query contains the CREATE TABLE SQL statement that contains the name of the table
try {
String query = "CREATE TABLE CustomerAddress (" + " CustomerNumber CHAR(30), " + " CustomerStreet CHAR(30),
" + " CustomerCity CHAR(30), " + " CustomerZip CHAR(30))";
DataRequest = Database.createStatement();
DataRequest.execute(query);
DataRequest.close();
}
Requiring Data in a Column
There are business rules that require a value in specific columns of a row, such as the columns that contain a customer’s name. You can require the DBMS to reject rows that are missing a value in specific columns by using the NOT NULL clause in the query when the table is created. Listing 11-4 illustrates how the NOT NULL clause is used in a query.
try {
String query = "CREATE TABLE CustomerAddress ( " +
"CustomerNumber CHAR(30) NOT NULL," +
"CustomerStreet CHAR(30) NOT NULL," +
"CustomerCity CHAR(30) NOT NULL," +
"CustomerZip CHAR(30) NOT NULL)";
DataRequest = Database.createStatement();
DataRequest.execute (query );
DataRequest.close();
}
Setting a Default Value for a Column
The DBMS can enter a default value into a column automatically if the column is left empty whenever a new row is inserted into the table. You determine the value entered by the DBMS by creating a default value when you create the table. Any value can be used as the default value as long as the value conforms to the data type and size of the column.
A default value is assigned to a column when you create the table by using the DEFAULT clause. The DEFAULT clause is followed in the query by the value that the DBMS will place in the column if the column is empty in the incoming row
try {
String query = "CREATE TABLE CustomerAddress ( " +
"CustomerNumber CHAR(30) NOT NULL," +
"CustomerStreet CHAR(30) NOT NULL," +
"CustomerCity CHAR(30) NOT NULL," +
"CustomerZip CHAR(30) NOT NULL DEFAULT '07660')";
DataRequest = Database.createStatement();
DataRequest.executeUpdate (query );
DataRequest.close();
}
Drop a Table
A developer may have the right to remove a table, but this is usually reserved for the development environment only. The decision to drop a table shouldn’t be made lightly because once a table is dropped you cannot recover it. Instead, the table must be re-created and the data reinserted into the table. In addition to losing data elements stored in the table, dropping a table may affect the integrity of the database and tables that relate to values in the dropped table.
try {
String query = new String ("DROP TABLE CustomerAddress");
DataRequest = Database.createStatement();
DataRequest. execute(query);
DataRequest.close();
}
Indexing
The database schema describes indexes along with tables that are used in the database. Writing a query, as illustrated in the next section, creates an index. Likewise, a query is used to remove an existing index, which is referred to as dropping an index. Developers are typically permitted to create or drop indexes within the development environment, and only the database administrator has rights to do the same in the production environment, although these rights may vary according to IT department policy. Executing the query to create or drop an index follows procedures similar to those for
creating and dropping a table. That is, the query can be executed within or outside a J2ME application or Java application. Listings in the following sections should be used with the Model A program since no ResultSet is returned when the query is executed.
Create an Index
An index is created by using the CREATE INDEX statement in a query, The CREATE INDEX statement contains the name of the index and any modifier that describes to the DBMS the type of index to be created. In addition, the
CREATE INDEX statement uses the ON clauses to identify the name of the table and the name of the column whose value is used for the index.
try {
String query = "CREATE UNIQUE INDEX CustNum " +
"ON CustomerAddress (CustomerNumber)";
DataRequest = Database.createStatement();
DataRequest.execute (query);
DataRequest.close();
}
Designating a Primary Key
A primary key (see Chapter 9) can be designated when a table is created by using the Primary Key modifier, as illustrated in Listing 11-8. This example contains a query that creates a table, as described previously in this chapter, and one column within the table is designated the primary key. You’ll remember that the primary key uniquely identifies each row in a table. Therefore, the column or columns chosen as the primary key must have unique values. That is, the column cannot contain duplicate values.
try {
String query = "Create Table Orders ( " +
"OrderNumber CHAR(30) NOT NULL, " +
"CustomerNumber CHAR(30), " +
"ProductNumber CHAR(30), " +
"CONSTRAINT ORDERS_PK PRIMARY KEY (OrderNumber))";
DataRequest = Database.createStatement();
DataRequest. execute(query);
DataRequest.close();
}
Creating a Secondary Index
Asecondary index is created by using the CREATE INDEX statement in a query without the use of the UNIQUE modifier. This means that a secondary index can have duplicate values. In this example, the index is called CustZip, and its key is the CustomerZip column of the CustomerAddress table.
try {
String query = new String ("CREATE INDEX CustZip " +
"ON CustomerAddress (CustomerZip) ");
DataRequest = Database.createStatement();
DataRequest. execute(query);
DataRequest.close(); }
Drop an Index
An existing index can be removed from the database by using the DROP INDEX statement, which is similar in construction to the DROP TABLE statement discussed previously in this chapter. In this example, the CustName index that is associated with the Customers table is dropped.
try {
String query = new String("DROP INDEX CustName ON Customers ");
DataRequest = Database.createStatement();
DataRequest. execute(query);
DataRequest.close();
}
Inserting Data into Tables
Once a database, tables, and indexes are created, a J2ME application can insert a new row into a table. In the next several sections of this chapter, you’ll learn the technique for writing a query that inserts data into all or some of the columns of a new row. As you learned in Chapter 6, the executeUpdate() method is used to execute a query
CustomerNumber, number
Create table it (FirstName, VARCHAR, 50
LastName, VARCHAR, 50
DateOfFirstOrder, date)
Insert a Row
The INSERT INTO statement is used to insert a new row into a table. The INSERT INTO statement contains the name of the table into which the row is to be inserted and the name of the columns in which values are inserted. The VALUES clause is used to define the values to be placed into the row.
try {
String query = "INSERT INTO Customers " +
" (CustomerNumber, FirstName, LastName, DateOfFirstOrder) " +
" VALUES (1,'Mary','Smith','10/10/2001') ";
DataRequest = Database.createStatement();
DataRequest.executeUpdate (query);
DataRequest.close();
}
Insert the System Date into a Column
Sometimes business rules require that the current date be placed into a column of a new row. For example, the system date is usually used as the date of a new order. You can place the system date into a column by calling the CURRENT_DATE function. This is not implemented in all versions of DBMSs. If you’re using Microsoft Access for your
DBMS, for example, you use NOW. Oracle uses SYSDATE, and DB2 uses CURRENT DATE (no underscore).
The CURRENT_DATE function directs the DBMS to use the system date of the server as the value for the column. In this example, the CURRENT_DATE function is called to provide the value for the DateOfFirstOrder column in the new row of the Customers table.
try {
String query = new String (
"INSERT INTO Customers (CustomerNumber ,FirstName, "+
" LastName, DateOfFirstOrder )" +
" VALUES ( 4,'Mary','Jones', CURRENT_DATE)");
DataRequest = Database.createStatement();
DataRequest.executeUpdate (query );
DataRequest.close();
}
Insert the System Time into a Column
Call the CURRENT_TIME() function whenever a column requires the current time. The CURRENT_TIME() function is called by the DBMS when the query is processed, and it returns the current time of the server. Listing 11-14 illustrates how to call the CURRENT_TIME() function within a query.
try {
String query = new String (
"INSERT INTO Customers (CustomerNumber ,FirstName, "+
" LastName, TimeOfFirstOrder ) " +
" VALUES ( 2,'Bob','Jones', CURRENT_TIME() )") ;
DataRequest = Database.createStatement();
DataRequest.executeUpdate(query );
DataRequest.close();
}
Insert a Timestamp into a Column
Atimestamp consists of both the current date and time and is used in applications where both date and time are critical to the operation of the business. For example, Wall Street firms timestamp orders as they arrive because they must correlate with the market price of stocks at precisely the date and time the order is processed.
try {
String query = new String (
"INSERT INTO Customers (CustomerNumber ,FirstName, "+
" LastName, FirstOrder ) " +
" VALUES ( 2,'Bob','Jones', CURRENT_TIMESTAMP()) )";
DataRequest = Database.createStatement();
DataRequest.executeUpdate (query );
DataRequest.close(); }
Insert a Timestamp into a Column
A timestamp consists of both the current date and time and is used in applications where both date and time are critical to the operation of the business. For example, Wall Street firms timestamp orders as they arrive because they must correlate with the market price of stocks at precisely the date and time the order is processed.
try {
String query = new String (
"INSERT INTO Customers (CustomerNumber ,FirstName, "+
" LastName, FirstOrder ) " +
" VALUES ( 2,'Bob','Jones', CURRENT_TIMESTAMP()) )";
DataRequest = Database.createStatement();
DataRequest.executeUpdate (query );
DataRequest.close(); }
Selecting Data from a Table
Retrieving information from a database is the most frequently used routine of J2ME components that interact with a database. Some developers feel that retrieving information is one of the most complex routines to write because there are various ways information can be retrieved. The following sections illustrate the various techniques to retrieve data
using the executeQuery() method. As you learned in Chapter 6, the executeQuery() method returns a ResultSet. Therefore, you’ll need to use the Model B program with these code segments
· FirstName, VARCHAR, 50
· LastName, VARCHAR, 50
· Street, VARCHAR, 50
· City, VARCHAR, 50
· State, VARCHAR, 2
· ZipCode, VARCHAR, 12
Select All Data from a Table
The SELECT statement is used to retrieve data from a table, In this example, all the columns of all the rows of the Customers table are returned in the ResultSet by the DBMS after the query is processed. As you’ll recall from the beginning of this chapter, the DisplayResults() method calls the DownRow() method, which extracts data from the current row of the ResultSet and displays the row on the screen. In this example, the DownRow() method is passed the ResultSet object om the DisplayResults() method, as described at the beginning of this chapter. The DownRow() method then declares String objects that are assigned the value of each column in the ResultSet. There is also a String object called printrow, which is assigned all the other String objects and is used to display the values on the screen. The getString() method is called to gather the value of a specific column in the ResultSet. The getString() method returns the value of the column, which is assigned to a String object.
try {
String query = new String ("SELECT " +
" FirstName, LastName, Street, City, State, ZipCode " +
" FROM Customers");
DataRequest = Database.createStatement();
Results = DataRequest.executeQuery (query);
DisplayResults (Results);
DataRequest.close(); }
private void DownRow ( ResultSet DisplayResults ) throws SQLException
{
String FirstName= new String(); String LastName= new String();
String Street= new String(); String City = new String();
String State = new String(); String ZipCode= new String();
String printrow;
FirstName = DisplayResults.getString ( 1 ) ; LastName = DisplayResults.getString ( 2 ) ;
Street = DisplayResults.getString ( 3 ) ; City = DisplayResults.getString ( 4 ) ;
State = DisplayResults.getString ( 5 ) ; ZipCode = DisplayResults.getString ( 6 ) ;
printrow = FirstName + " " + LastName + " " +City + " " + State + " " + ZipCode;
System.out.println(printrow);
}
Request One Column
You can specify a column that you want returned from the table by using the column name in the SELECT statement, LastName column of all the rows in the Customers table is returned in the ResultSet.
try {
String query = new String ("SELECT LastName FROM Customers");
DataRequest = Database.createStatement();
Results = DataRequest.executeQuery (query);
DisplayResults (Results);
DataRequest.close();
}
private void DownRow ( ResultSet DisplayResults )throws SQLException
{
String LastName= new String();
LastName = DisplayResults.getString(1);
System.out.println(LastName);
} Request Multiple Columns
try {
String query = new String (
"SELECT FirstName, LastName FROM Customers");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close();
}
private void DownRow ( ResultSet DisplayResults )throws SQLException
{
String FirstName= new String(); String LastName= new String();
String printrow; FirstName = DisplayResults.getString ( 1 ) ; LastName = DisplayResults.getString ( 2 ) ;
printrow = FirstName + " " + LastName; System.out.println(printrow);
}
Request Rows
Specific rows can be retrieved from a column by using the WHERE clause in conjunction with the SELECT statement. The WHERE clause contains an expression that is used by the DBMS to identify rows that should be returned in the ResultSet. Any logical expression can be used to include or exclude rows based on values in columns of a row.
try {
String query = new String ("SELECT " +
" FirstName, LastName, Street, City, State, ZipCode " +
" FROM Customers " +
" WHERE LastName = 'Jones' ");
DataRequest = Database.createStatement();
Results = DataRequest.executeQuery (query);
DisplayResults (Results);
DataRequest.close();
}
Request Rows and Columns
try {
String query = new String("SELECT FirstName, LastName " +
" FROM Customers " +
" WHERE LastName = 'Jones' ");
DataRequest = Database.createStatement();
Results = DataRequest.executeQuery (query);
DisplayResults (Results);
DataRequest.close(); }
AND, OR, and NOT Clauses
The WHERE clause in a SELECT statement can evaluate values in more than one column of a row by using the AND, OR, and NOT clauses to combine expressions. As you’ll learn in the next three sections, compound expressions in the WHERE clause are individually evaluated, and then the results of these evaluations are further evaluated based on the AND, OR, and NOT clauses in the WHERE clause. AND Clause The purpose of the AND clause is to join two subexpressions together to form one compound expression. The AND clause tells the DBMS that the boolean value of both
subexpressions must be true for the compound expression to be true. If the compound expression is true, the current row being evaluated by the DBMS is returned to your program.
try {
String query = new String ("SELECT FirstName, LastName " +
" FROM Customers " +
" WHERE LastName = 'Jones' " +
" AND FirstName = 'Bob'");
DataRequest = Database.createStatement();
Results = DataRequest.executeQuery (query);
DisplayResults (Results);
DataRequest.close(); }
OR Clause
The OR clause is used to create a compound expression using two subexpressions in the same way as the AND clause. However, the OR clause tells the DBMS that the compound expression evaluates to a boolean true if either of the two subexpressions evaluates to a boolean true.
try {
String query = new String ("SELECT FirstName, LastName " +
" FROM Customers " +
" WHERE FirstName = 'Mary' " +
" OR FirstName = 'Bob'");
DataRequest = Database.createStatement();
Results = DataRequest.executeQuery (query);
DisplayResults (Results);
DataRequest.close(); }
NOT Clause
The NOT clause reverses the logic of the subexpression contained in the WHERE NOT clause. If the subexpression evaluates to a boolean true, the NOT clause reverses the logic to return a boolean false. In contrast, if the subexpression evaluates to a Boolean false, the compound expression evaluates to a boolean true.
try {
String query = new String( "SELECT FirstName, LastName " +
"FROM Customers " +
"WHERE NOT FirstName = 'Mary' " );
DataRequest = Database.createStatement();
Results = DataRequest.executeQuery (query);
DisplayResults (Results);
DataRequest.close(); }
Join Multiple Compound Expressions
As just discussed, the AND and OR clauses are used to link together two or more subexpressions, which results in a compound expression. There can be multiple compound expressions within a WHERE clause expression.
WHERE FirstName = 'Bob' AND LastName = 'Smith AND (Dept = '42' OR Dept = '45')
Equal and Not Equal Operators
The equal and not equal operators are used to determine whether the value in the WHERE clause expression is or isn’t in the specified column. You’ve been using the equal operator throughout examples in this chapter. The next section illustrates the use of the not equal operator.
Not Equal Operator
The not equal operator is used in a WHERE clause expression or subexpression to identify rows that should not be returned by the DBMS .
try {
String query = new String ("SELECT " +
"FirstName, LastName, Street, City, State, ZipCode, Sales " +"FROM Customers " +"WHERE NOT Sales = 50000 " );
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close();
Database.close();
}
private void DownRow ( ResultSet DisplayResults )throws SQLException
{
String FirstName= new String(); String LastName= new String();
String Street= new String(); String City = new String();
String State = new String(); String ZipCode= new String();
long Sales; String printrow;
FirstName = DisplayResults.getString ( 1 ) ;
LastName = DisplayResults.getString ( 2 ) ;
Street = DisplayResults.getString ( 3 ) ;
City = DisplayResults.getString ( 4 ) ;
State = DisplayResults.getString ( 5 ) ;
ZipCode = DisplayResults.getString ( 6 ) ;
Sales = DisplayResults.getLong ( 7 ) ;
printrow = FirstName + " " + LastName + " " +
City + " " + State + " " + ZipCode + " " + Sales;
System.out.println(printrow);
}
Less Than and Greater Than Operators
The less than and greater than operators direct the DBMS to assess whether or not the value in the specified column of the current row is less than or greater than the value in the WHERE clause expression. Keep in mind that a value in a column that equals the value in the WHERE clause expression is evaluated as a boolean false. Therefore the row containing that value isn’t returned in the ResultSet. The value in the column must be less than or greater than but not equal to the value in the WHERE clause expression.
Less Than Operator
try {
String query = new String ("SELECT " +
"FirstName, LastName, Street, City, State, ZipCode, Sales " + " FROM Customers " + " WHERE Sales < 50000 " );
DataRequest = Database.createStatement();
Results = DataRequest.executeQuery (query);
DisplayResults (Results);
DataRequest.close(); }
Greater Than Operator
try {
String query = new String ("SELECT " +
"FirstName, LastName, Street, City, State, ZipCode, Sales " +"FROM Customers " +"WHERE Sales > 40000 " );
DataRequest = Database.createStatement();
Results = DataRequest.executeQuery (query);
DisplayResults (Results);DataRequest.close(); }
Less Than or Equal to and Greater Than or Equal To
Less Than or Equal To
try {
String query = new String ("SELECT " + "FirstName, LastName, Street, City, State, ZipCode, Sales " + "FROM Customers " +"WHERE Sales <= 50000 " );
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close();
}
Greater Than or Equal To
try {
String query = new String ("SELECT " +"FirstName, LastName, Street, City, State, ZipCode, Sales " +"FROM Customers " +"WHERE Sales >= 50000 ");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close();}
Between Operator
try {
String query = new String("SELECT " +"FirstName, LastName, Street, City, State, ZipCode, Sales " +"FROM Customers " +
"WHERE Sales BETWEEN 20000 AND 39999 " );
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close();
}
LIKE Operator
The LIKE operator directs the DBMS to return a row in the ResultSet if a value in a specified column partially matches the value of the WHERE clause expression. The WHERE clause expression must include a character that is an exact match and a wildcard
¦Underscore (_) A single-character wildcard character. For example, if you are unsure whether the customer’s last name is Anderson or Andersen, use the underscore in place of the character that is in question, as in Anders_n.
Percent (%) A multicharacter wildcard character used to match any number of characters. For example, Smi% is used to match a value of a column where the first three characters are Smi followed by any other character(s).
try {
String query = new String ("SELECT " +
"FirstName, LastName, Street, City, State, ZipCode, Sales " + "FROM Customers " +
"WHERE LastName LIKE 'Smi%' ");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close(); }
IS NULL Operator
The IS NULL operator is used to determine whether a specified column does not contain any value. You’ll find this useful whenever you need to identify rows in a column that are missing information. Note that the number zero or a space are not NULL values. For example, a zero value in the Sales column of a table is a real value. Likewise, a column that contains a space isn’t NULL because a space is a valid ASCII character. NULL is void of any value and occurs when a row is inserted into a table without having a value, or the value is explicitly set to NULL.
try {
String query = new String ("SELECT " +
"FirstName, LastName, Street, City, State, ZipCode, Sales " + "FROM Customers " +
"WHERE State IS NULL ");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close(); }
DISTINCT Modifier
The SELECT statement returns all rows in a table unless a WHERE clause is used to exclude specific rows. However, the ResultSet includes duplicate rows unless a primary index is created for the table or only unique rows are required in the table,
try {
String query = new String ("SELECT DISTINCT " +
"FirstName, LastName, Street, City, State, ZipCode, Sales " + "FROM Customers ");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close(); }
IN Modifier
The IN modifier is used to define a set of values used by the DBMS to match values in a specified column. The set can include any number of values and appear in any order.
Let’s say that you want a list of customers who purchase three products. You place the three product identification numbers in a set; then tell the DBMS to search the order table for orders containing the product numbers in the set. If the product numbers match, customer information associated with the row is returned to your program.
try {
String query = new String ("SELECT " + "FirstName, LastName, Street, City, State, ZipCode, Sales " + " FROM Customers " +" WHERE Sales IN (20000, 30000, 40000) " );
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close();}
NOT IN Modifier
The NOT IN modifier is similar to the IN modifier, except the NOT IN modifier reverses the logic. That is, it identifies a set of values that shouldn’t match rows returned to the program. Listing 11-38 shows how the NOT IN modifier is used in a program. In this
try {
String query = new String ("SELECT " + "FirstName, LastName, Street, City, State, ZipCode, Sales " + " FROM Customers " + " WHERE Sales NOT IN (20000, 30000, 40000) " );
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query); DisplayResults (Results);
DataRequest.close();}
Metadata
Metadata is data that describes data, which you learned about in Chapter 6. Metadata is returned with the ResultSet object and can be extracted from the ResultSet object by creating a ResultSetMetaData. Techniques for doing this are described in the next several sections of this chapter. Metadata can be used in a J2ME application for various purposes,
such as to display the column name of a column and determine the data type of a column. The most commonly used metadata are.
¦Column name
¦ Column number
¦ Column data type
¦ Column width
Number of Columns in ResultSet
private void DownRow ( ResultSet DisplayResults )throws SQLException
{
ResultSetMetaData metadata = DisplayResults.getMetaData ();
int NumberOfColumns;
String printrow;
NumberOfColumns = metadata.getColumnCount ();
System.out.println("Number Of Columns: " + NumberOfColumns);}
Data Type of Column
private void DownRow ( ResultSet DisplayResults ) throws SQLException
{
ResultSetMetaData metadata = DisplayResults.getMetaData ();
String ColumnType = new String();
String printrow;
ColumnType = metadata.getColumnTypeName ( 9 );
System.out.println("Column Type: " + ColumnType ); }
Name of Column
Retrieving the column name from the metadata uses a process similar to copying the data type of a column from the metadata the getColumnLabel() method is used to copy the column name from a specific column and assign the column name to the String object ColumnName. The number of the column in the ResultSet is passed to the getColumnLabel() method.
private void DownRow ( ResultSet DisplayResults ) throws SQLException
{
ResultSetMetaData metadata = DisplayResults.getMetaData ();
String ColumnName = new String();
String printrow;
ColumnName = metadata.getColumnLabel (9) ;
System.out.println("Column Name: " + ColumnName); }
Column Size
The column size, also referred to as the column width, is called the display size and represents the number of characters needed to display the maximum value that might be stored in the column.
private void DownRow ( ResultSet DisplayResults ) throws SQLException
{
ResultSetMetaData metadata = DisplayResults.getMetaData ();
int ColumnWidth;
String printrow;
ColumnWidth = metadata.getColumnDisplaySize ( 9 ) ;
System.out.println("Column Width:" + ColumnWidth); }
Updating Tables
Update Row and Column
try {
String query = new String("UPDATE Customers " +
"SET Street = '5 Main Street' " +
" WHERE FirstName = 'Bob'");
DataRequest = Database.createStatement();
DataRequest.executeUpdate (query);
DataRequest.close(); }
Update Multiple Rows
Multiple rows of a table can be updated by formatting the WHERE clause expressions to include criteria that qualify multiple rows for the update. Four common WHERE clause expressions are used to update multiple rows of a table:
The IN test The WHERE clause expression contains multiple values in the IN clause that must match the value in the specified column for the update to occur in the row.
The IS NULL test Rows that don’t have a value in the specified column are updated when the IS NULL operator is used in the WHERE clause expression.
The comparison test The WHERE clause expression contains a comparison operator, as described previously in this chapter, that compares the value in the specified column with a value in the WHERE clause expression. All rows A query can direct the DBMS to update the specified column in all rows of a table by excluding the WHERE clause in the query.
IN Test
try {
String query = new String ("UPDATE Customers " + "SET Discount = 25 " + "WHERE Discount IN (12,15)");
DataRequest = Database.createStatement(); DataRequest.executeUpdate (query);
DataRequest.close(); }
IS NULL Test
try {
String query = new String ("UPDATE Customers " + "SET Discount = 0 " + "WHERE LastName IS NULL ");
DataRequest = Database.createStatement(); DataRequest.executeUpdate (query);
DataRequest.close(); }
Update Based on Values in Column
try {
String query = new String ("UPDATE Customers " + "SET Discount = 20 " + "WHERE Discount > 20 ");
DataRequest = Database.createStatement(); DataRequest.executeUpdate (query);
DataRequest.close(); }
Update Every Row
try {
String query = new String ("UPDATE Customers " + "SET Discount = 0 ");
DataRequest = Database.createStatement(); DataRequest.executeUpdate (query);DataRequest.close(); }
Update Multiple Columns
try {
String query = new String ("UPDATE Customers " +"SET Discount = 12, Street = 'Jones Street'" + "WHERE LastName = 'Jones'");
DataRequest = Database.createStatement(); DataRequest.executeUpdate (query);
DataRequest.close(); }
Update Using Calculations
try {
String query = new String ("UPDATE Customers " + "SET DiscountPrice = Price * ((100 - Discount) / 100) ");
DataRequest = Database.createStatement(); DataRequest.executeUpdate (query);
DataRequest.close(); }
Deleting Data from a Table
Deleting rows is necessary to purge erroneous information from the database and to remove information that is no longer needed. However, you must build in safeguards to assure that critical information isn’t inadvertently deleted from the database. Before you delete a row from a table, you must be certain that other tables are not negatively affected. The next section illustrates the technique for removing a row from a table by using the DELETE FROM statement. Multiple rows can be deleted by including a WHERE clause in the DELETE FROM statement, as described in detail in the “Updating Tables” Delete a Row from a Table The DELETE FROM statement includes the name of the table and a WHERE clause
containing an expression that identifies the row or rows to remove from the table.
try {
String query = new String ("DELETE FROM Customers " + "WHERE LastName = 'Jones' and FirstName = 'Tom'");
DataRequest = Database.createStatement(); DataRequest.executeUpdate (query); DataRequest.close();
}
Joining Tables
how rows of data elements that are placed in tables are related to each other by linking rows using a common value in each row of two tables. Linking rows is called joining tables. Tables are joined in a query using a two-step process. First, both tables that are being joined must be identified in the FROM clause, where tables are listed one after the other and are separated by a comma.
WHERE CustomerNumber = CustNum
Join Two Tables
try {
String query = new String ( "SELECT FirstName, LastName, City, State, ZipCode " + "FROM Customers, ZipCode " +
"WHERE Zip = ZipCode")
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close();
}
private void DownRow ( ResultSet DisplayResults )
throws SQLException
{
String FirstName= new String();
String LastName= new String();
String Street= new String();
String City = new String();
String State = new String();
String ZipCode= new String();
String printrow;
FirstName = DisplayResults.getString ( 1 ) ;
LastName = DisplayResults.getString ( 2 ) ;
Street = DisplayResults.getString ( 3 ) ;
City = DisplayResults.getString ( 4 ) ;
State = DisplayResults.getString ( 5 ) ;
ZipCode = DisplayResults.getString ( 6 ) ;
printrow = FirstName + " " + LastName + " " +
City + " " + State + " " + ZipCode + " " + Sales + " " + Profit;
System.out.println(printrow);
}
Parent-Child Join
Aparent-child join is used to join tables that have a parent-child relationship. This means rows in the parent table must exist for rows in the child table to exist.
try {
String query = new String (" SELECT OrderNumber, ProductName " +" FROM Orders, Products " + " WHERE ProdNumber = ProductNumber");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close();
}
private void DownRow ( ResultSet DisplayResults )
throws SQLException
{
int OrderNum, ProdNum;
String printrow = new String();
OrderNum = DisplayResults.getInt ( 1 ) ;
ProdNum = DisplayResults. getInt ( 2 ) ;
printrow = OrderNum + " " + ProdNum;
System.out.println(printrow);
}
Multiple Comparison Join
The WHERE clause expression used to join tables can be a compound expression consisting of two or more subexpressions. Each is evaluated separately, as described previously in this chapter. A compound expression is used to specify more than one selection criteria used to join two tables, In this example, the DBMS is directed to join rows of the Products table and rows of the Orders table. There are two components of the subexpression in this example. The first requires the DBMS to match product numbers in both tables. The other subexpression requires that the value in the
Quantity column is greater than 2.
try {
String query = " SELECT OrderNumber, ProductName, Quantity " +" FROM Orders, Products " +" WHERE ProdNumber = ProductNumber " + " AND Quantity > 2";
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close(); }
private void DownRow ( ResultSet DisplayResults )
throws SQLException
{
int OrderNum, ProdNum, Quantity;
String printrow = new String();
OrderNum = DisplayResults.getInt ( 1 ) ; ProdNum = DisplayResults. getInt ( 2 ) ;
Quantity = DisplayResults. getInt ( 3 ) ; printrow = OrderNum + " " + ProdNum + " " + Quantity;
System.out.println(printrow); }
Multitable Join
More than two tables can be joined together by using the name of each table in the join in the FROM clause and by defining the join with the appropriate column names in the WHERE clause expression.
try {
String query = new String (
"SELECT FirstName, LastName,OrderNumber, ProductName, Quantity " +" FROM Customers, Orders, Products " +
" WHERE ProdNumber = ProductNumber " +" AND CustNumber = CustomerNumber");
DataRequest = Database.createStatement();Results = DataRequest.executeQuery (query);
DisplayResults (Results);DataRequest.close(); }
private void DownRow ( ResultSet DisplayResults )
throws SQLException
{
int OrderNum, Quantity; String FirstName = new String(); String LastName = new String();
String ProductName = new String(); String printrow = new String(); FirstName = DisplayResults.getString ( 1 ) ;
LastName = DisplayResults.getString ( 2 ) ; OrderNum = DisplayResults.getInt ( 3 ) ;
ProductName = DisplayResults.getString ( 4 ) ; Quantity = DisplayResults. getInt ( 5 ) ;
printrow = FirstName + " " +LastName + " " +OrderNum + " " ProductName + " " +Quantity ;System.out.println(printrow);}
Create a Column Name Qualifier
Column names should reflect the kind of data element stored in the column, as you learned in Chapter 5. However, there is likely to be a conflict when two or more tables use the same column name to store the data element. This is the case when both the Products table and the Orders table contain product numbers.
try {
String query = new String ("SELECT Customers.CustNumber, " + " FirstName, LastName, OrderNumber, " +
" ProductName, Quantity " + " FROM Customers, Orders, Products " + " WHERE ProdNumber = ProductNumber " +
" AND Customers.CustomerNumber = Orders.CustomerNumber"); DataRequest = Database.createStatement();
Results = DataRequest.executeQuery (query); DisplayResults (Results);
DataRequest.close(); }
private void DownRow ( ResultSet DisplayResults )
throws SQLException
{
int CustomerNumber, OrderNum, Quantity;
String FirstName = new String(); String LastName = new String(); String ProductName = new String();
String printrow = new String(); CustomerNumber = DisplayResults. getInt ( 1 ) ;
FirstName = DisplayResults.getString ( 2 ) ; LastName = DisplayResults.getString ( 3 ) ;
OrderNum = DisplayResults.getInt ( 4 ) ; ProductName = DisplayResults.getString ( 5 ) ;
Quantity = DisplayResults. getInt ( 6 ) ; printrow = CustomerNumber + " " + FirstName + " " + LastName + " " +OrderNum + " " +ProductName + " " +Quantity ;System.out.println(printrow);
}
Create a Table Alias
A query can be made readable by using table aliases. A table alias is an abbreviation for the name of the table that is used in place of the table name in the join and in the SELECT statement. Listing 11-61 shows you how to create and use a table alias in a query. Following each table name in the FROM clause is a letter used as the table alias. The table alias is
used in place of the table name in the column name qualifier. Any letter or combination of letters can be used as a table alias, but you should makethe table alias:
· With as few letters as possible to save space in the query string
· Representative of the table name, such as the first letter(s) of the name
· Unique and not a duplicate of another table name, table alias, or column name
try {
String query = new String ("SELECT c.CustNumber , " + " c.FirstName, c.LastName, o.OrderNumber, " +
" p.ProductName, o.Quantity " + " FROM Customers c, Orders o, Products p" + " WHERE o.ProdNumber = p.ProductNumber " + " AND c.CustomerNumber = o.CustomerNumber");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close();}
Inner and Outer Joins
The joins shown in previous sections of this chapter link rows that have matching values in the column specified in the join. A row in a table that doesn’t match is excluded from the join. There are two kinds of joins, each of which either excludes or includes rows in both tables of the join that don’t match. These are
· An inner join excludes rows of either table that don’t have a matching value.
· An outer join includes rows of either table that don’t have a matching value.
Inner Join
Joins discussed previously in this chapter use an inner join to include only rows of both tables that have matching values. Unmatched rows are excluded, and therefore those rows are not returned in the ResultSet.
try {
String query = new String (" SELECT FirstName, LastName, ProductName, Quantity " +
" FROM Customers,Orders, Products " + " WHERE ProdNumber = ProductNumber " + " AND Customers.CustNumber = Orders.CustomerNumber");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close(); }
private void DownRow ( ResultSet DisplayResults )
throws SQLException
{
int OrderNum, Quantity;
String FirstName = new String(); String LastName = new String(); String ProductName = new String();
String printrow = new String(); FirstName = DisplayResults.getString ( 1 ) ;
LastName = DisplayResults.getString ( 2 ) ; OrderNum = DisplayResults.getInt ( 3 ) ;
ProductName = DisplayResults.getString ( 4 ) ; Quantity = DisplayResults. getInt ( 5 ) ;
printrow = FirstName + " " +LastName + " " +OrderNum + " " +ProductName + " " +Quantity ;System.out.println(printrow);
}
Outer Join—Left, Right, Full
An outer join occurs when matching and nonmatching rows of either or both tables are contained in the join. There are three kinds of outer joins: Left outer join :All matched and unmatched rows of the first table and matched rows of the second table are included in the join.
Right outer join Matched rows of the first table and matched and unmatched rows of the second table are included in the join. Full outer join Matched and unmatched rows of both tables are includedin the join.
try {
String query = new String (" SELECT FirstName, LastName,OrderNumber " +" FROM Customers LEFT JOIN Orders " +
" ON Customers.CustNumber = Orders.CustomerNumber");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close(); }
private void DownRow ( ResultSet DisplayResults )
throws SQLException
{
int, OrderNum;
String FirstName = new String(); String LastName = new String();
String printrow = new String(); FirstName = DisplayResults.getString ( 1 ) ;
LastName = DisplayResults.getString ( 2 ) ; OrderNum = DisplayResults.getInt ( 3 ) ;
printrow = FirstName + " " + LastName + " " + OrderNum;System.out.println(printrow);
}
Right Outer Join
try {
String query = new String (" SELECT FirstName, LastName,OrderNumber" +" FROM Customers c, Orders o" +
" WHERE c.CustNumber =* o.CustomerNumber");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close(); }
Full Outer Join
try {
String query = new String(" SELECT FirstName, LastName,OrderNumber " +" FROM Customers c, Orders o" +
" WHERE c.CustNumber *=* o.CustomerNumber");
DataRequest = Database.createStatement();Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close();}
Calculating Data
The DBMS can calculate values in a table and return the result of the calculation in the ResultSet by using one of the five built-in calculation functions:
SUM() tallies values in a column passed to the built-in function.
AVG() averages values in a column passed to the built-in function.
MIN() determines the minimum value in a column passed to the built-in function.
MAX() determines the maximum value in a column passed to the built-in function.
COUNT() determines the number of rows in a column passed to the built-in function
SUM()
try {
String query = new String ("SELECT SUM(Quantity) " +"FROM Orders ");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close();
}
private void DownRow ( ResultSet DisplayResults )
throws SQLException
{
long ReturnValue; ReturnValue = DisplayResults.getLong ( 1 ) ;
System.out.println(ReturnValue);
}
AVG()
try {
String query = new String ("SELECT AVG(Quantity) " +"FROM Orders ");
DataRequest = Database.createStatement();Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close();}
MIN()
try {
String query = new String ("SELECT MIN(Quantity) " +"FROM Orders ");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close(); }
MAX()
try {
String query = new String ("SELECT MAX(Quantity) " + "FROM Orders ");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close();}
COUNT()
try {
String query = new String ("SELECT COUNT(Quantity) " +"FROM Orders ");
DataRequest = Database.createStatement();Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close();}
Count All Rows in a Table
The COUNT() built-in function is also used to return the number of rows in a table. This is accomplished by passing the COUNT() built-in function an asterisk rather than the name of a column.
try {
String query = new String ("SELECT COUNT(*) " +"FROM Orders ");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close();}
Retrieve Multiple Counts
try {
String query = new String ("SELECT COUNT(*), COUNT(Quantity) " +"FROM Orders ");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results);DataRequest.close(); }
private void DownRow ( ResultSet DisplayResults )
throws SQLException
{
long TotalRows, TotalValues;
TotalRows = DisplayResults.getLong ( 1 ) ;TotalValues = DisplayResults.getLong ( 2 ) ;
System.out.println("Rows: " + TotalRows + "\nValues: " +TotalValues);
}
Calculate a Subset of Rows
try {
String query = new String (" SELECT COUNT(OrderNumber), AVG(Quantity), SUM(Quantity) " +
" FROM Orders o, customers c " +" WHERE o.CustomerNumber = c.CustNumber");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close(); }
private void DownRow ( ResultSet DisplayResults )
throws SQLException
{
long Orders; long Average; long Sum;
Orders = DisplayResults.getLong ( 1 ) ; Average = DisplayResults.getLong ( 2 ) ; Sum = DisplayResults.getLong ( 3 ) ;
System.out.println("Total Orders = " + Orders); System.out.println("Average Qty = " + Average);
System.out.println("Total Qty = " + Sum); }
Calculate Without Using Built-in Functions
try {
String query = new String (" SELECT StoreNumber, Sales - Estimate " +" FROM Sales ");
DataRequest = Database.createStatement();Results = DataRequest.executeQuery (query);
DisplayResults (Results); DataRequest.close(); }
private void DownRow ( ResultSet DisplayResults )
throws SQLException
{
String Store; long Difference; Store = DisplayResults.getString ( 1 ) ; Difference = DisplayResults.getLong ( 2 ) ;
System.out.println("Store = " + Store); System.out.println("Difference = " + Difference); }
Grouping and Ordering Data
Columns are returned in the ResultSet in the order that the column names appear in the SELECT statement of the query. The order in which rows appear in the ResultSet can be grouped into similar values or sorted in ascending or descending order by using the
GROUP BY clause or the ORDER BY clause, which are discussed in the next few sections of this chapter.
GROUP BY
The GROUP BY clause specifies the name of the column whose values are used to group rows in the ResultSet. Listing 11-81 illustrates this technique. In this example, the StoreNumber column and the sum of the values in the Sales column from the Sales table
are returned. The GROUP BY clause organizes the ResultSet by the value in the StoreNumber column. This means that the ResultSet contains the sum of the Sales column for each StoreNumber.
try {
String query = new String (" SELECT StoreNumber, SUM(Sales) " + " FROM Sales " +" Group By StoreNumber");
DataRequest = Database.createStatement();Results = DataRequest.executeQuery (query);
System.out.println("Store Sales");System.out.println("----- -----");
DisplayResults (Results);DataRequest.close();
}
private void DownRow ( ResultSet DisplayResults )throws SQLException
{
long Store; long Sum; Store = DisplayResults.getLong ( 1 ) ;
Sum = DisplayResults.getLong ( 2 ) ; System.out.println(Store + " " + Sum);
}
Group Multiple Columns
The DBMS can create a subgroup within a group in the ResultSet. For example, the business unit may want to see orders organized by store, and within each store, orders are organized by product. Asubgroup is created by placing the name of the column used for the subgroup as the second column name in the GROUP BY clause of the query. Any number of subgroups can be created, depending on the limitations established by the manufacturer of the DBMS used by your program. Column names in the GROUP BY clause must be separated with a comma.
try {
String query = new String (" SELECT StoreNumber,SalesRepNumber, SUM(Sales) " +
" FROM Sales " +" Group By StoreNumber, SalesRepNumber");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
System.out.println("Store SalesRep Sales");System.out.println("----- -------- -----");
DisplayResults (Results); DataRequest.close(); }
private void DownRow ( ResultSet DisplayResults )throws SQLException
{
long StoreNumber; long SalesRepNumber; long Sum;
StoreNumber = DisplayResults.getLong ( 1 ) ; SalesRepNumber = DisplayResults.getLong ( 2 ) ;
Sum = DisplayResults.getLong ( 3 ) ;
System.out.println(StoreNumber + " " +SalesRepNumber + " " +Sum);
}
Conditional Grouping
The number of rows that are included in a group can be limited by a conditional expression in the query. A conditional expression is similar to the WHERE clause expression discussed previously in this chapter. The DBMS uses the conditional expression to qualify whether or not the current row should be included in any group of the ResultSet. Only rows that meet the condition are returned. A row that doesn’t meet the condition is excluded. The conditional expression is placed in the HAVING clause of the query. The HAVING clause sets the criteria for a row to be included in a group.
try {
String query = new String ("SELECT StoreNumber, SUM(Sales) " + " FROM Sales " +" Group By StoreNumber" +
" HAVING SUM(Sales) > 400");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
System.out.println("Store Sales");System.out.println("----- -----");
DisplayResults (Results);DataRequest.close(); }
private void DownRow ( ResultSet DisplayResults )throws SQLException
{
long StoreNumber; long Sum;StoreNumber = DisplayResults.getLong ( 1 ) ;
Sum = DisplayResults.getLong ( 2 ) ;System.out.println(StoreNumber + " " + Sum);
}
Working with NULL Columns
Sorting Data
The ResultSet can be placed in alphabetical or numerical order by using the ORDER BY clause in the query. In this example, values in the StoreNumber column and values in the Sales column from the Sales tableare returned in the ResultSet. The ORDER BY clause specifies the StoreNumber column, which sorts the value of the StoreNumber column in ascending numerical order
Sorting Data
The ResultSet can be placed in alphabetical or numerical order by using the ORDER BY clause in the query. In this example,
values in the StoreNumber column and values in the Sales column from the Sales table are returned in the ResultSet. The ORDER BY clause specifies the StoreNumber column, which sorts the value of the StoreNumber column in ascending numerical order.
try {
String query = new String ("SELECT StoreNumber, Sales " +" FROM Sales " +
" ORDER BY StoreNumber");
DataRequest = Database.createStatement();Results = DataRequest.executeQuery (query);
System.out.println("Store Sales");System.out.println("----- -----");
DisplayResults (Results); DataRequest.close();
}
private void DownRow ( ResultSet DisplayResults )throws SQLException
{
long StoreNumber; long Sales;
StoreNumber = DisplayResults.getLong ( 1 ) ; Sales = DisplayResults.getLong ( 2 ) ;
System.out.println(StoreNumber + " " + Sales); }
Major and Minor Sort Keys
try {
String query = new String ("SELECT StoreNumber, Sales " +" FROM Sales " +" ORDER BY StoreNumber, Sales");
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
System.out.println("Store Sales"); System.out.println("----- -----");
DisplayResults (Results); DataRequest.close(); }
Descending Sort
try {
String query = new String ("SELECT StoreNumber, Sales "" FROM Sales " +" ORDER BY StoreNumber DESC ");
DataRequest = Database.createStatement();Results = DataRequest.executeQuery (query);
System.out.println("Store Sales");System.out.println("----- -----");
DisplayResults (Results);DataRequest.close();
}
Sorting on Derived Data
try {
String query = new String (" SELECT StoreNumber, (Sales-Estimate) " +" FROM Sales " +" ORDER BY 2 ");
DataRequest = Database.createStatement();Results = DataRequest.executeQuery (query);
System.out.println("Store Sales");System.out.println("----- -----");DisplayResults (Results);
DataRequest.close();}
Subqueries
In the real world, you’ll find that you need to create complex queries that do more than simply request columns from the database. Instead, you might request columns based on the result of another query. You can direct the DBMS to query the result of a query by creating a subquery. A subquery joins two queries to form one complex query, which efficiently identifies data to be included in the ResultSet.
Create a Subquery
A subquery is a query whose results are evaluated by an expression in the WHERE clause of another query, the subquery is defined below the first WHERE clause. The subquery joins rows of the Sales table and the Order table by store number value, as shown in the second WHERE clause. Once joined, the subquery totals the value of the Amount column for each store number and returns the
results to a temporary table.
try {
String query = new String (" SELECT StoreNumber " +" FROM Sales "+ " WHERE Estimate = (SELECT SUM(Amount) " + " FROM Orders, Sales " +" WHERE StoreNum = StoreNumber) ");
DataRequest = Database.createStatement();Results = DataRequest.executeQuery (query);
System.out.println("Store");System.out.println("-----");DisplayResults (Results);
DataRequest.close();}
private void DownRow ( ResultSet DisplayResults )throws SQLException
{
long Store;Store = DisplayResults.getLong ( 1 ) ;System.out.println(Store);
}
VIEWs
You can reduce the complexity of your J2ME application by creating one or more views of the database for each user ID that is passed to the J2ME application for data access. A VIEW is similar to creating a table that contains only data the user ID is permitted to access. A VIEW limits columns and rows that can be queried to specific information pertaining to a user ID. A VIEW is like a filter that hides information from a user ID. Each VIEW is uniquely identified with a name and contains selection criteria for columns and rows that appear in the VIEW when the VIEW is used by a J2ME component. Once a VIEW is created, the J2ME application references a VIEW the same way that a table is referenced in a query.
Rules for Using VIEWs
In many situations, using VIEWs increases the efficiency of interacting with tables in a database. However, there are times when VIEWs are not appropriate for an application. The following is a set of rules that govern how you should use VIEWs in your application:
· Create as many VIEWs as necessary to simplify access to a database.
· Restrict access to a table on need-to-know basis.
· Work with the owner of the data to establish reasonable restrictions.
· Classify users into groups that have similar access requirements to information.
· Create a VIEW for each classification of user rather than for each user.
· More than one column can be used in a VIEW.
· More than one table can be used in a VIEW.
· A VIEW is treated as a table in a query regardless of the number of columns and tables that are used to create the VIEW.
· Use a VIEW whenever your program accesses some columns in many tables.
· The view simplifies the number of tables that a J2ME application needs to access directly. Beware of data security restrictions that affect the underlying columns and tables used to create the VIEW. AVIEW inherits data security restrictions from tables used to create the VIEW. Therefore, if the user ID doesn’t have rights to information in a table, the VIEW also has the same restrictions.
· Create as many VIEWs as necessary to simplify access to a database. However, querying a VIEW is not necessarily an efficient way to query a database.
Create a VIEW
AVIEW is created by using the CREATE VIEW statement, as illustrated in
try {
String query = new String (" CREATE VIEW Store278 AS " +" SELECT * " +
" FROM Orders " +" WHERE StoreNum = 278");DataRequest = Database.createStatement();
DataRequest.execute(query);
DataRequest.close(); }
Select Columns to Appear in the VIEW
try {
String query = new String (" CREATE VIEW StoreProd AS " +" SELECT StoreNum, ProdNumber " +
" FROM Orders ");
DataRequest = Database.createStatement();DataRequest.executeQuery (query);
DataRequest.close();}
Create a Horizontal VIEW
try {
String query = new String (" CREATE VIEW cust901 AS " +" SELECT * " + " FROM Orders" + " WHERE CustomerNumber = 901");
DataRequest = Database.createStatement();Results = DataRequest.execute(query);
DataRequest.close();
}
Create a Multitable VIEW
try {
String query = new String (" CREATE VIEW ProdDesc AS " +" SELECT StoreNum, ProdNumber, ProductName " + " FROM Orders, Products " +" WHERE ProdNumber = ProductNumber");
DataRequest = Database.createStatement(); Results = DataRequest.execute(query);
DataRequest.close();
}
Group and Sort VIEWs
try {
String query = new String (" CREATE VIEW GroupProdDesc AS " +" SELECT StoreNum, ProdNumber, ProductName " +" FROM Orders, Products " +" WHERE ProdNumber = ProductNumber" +
" ORDER BY ProdNumber ");
DataRequest = Database.createStatement();Results = DataRequest.execute(query);DataRequest.close();
}
Modify a VIEW
A VIEW can be modified, and those modifications affect the underlying tables that are used to create the VIEW. There are three ways to modify a VIEW:
Update Values in one or more columns of a VIEW are changed to values supplied by the query.
Insert A new row is added to the VIEW and indirectly to the underlying table(s) used to create the VIEW.
Delete A row is removed from the VIEW and from the underlying table(s) used to create the VIEW.
Rules for Updating a View
A View can be used to update columns and rows in the underlying tables that comprise the View. However, updating a View is possible only if you adhere to a set of rules that govern the use of updating a View.
· Calculation, expressions, and built-in column functions cannot be used in the SELECT statement of the VIEW. Instead, only names of columns can be used.
· Exclude the GROUP BY clause and HAVING clause from the VIEW.
· Duplicate rows must be included in the modification. You cannot use the DISTINCT modifier in the VIEW if values of the view are going to be modified.
· The user ID used in your program must have rights to modify the underlying tables that make up the VIEW.
· Subqueries cannot be used in a VIEW if rows of the VIEW are to be modified.
Updating a View
try {
String query = new String (" UPDATE Store278 " + " SET Amount = 700 " +" WHERE OrderNumber = 334 ");
DataRequest = Database.createStatement();Results = DataRequest.execute(query);
DataRequest.close(); }
Inserting a Row into a VIEW
try {
String query = new String (" INSERT INTO Store278 " +" (OrderNumber, ProdNum, CustomerNumber, StoreNum, Amount) " +" VALUES (325, 9545 ,301 ,278 ,400) ");
DataRequest = Database.createStatement();Results = DataRequest.execute(query);
DataRequest.close(); }
Deleting a Row from a VIEW
try {
String query = new String (" DELETE FROM Store278 " +" WHERE OrderNumber = 555 ");
DataRequest = Database.createStatement();Results = DataRequest.execute(query);
DataRequest.close();}
Dropping a VIEW
AVIEW can be removed by using the DROP VIEW statement. The DROP VIEW statement requires the name of the VIEW that is to be dropped. Two modifiers are used with the DROP VIEW statement:
CASCADE Remove all VIEWs that depend on the VIEW specified in the DROP VIEW statement as well as the specified VIEW.
RESTRICT Remove only the VIEW specified in the DROP VIEW statement. All dependent VIEWs remain intact.
try {
String query = new String (" DROP VIEW Store278 CASCADE");
DataRequest = Database.createStatement();
Results = DataRequest.DataRequest.execute(query);
DataRequest.close();
}
