» Unlabelled » JDBC

JDBC

Posted by Unknown Thursday, 14 November 2013 0 comments
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,
  • Obtaining a database connection.
  • Execute queries against the connected database and receive results.
  • Process the received results.
//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,
  • MySQL JDBC Connection URL: jdbc:mysql://hostname:3306/ – 3306 is mysql default port
  • Java DB Connection URL: jdbc:derby:testdb;create=true
  • Microsoft SQL Server JDBC Connection URL – jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=northwind
  • Oracle JDBC connection URL – jdbc:oracle:thin:@hostname:1521:MyDatabase
  • PostgresSQL JDBC Connection URL – jdbc:postgresql:template1
  • DB2 JDBC Connection URL – jdbc:db2:test
  • Derby JDBC Connection URL – jdbc:derby:/test;create=true
  • H2 Connection URL: jdbc:h2:tcp://localhost:9092/test
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.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){}
    }
  }
}

Categories: icon
Pin It

Related Post:



0 comments:

Post a Comment

Copyright © 2012 Java . All rights reserved.