JDBC insights / Database interaction using JDBC / Connecting to database using JDBC

JDBC stands for Java DataBase Connectivity and is a technology for database interaction from java program. JDBC is a collection of multiple interfaces and classes which provide methods for database connection and operations. For performing any operation on a database using JDBC, requires some steps. They are :

  • Load Database driver class.
  • Retrieving Database Connection.
  • Getting Statement or PreparedStatement object.
  • Getting ResultSet (Required only when a Select operation is performed and you want to iterate through the records fetched)

We shall dig into each of the above steps in detail.

Loading Database Driver class

Load the appropriate driver class in memory for the database type which we want to connect. Driver classes for each database vendor are different. For Example, database driver class for MySQL is com.mysql.jdbc.Driver.This is done using forName method of java.lang.Class class as :

   Class.forName("com.mysql.jdbc.Driver");

As soon as we load the driver class, this class is registered with java.sql.DriverManager class which uses it while making a database connection. Starting java 6  JDBC 4.0, this step is not necessary as the driver manager automatically loads the driver class if it finds the driver library added in the classpath.


Retrieving Database Connection

Database connection can be retrieved using one of various getConnection() methods of java.sql.DriverManager class. All methods differ in the type of arguments they take but all methods take the url of the database to which you want to connect.
URL of the database is in format jdbc:subprotocol:subname which means that the URL for every database should start with jdbc and they differ in subprotocol and subname according to the database vendor, where subprotocol and subname are arbitrary strings. URL also contains the name of the database to which we want to connect.
All connection methods return a java.sql.Connection object which may be visualized as a database connection. java.sql.Connection is an interface and its implementation lies in the database driver which we need to add in the classpath of our application.
Different versions of getConnection() method are as follows :

  1. getConnection(String url) – This method takes only the url of the database to which we are connecting. This method is generally used when the database to which we are connecting does not require a user name and password to get connected.
  2. getConnection(String url, String user, String password) – This method takes the database URL and the user name and password required to connect to the database.
  3. getConnection(String url, Properties properties) – This method takes the URL of the database and a Properties object which is of type java.util.Properties and contains connection properties such as user name and password in the form of name-value pairs.

Here is how we would get connection for a MySQL database using second variant of getConnection() method :

   DriverManager.getConnection("jdbc:mysql://localhost:3306/codippa","root","root");
   // here codippa is the database to which we are connecting

Getting Statement or PreparedStatement object

Either of these two objects are required when we want to execute a query over a table in the database to which we connected above. Both of these objects belong to java.sql package and can be used to execute queries. But then comes the question, why two objects? when both do the same job.
They both do the same job but there are differences between the two. Elaborating the differences requires a separate space but for now, the main difference is that java.sql.Statement can execute only static queries. Static means you cannot set explicit parameter values in an SQL query using a java.sql.Statement object except using String manipulations while java.sql.PreparedStatement has the capability of setting parameter values in dynamic queries.

For example, Select * from users where userId=1 is a static query while Select * from users where userId=? is a dynamic query where you don’t know the userId until execution time. If you are using java.sql.Statement then you cannot set the value of userId parameter using Statement object. You can only modify the query by appending the userId to its end by String manipulation and pass it to the Statement object for execution. But with java.sql.PreparedStatement, you can set userId values using its setInt() method.

java.sql.Statement and java.sql.PreparedStatement can be obtained using createStatement() and prepareStatement() methods of java.sql.Connection respectively, which was retrieved using one of the DriverManager.getConnection() methods above. Code follows :

  Connection connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/codippa","root","root");
  // get statement object
  Statement statement = connection.createStatement();
  // get prepared statement object
  PreparedStatement prepStatement = connection.prepareStatement();

Getting ResultSet

java.sql.ResultSet object is required only when you fetch records from the database using methods using java.sql.Statement or java.sql.PrepareStatement objects. This object is required for iterating through the records and represents the table data. java.sql.ResultSet maintains a cursor pointing to a row of table data and it initially positioned before the first row. It has a next() method which moves this pointer to the next row and returns true if it is positioned to the next row and false, if there are no more rows to point to.

java.sql.ResultSet object has methods to retrieve column values based on their index (such as getString(int columnIndex)) and their names (such as getString(String columnName)).

Concluding the post, complete code for executing a query, fetching a record and iterating through it follows :

   public static void executeQuery() throws ClassNotFoundException, SQLException {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
                        // load driver class
                        Class.forName("com.mysql.jdbc.Driver");
                        // get database connection
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/codippa", "root", "root");
			statement = connection.createStatement();
                        // fetch a record
                        resultSet = statement
					.executeQuery("select * from post where Id=1");
			int column = 1;
                        // iterate over the record
			while (resultSet.next()) {
                                // fetch column value based on its index
				System.out.println(resultSet.getString(column));
				++column;
			}
		} catch (ClassNotFoundException e) {
			throw e;
		} catch (SQLException sqe) {
			throw sqe;
		} finally {
                        if (statement != null) {
				statement .close();
			}
			if (connection != null) {
				connection.close();
			}
		}
	}

Let’s tweak in :

  1. java.sql.Connection, java.sql.Statement, java.sql.PreparedStatement and most other entities in JDBC are interfaces and their implementation lies in driver libraries. This makes sense so that each Database vendor can provide its own implementation for these interfaces according to their database products.
  2. People might ask that instead of loading a class using Class.forName, why can’t we directly make an instance of the driver class as that would also load the class. Well, think of the scenario when the class name is being read from a properties file or XML. In such scenario, Class.forName can only be used.
  3. Besides holding user name and password in DriverManager.getConnection()method, the properties object may also hold other connection parameters such as database port.
  4. Database name which is given in getConnection() method should exist at at the time of getting connection otherwise an unknown database error is thrown.
  5. All methods of database interaction throw a java.sql.SQLException which needs to be handled or re-thrown by your code.
  6. Loading a class has a risk that the class could not be found on the classpath and hence Class.forName method throws a java.lang.ClassNotFoundException.
  7. It is a good practice to close the java.sql.Connection and java.sql.Statement objects in finally block and put a null check before closing them.
  8. Closing a java.sql.Statement object automatically closes any java.sql.ResultSet object that the statement may have opened so there is no need of explicitly closing the java.sql.ResultSet object.

Learnt something from this post? Great!!! Don’t forget to share this post.

This Post Has 4 Comments

  1. Was really helpful. Thank you

    1. Thanks Ruchika. Keep visiting !!!

  2. very good article!

    1. Thank you..Keep visiting !!!

Mark Your Impression

Close Menu