Using Java to Manage a
Database
Applications
usually get their data from a database rather than from text files. A relational database consists of one or more
tables each of which is made up of rows and columns. Both rows and columns are numbered beginning
with one. Each row defines the
information for a single object. The
columns then are the fields of the object.
The following is
an example of a database for an address book that has a table, called
AddressTable. It is contained in an Access[1] database called addresses.mdb. Each person’s
address is contained in a separate row.
The field names here are Name, Email, and Telephone. All the data in this example are of type
text, strings.
The Database Connection
To connect to a database using
a Java program, you must first register the database with the operating system
so that it can find the data source.[2] The connection is done with a jdbc-odbc
bridge. Jdbc stands for Java database
connectivity API (application programming interface), while the ‘O’ in Odbc
stands for Open. Odbc is a protocol from
Microsoft that is based on the X/Open SQL specification.
In a Java program, we create
a Connection object. The lines of code required are:
Class.forName
("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection
("jdbc:odbc:addresses");
where addresses is the data source name used for the database in the
registration information. Both the
Connection and DriverManager objects are contained in the Java package, java.sql. This package is a standard part of JDK and so
does not have to be downloaded separately.
But it must be imported into any program that connects to a database.
SQL
Queries
SQL stands for
Structured Query Language[3]
and is usually pronounced sequel. SQL is the standard way to interact with
relational databases and is not part of Java.
SQL is not case sensitive, so you
can mix upper and lower cases, but commands traditionally use upper case. In these examples, all commands begin with an
upper case letter. Some examples of
commands are Select, Insert, Delete, and Update. You can also modify commands by using
connectors such as Where or Set.
The
Select Query
Select is
used to obtain information from a database table. For example,
"Select * From AddressTable"
will get all (*) the data
from the table, AddressTable. If you do
not want all the data, you can add a clause that will further define the rows
needed. This is done with the modifier, Where.
For example, if you just want
the names that begin with the letter A, you can use the query
"Select * From AddressTable Where Name Like 'A%'"
The 'A%'
combination is used to indicate a pattern that begins with the letter A. Note the single quotes. SQL queries can also use =, <>, >,
<, >=, <=, or Between as well as Like.[4] For Between you must specify a range. The query
"Select * From AddressTable Where Name Between 'A%' And
'C%'"
returns the first two entries in the table.
Statements
and ResultSets
When sending a query to a
database from a program, you first create a Statement
object for the query, and then you execute it.
If the database is able to execute the query, it returns a ResultSet with the data. A query that returns all the data in the
AddressTable uses the following code:
Statement stmt = con.createStatement ();
String query = "Select * From AddressTable";
ResultSet rs = stmt.executeQuery (query);
The result set consists of a
sequence of rows. It has a cursor that
is initially set to zero, not a valid row in a table. The method, rs.next (), is used to move the
cursor to the next row in the set. In addition it returns a boolean value. If the result set is empty, this value will
be false, otherwise it returns true.
Because it returns a boolean, it can be used as the condition for a while or an if statement.
The particular fields in the
row can be obtained using get methods
(accessor methods). Since all the items
in this database are strings, the get method used here is getString. Other data types
can be stored in a database too, so there are getXXX () statements for them as
well. For example, if the data in the
column are integers, a getInt() method is used.
Example
Program that Displays the Entire Table
The following program
displays the whole table. This is only
reasonable if the table does not contain very much data. It throws a SQLException and a
ClassNotFoundException. The first is
thrown if there is a problem in accessing the database. The second is thrown if there is a problem
getting the connection driver. This would
happen if the driver class could not be found.
// Addresses is a Java application that gets data from a database and displays it on the screen.
import java.sql.*;
public class Addresses
{
public static void main (String [] args)
{
try
{
// Get a connection to the database.
Class.forName
("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection
("jdbc:odbc:addresses");
// Create a statement and a query.
Statement stmt = con.createStatement ();
String query = "Select * From
AddressTable";
// Execute the query and retrieve a ResultSet.
ResultSet rs = stmt.executeQuery (query);
System.out.println ("Address Table
Results");
// Display the data in the ResultSet.
while (rs.next ())
{
System.out.println ();
System.out.println ("Name: " +
rs.getString ("Name") );
System.out.println ("Email: "
+ rs.getString ("Email"));
System.out.println ("Telephone:
" + rs.getString ("Telephone"));
}
con.close (); // Close the connection to the
database.
} catch (SQLException e) {System.out.println
("SQL Exception");}
catch
(ClassNotFoundException e) {System.out.println ("Driver not found");}
} // main
} // Addresses
Finding
a Name in the Table
The Where modifier can be used to find an email address given a name in
a database table. If the name occurs
only once in the table, the result set will just contain that row. However, if there are several rows with the
same name, the result set will include all of them.
If the name is stored in a
variable in the program, it must be surrounded by quotation marks in the query
string. The entire string must also be
contained in quotation marks. The
easiest way to include the quotes surrounding the variable is to use single
quotes inside of the double quotes.
Since these are difficult to see in the document, boldface has been used
for them. The resulting query string is
"Select * From AddressTable Where Name = '" + name + "'"
Note the extra single quote
at the end.
The program also uses the Scanner
class that is included with Java 1.5.[5]
import java.sql.*;
import java.util.Scanner;
/* FindEmail is used to find an email address for a person in a
database. It finds the name and then
displays the person's email address. */
public class FindEmail
{
static Scanner keyboard = new Scanner (System.in);
public static void main (String [] args)
{
try
{
System.out.print ("Whose email address do
you want to find? ");
String name = keyboard.nextLine ();
// Get a jdbc-odbc bridge and connect to
addresses.mdb.
Class.forName
("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection
("jdbc:odbc:addresses");
// Create a statement and execute the query.
Statement stmt = con.createStatement ();
String query = "Select * From AddressTable
Where Name = '" + name + "'";
ResultSet rs = stmt.executeQuery (query);
// If the query returns a result, the ResultSet
will be non-void.
if (rs.next ())
{
String email = rs.getString
("Email");
System.out.println ("The email
address for " + name + " is " + email);
}
else System.out.println ("The name was not
found in the database.");
} catch (ClassNotFoundException e){System.out.println
("Class Not Found exception.\n");}
catch
(SQLException e){System.out.println ("SQL Exception");}
} // main
} // FindEmail
A
Table with Integer and Double Data
The preceding example has a
table with text data only. The following
example contains a column that stores integers and one that stores doubles. The table contains data that a grocery store
might have with information about some fruit that it sells. It has fields for the product’s id, name,
quantity in stock, and price per pound.
The id and name fields are text.
The quantity field is an Integer[6]
and the price field is Currency. A
sample table is shown below.
Java, like other languages,
must translate the types given by the database into its own datatypes. Integer in Access is interpreted as int by
Java and Currency as double. So to
retrieve data in the quantity field use rs.getInt ("quantity") and
for the price field use rs.getDouble ("price").
A program that prompts for
the name of a fruit and then returns the data in the row follows:
// FindFruit is used to find the quantity and price of fruit in the
table, fruit.
import java.util.Scanner;
import java.sql.*;
public class FindFruit
{
static Scanner keyboard = new Scanner (System.in);
public static void main (String [] args)
{
try
{
System.out.print ("What fruit do you want
to find? ");
String name = keyboard.nextLine ();
// Get a jdbc-odbc bridge and connect to
grocery.mdb.
Class.forName
("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection
("jdbc:odbc:grocery");
// Create a statement and execute the query.
Statement stmt = con.createStatement ();
String query = "Select * From fruit Where
Name = '" + name + "'";
// If the query returns a result, the ResultSet
will be non-empty.
ResultSet rs = stmt.executeQuery (query);
if (rs.next ())
{
System.out.println ("ID: " +
rs.getString ("id"));
System.out.println ("Name: " +
name);
System.out.println ("Quantity:
" + rs.getInt ("quantity"));
System.out.println ("Price: $"
+ rs.getDouble ("price"));
}
else System.out.println ("The name was not
found in the table.");
} catch (ClassNotFoundException e){System.out.println
("Class Not Found exception.\n");}
catch
(SQLException e){System.out.println ("SQL Exception");}
} // main
} // FindFruit
Queries
with Non-String Keys
If we want to find all the
fruit that have a quantity greater than some set amount, the quotes around the
parameter are not required. Quotes
around parameters are only used for strings.
"Select * From fruit Where Quantity >= " + amount
This query returns the data
for Apples and Bananas when the amount entered is 20. Note the greater than or equal sign.
// FindQuantity is used to find all entries with a quantity larger
than some amount read in.
import java.util.Scanner;
import java.sql.*;
public class FindQuantity
{
static Scanner keyboard = new Scanner (System.in);
public static void main (String [] args)
{
try
{
System.out.print ("Enter the amount for
the lower limit: ");
int amount = keyboard.nextInt ();
// Get a jdbc-odbc bridge and connect to
grocery.mdb.
Class.forName
("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection
("jdbc:odbc:grocery");
// Create a statement and execute the query.
Statement stmt = con.createStatement ();
String query = "Select * From fruit Where
Quantity >= " + amount;
// If the query returns a result, the ResultSet
will be non-empty.
ResultSet rs = stmt.executeQuery (query);
while (rs.next ())
{
System.out.println ("ID: " +
rs.getString ("id"));
System.out.println ("Name: " +
rs.getString ("name"));
System.out.println ("Quantity:
" + rs.getInt ("quantity"));
System.out.println ("Price: $"
+ rs.getDouble ("price"));
}
} catch (ClassNotFoundException e){System.out.println
("Class Not Found exception.\n");}
catch
(SQLException e){System.out.println ("SQL Exception");}
} // main
} // FindQuantity
References
1. Susan Anderson-Freed, Weaving a Website, Prentice Hall, 2002
2. Karl Moss, Java
Servlets Developer’s Guide, McGraw-Hill/Osborne, 2002.
3. W3Schools Online Web Tutorials, http://www.w3schools.com.