Introduction to JDBC

October 22 2009Not Commented

Categorized Under: Java

This tutorial is based on the following software environment.

Windows Vista Home Premium, Eclipse 3.5, JDK 1.6, Tomcat 6, MySQL 5, MySQL JDBC driver 5.1

JDBC API consists of two packages.

  • java.sql – this is the main package consists of core classes and interfaces.
  • javax.sql – JDBC optional package provides additional classes and interfaces to access database like DataSource interface.

There are four types of JDBC driver.

  • JDBC ODBC bridge (type 1) – Sun provides interfaces between JDBC and ODBC which converts all JDBC calls to corresponding ODBC calls. Here ODBC driver should be configured in client system to access database.
  • Native API partly java driver (type 2) – This driver uses native API to access database. Many db vendors provide native API which is considerably faster when compared to type 1 driver. This driver is vendor specific because each vendor has its own implementation of API. In many cases we have to install client libraries to use the driver.
  • Net protocol fully java driver (type 3) - here the driver makes a call to middle-ware server using database independent net protocol. The middle-ware server is responsible for converting JDBC API calls to corresponding vendor specific API.
  • Native protocol fully java driver (type 4) – this is the most advanced JDBC driver which makes a direct connection from driver to database using vendor specific protocol. This driver will be different for different vendors because each one has its own protocol based implementation.

JDBC programming consists of the following basic steps.

  • Register the driver. When you initialize the driver class, it is automatically registered with JDBC drivers.
  • Make a connection to the database using Connection object. You may use DriverManager class or DataSource object to create Connection object.
  • Create Statement object. This act as a channel to send SQL queries. Alternatively you may use PreparedStatement which uses precompiled SQL statement.
  • Send SQL statements to database.
  • Process the result returned from SQL statement. For eg, SELECT statement returns number of records. ResultSet object may use for this purpose.
  • Close all resources like ResultSet, Statement and Connection objects. When you use pooled database connection, you may release the connection so that other clients could pick up the connection.

OK, now let us create an example. This tutorial is based on MySQL which is a popular open source database. If you want to know how to install MySQL database in your local Windows machine, please refer the ODBC tutorial.

Lotus Notes/Domino RDBMS integration using ODBC 

If your MySQL db is available in your network, you may access the same using the following information

  • Host Name – Name of the machine where MySQL is installed. Alternatively you may use IP address.
  • User Name / Password – for accessing MySQL

Ask your database administrator for the above details. You may download any SQL client like SQLYog for accessing database.

http://www.webyog.com/en/

Ok, first connect to MySQL from MySQL client. Then create a database named mydb. Switch to mydb database. Create an employee table with four fields.

  • employeeid int not null auto increment
  • name varchar(100)
  • age int
  • designation varchar(100)

Set the primary key as employeeid. Insert some records into employee table.

You may execute the above commands using SQL statements or by utilizing GUI features of MySQL client. For SQL statements refer the ODBC tutorial provided in the above link.

Ok! now your database is ready with an employee table and some records. 

We have to download MySQL JDBC driver. Go to http://www.mysql.com/products/connector/ and download JDBC driver. Unzip the downloaded file. It contains JAR file named mysql-connector-java-<version>-bin.jar file. This JAR file contains APIs for JDBC. We need only this file for our application.

Let us get into Eclipse side. If you haven’t had Eclipse IDE with Tomcat , refer the following tutorial.

Installing and Configuring Eclipse with Tomcat in Windows

If you are not comfortable with Servlets, refer the following tutorial. In this example we are using Servlet to connect to MySQL using JDBC API.

Introduction to Servlets

Open Eclipse. Java EE perspective is the default perspective. If you are not sure go to Window–>Open Perspective–>Other. Select Java EE and click OK.

Go to File–>New–>Dynamic Web project. Project Wizard appears.

servletexample_1

Give the project name as JDBCExample. All other options do not require modification if you are using Eclipse with Tomcat as server. If you have configured multiple servers you may change the target runtime. Click Finish button. Now we have to add the project under server. Go to server window, right click on server entry and select Add and Remove. Move the project from left to right window in properties box. Click Finish button.

For compiling and executing JDBC application, the JDBC JAR file should be available in class path. You may place it in application root –>WEB-INF–>lib folder of application. Any application specific additional JAR files should be placed here. Alternatively you may place the JAR file in application server classpath specific folder. If you are using Tomcat, any JAR file put under Tomcat installation directory/lib folder is shared by all applications and Tomcat internal classes. In Eclipse, all JAR files inside root directory/lib folder of Tomcat are available in classpath for our project if you are using Tomcat as server for Eclipse environment.In older versions of Tomcat it is under common/lib folder of root folder.

Go to Windows explorer. Open Tomcat installed directory–>lib. Now put the JDBC JAR file inside this folder.

jdbcexample_1

 Go to Eclipse and right click on project JDBCExample and select Properties. Go to Java Build Path in Properties box. Under Libraries tab, you may find that all JAR files under root directory/lib folder of Tomcat are available in build path.

OK, now let us create a servlet. In project explorer expand the project. Right click on src folder and select New–>Servlet. Create Servlet wizard appears. Specify the package name as example and Class name as EmployeeListServlet. Press Next button.

 jdbcexample_2

Next screen is about URL Mappings for servlet related to deployment descriptor. Keep it as default and press Next button.

Uncheck the option Constructors from super class. Uncheck the option doPost method. We just require doGet method stub which we want to override in our example. Press Finish button.

EmployeeListServlet.java file is opened in editor. Now paste the following code in doGet method skeleton.

response.setContentType("text/html");
PrintWriter out = response.getWriter();
 
Connection con = null;
Statement stmt = null;
ResultSet rs = null;		
 
try{
     // initialize the driver
     Class.forName("com.mysql.jdbc.Driver");
     // get a connection object
     con = DriverManager.getConnection("jdbc:mysql://localhost/mydb",
     					"root", "password");
     // create statement object
     stmt = con.createStatement();
     // insert one record into employee table
     stmt.executeUpdate("INSERT INTO employee (name, age,   designation)" +
		" VALUES ('Michael', 23, 'manager');");
     // get all records from employee table
     rs = stmt.executeQuery("SELECT * FROM employee;");
     // iterate through result set and show the records in tabular format
     out.println("<table border='1'>");
     out.println("<tr><td>Employee ID</td><td>Name</td><td>Age</td>" +
		"<td>Designation</td></tr>");
     while (rs.next()){
	out.println("<tr><td>"+rs.getString("employeeid")+
	"</td><td>"+rs.getString("name")+"</td><td>"+
	rs.getString("age")+"</td><td>"+
	rs.getString("designation")+"</td></tr>");
     }
     out.println("</table>");
     //close the resources
     rs.close();
     stmt.close();
     con.close();
}catch(Exception e){
 
}

In the above code you have to make some changes. In DriverManager.getConnection() method change localhost to your MySQL DB’s host name or IP address if the DB server is not in your local machine. Also second and third parameters are username and password to access DB. Change these paramaters with your username and password.

We have to import two packages. Add the following statements just below other import statements in top of the file.

import java.sql.*;
import java.io.*;

Save the file. OK, now our servlet is ready. Now let us check the servlet. Go to server window and start Tomcat server. Once server is started go to browser, paste the following URL and hit enter.

 http://localhost:8080/JDBCExample/EmployeeListServlet

If you get the employee records in tabular format, then everything is OK. Now let us walk through the code. If you are using any other application server this URL may be different. Also the port may be different if you change the default Tomcat port.

First we initialize the driver class. com.mysql.jdbc.Driver is the driver class for MySQL. This may be different for different driver versions and also for other DB drivers. Then we get a Connection object using DriverManager.getConnection method. First parameter is database url which is again different for other drivers. You have to go through the driver documentation for more information. In our case this URL consists of host name and db name. Second parameter is database username and third parameter is password.

After connection is established, we create Statement object. This is used to send SQL queries. Then we insert one record into employee table using INSERT statement.

Then we send SELECT query to get all records from employee table. This result is stored in ResultSet object. We iterate through the result set and send output using PrintWriter object.

Finally we close Connection, Statement and resultSet object as a clean-up process. Please note that you have to close these objects in reverse order. From ResultSet to Connection.

cheers,

Leave a Reply

*