Supermarket operation support system - Software Development Case Studies in Java - page 384

Sidebar 14.2 JDBC
JDBC is the standard Java protocol for accessing a database that is compliant with the SQL92 stan-
dard. To use the JDBC API with a particular database management system, you need a JDBC tech-
nology-based driver to mediate between JDBC technology and the database.
The connection to the database is represented by a Connection object. It is possible to operate
on the database through a Statement object, which offers methods to update and query the data-
base. The rows resulting from a query are stored in a ResultSet object.
We take as an example the steps to be performed in order to use the Cloudscape database that
is bundled with the J2EE distribution. The following example shows how to list the content of the
Item table.
// load the cloudscape driver
Class.forName("COM.cloudscape.core.RmiJdbcDriver");
// define the connection string
String connection # "jdbc:rmi:jdbc:cloudscape:Supermarket;create # true";
// ask the driver manager to create a connection
Connection conn # DriverManager.getConnection(connection);
// create a statement
Statement stmt # conn.createStatement();
// define a query
String query # "SELECT * FROM ITEM";
// execute a query
ResultSet rs # stmt.executeQuery(query);
// loop over the rows in the result set
while (rs.next()){
// get the long field ID
long id # rs.getLong("ID");
// get the String field Name
String name # rs.getString("Name");
// prints the values
System.out.println("ID # " ! id ! "; Name # " ! name);
}
CREATE TABLE employee (
ID LONGINT CONSTRAINT pk_employee PRIMARY KEY ,
name VARCHAR (30),
password VARCHAR (15)
);
CREATE TABLE trans (
ID LONGINT CONSTRAINT pk_transaction PRIMARY KEY ,
employee INT ,
customer INT ,
is_open INT
);
CREATE TABLE acquisition (
ID LONGINT CONSTRAINT pk_acquisition PRIMARY KEY ,
trans INT ,
item INT ,
quantity INT
);