<< ODBC And CSQL - TOC - Squirrel SQL Client >>
CSQL and JDBC
This chapter gives a overview on writing applications on CSQL JDBC Driver. The CSQL JDBC Driver 2.0 is a JDBC type 2 driver for CSQL database. This chapter intends to provide reader sufficient knowledge for connecting CSQL server through JDBC and completing the basic database operations. This document contains a general description of the services provided by JDBC and some code example using JDBC explained
Overview of JDBC
Important Packages
- java.sql
- javax.sql
Important classes
- java.sql.Date
- java.lang.DriverManager
- java.sql.Time
Important Interfaces
- java.sql.Driver
- java.sql.Connection
- java.sql.PreparedStatement
- java.sql.ResultSet
- java.sql.Statement
- java.sql.SqlException
- java.sql.ParameterMetadata
- java.sql.ResultSetMetadata
- java.sql.DatabaseMetadata
- java.sql.SqlException
- javax.sql.DataSource
- javax.sql.ConnectionPoolDataSource
- javax.sql.PooledConnection
CSQL Native Library
- libcsqljdbc.so
CSQL JDBC Driver
- CSqlJdbcDriver.jar
Background
The JDBC API defines Java classes to represent database connections, SQL statements, Result sets, etc. It allows a Java Programmer to issue SQL statements and process the results. JDBC is the primary API for database access in Java. Because JDBC is a standard specification, a Java program that uses the JDBC API can connect to any database management system (DBMS) for which there is a JDBC Driver.
CSQL JDBC driver is implemented using native methods to bridge to database access libraries.
What is JDBC Driver
The JDBC API defines the Java Interfaces and classes that programmers use to connect to database and send queries. A Java program that uses the JDBC API loads the specified driver for a particular DBMS before it actually connects to a database. The JDBC DriverManager class then sends all JDBC API calls to the loaded driver.
The JDBC API is consistent with the style of the core Java interfaces and classes, such as java.lang and java.awt. The next section describes the interfaces, classes and exceptions that make up the JDBC API.
Classes, Interfaces and Methods in Details
This section details CSQL’s implementation of the following java.sql classes, interfaces and methods. Using these standard interfaces and classes, programmers can write applications that connect to databases, send queries written in structured query language (SQL), and process the results.
Majority of JDBC API is located in java.sql package. The following are core JDBC java.sql Classes, Interfaces and Methods.
Interfaces
Below mentioned interfaces and classes are supported.
| Interface | Description |
| java.sql.Connection | Interface used to establish a connection to a database. SQL statements run within the context of a connection. |
| java.sql.Driver | Interface used to locate the driver for a particular database management system. |
| java.sql.PreparedStatement | Interface used to send precompiled SQL statements to the database server and obtain result. |
| java.sql.ResultSet | Interface used to process the results returned from executing an SQL statement. |
| java.sql.ResultSetMetaData | Interface used to return information about the columns in a ResultSet object. |
| java.sql.Statement | Interface used to send static SQL statements to the database server and obtain results. |
| java.sql.ParameterMetadata | It describes the number, type, and properties of parameters to prepared statements. |
| java.sql.DatabaseMetadata | It gives all the database metadata information. |
| javax.sql.DataSource | An alternative to the DriverManager facility, a DataSource object is the preferred means of getting a connection. |
| javax.sql.ConnectionPoolDataSource | A factory for PooledConnection objects |
| javax.sql.PooledConnection | A PooledConnection object represents a physical connection to a data source. The connection can be recycled rather than being closed when an application is finished with it, thus reducing the number of connections that need to be made. |
Classes
| Classes | Description |
| java.sql.Date | Subclass of java.util.Date used for the SQL DATE data type. |
| java.lang.DriverManager | Class used to manage a set of JDBC Drivers |
| java.sql.Time | Subclass of java.util.Date used for the SQL TIME data type |
| java.sql.TimeStamp | Subclass of java.util.Date used for the SQL TIMESTAMP data type |
| java.sql.Types | Class used to define constants that are used to identify standard SQL data types, such as CHAR and INTEGER. |
| java.sql.String | Class used to identify text data types such as CHAR. |
Exception Classes
| Exception Class | Description |
| java.sql.SQLException | Exception that provides information about a database error. |
Data types
Data Types of CSQL and Java Programming are not same; it needs some mechanism for transferring data between database using CSQL data types and application.
This JDBC 3.0 supports all primitive types like – integer, char, float, string, including Date, Time, and TimeStamp.
The Below table represent the Java mapping for CSQL data types.
| CSQL Type(java.sql.types) | Java Type |
| TINYINT | Byte |
| SMALLINT | short |
| INTEGER | Int |
| LONG | bigint |
| BIGINT | Long |
| FLOAT | Double |
| DOUBLE | Double |
| CHAR | Java.lang.String |
| DATE | Java.sql.Date |
| TIME | Java.sql.Time |
| TIMESTAMP | Java.sql.TimeStamp |
| BINARY | Byte[] |
Using CSQL JDBC Driver
Getting Started
The following steps for CSQL JDBC Driver.
- Setting the CLASSPATH
- Setting the development environment
- Loading the Driver
- Registering JDBC Driver
- Connecting to the database
Setting up the CLASSPATH
After installing CSQL, the JDBC Driver should be found in csql/lib/CSqlJdbcDriver.jar. The library libcsqljdbc.so is required because CSQL JDBC Driver is implemented using native method to access the database server. Both of these files are available with the CSQL Installation Package.
Below path is for CSQL JDBC Driver.
export CLASSPATH=$CSQL_INSTALL_ROOT/lib/CsqlJdbcDriver.jar:.
The setupenv.ksh file contains the class path for the JDBC Driver and is present in CSQL’s root directory and at the time of building the CSQL it is automatically set.
Refer CSQL User Manual to know how to install CSQL and Setting up Required JDK.
Setting the development environment
CSQL JDBC Driver expects for JDK version 1.5. JDBC interface is included in the java.sql Package. Any source that a use JDBC needs to import the java.sql.package.The application always needs to import this package.
import java.sql.* ;
Loading the driver
The JDBC driver should be loaded before making the connection. The below method will load the driver, and while loading, the driver will automatically register itself with JDBC. After execution of this code, the driver registers itself in the DriverManager, which handles loading and unloading driver and interfacing connection requests with the appropriate driver.
Class.forName (“csql.jdbc.JdbcSqlDriver);
In the above method the driver class name as an argument and once the driver is loaded it creates an instance of itself.
Note: The forName( ) method can throw a ClassNotFoundException if the driver is not available.
Connecting to the database
Once the driver is successfully registered with the driver manager a connection is established by creating a Java Connection object with the following code.
Connection con = DriverManager. getConnection ( “jdbc:csql“ , “root” , “manager” ) ;
The argument “jdbc: csql “ is the URL (Uniform Resource Locator) which represents Database. Another two arguments “root” and “manager” corresponds to user name and password of the database. The getConnection method returns a new connection object to the database.
The CSQL database maintains a list of JDBC URLS for different connections for different purposes.
Below are the URLs and their uses in brief:
| jdbc:csql | Connect to database without network. |
| jdbc: adapter | Connect to database through adapter without network for any operation in target database |
| jdbc:gateway | Connect to database through gateway without network |
| jdbc:csql://<Host Name>:<Port>/csql | Connect to database with network |
| jdbc:adapter://<Host Name>:<port>/csql | Connect to database through adapter with network for any operation on target database |
| jdbc:gateway://<Host Name>:<port>/csql | Connect to database through gateway with network |
Closing the Connection
To close the connection, call the close method of the Connection. This method is belongs to Connection interface.
con.close( );
Running SQL Statements with JDBC
This section outlines some important methods for SQL operations, which execute in the database.
CREATE and DROP
Create statement object using,
Statement cStmt = con.createStatement( ) ;
This cStmt object is used to send and execute SQL Statements to a database.
Create Table After creating the statement object, a table cab be created in database using execute( ) function.
cStmt.execute ( “CREATE TABLE T1 ( f1 integer, f2 char( 20));” ) ;
The execute() method is for execution of SQL statements in the database. The return type of this Statement object is Boolean value. After execution of this statement the ‘T1’ table will be created in database.
Drop Table Using the same statement object the table can be dropped from the database.
cStmt.execute( “DROP TABLE T1 ; ”) ;
INSERT
Simple Statement Following code expects that a Connection object con is established before calling the code.
cStmt.execute (“INSERT INTO T1 ( f1, f2 ) VALUES ( 1, ‘ABC’ ) ;” ) ;
Note that the insert is not committed by the code unless the database is in autocommit mode .
Statement with parameters The code below creates a PreparedStatement object for a query, assigns values for its parameters and executes the query. In order to bind the input parameter, PreparedStatement provides setInt( ) and setString( ) method to set the IN parameters.
After bind the parameters value the SQL statement is executed by the executeUpdate( ) method. It returns the number of records affected by the insert.
The below code expects a Connection object con to be established.
PreparedStatement stmt = null;
int count = 0 ;
stmt = con . prepareStatement ( “ INSERT INTO T1 ( f1, f2 ) VALUES ( ? , ?) ;” ) ;
for (int i=0; i<10 ; i++ )
{
stmt.setInt ( 1, i ) ;
stmt.setString ( 2, String . valueOf ( i + 100 )) ;
stmt.executeUpdate( ) ;
count++ ;
}
After executing the above code, 10 records will be inserted into the table ‘T1’. Note that the insert is not committed by the code unless the database is in autocommit mode.
SELECT (Using the ResultSet Interface)
ResultSet Interface:
After traversal, the ResultSet must be closed by calling close( ). ResultSet is currently read only.
The below code loops through the result set and prints the data in each column in each row by using getString and getInt method. Before that the query needs to be executed using executeQuery( ) method.
The below code expects a Connection object con to be established.
PreparedStatement selStmt = null;
ResultSet rs = null;
int count =0;
selStmt = con.prepareStatement( “SELECT * from T1 where f1 = ? ;” ) ;
for( int i=0; i<10 ; i++ )
{
selStmt.setInt( 1, i ) ;
rs = selStmt.executeQuery( ) ;
while (rs.next( ) )
{
System.out.println (“Value Is “ + rs.getInt (1)+ ” “+
rs.getstring(2));
count ++ ;
}
rs.close( ) ;
}
NOTE: CSQL supports only read only forward cursors.
UPDATE
The executeUpdate( ) method is used to update the records of a table in a database. This method is similar to the executeQuery( ) used to issue a select, however it does not return a ResultSet, instead it returns the number of records affected by the update
The below code will issue a update with where clause (Parameterized value) and assumes the connection object is to be established.
PreparedStatement stmt = null;
int count = 0;
stmt = con.prepareStatement( “UPDATE T1 SET f2 = ? WHERE f1 =? ;”);
for (int i =0; i< 10; i +=2) {
stmt.setString (1, String.valueOf(i+200));
stmt.setInt(2, i);
ret = stmt.executeUpdate();
count++;
}
DELETE
To delete data performing INSERT statement the executeUpdate( ) method is used. This method returns the number of records affected by the DELETE statement.
Below code is for delete statement and assumes that connection object is to be established. Refer a sample example in the Section 5.10
PreparedStatement stmt = null;
stmt = con.prepareStatement("DELETE FROM T1 WHERE f1 =?;");
for (int i =0; i< 10 ; i +=3)
{
stmt.setInt(1, i);
ret = stmt.executeUpdate();
if (ret != 1) break; //error
count++;
}
Transaction and AutoCommit mode
When a Transaction is created using JDBC, by default it is in auto-commit mode. This means that each SQL statement is treated as a transaction and will be automatically committed immediately after it is executed. Sometimes groups a statement is executed together or fails together. Transactions are used to group a set of statements so that they all execute successfully, or all fail. The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode. The following line of code will do this.
con.setAutoCommit(false);
Transaction Methods
SetAutoCommit (boolean flag ) -If set true, every executed statement is committed immediately. commit() - Relevant only if setAutoCommit (false) rollback() - Relevant only if setAutoCommit (false)
The following lines of code will for disable auto commit mode and assumes the connection object is to be established.
con.SetAutoCommit (false);
PreparedStatement stmt = null, selStmt= null;
stmt = con.prepareStatement("INSERT INTO T1 (f1, f2) VALUES (?, ?);");
int count =0;
int ret =0;
for (int i =0 ; i< 10 ; i++)
{
stmt.setInt (1, i);
stmt.setString(2, String.valueOf(i+100));
ret = stmt.executeUpdate();
if (ret != 1) break; //error
count++;
}
stmt.close();
con.commit ();
Note : The autoCommit state can be monitored by connection.getAutoCommit( ) method.
Code Examples
/*
* Sample JDBC application
*
* This Simple JDBC application does the following using CSQL JDBC Driver.
*
* 1. Loading the Driver
* 2. Connects to CSQL using Driver
* 3. Created table T1(f1 int,f2 char(20)
* 4. Performing INSERT, UPDATE, DELETE statement using parameterized value
* 5. Create SELECT query.
* 6. Fetches and dumps all the rows.
* 7. Drop the T1 table
* 8. Closes the connection
*
* To build and run the application
* 1. Make sure you have a working JDK
* 2. Install and start CSQL to connect
* 3. Build and run the application ensure that the server is up.
*
* For more information refer below file in CSQL installation root
* csql/example/jdbc/jdbcexample.java
* /
import java.sql.*;
public class jdbcexample {
public static void main(String[] args) {
try {
Class.forName("csql.jdbc.JdbcSqlDriver");
Connection con = DriverManager.getConnection("jdbc: csql", "root", "manager");
con.setAutoCommit();
Statement cStmt = con.createStatement();
cStmt.execute("CREATE TABLE T1 (f1 integer, f2 char (20));");
System.out.println("Table t1 created");
cStmt.execute("CREATE INDEX IDX ON T1 ( f1);");
System.out.println("Index created on T1 (f1) ");
cStmt.close();
con.commit();
PreparedStatement stmt = null, selStmt= null;
stmt = con.prepareStatement("INSERT INTO T1 (f1, f2) VALUES (? ?);");
int count =0;
int ret =0;
for (int i =0 ; i< 10 ; i++)
{
stmt.setInt(1, i);
stmt.setString(2, String.valueOf(i+100));
ret = stmt.executeUpdate();
if (ret != 1) break; //error
count++;
}
stmt.close();
con.commit();
System.out.out.println("Total Rows inserted " + count);
count =0;
stmt = con.prepareStatement("UPDATE T1 SET f2 = ? WHERE f1 = ?");
for (int i =0; i< 10 ; i +=2)
{
stmt.setString (1, String.valueOf (i+200));
stmt.setInt(2, i);
ret = stmt.executeUpdate();
if (ret != 1) break; //error
count++;
}
stmt.close();
con.commit();
System.out.println ("Total Rows updated ");
count =0;
stmt = con.prepareStatement ("DELETE FROM T1 WHERE f1 = ?;");
for (int i =0 ; i< 10 ; i +=3)
{
stmt.setInt(1, i);
ret = stmt.executeUpdate();
if (ret != 1) break; //error
count++;
}
stmt.close();
con.commit();
System.out.println("Total Rows deleted " + count);
count =0;
selStmt = con.prepareStatement("SELECT * from T1 where f1 = ?;");
ResultSet rs = null;
for (int i =0; i< 10 ; i++)
{
selStmt.setInt(1, i);
rs = selStmt.executeQuery();
while (rs.next())
{
System.out.println("Tuple value is " + rs.getInt(1) + “ ” + rs.getString(2));
count++;
}
rs.close();
}
selStmt.close();
con.commit();
System.out.println("Total Rows selected " + count);
cStmt.execute ("DROP TABLE T1;");
System.out.println ("Dropped table T1");
cStmt.close();
con.close();
}catch(Exception e){
System.out.println("Exception in Test: "+e);
e.printStackTrace();
}//try end
}//main method
}//class
Java Code Example output
Table t1 created Index created on T1 (f1) Total Rows inserted 10 Total Rows updated 5 Total Rows deleted 4 Tuple value is 1 101 Tuple value is 2 202 Tuple value is 4 204 Tuple value is 5 105 Tuple value is 7 107 Tuple value is 8 208 Total Rows selected 6 Dropped table T1
<< ODBC And CSQL - TOC - Squirrel SQL Client >>