7/10/11

Java : Using JDBC API Tutorial

You need to use database drivers and the JDBC API while developing a Java application to retrive or store data in a database. The JDBC API classes and interfaces are available in the java.sql  and the javax.sql  packages.


The commonly used classes and interfaces in the JDBC API are :


DriverManager class : Loads the driver for a database.

span style="font-family: Verdana, sans-serif;">
Driver interface : Represents a database driver. All JDBC driver classes must implement the Driver interface.


Connection interface : Enables you to establish connection between Java application and a database.


Statement interface : Enables you to execute SQL statements.


Resultset interface : Represents the information retrieved from a database.


SQLException class : Provides the information about the exception that occurs while interacting with the databases.

To query a database and display the result using Java applications, you need to :
  • Load a driver
  • Connect to a database
  • Create and execute JDBC statements.
  • Handle SQL Exceptions.
Loading a Driver :


The first step is to load the driver and register the required driver using driver manager. you can load and register a driver :
  1. Programmatically :
    • Using the forName( ) method
    • Using the registerDriver( ) method
  2. Manually :
    • By setting the system property.
Using the forName( ) method :
The forName( ) method is available in the java.lang.Class class. The syntax to load a JDBC driver to access a database is :
                                 Class.forName("<driver-name>");
Using the registerDriver( ) method :


You can create a instance of the Driver class to load a JDBC driver. The syntax is :
                                 Driver d = new <driver-name>;
Once you have created the Driver object, call the registerDriver( )  method to register it with the DriverManager. Example :
                                DriverManager.registerDriver(d):
Connection to a Database :


You can create an object of the Connection  interface to establish connection between the Java application and the database. You can create multiple Connection objects in a Java application to access and retrieve data from multiple databases. the DriverManager class provides the getConnection( ) method that helps to establish connection. It has the following three forms :


  1. Connection getConnection (String <url>) : Accepts the JDBC URL of the database, which you need to access, as a parameter. You can use the following code snippet to connect to a database using getConnection( ) method with a single parameter :

    String url="jdbc:odbc:MyDataSource";
    Connection con=DriverManager.getConnection(url);

    The syntax for JDBC URL is :

    <protocol>:<subprotocol>:<subname>
    • Protocol name : Indicates the name of the protocol that is used to access the database. In JDBC, the name of the protocol is always jdbc.
    • Sub-Protocol name : Indicates the mechanism to retrieve data from a database. For example, you use the JDBC-ODBC bridge to access the database, then the name of the sub-protocol is odbc.
    • Subname : Indicates the Data Source Name (DSN) that contains the database information, such as the name of the database, location of the database server, username, and password to access a database server.
  2. Connection getConnection( String <url> , String <username> , String <password>) : Accepts the JDBC url  of a database. It also accepts the username and password  of the authorized database user.You can use the following code snippet to connect to a database using getConnection( ) method :

    String url="jdbc:odbc:MyDataSource";
    Connection con=DriverManager.getConnection(url,"NewUser","NewPassword");
  3. Connection getConnection ( String <url>, Properties <properties> ) : Accepts the JDBC URL of a database and an object of the java.util.Properties as parameter.

    You can use the following code snippet to connect to a database by specifying properties :

    String url="jdbc:odbc:MyDataSource";
    Properties p= new Properties();
    p.setProperty("user","NewUser");    p.setProperty("password","NewPassword");
    Connection con=DriverManager.getConnection(url,p);


Creating and Executing JDBC Statements :


You need to create Statement object to send requests to and retrieve results from a database. The Connection object provides the createStatement() method to create a Statement object. You can use the following code snippet to create a Statement object :


Connection con = DriverManager.getConnection("jdbc:odbc:MydataSource","User","Password");
Statement stmt=con.createStatement();
The Statement interface contains the following methods to send static SQL statements to a database :
  1. ResultSet executeQuery(String str) : Executes SQL statement and returns a single object of the type, ResultSet. The syntax for executeQuery() method is :

    Statement stmt = con.createStatement();
    ResultSet rs= stmt.executeQuery(<SQL Statement>);
  2. int executeUpdate ( String str ) : Executes the SQL statements and returns the number of data rows that are affected after processing the SQL statement. When you need to modify data in a database table using the Data Manipulation Language (DML) statements, you can use the executeUpdate( ) method. The syntax is :

    Statement stmt = con.createStatement();
    int count= stmt.executeUpdate(<SQL Statement>);
  3. boolean execute ( String str ) : Executes an SQL statement and returns a boolean value. You can use this method when the type of SQL statement passed as parameter is not known or when the statement being executed returns a result set or an update count.


Handling SQL Exceptions :


The java.sql package provides the SQLException class, which is derived from the java.lang.Exception  class. The SQLException is thrown by various methods in the JDBC API and anavles to determine the reason of the errors that occur while connecting a Java application to a database. The SQLException  class provides the following error information :
  1. Error Message : Is a string that describes error.
  2. Error code : Is an integer value that is associated with error. The error code is vendor-specific and depends upon database in use.
  3. SQL state : Is an X/OPEN error code that identifies the error. Various vendors provide different error messages to define same error. As a result, an error may give different error message.
The SQLException  class contains various methods that provide error information. the methods in the SQLException class are :
  1. int getErrorCode() : Returns the error code associated with the error occurred.
  2. String getSQLState() : Return X/Open error code.
You can use the following code snippet to catch SQLException :


try{
      String str = "DELETE FROM authors WHERE au_id='998-72-3568'";
      Statement stmt = con.createStatement();
      int count = stmt.executeUpdate(str);
     }catch ( SQLException e){
      System.out.println("Error : " + e.getErrorCode());
     }


NOTE : Here is a post on connection on SQL Server 2008 and Java using JDBC-ODBC Bridge.

SHARE THIS POST: