Java Database Connector (JDBC)

 Java Database Connector (JDBC)

Basic Principles

Basically, the JDBC API is a software library providing a number of Java classes and interfaces that allows programmers to:

^Ø  Establish a connection to a wide range of different Relational Database Management Systems (RDBMS). Note, that each major RDBMS, such as Oracle, Microsoft SQL, or MySQL is supported.

^Ø  Access a database within a particular system.

^Ø  Manipulate the structure of a database by creating, deleting, or altering relations from that database.

^Ø  Manipulate the content of a database by inserting, deleting, or updating records in database relations.

^Ø  Retrieve the content from a database by querying the database.

Basically, the JDBC operates with two main classes:

1.     DriverManager class operates with a library of drivers for different DBMS implementations. The DriverManager class loads requested drivers, physically installs connrection to a database and return an instance of a data class "Connection".

2.     An instance of the class "Connection" represent a single connection to a particular database. All the communication to the database is carryed out via this object.

Installing Connection:

Establishing JDBC Connection means obtaining a correct instance of so-called "Connection" class.

Usually, establishing a connection is carried out in two steps:

1.                 loading an appropriate JDBC driver for the installed RDBMS.

2.                 installing connection and getting reference to the Connection object Loading a JDBC driver is very simple and involves just a single line of Java code.

...

try

{ Class.forName("com.mysql.jdbc.Driver"); } catch(ClassNotFoundException exc){exc.printStackTrace();}

...

This line of code just notifyes the DriverManager which particular Java class should be

loaded as a JDBC driver class.

The next step in establishing a database connection is a message to loaded driver requesting actual connection to the RDBMS. The operation is carryed out by sending message "getConnection" to the driver manager. Note that "DriverManager" returns a "Connection" instance that is used for further processing the database.

try

{

...

Connection connection_;

String dbms = "jdbc:mysql://" + host + "/" + db;

connection_ = DriverManager.getConnection(dbms, username, password);

}

catch(ClassNotFoundException exc){exc.printStackTrace();}

Method "getConnection()" accepts three arguments:

1.     A so-called Database URL, which encoded using standard URL syntax (protocol + host + object). The protocol part starts always with "jdbc:" folowed by the name of the RDBMS (in our case "mysql") and terminated with "://" symbols. Thus, the protocol part in our example is "jdbc:mysql://". The host part identifies a server where the DBMS is running. In our case (Servlets & DBMS on the same computer) "localhost" can be used to identify the host. Finally, the name of a particular database must be supplied preceeded with the slash character. In our case this would be "/example".

2.     A registered username that has the proper privileges for manipulating the database.

3.     A password valid for the username.

Working with a Database

In order to actually work with a databaase, a special "Statement" class is used.

In order to create an instance of such "Statement" class, a message "createStatement" is sent to the previously created instance of JDBC connection.

...

try

{

Statement statement = connection_.createStatement();

}

catch(SQLException exc)

{

exc.printStackTrace();

}

If an error occurs during the execution of the createStatement() method a SQLException will be thrown. Instances of the Statement Class provides a public interface to insert, update, or retrieve data from a database. Depending on a particular database operation, an appropriate method should be invoked. For instance,

•                     executeUpdate() can be used to insert data into a relation

•                     executeQuery() can be used to retrieve data from a database

...

try

{

String insert_sql_stmt = "INSERT INTO " + table + " VALUES(" + values + ")"; statement.executeUpdate(insert_sql_stmt);

}

catch(SQLException exc){exc.printStackTrace();} ...

Other methods of the "statement" class can be also applyed to its instances.

if we need to retrieve the keys automatically generated by the "executeUpdate" statement, we need to pass the "Statement.RETURN_GENERATED_KEYS" argument in advance.

try

{

String sql = "INSERT INTO " + table + " VALUES(" + values + ")"; statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS); ResultSet keys = statement.getGeneratedKeys();

}

catch(SQLException exc){exc.printStackTrace();}

Similarly, to retrieve data from a database we need to obtain an instance of the Statment class, and then to invoke executeQuery() method on this instance. This method takes a string containing SQL source as an argument.

try

{

String sql = "SELECT ...";

ResultSet query_result = statement.executeQuery(sql);

}   catch(SQLException exc){exc.printStackTrace();}

Note, that the "sql" argument should contain a valid SQL Select statement. The executeQuery() method returns an instance of the ResultSet class. Generally, execution of any JDBC statement that returns data from a database, results in an instance of the ResultSet class. This instances may be seen as a number of rows (tuples) that hold the current results. The number and type of columns in this object corresponds to the number and types of columns returned as the result from the database system.

Consider the following sample database:

Customer(cn,cname,ccity);

Product(pn,pname,pprice);

Transaction(cn,pn,tdate,tqnt);

...

try

{

String sql = "SELECT * FROM Customer;";

ResultSet query_result = statement.executeQuery(sql);

...

The "executeQuery" command will result in obtaining an instance of the ResultSet class which will hold all tuples from the Customer table as rows, each row will contain 3 values: "cn", "cname" and "ccity". Normally, the SQL statement exlicitly defines the "ResultSet" internal structure. Once when we set a current row of the ResultSet, we can retrieve values by means of a number of methods. The methods correspond to a column type. Thus, to retrieve the value of a string column, we invoke a getString() method. Similarily, to retrive an integer value we simply invoke a getInt() method.

try

{

String sql = "SELECT cname, pname, qnt";

sql = sql + " FROM Customer, Product, Transaction";

sql = sql + " where Customer.ccity = \"Graz\" And"; sql = sql + " Customer.cn = Transaction.cn And"; sql = sql + " Transaction.pn = Product.pn";

ResultSet query_result = statement.executeQuery(sql); while(query_result.next())

{

String customerName = query_result.getString("cname"); String productTitle = query_result.getString("pname"); int productQuantity = query_result.getInt("cid");

... }