Course Content‎ > ‎

Section 07: Database Connectivity


Introduction

The vast majority of professional web sites today have some sort of database connectivity. Webmasters have hooked online front ends to all manner of legacy systems, including package tracking and directory databases, as well as newer systems such as e-Commerce Systems. Although database-backed systems may be more challenging to develop, the advantages of allowing a database to manage data records are manyfold. Within the database, data definition and manipulation is handled through Structured Query Language (SQL) as was covered in the previous section. Typically, the application tier (in this module anyway!) is implemented through the Java programming language. Now, since the database doesn't "speak Java" directly and Java isn't SQL, we need to form an interface to allow the two tiers to communicate.


Java Database Connectivity (JDBC)

In the late 90s, Sun Microsystems became known for working with vendors to define Java-based and vendor-friendly APIs for common services. Sun had a habit of adopting the best ideas in the industry and then making the Java implementation an open standard - usually successfully. The Java Database Connectivity API, called JDBC was a perfect example. An API, or Application Programming Interface is a set of classes, methods, and resources that programs can use to do their work. JDBC is a Java API for database connectivity, which is part of the overall Java API developed by Sun Microsystems.

JDBC was based largely on Microsoft's ODBC (Open Database Connectivity) but has since largely surpassed it. Compared to ODBC, JDBC has more flexible APIs which programmers can use in their applications. JDBC has all the advantages that ODBC has and caters to the needs of programmers under a variety of platforms. JDBC provides Java developers with an industry standard API for database-independent connectivity between Java Applications (Applets, Servlets, JSPs, EJBs etc.) and a wide range of relational database management systems such as Oracle, Informix, Microsoft SQL Server and Sybase. JDBC accomplishes most of what it does through a native API that translates Java methods to native calls. In its simplest form, JDBC makes it possible to do the following:

  • Connect to a database

  • Execute SQL statements to query your database

  • Generate query results

  • Perform updates, inserts and deletions

  • Execute Stored Procedures

The benefits of using JDBC include the following:

  • A developer only needs to write one API to access any relational database

  • There is no need to rewrite code for different databases

  • There is no need to know the database vendor's specific APIs

  • It provides a standard API and is vendor independent

  • Almost every database vendor has some sort of JDBC driver

  • JDBC is part of the standard Java 2 platform


JDBC Architecture

The JDBC architecture consists of two layers: first, the JDBC API, which supports Java application-to-JDBC Driver Manager communications and secondly the JDBC Driver API, which is handles Driver Manager-to-Database communications. The figure below shows the structure of the main interfaces and classes within JDBC and how JDBC programs interact with databases.

Figure 7.1. JDBC Architecture


As in ODBC, JDBC has an underlying driver manager that takes care of bridging the application with the DBMS. The driver manager supports multiple drivers connecting to many databases. The drivers themselves can be written either in Java or by using native methods. A driver written purely in Java can be used in any platform by downloading or being a part of an applet for example. A driver written using native methods gets tied up to the underlying platform in which the application runs or the applet runs.

JDBC works under two models, the two-tier model and the three-tier model.


JDBC Two-Tier Model

In this model, the application communicates directly with the database as shown in the figure below.

Figure 7.2. JDBC Two-Tier Model


The client consists of the application and the JDBC driver(s). The client takes the responsibility of presentation, business logic, transaction management and resource management. The JDBC driver also resides on the client. This driver receives the request from the application and performs the necessary transformation (ie. transforms the request to vendor specific database calls). This transformed request is passed to the DBMS directly. Because the application and the driver reside on the same machine, the connection between the two is direct. The server is the DBMS, which receives the request from the client in a format it can understand.

The major disadvantage with this model is that there is a heavy burden on the client, as it takes care of both the presentation as well as the business logic (check back on the earlier sections of this course!). Also, the number of concurrent connections is limited because the connections are held by the application.


JDBC Three-Tier Model

This model consists of a client, a middle tier, and a server. The client holds the application front-end, which concentrates on the presentation and communicates with the middle tier requesting data. The middle tier takes care of the business logic, communicating with the data source, handling transactions and connection pooling. The middle tier typically consists of an application, an application server and the JDBC driver. The application takes care of the business logic. The application server manages transactions, handling concurrent connections and identifying the drivers. The JDBC driver provides the connectivity to the data source and implemented the JDBC API, sending the request in the form that the data source can understand. The third layer is the actual data source, which is typically a DBMS (but can be any information system with JDBC driver support, such as a spreadsheet). The advantages in using a three-tier model have already previously been discussed, and won't be repeated here. Essentially, this architecture offers advantages in scalability, usability, maintainance and performance.

Figure 7.3. JDBC Three-Tier Model




Connecting to a Database

Connecting to a database consists of the following steps:

  1. Load the JDBC Driver: The driver class should be in the CLASSPATH environment variable when running an application, or within the relevant libraries of the application server. The specific driver class is loaded into the JVM for use later, when we go to open up a connection to the database. A class can be loaded by using the Class object's forName() method. When the driver is loaded into memory, it registers itself with the java.sql.DriverManager classes as an available database driver. Typically, the code is:

    Class.forName(<driver class>); eg. Class.forName("oracle.jdbc.driver.OracleDriver");
  2. Connect to the Database by using the getConnection() method of the DriverManager object. The parameters to this method is the database's URL username, and password. The database's URL contains the address of the database residing in the network and any other information such as the subprotocol and the port number the middle tier is listening to (if three-tier). JDBC URLs usually begin with jdbc:subprotocol:subname. For example, the Oracle JDBC-Thin driver uses a URL of the form of jdbc:oracle:thin:@database:port:sid. The JDBC-ODBC bridge uses jdbc:odbc:data-sourcename;odbcoptions. During the call to getConnection(), the DriverManager object asks each registered driver if it recognises the URL. If a driver says yes, the driver manager uses that driver to create the Connection object. The basic construct for this stage is as follows:

    DriverManager.getConnection(, username, password); eg. Connection con = null; con = DriverManager.getConnection(" jdbc:oracle:thin:@ee417.c7clh2c6565n.eu-west-1.rds.amazonaws.com:1521:ee417", "EE_USER", "EE_PASS");
  3. Perform Database Operationsthe desired operations can then be executed, such as creating statements, executing statements and manipulating the ResultSet object. All this can be done only when the connection is live (open). For example, a simple query is performed by creating a Statement object and using the executeQuery() method of Statement (Note: for updates etc. you should use executeUpdate()).  JDBC supports three types of statements namely: Statement (used for executing an SQL Statement immediately),PreparedStatement (used for executing a compiled SQL statement) and CallableStatement (used for executing Stored Procedures). Examples will be provided covering the first two types of Statement, however since we did not cover Stored Procedures in detail, we will avoid more detail on the CallableStatement type.

    This method returns a java.sql.ResultSet which encapsulates the retrieved data. The ResultSet object can be seen as a representation of the query result returned one row at a time. Typically, a while loop is used to enumerate through the rows and return the data to the application. So a basic example for this stage is:

    Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT SURNAME, FIRSTNAME FROM EE_ADMIN.CUSTOMERS WHERE ID=2311"); while (rs.next()) { System.out.println("Surname=" + rs.getString("SURNAME") + " and Firstname = " + rs.getString("FIRSTNAME")); } Expected output: Surname=Corcoran and Firstname=Sheila
  4. Release Resources - Connections are costly. When all necessary operations are complete, it is safe and advisable to close the Statements, ResultSets and Connections. Eg.

    if (rs != null) rs.close(); if (stmt != null) stmt.close(); if (con != null) con.close();


JDBC Example Applications

Firstly, we provide a very basic application program, which you can try yourself. The machine 136.206.35.131 is currently running an Oracle database and it can be connected to through the standard port of 1521. There are a few things needed to get this application working:

  • Sample Database Structure - The sample database structure is based upon the tables encountered in the previous chapter on databases. At the end of the databases section, there is a set of links to the various log files, showing how existing tables have been set up within the database. If you need at any stage to discover how the database is laid out, access privilages etc. - the information can typically be found within these set up logs.

  • A Java Compiler - Java has been installed on the laboratory computers alloted to Postgraduate Students. Please let me know if there are any issues with regards to Java working on these machines. As has been mentioned in the course description, basic Java knowledge is a prerequisite for this module and core concepts such as compiling and executing will not be dealt with in detail. Users at home, should download an appropriate Java Development Kit from Sun. The Downloads Page contains information regarding a JDK.

  • JDBC Drivers - The vendor-specific (ie. Oracle) JDBC drivers are required in order to connect the the Oracle database. These drivers will be required at both compile and execution time. These full Oracle bundle of JDBC Drivers for the current database is linked below. Please note, that it is a little large, so if you're at home, started the download and grab some coffee! JDBC for Oracle 

  • Source Code - Naturally enough, we need the program itself which is linked below. It is on the server as a .java file so you should save it to wherever you intend to do the compilation and execution (ie. make a directory). JDBCExample.java

Once you have all the various pieces in place, you should have a directory containing the file and the unzipped drivers. The unzipped drivers should be represented by two directories, javax and oracle (only oracle is used in this example). You should then be able to compile in the following way:

javac -classpath .;ojdbc6.jar JDBCExample.java

Then to run the application, you should do the following (working output is also shown!):

java -classpath .;ojdbc6.jar JDBCExample Connecting to the VCP Database...... Connection Successful..... creating statement.... Name=Joe Murphy Name=Peter Smith Name=Mary Jones Name=Jack McFadden Name=Sheila Corcoran Name=Ann Ryan

Important: Ensure that your ojdbc6.jar file is in the current directory in which you are compiling and executing if you are working from command line.  Alternatively, ensure that the ojdbc6.jar file is added to your classpath environment variable.  The steps for adding environment variables were worked through during the installation instructions for Java linked from the Downloads Page.

WARNING: For various security reasons, certain ports are blocked on the central DCU networks.  One of these is the database port 1521 on the local wireless network.  Therefore, this example will *NOT* work if you are on the wireless network.  To get these examples working you must be either off-campus or on a wired engineering network.

Hopefully now at this stage, you are connecting to the database - try a few alternative SELECTs using what you learned in the database chapter. How about modifying the above example to provide the following output (don't worry about the order!). This only takes a minor modification, but should help your understanding about merging SQL into JDBC statements.

java -classpath .;ojdbc6.jar JDBCExample2 Connecting to the VCP Database...... Connection Successful..... creating statement.... Ann Ryan drives registration 90C15432 Jack McFadden drives registration 0D7733 Joe Murphy drives registration 0D32909 Mary Jones drives registration 3MH7733 Joe Murphy drives registration 0L1733 Peter Smith drives registration 2C4423 Sheila Corcoran drives registration 99MH90430 Ann Ryan drives registration 2DL6328

To do this problem, you should check back to the databases section, under Joins. If you really need to cheat though, here is a link to the working source code

Some of the more industrious students may have already, or are thinking about writing an application which will modify, add information or change database structure on these tables. While this is certainly encouraged, in order to preserve the above examples in working order, privilages have not been granted to perform these actions on the EE_Admin schema. However, EE_User itself has its own schema which students are free to work with, creating database structures, modifying existing structures, inserting, updating and deleting information. It should be noted that this is shared account which means that students will automatically have full access to any tables you create - please make sure to use unique names for your tables (columns are ok once they are within a unique table) perhaps proceeding them with your initials, such as DM_EMPLOYEES. Please respect other students tables and avoid malicious behaviour, which could cause downtime for all other students. Please do not use potentially infinite loops to populate tables with data, as this would most likely cause adverse behaviour.

To ease the creation of JDBC applications, you should use the SQL Query Tool, which allows you to try queries in advance directly, before implementing them in a Statement through JDBC. This is particularly useful if you are not very familiar with SQL, which you should practice using the Query Tool, referencing the notes. Queries that work within the query tool, should also typically work within your JDBC based code.


Prepared Statements

As was mentioned previously, there are three different types of statements used in JDBC. The PreparedStatement object is used for precompiling an SQL statement. This precompiled statement can be effectively used whenever needed. A query passed to a prepared statement consists of parameters that need to be filled in before passed for execution. The parameters can be set by using the setXXXmethods in the PreparedStatement interface, where XXX identifies the type of parameter, such as integerstringdouble etc. For for example:

PreparedStatement pstmt = con.prepareStatement("INSERT INTO CUSTOMERS (ID,SURNAME,FIRSTNAME,EMAIL,COUNTRY,PHONE) VALUES (?,?,?,?,?,?)"); pstmt.clearParameters(); // Clears any previous parameters pstmt.setInt(1, 3054); pstmt.setString(2, "Darcy"); pstmt.setString(3, "Simon"); pstmt.setString(4, "darcys@marconi.com"); pstmt.setString(5, "United States"); pstmt.setString(6, "+44 213 5553343"); pstmt.executeUpdate();

For a statement, such as an UPDATE, DELETE or DDL, the method to use is executeUpdate(). If the return type of the SQL statement is unknown, you should use the execute() method. Finally, for a SELECT statement that returns a ResultSet Object, the method to use isexecuteQuery(). I.e.

ResultSet rs = pstmt.executeQuery();

PreparedStatement's are precompiled by the database for faster execution. Once it has been compiled, it can still be customized by adjusting the predefined parameters. Prepared statements are particularly useful in applications that have to run the same SQL command over and over. Additionally, they have an important usefulness when applied to JDBC. Consider the scenario where we wish to perform the following SQL:

INSERT INTO CUSTOMERS (ID,SURNAME,FIRSTNAME,EMAIL,COUNTRY,PHONE) VALUES (3843,'O'Riordan','Sally','sals@yahoo.com','Ireland','024543221')

As was discussed in the Database section, 'O'Riordan' is problematic - the string terminates twice. The solution in SQL is to add an extra ' to make 'O''Riordan' which would allow the statement to execute correctly. However, this solution requires you to escape every character that your database treats as special character - which causes a coding overhead and is no longer platform independent. Let us consider the following Java method, which uses the normal Statement object:

private boolean addCustomer(Integer id, String lastname, String fname, String email, String country, String ph) { ...... assuming that drivers/connection had been set up etc. Statement stmt = con.createStatement(); stmt.executeUpdate("INSERT INTO CUSTOMERS (ID,SURNAME,FIRSTNAME" + ",EMAIL,COUNTRY,PHONE) VALUES (id,lastname,fname,email,country,ph) stmt.close(); ...... etc... }

If the value "O'Riordan" was passed in as the lastname parameter to the method - the Statement will never execute. This means we would either have to write some sort of parser, which handles special characters or we can use the PreparedStatement, which allows us to simply:

pstmt.setString(2, lastname);

Problem Solved!


Transaction Control

As discussed in the section on 'Transactions' in the Databases Chapter, if you are executing a series of update statements in a single transaction, it is important to ensure that all succeed or all fail. The example given earlier was that if a customer ordered an item, both the updating of the orders table and the decrementing of the quantity in the stock table are necessary for a successful transaction. Using a transaction-aware DBMS, you can begin a transaction, perform any number of actions, and either commit the results to the database or roll back all of your SQL statements.

Transaction management with JDBC takes place via the Connection object. By default, when a new Connection object is created, it is set to auto commit mode, which means that every time you execute a statement, it is committed to the database. However, the Connection object provides methods to explicitly control a transaction and commit or roll back at will:

void setAutoCommit(boolean autoCommit) throws SQLException void commit() throws SQLException void rollback() throws SQLException }

At the beginning of a transaction, we call setAutoCommit(false). Then, if the group of update operations is succesful, we call the commit()method. If any error occurs, we call the rollback() method to undo any changes.

As an example let us consider the following code snippet:

try { con.setAutoCommit(false); stmt = con.createStatement(); stmt1 = con.createStatement(); stmt2 = con.createStatement(); stmt3 = con.createStatement(); stmt1.execute("DROP TABLE NEWTESTTABLE"); stmt.execute("CREATE TABLE NEWTESTTABLE (ID INTEGER, NAME VARCHAR(50), PRIMARY KEY(ID))"); stmt2.executeUpdate("INSERT INTO NEWTESTTABLE VALUES (25, 'John Doe')"); stmt3.executeUpdate("INSERT INTO NEWTESTTABLE VALUES (23, 'Ann Jones')"); con.commit(); } catch (SQLException e) { try { con.rollback(); } catch (Exception excp) { System.out.println("Error occurred during rollback!"); } }

This snippet is taken from the full transaction example, which you should try for yourself. While not exceptionally apparent why a transaction would be suitable in this situation, this simple example just demonstrates how transactions are coded in JDBC. Using this example, it is possible to apply it to any situations where transactions would be suitable (multiple grouped updates/inserts etc.).

The two first SQL statements (the DROP and CREATE TABLEs) are principally in place so that we have a table in the database to work on. It is primarily the last two INSERT statements that we are concentrating on. Essentially what the setAutoCommit(false)con.commit() andcon.rollback() statements ensure is that either:

  • Both of the INSERT statements are executed if no problems

  • Neither of the INSERT statements are executed if problems occur

The transactions apply to UPDATES and INSERTS, so in this example if we were to rollback(), the table would still have been dropped and subsequently created.

Try out the above example source and get it running. Then add in another statement which updates the 'Ann Jones' entry to an ID of 25 (violating the primary key uniqueness constraint) and run the application again. What happens? Try your own further examples!

Note: During rollback only data manipulation statements are rolled back.  Data definition statements (such as dropping and creating the structures) are not rolled back as they are immediately committed.


Servlets and Databases

All database operations that can be performed on a stand-alone application can be performed on servlets too. In this scenario, all database operations are performed on the server side, and only data is passed to the client. The client is provided with no information regarding where the information has come from (ie. database details, username/password etc.). In fact, any type of Java server application (not just servlets) can connect to a database using JDBC.

Here is an example servlet which will open a connection to the database, read in some information, output that information to the browser and close the connection.  The example is essentially a servlet example of JDBCExample.java.

Source Code: JDBCServlet.java 


Connection Pooling

An application using databases as storage for information requires a lot of communication among the database engines to retrieve the information. When multiple clients access the same Web Application, the burden falls on the server to satisfy the entire client request and to dispatch the requested information. Naturally enough, the server should be efficient and powerful enough to both handle these requests and to handle the connection between itself and the DBMS. However, connecting to a database is a time-consuming activity since the database connection requires resources (communication, memory, authentication, and so on) to create each connection, maintain it and then to release it when it is no longer required. The overhead is particularly high for Web-based applications because Web users connect and disconnect more frequently. Establishing a connection once and then reusing it for subsequent requests can dramatically improve performance and response times of Web-based applications. However, allowing clients to hold on to connections can limit the number of connections which can be made to your Web Applications. For example, the default number of Connections which Oracle handles is 50 (although this can be greatly increased).


Figure 7.5. Non-Pooled Architecture


In a recent example I made, comparisons were made between code creating a new database connection vs code utilising a pooled connection. These two connections are made, with a simple select statement from a small table (same one in each case). The first connection is made through standard JDBC techniques of obtaining a Connection through a JDBC driver, creating and executing a Statement. A typical output from this part of the servlet is as follows:

JDBC Driver Version is: 8.1.7.1.0 Data = molloyda Database Query/Connection Total Time = 20 ms Database Connection Time Only = 16 ms

It can be seen how the database connection time was responsible for 16ms of the 20ms total transaction duration. The Statement and processing of the ResultSet accounted for 4ms. It should be noted, that if the query was more complex, using large tables, joins or complicated views this could be considerably larger, possibly even exceeding the connection time. However, the 16ms connection time is unnecessary with a readily pooled set of connections. The second part of the output shows the overhead in the situation where a ConnectionPool is used:

Connection using thin drivers (with connection pooling!)..... JDBC Driver Version is: 8.1.7.1.0 Data = molloyda Database Connection/Query Time = 4 ms Database Connection Time Only = 0 ms

Use of the connection pool in this circumstance results in a reduction of 75% of the entire duration. In fact the connection time is essentially reduced to 0ms, as the Connection is already waiting to be used. The most obvious benefit of Connection Pooling is in quicker response times to the end user, particularly from less powerful servers and remote databases. The 16ms in this case is a relatively powerful machine running a local database - if the database were remote the connection time could be considerably larger, and more noticable to the client. Another major benefit applies to the resources on the server and in particular connectivity with the database. If a database could simultaneously handle up to a maximum of 100 connections and assuming that all transactions were identical to that above we would have:

Figure 7.6. Connection Pooling


100 * 1000/20 = 5,000 connections per second maximum (non pooled) or 100 * 1000/4 = 20,000 connections per second maximum (connection pooled)

It should be noted that as the database statements become more complex the benefit of pooling is less significant (but still beneficial).  For example, if the database connection took 16ms but the operation took 500ms, then the comparison would be:   100*1000/516 = 194 connections per second      vs  100*1000/500 = 200 connections per second.  

However, the obvious benefits in resources are immediately apparent. While these values both seem somewhat high, when you consider that connections in practice may take considerably longer and multiple connections per transaction may be made, the database could rapidly become the bottleneck in your Web Application.

Most decent Application Servers establish a pool of database connections that is shared by all the applications. Connection Pools can intelligently manage the size of the pool and make sure each connection remains valid.

Comments