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();
}
No comments:
Post a Comment