This chapter provides a set of brief tutorials to get you up and running with the FairCom DB JDBC driver. These tutorials will take you through the basic use of the FairCom JDBC Interface, including relational modeling, indexing, locking, and transaction processing.
Introductory Tutorial
<faircom>\drivers\sql.jdbc\tutorials\JDBC_Tutorial1.java
This tutorial will take you through the basic use of the FairCom DB JDBC Interface.
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 #1: Introductory - Simple Single Table
We wanted to keep this program as simple as possible. This program does the following:
- Initialize() - Connects to the FairCom Database Engine.
- Define() - Defines and creates a "customer master" (custmast) table/file.
- Manage() - Adds a few rows/records; Reads the rows/records back from the database; displays the column/field content; and then deletes the rows/records.
- Done() - Disconnects from FairCom Database Engine.
Note our simple Main() function:
import java.sql.*;
import java.io.*;
public class JDBC_Tutorial1 {
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.
Connection String
Beginning with FairCom DB V11.2 and FairCom RTG V2, the connection string is in the following format:
jdbc:ctree://<host>[:portnumber]/<dbname>[?param=value[¶m=value]...]
The valid values for param are:
- characterEncoding - Replace encoding with a valid Java encoding name (e.g., US‑ASCII, ISO‑8859-1, UTF‑8, etc.).
- password
- User
- ssl - Values of basic (no peer certificate authentication) or peerAuthentication (server certificate authentication)
When peerAuthentication is requested, the client’s trust store must contain the server’s certificate as shown in the example below.
The tutorials use a connection string that is set for the default configuration:
"jdbc:ctree://localhost:6597/ctreeSQL", "ADMIN", "ADMIN"
TLS/SSL Examples
Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?ssl=basic");
System.setProperty("javax.net.ssl.trustStore","TrustStore.key");
System.setProperty("javax.net.ssl.trustStorePassword","mypassword""");
Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?ssl=peerAuthentication");
For backward compatibility, the older format ("jdbc:ctree:6597@localhost:ctreeSQL", "ADMIN", "ADMIN") is still supported but should be considered deprecated.
Initialize Code
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");
// 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);
}
}
Manage
Manage() provides data management functionality for your application and/or process.
Below is the code for Manage():
//
// Manage()
//
// This function performs simple record functions of add, delete and gets
//
private static void Manage ()
{
System.out.println("MANAGE");
// delete any existing records
Delete_Records();
// populate the table with data
Add_Records();
// display contents of table
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);
}
}
//
// Add_Records()
//
// This function adds records to a table in the database from an
// array of strings
//
private static void Add_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);
}
}
//
// 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);
}
}
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_Tutorial1.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