<faircom>\drivers\sql.jdbc\tutorials\JDBC_Tutorial3.java
Now we will explore row/record locks using the FairCom DB JDBC Interface.
The functionality for this tutorial focuses on inserting/adding rows/records, then updating a single row/record in the customer master table under locking control. The application will pause after a LOCK is placed on a row/record. Another instance of this application should then be launched, which will block, waiting on the lock held by the first instance. Pressing the <Enter> key will enable the first instance to proceed. This will result in removing the lock thereby allowing the second instance to continue execution. Launching two processes provides a visual demonstration of the effects of locking and a basis for experimentation on your own.
As with all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and you’re Done() !
Tutorial #3: Locking
Here we demonstrate the enforcement of data integrity by introducing record/row "locking".
- Initialize() - Connects to the FairCom Database Engine.
- Define() - Defines and creates a "customer master" (custmast) table/file.
- Manage() - Adds a few rows/records, manipulates them, and displays the results.
- Done() - Disconnects from FairCom Database Engine.
Note our simple Main() function:
import java.sql.*;
import java.io.*;
public class JDBC_Tutorial3 {
static Connection conn;
static Statement stmt;
//
// main()
//
// The main() function implements the concept of "init, define, manage
// and you're done..."
//
public static void main (String[] args)
throws java.io.IOException
{
Initialize();
Define();
Manage();
Done();
System.out.print("\nPress <ENTER> key to exit . . .");
System.in.read();
System.exit(0);
}
We suggest opening the source code with your own editor.
Continue now to review these four steps.
Init
First we need to open a connection to a database by providing the FairCom Database Engine with a user name, password and the database name.
Below is the code for Initialize():
//
// Initialize()
//
// Perform the minimum requirement of logging onto the c-tree Server
//
private static void Initialize ()
{
System.out.println("INIT");
try
{
// load the driver
Class.forName ("ctree.jdbc.ctreeDriver");
// connect to server
System.out.println("\tLogon to server...");
conn = DriverManager.getConnection ("jdbc:ctree://localhost:6597/ctreeSQL", "ADMIN", "ADMIN");
// disable commit after each single SQL statement
conn.setAutoCommit(false);
// create a statement handle
stmt = conn.createStatement();
}
catch (SQLException e)
{
Handle_Exception(e);
}
catch (Exception e)
{
Handle_Exception(e);
}
}
Define
Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.
Below is the code for Define():
//
// Define()
//
// Create the table for containing a list of existing customers
//
private static void Define ()
{
System.out.println("DEFINE");
try
{
stmt.executeUpdate("DROP TABLE ordritem");
}
catch (SQLException e)
{
}
try
{
stmt.executeUpdate("DROP TABLE custordr");
}
catch (SQLException e)
{
}
try
{
stmt.executeUpdate("DROP TABLE itemmast");
}
catch (SQLException e)
{
}
try
{
stmt.executeUpdate("DROP TABLE custmast");
}
catch (SQLException e)
{
}
try
{
// create table
System.out.println("\tCreate table...");
stmt.executeUpdate("CREATE TABLE custmast (" +
"cm_custnumb CHAR(4), " +
"cm_custzipc CHAR(9), " +
"cm_custstat CHAR(2), " +
"cm_custrtng CHAR(1), " +
"cm_custname VARCHAR(47), " +
"cm_custaddr VARCHAR(47), " +
"cm_custcity VARCHAR(47))"
);
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
stmt.executeUpdate("CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)");
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
conn.commit();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
Manage
Manage() provides data management functionality for your application and/or process.
Below is the code for Manage():
//
// Manage()
//
// This function performs record adds and updates using locking
//
private static void Manage ()
{
System.out.println("MANAGE");
// delete any existing records
Delete_Records();
// populate the table with data
Add_CustomerMaster_Records();
// display contents of table
Display_Records();
// update a record under locking control
Update_CustomerMaster_Record();
// display again after update and effects of lock
Display_Records();
}
//
// Delete_Records()
//
// This function deletes all the records in the table
//
private static void Delete_Records ()
{
System.out.println("\tDelete records...");
try
{
stmt.executeUpdate("DELETE FROM custmast");
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
conn.commit();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Add_CustomerMaster_Records()
//
// This function adds records to table CustomerMaster from an
// array of strings
//
private static void Add_CustomerMaster_Records ()
{
System.out.println("\tAdd records...");
String data[] = {
"('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",
"('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",
"('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",
"('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"
};
try
{
// add one record at time to table
for (int i = 0; i < data.length; i++) {
stmt.executeUpdate("INSERT INTO custmast VALUES " + data[i]);
}
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
conn.commit();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Display_Records()
//
// This function displays the contents of a table.
//
private static void Display_Records ()
{
System.out.print("\tDisplay records...");
try
{
// execute a query statement
ResultSet rs = stmt.executeQuery ("SELECT * FROM custmast");
// fetch and display each individual record
while (rs.next()) {
System.out.println("\n\t\t" + rs.getString(1) + " " + rs.getString(5));
}
rs.close();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Update_CustomerMaster_Records()
//
// Update one record under locking control to demonstrate the effects
// of locking
//
private static void Update_CustomerMaster_Record()
{
System.out.println("\tUpdate record...");
try
{
stmt.executeUpdate("UPDATE custmast SET cm_custname = 'KEYON DOOLING' where cm_custnumb = '1003'");
System.out.println("\tPress <ENTER> key to unlock");
System.in.read(new byte[256]);
conn.commit();
}
catch (SQLException e)
{
Handle_Exception(e);
}
catch (Exception e)
{
Handle_Exception(e);
}
}
Done
When an application and/or process has completed operations with the database, it must release resources by closing the open files and disconnecting from the database engine.
Below is the code for Done():
//
// Done()
//
// This function handles the housekeeping of closing, freeing,
// disconnecting and logging out of the database
//
private static void Done ()
{
System.out.println("DONE");
try
{
stmt.close();
// logout
System.out.println("\tLogout...");
conn.close();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
Additional Resources
We encourage you to explore the additional resources listed here:
Complete source code for this tutorial can be found in JDBC_Tutorial3.java in your installation directory, within the <faircom>\drivers\sql.jdbc\tutorials directory for your platform.
- Additional documentation may be found on the FairCom Web site at: www.faircom.com