Understanding JDBC and building database applications with it

Background

JDBC stands for Java database connectivity. This includes connecting to DB in Java, running queries and processing results.

A relational DB that has tables consisting of rows and columns. You can interact with a relational DB with -

• JDBC APIs. You get a connection, create a statement and get result set of the query.
• Use JPA(Java persistence API). This uses a concept called ORM (Object relational mapping) where you map Java objects to tables and operate on these objects. For eg. hibernate is one such framework.
• SQL (Structured query language) is used to interact with the relational DB. 

 In this post we are going to understand JDBC.

Interfaces in JDBC

All JDBC classes in Java are part of java.sql.* package.  There are 4 important interfaces that you need to understand -

1. Driver : Know how to get connection from DB
2. Connection : Knows how to interact with DB
3. Statement : Knows how to run SQL on the DB
4. ResultSet : Knows the result returned by the SQL query from the DB.

 To see a sample Java code on how to connect to a DB from Java you can see one of my previous posts -

• Connect to a database in Java

Above code snippet uses mysql DB but you can use any DB really. There are some common things that we sill see in a moment.

NOTE : You no longer have to explicitly load the driver class using Class.ForName(driver). From JDBC 4 driver class is automatically loaded from the class path. 

Building DB application with JDBC

Let's start by looking at how JDBC url looks like and is constructed -

 As you can see JDBC URL is split into 3 parts -

1. 1st part is the jdbc protocol
2. 2nd part is the name of the DB. For eg. mysql, postgres, derby or oracle
3. 3rd part is respective DB specific format

You have already seem mysql connection string in code above -

• jdbc:mysql://localhost:3306/testDB

 Some other examples are -

• jdbc:postgresql://localhost/testDB
• jdbc:oracle:thin:@192.168.1.45:1699:testDB
• jdbc:derby:testDB

 Once you know the URL first step is to load the DB specific driver. As mentioned before traditionally you needed to explicitly load the driver using -

• Class.ForName(driver)

But since JDBC 4 you don't. Java loads automatically for you if it's present in the classpath.  Once driver is loaded next you need to get the Connection from it. You can do so with -

• Connection conn = DriverManager.getConnection("jdbc:derby:testDB");

 NOTE : If you get exception like "java.sql.SQLException: No suitable driver found for..." then the driver is not present on the classpath. Add it.

Once you have the connection you can get the Statement from it as follows -

• Statement stmt = conn.createStatement();

  Once you have the statement you are all set to execute queries on DB -

• ResultSet rs = stmt.executeQuery("select * from countries");
• int res = stmt.executeUpdate("insert into countries values(1, 'India')");

 NOTE : ResultSet points to a location before 1st row when it is result. To access the data you need to call rs.next() which returns a boolean stating if more result is present. If it does you can access it via rs.getInt(1) etc.

NOTE : Column indexes start with 1. So something like rs.getInt(0)will throw SQL exception.

Once you have processed the result set never forget to close the resources and that include your -

• ResultSet
• Statement
• Connection

NOTE : It is very important to close resources in the right order. If you don't want to close it manually you can always use it under try with resource statements so that Java closes them for you. If doing manually you can close it in finally statement with null checks.

Why do we use a DataSource instead of a DriverManager?

 Always use a datasource over DriverManager as-

Client app need not know about the DB details, username , password. App server will take care of it. With datasource all you need is a jndi name properties of which can be configured at app level.

App server takes care of creating and closing connections. You don’t have to manage it in your client application.

Data source has support for creating pool of connection whereas data manager does not.

 Why do we use a PreparedStatement instead of a Statement

You should always use PreparedStatement instead of a Statement. PreparedStatement is subclass of Statement. This has multiple reasons -

• Performance: A PreparedStatement figures out a plan to run the SQL and remembers it. Helps when same query is run multiple times.
  
  • Databases like sql server or oracle databases  have execution plan cache. They can resue the execution plan while running the same query again. This saved efforts in rebuilding execution plan. However this will work only when the sql query is exactly the same. If it has different values than db treats it as different query. In case of Prepared statements in which we use bind parameters we use placeholders so that the query remains the same and subsequently the execution plan. Bind parameters are hidden to the database query optimizer.
• Security: To prevent SQL injection. It's a famous hacking technique. Go ahead read it up on google.
• Readability: No String concatenations in building queries.

Related Links

• Connect to a database in Java(OSFG) 
• Whats new in Java 8?(OSFG)
• Whats new in Java7?(OSFG) 
• Why do we use a DataSource instead of a DriverManager?(SO) 
• Parameterized queries and bind parameters