Accessing Databases with JDBC

jDataLab

6 minute read

A database driver provides interfaces for data retrieval from existing databases. With a database driver, an application can perform database operations to either query or update a database. In a Java application, to connect with individual databases, JDBC (Java Database Connectivity) requires drivers for each distinct database type.

If you want to know what a database driver does, read What is a Database Driver?

For instance, if a Java application needs a connection to data source from a MySQL database, we must include the library MySQL JDBC Driver which is a Jar file, namely mysql-connector-java-5.1.23-bin.jar. The version number in the Jar file may vary. The name of the driver is com.mysql.jdbc.Driver.

If we develop a Java application project in the NetBeans IDE, the MySQL JDBC Driver can be imported by right-click the node Libraries in the Projects tab window.

The following part will show you how to access, query and update a database by using the interface DriverManager. (An alternative to the DriverManager facility is the interface DataSource.)

Before starting this tutorial, you should already have a database counselor set up. If you haven’t do so, follow Creating a New MySQL Database in NetBeans IDE to create the database and populate it as well.

1. Loading a JDBC driver by Class.forName

Before making a database connection, load the driver by calling Class::forName. Class::forName returns the Class object associated with the class or interface with the given string name.

We need to import the library java.sql.Connection.

import java.sql.Connection;

To load the MySQL JDBC driver, firstly, specify the driver name in a string:

1String driverName = "com.mysql.jdbc.Driver";

Then call Class::forName with the driver name:

Class.forName(driverName);

Be aware that Class::forName may produce an exception if the loading of the driver has failed. The exception type is ClassNotFoundException.

To handle the exception, we can surround the Class.forName statement with a try-catch statement. The following snippet shows one way of handling the exception. Whenever the exception occurs, display a message in a Java Swing dialog box.

1try {
2	Class.forName(driverName);
3} catch (ClassNotFoundException e) {
4	JOptionPane.showMessageDialog(null, "Could not load the driver", null, JOptionPane.INFORMATION_MESSAGE);
5}

2. Establishing a Database Connection by DriverManager::getConnection

After successfully loading the driver, we can call DriverManager::getConnection to establish a connection to the given database URL. Its method signature is displayed below.

1public static Connection getConnection(String url,
2                                       String user,
3                                       String password)
4                                throws SQLException

DriverManager::getConnection can have up to three parameters as listed in the following:

  • url: a database url of the form jdbc:subprotocol:subname. To access a MySQL database with the name counselor from the localhost with the default port number, the url is jdbc:mysql://localhost:3306/counselor

  • user: the account name of the database user on whose behalf the connection is being made

  • password: the associated password

DriverManager::getConnection returns a connection to the specified URL. Connection is a public interface. A Connection instance stores a connection (session) with a specific database.

If a database access error occurs or the argument url is null, DriverManager::getConnection throws a SQLException.

Example:

The following statement will attempt to make a connection to the URL jdbc:mysql://localhost:3306/counselor with the username ‘u’ and the password ‘p’ and store the connection in a Connection object myconnection.

1Connection myconnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/counselor", "u", "p");

3. Preparing a SQL Statement by Connection::prepareStatement

After a database connection has been successfully established and a valid Connection object is alive, the application can retrieve data from the connected database by sending SQL statements to the database.

In a JDBC driver, a SQL statement needs to be prepared by calling the interface Connection::prepareStatement.

The method takes an SQL statement in a String and returns a new PreparedStatement object for sending parameterized SQL statements to the database. If a database access error occurs or this method is called on a closed connection, an exception of type SQLException will be thrown. The method signature is shown below.

1PreparedStatement prepareStatement(String sql) throws SQLException

Example:

To prepare a query for all the counselors from the counselor table, with the current Connection object myconnection, the following statements will prepare such a query in a PreparedStatement object query.

1PreparedStatement query = null;    
2query = myconnection.prepareStatement("select * from counselor");

Placeholders

The parameter sql can also contain one or more ? for placeholders that can be substituted with user choices. The PreparedStatement object provides interfaces to make substitutions of various data types.

For instance, the user can look up a counselor for a specific nickname, however, it is unknown until the user enters the name each time. In this case, we can place a ? in the WHERE clause of the SQL statement, later replace it with the user choice by using PreparedStatement::setString.

1PreparedStatement query = null;    
2query = myconnection.prepareStatement("select * from counselor where nick_name=?");
3// statements to get the user choice
4String name = "The Snake";
5query.setString(1, name); // Set the first ? to 'The Snake'

4. Executing a Prepared SQL Statement

Now it is ready for executing the prepared SQL SELECT statement in a PreparedStatement object.

4.1 Run Queries

Given a PreparedStatement object in query, PreparedStatement::executeQuery will run a SQL SELECT statement and return the resulting table in a ResultSet object.

1ResultSet rs = query.executeQuery();

4.2 Run Updates

For SQL Data Manipulation Language (DML) statements, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing such as Create Table, Alter Table, Delete Table, the method PreparedStatement::executeUpdate will execute a DML statement and returns an integer, which is either the row count that have been updated by the statement or zero if the SQL statement returns nothing.

1PreparedStatement update = null;    
2update = myconnection.prepareStatement("update counselor set nick_name=\'Snake\' where nick_name like \'%Snake%\'");
3int result = update.executeUpdate();

5. Parsing a ResultSet Object

A ResultSet object stores a resulting table after executing a SQL query. The set is so complex that special interfaces are needed for reading each row out of the set. There are several ways of extracting rows from a ResultSet object.

Assume that rs is a ResultSet object that has been generated after running a SQL query. The following snippet shows a way of iterating every row in a ResultSet object by using the ResultSet interface and group them into a string result for a display in a JOptionPane dialog box.

 1String result = "";
 2try {
 3    int ncol = rs.getMetaData().getColumnCount(); // get table column count
 4    for (int i = 1; i <= ncol; i++) { // iterate from 1 to column count
 5        result += rs.getMetaData().getColumnName(i) + " "; // read each table column name
 6    }
 7
 8    result += "\n";// append a new line to the result string
 9
10    while (rs.next()) { // when there is a row next, do the loop
11        for (int i = 1; i <= ncol; i++) {
12            String currCol = rs.getString(i) + " "; // read the cell at position i and append a space to it.
13            result += currCol; // append the cell to the result string
14        }
15        result += "\n"; // create a new line
16    }
17
18    rs.last();// move the cursor to the last row.
19    int nrow = rs.getRow(); // get row count
20    result += "The total number of rows is " + nrow + "\n"; // write row size into result
21	
22	JOptionPane.showMessageDialog(null, result, null, 0); // display the result in a message box
23	
24} catch (SQLException ex) {
25    Logger.getLogger(Accessor.class.getName()).log(Level.SEVERE, null, ex);
26}

The dialog box for the parsed result should be similar to the following picture:

6. Closing the Connection

Once completing the operations with the database, close the connection by Connection::close.

1if (myconnection != null && !myconnection.isClosed()) {
2	myconnection.close();
3}