JDBC Introduction

JDBC is an unofficial acronym for java database connectivity. JDBC helps us to connect to a database and execute SQL statements against a database. JDBC api provides set of interfaces and there are different implementations respective to different databases.

I wrote an article describing difference between abstract class and interface. Lot of people are asking for example with a nice usage scenario. JDBC is a direct and excellent example. JDBC provides a set of interfaces and it is a contract between java developers and database providers. Every database is unique in its own way and we dont want to create separate programs for each database.

Java application developers program using the jdbc interfaces and database developer provide the implementation for th jdbc interfaces. We add the respective implementation to the application and using the corresponding database. A classic use of java interfaces.

JDBC Overview

JDBC was first introduced as part of JDK 1.1. JDBC api has its own version and latest version being JDBC 4.1 part of Java SE 7. JDBC 4.1 is a maintenance release of JSR 221.

Using JDBC in our application can be segregated into three major steps as below,


    //step 1 - getting connection
    Connection connection = DriverManager.getConnection(
                            "jdbc:jdbcDriver:database",
                            databaseUsername, dbpassword);

    //step 2 - query execution and getting result
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery("SELECT * FROM employee");

    //step 3 - parsing the result from ResultSet
    while (resultSet.next()) {
        int empid = rs.getInt("empid");
        String empname = rs.getString("empname");
    }

Above is a general and basic illustration of using JDBC.

Database Connection

Getting a database connection consists of two substeps loading driver and creating connection.

Types of JDBC Drivers

There are four types of JDBC drivers,

  1. ┬áType 1 (Bridge) – JDBC-ODBC Bridge – calls native code of locally available ODBC driver.
  2. Type 2 (Native) – Native-API / Partly Java Driver – calls vendor’s native driver on client side and this code calls database over network.
  3. Type 3 (Middleware) – All Java / Net-Protocol Driver – pure-java driver that calls the server-side middleware.
  4. Type 4 (Pure), All Java / Native-Protocol Driver – pure-java driver that uses database native protocol.

Loading Driver

In JDBC 4.0 this step is not required and all the all divers found in classpath are loaded automatically. When we want to load a java class into JVM we use Class.forName and the same is followed here. We choose a type of JDBC driver and use Class.forName to load it.

try {
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(Exception e){
	System.out.println("Exception is loading the driver."+e);
}

Getting a Connection

There are two classes that allows to connect to a database DriverManager and DataSource. DriverManager is most popular and easier to use.
Key object is Connection which allows to interact with database and execute queries. DriverManager has a method named getConnection and we use this to get an instance of Connection.

For getConnection we need to pass username, password of database and a connection url. JDBC Connection URL varies based on the database used. Following are for some of the popular databases,

try{
 Connection connection = DriverManager.getConnection(connectionUrl,dbUserName,dbPassword");
}
catch( SQLException sqlEx ){
	System.out.println( "Exception in getting a connection"+sqlEx);
}

SQL Execution and Getting Results

Using the Connection obtained we execute SQL statements. Before executing the SQL we need to compose the statements. There are three difference ways for creating a sql statement and they are by using Statement or PreparedStatement or CallableStatement.

Statement:
This is used for creating a static sql statement. Why do we call this as static? Everytime we use this Statement object the sql query is compiled, irrespective of it is used earlier or not. Generally this Statement is used in simple scenarios and when we are sure a same query will never be executed again.

PreparedStatement:
We can use PreparedStatement to create a pre-compiled SQL statement. It gives better performance compared to Statement as it is precompiled and this is applicable when the same sql will be executed multiple times.

   PreparedStatement pstmt = connection.prepareStatement("UPDATE
   EMPLOYEE SET name = ? WHERE empid = ?");
   pstmt.setString(1, "Joe");
   pstmt.setInt(2, 10829);
 

CallableStatement:
This is different from the above two statement. This is specially used for executing stored procedures.

Once the statement is creating using any one of the above three statements then we call their respective execute methods and get the result back from database.

Parsing Results

ResultSet is the object returned when a select query is performed. ResultSet represents multiple records of data. We can read records from a ResultSet using a cursor. Cursor is pointer to a record. By default, after we read a record the pointer moves to the next record then we can read the following records. TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE are three types using which the cursor is controlled. This section calls for a detailed separate article :)

I am going to write a series of articles on JDBC and will cover the following JDBC Driver Types, PreparedStatement, CallableStatement, ResultSet, Cursors, Transactions, Stored Procedures, Metadata. Please add a comment to let me know if you want me to write on any other thing specific in JDBC.

JDBC Basic Example

package com.javapapers.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCBasicExample {
	private Connection connection = null;
	private Statement statement = null;
	private ResultSet resultSet = null;

	public static void main() throws Exception {

		try {

		// using mysql driver - download and add in classpath
		Class.forName("com.mysql.jdbc.Driver");

		connection = DriverManager.getConnection("jdbc:mysql://localhost/employeedb?"
							+ "user=dbusername&password=dbpassword");
		statement = connection.createStatement();
		resultSet = statement.executeQuery("select * from employee");

		while (resultSet.next()) {
			String employeename = resultSet.getString("name");
			int empid = resultSet.getString("empid");
			Date dateofBirth = resultSet.getDate("dateofbirth");
			System.out.println("Employee Name: " + employeename);
			System.out.println("Employee ID: " + empid);
			System.out.println("Employee Date of Birth: " + dateofBirth);
		}

		} catch(SqlException sqe) {
			sqe.printStacktrace();
		}
		finally {
		try {
			resultSet.close();
			statement.close();
			connection.close();
		}catch(Exception e){}
		}
	}
}

This JDBC tutorial was added on 17/06/2012.

Comments on "JDBC Introduction" Tutorial:

  1. shubham says:

    NICE ARTICLE

  2. Bhimaray says:

    thank you very much. can you explain me the difference between comarable,clonable and comarator interfaces

  3. Moovendhan says:

    Hi Joe,

    “Database developer provide the implementation for the jdbc interfaces”. This means for every DB (oracle,Mysql,etc) we need a different driver right ? and also what is the difference between these drivers and the four types of driver u mentioned ? Pls clarify.

    Nanri :)

  4. vanshaj says:

    Very Nice.. especially the diagram you explained is basically the thing we lack in interviews..:)

  5. Anonymous says:

    Was looking for a article on JDBC for my friends.. Got it.. Thanks..

  6. Mayur Kumar says:

    Hi Joe,

    it was nice article. it refreshed the JDBC knowledge. Can you please tell us when we have to choose which typw of databse drivers. which one is good in which situation.

    Thanks!

  7. Rajashekhar says:

    Useful article, Thank you…..

  8. alok says:

    very nice topic
    thanks

  9. Asraful says:

    Another six by you joe. Thanks a lot. Waiting for article on different type of jdbc connector.

  10. kartheek says:

    hey buddy, u r an excellent techie….carry on man, u rocks….!!!

  11. Shailendra kumar shail says:

    It would be so nice if you can throw some more focus on TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE
    with example..

  12. Sandeep Singh says:

    thanks Joe .. useful Article

  13. Deepti says:

    Hey Joe,

    Thanks for sharing.If you don’t mind would you please categorize the DB drivers performance wise

  14. Rajesh says:

    Hey Joe,

    Fantastic.nice article on jdbc…U rock man….

  15. vadhiraju says:

    hi joe,

    How the abstract class and interface
    works in the JDBC scenario.pls explain it.

  16. Y.Jayaramreddy says:

    Hi Joe,

    Thank you so much for your article.

    Y.Jayaram Reddy.

  17. Rashi Coorg says:

    Thanks a lot.. very nice one!!

  18. GeekDude says:

    Hi JOE, Clearly mentioned on Statements and resultset. Can you please explain difference between PreparedStatement and CallableStatement.

    Thanks.

  19. Arjun says:

    hi joe,

    how to insert images and document in database.I got few information related to that (i.e)need use clob or blob datatypes in database.but i dont know how to process the image in java(i.e) serialiseable.please help me..

    Thanks,
    Arjun

  20. sekar says:

    very article..

  21. srinivas says:

    Joe, good article. it would be good if you provide new features or feature list in version based for the jdbc release. Also mension the available Jdk version for each jdbc release. Thx

  22. Sameer says:

    Joe, recently I came across your blog and found it very interesting.
    Regarding JDBC, as explained Drivers provided by Databases contains the actual implementation regarding getting the connection, what about the driver types. Does all the databases need to provide implementation for all the driver types.

  23. Anonymous says:

    Please write an article on Rowset as well

  24. Anonymous says:

    awesome Sir…really find useful…

  25. Spandana says:

    Easy to understand. Thank you very much.

  26. Anjali says:

    Hi Joe

    Thanks for sharing the knowledge.

  27. sweta says:

    Nicely explained about the jdbc stuff

  28. bala says:

    JDBC is not this less… Need to cover more! You may need to write another post on this.

    Performance tuning JDBC is a major topic to tocuh.

  29. vineesh says:

    very nice article joe.thanks

  30. Murali says:

    Really nice blog which guided and explained me in creating DB connection using JDBC. Congrats Joe

  31. Praveen says:

    Hi Joe,
    Most of the times we see loading jdbc odbc driver. Can you give scenarios where other jdbc drivers can be used?

  32. RKG says:

    Hi,
    Very nice..
    Thanks for your explanation.

  33. abhishek says:

    plz explain this line

    DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”);

  34. abhishek says:

    nc article.:)

  35. […] Update and Delete in this server side application. We know how to do a basic web application using java jdbc and deploy it in server. So just for a change, here I have used PHP […]

  36. Taufique says:

    Joe nice article.
    Explained nicely and in a very decent manner.

    Thanks for your posts. :)

  37. Rami says:

    can anyone plz explain what a savepoint is? and how to handle dead locks in transactions?

  38. Joe says:

    Savepoint is a place in a transaction till where you can rollback the changes done. Without savepoint, if you rollback all the changes will be reverted. With savepoint, changes done till savepoint will be preserved.

    Handling dead locks in transaction is a large topic and requires a separate article for itself :-)

  39. Ankita says:

    Very clear and very helpful

  40. Alexandr says:

    Hi,

    Is some limitations of mysql server or mysql jdbc driver?
    If i work with resultset more 30 minutes, i get less records than really contain in database table. And have no errors or warrnings.

  41. Srikanth says:

    Thanks !! Greatly Explained !! Really helped me to prepare myself for the Web Technologies Exam tomorrow !!
    Thanks
    ~Srikanth

  42. Sumit Nigade says:

    thank you sir very clean and conceptual cover all concepts Excellent atricle

  43. jan says:

    Thanks Sir ,very nice and useful article

    i have one query , when i try the above example i need to create connection of static , please clarify this

  44. khushboo says:

    i am satisfied this notes thanks…

  45. remkl says:

    you are d0ing a marvellous job… joe.. well done.maverick.. i haven’t seen such a chaste usage of web to spread knowledge and showcase one’s technical prowess.. keep doing .

  46. Gowthami says:

    nice site

  47. Adlin says:

    Hi Joe,

    can you explain.why we’re using Class.forName in Jdbc connection

  48. somasekhar says:

    very very great platform to learn
    thank you

  49. Bogdan says:

    GG mate! Nice well-structured article.

  50. vikas yadav says:

    type 4, pure native java driver are best for use ,,,, they are portable and platform independent ,, other drivers are not platform independent ,, you will need native code and that work’s with the help of Java Native Interface (JNI) ,, so for different – 2 OS there will be different drivers ,,, ,, But not sure ,, :)

  51. manoj joon says:

    Dear joe
    please write article on Resultset,preparestatment,callablestatment,cursors and transactions

  52. suganya says:

    Can you please write about rowsets.

Comments are closed for this "JDBC Introduction" tutorial.