Header javaperspective.com
JavaPerspective.com  >   Advanced Tutorials  >   2. JDBC - database access  >   2.1. What is JDBC?

2.1. What is JDBC?
Last updated: 28 January 2013.

Java DataBase Connectivity is a standard Java API which provides simple ways to interact with relational databases.

JDBC offers a database-independent API that can be used to connect a Java application to a local or remote database and issue SQL statements. For example, you can use the JDBC API to select rows from a table, insert rows, update rows and delete rows. The key point is that these operations are performed with the same set of JDBC API calls, regardless of the type of database management system (Oracle, Microsoft SQL Server, PostgreSQL, IBM DB2, MySQL,...). For each type of database, JDBC uses a driver which translates each JDBC API call into a database-specific protocol that is understandable by the database management system.

Hence, there is a JDBC driver for each type of database. JDBC drivers are simple JAR files that you have to include in your application's classpath. For most types of databases, you can find and download a free driver. As an example, if you want to access an Oracle database with JDBC, you can use the Oracle driver ojdbc6.jar. Likewise, you can use sqljdbc4.jar if you want to access a Microsoft SQL Server database.

To handle drivers, JDBC provides a driver manager that is used to create database connections. The following figure shows how the JDBC architecture is structured:


JDBC architecture



The rest of this page explains how to connect to a database with JDBC. A typical database access involves 3 steps:
  1. Establish a connection with the database by calling one of the methods getConnection provided by the class java.sql.DriverManager.
  2. Use the created connection to execute one or more SQL statements.
  3. Close the connection by calling the method close provided by the class java.sql.DriverManager.
To establish a connection with the database, you need to provide a connection URL. The format of a connection URL depends on the driver in use. For example, if you are accessing an Oracle database with ojdbc6.jar, the connection URL will look like this:

jdbc:oracle:thin:@localhost:1521:orcl
The following sample shows how to access an Oracle database with the above connection URL:

String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String userName =
"yourName";
String userPassword =
"yourPassword";

Connection connection =
null;

try{
   
connection = DriverManager.getConnection(url, userName, userPassword);

   
// Use the connection to execute SQL statements
    // ...
    // ...
}
catch(SQLException e){
   
e.printStackTrace();
}
finally{
   
if(connection != null){
         
try{
               
connection.close();
         
}
         
catch(SQLException e){
               
e.printStackTrace();
         
}
    }
}

In the earlier versions of the JDK, before calling the method getConnection, it was necessary to explicitly load the driver with a call like this:

Class.forName("oracle.jdbc.driver.OracleDriver");

Since Java SE 6, such a call is no longer needed.

As you can see, I have closed the connection in the finally block to ensure that it will be closed in all cases. The reason behind it is that every operating system has a file descriptor limit that must not be exceeded. File descriptors are created by the following objects: java.sql.Connection, java.io.FileInputStream, java.io.FileOutputStream and java.net.Socket. If your application does not close its database connections, you will eventually exceed the file descriptor limit and get this error: java.net.SocketException: Too many open files.

You may also have noticed that SQL exceptions are handled within catch blocks. Instead, you might prefer to leave exception handling to some other code. As an illustration, the method accessDatabase shown below establishes a connection with a SQL Server database without handling SQL exceptions. If SQL exceptions occur, they will be thrown to the caller of the method accessDatabase.

public void accessDatabase() throws SQLException {

   
String url = "jdbc:microsoft:sqlserver://localhost:1433;databaseName=YourDatabase";
    String userName =
"sa";
    String userPassword =
"sa";

    Connection connection =
null;

   
try{
         
connection = DriverManager.getConnection(url, userName, userPassword);

         
// Use the connection to execute SQL statements
          // ...
          // ...
   
}
   
finally{
         
if(connection != null)
               
connection.close();
   
}

}

The next tutorials will show you how to execute SQL statements with JDBC.


You are here :  JavaPerspective.com  >   Advanced Tutorials  >   2. JDBC - database access  >   2.1. What is JDBC?
Next tutorial :  JavaPerspective.com  >   Advanced Tutorials  >   2. JDBC - database access  >   2.2. Selecting rows from a table

Copyright © 2013. JavaPerspective.com. All rights reserved.  ( Terms | Contact | About ) 
Java is a trademark of Oracle Corporation
Image 1 Image 2 Image 3 Image 4 Image 5 Image 6 Image 7