Java JDBC Tutorial with Examples

package org.o7planning.tutorial.jdbc;

import java.sql.Connection;
import java.sql.SQLException;

public class ConnectionUtils {

    public static Connection getMyConnection() throws SQLException,
            ClassNotFoundException {
  
        // Using Oracle
        // You may be replaced by other Database.
        return OracleConnUtils.getOracleConnection();
    }

    //
    // Test Connection ...
    //
    public static void main(String[] args) throws SQLException,
            ClassNotFoundException {

        System.out.println("Get connection ... ");

        // Get a Connection object
        Connection conn = ConnectionUtils.getMyConnection();

        System.out.println("Get connection " + conn);

        System.out.println("Done!");
    }

}

package org.o7planning.tutorial.jdbc;

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

public class OracleConnUtils {

    // Connect to Oracle.
    public static Connection getOracleConnection() throws SQLException,
            ClassNotFoundException {
        String hostName = "localhost";
        String sid = "db11g";
        String userName = "simplehr";
        String password = "1234";

        return getOracleConnection(hostName, sid, userName, password);
    }

    public static Connection getOracleConnection(String hostName, String sid,
            String userName, String password) throws ClassNotFoundException,
            SQLException {
       
        // Declare the class Driver for ORACLE DB
        // This is necessary with Java 5 (or older)
        // Java6 (or newer) automatically find the appropriate driver.
        // If you use Java> 5, then this line is not needed.        
        Class.forName("oracle.jdbc.driver.OracleDriver");

        String connectionURL = "jdbc:oracle:thin:@" + hostName + ":1521:" + sid;

        Connection conn = DriverManager.getConnection(connectionURL, userName,
                password);
        return conn;
    }
}

package org.o7planning.tutorial.jdbc;

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

public class MySQLConnUtils {


   // Connect to MySQL
   public static Connection getMySQLConnection() throws SQLException,
           ClassNotFoundException {
       String hostName = "localhost";

       String dbName = "simplehr";
       String userName = "root";
       String password = "1234";

       return getMySQLConnection(hostName, dbName, userName, password);
   }

   public static Connection getMySQLConnection(String hostName, String dbName,
           String userName, String password) throws SQLException,
           ClassNotFoundException {
       // Declare the class Driver for MySQL DB
       // This is necessary with Java 5 (or older)
       // Java6 (or newer) automatically find the appropriate driver.
       // If you use Java> 5, then this line is not needed.
       Class.forName("com.mysql.jdbc.Driver");

       String connectionURL = "jdbc:mysql://" + hostName + ":3306/" + dbName;

       Connection conn = DriverManager.getConnection(connectionURL, userName,
               password);
       return conn;
   }
}

package org.o7planning.tutorial.jdbc;

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

public class SQLServerConnUtils_JTDS {


   // Connect to SQLServer
   // (Using JDBC Driver of JTDS library)
   public static Connection getSQLServerConnection()
           throws SQLException, ClassNotFoundException {
       String hostName = "localhost";
       String sqlInstanceName = "SQLEXPRESS";
       String database = "simplehr";
       String userName = "sa";
       String password = "1234";

       return getSQLServerConnection(hostName, sqlInstanceName, database,
               userName, password);
   }

   // Connect to SQLServer & using JTDS library
   public static Connection getSQLServerConnection(String hostName,
           String sqlInstanceName, String database, String userName,
           String password) throws ClassNotFoundException, SQLException {

       // Declare the class Driver for SQLServer DB
       // This is necessary with Java 5 (or older)
       // Java6 (or newer) automatically find the appropriate driver.
       // If you use Java> 5, then this line is not needed.        
       Class.forName("net.sourceforge.jtds.jdbc.Driver");

       // Example:
       // jdbc:jtds:sqlserver://localhost:1433/simplehr;instance=SQLEXPRESS
       String connectionURL = "jdbc:jtds:sqlserver://" + hostName + ":1433/"
               + database + ";instance=" + sqlInstanceName;

       Connection conn = DriverManager.getConnection(connectionURL, userName,
               password);
       return conn;
   }

}

package org.o7planning.tutorial.jdbc;

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

public class SQLServerConnUtils_SQLJDBC {

   // Connect to SQLServer
   // (Using JDBC Driver: SQLJDBC)
   public static Connection getSQLServerConnection()
           throws SQLException, ClassNotFoundException {
       String hostName = "localhost";
       String sqlInstanceName = "SQLEXPRESS";
       String database = "simplehr";
       String userName = "sa";
       String password = "1234";

       return getSQLServerConnection(hostName, sqlInstanceName,
               database, userName, password);
   }

   //
   // Connect to SQLServer & using SQLJDBC Library.
   public static Connection getSQLServerConnection(String hostName,
           String sqlInstanceName, String database, String userName,
           String password) throws ClassNotFoundException, SQLException {
       
       // Declare the class Driver for SQLServer DB
       // This is necessary with Java 5 (or older)
       // Java6 (or newer) automatically find the appropriate driver.
       // If you use Java> 5, then this line is not needed.        
       Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");


       // jdbc:sqlserver://ServerIp:1433/SQLEXPRESS;databaseName=simplehr
       String connectionURL = "jdbc:sqlserver://" + hostName + ":1433"
               + ";instance=" + sqlInstanceName + ";databaseName=" + database;

       Connection conn = DriverManager.getConnection(connectionURL, userName,
               password);
       return conn;
   }

}

package org.o7planning.tutorial.jdbc;

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

public class ODBCConnUtils {

    // Connect to ODBC Data Source named "simplehr-ds".
    public static Connection getJdbcOdbcConnection() throws SQLException,
            ClassNotFoundException {
        String odbcDataSourceName = "simplehr-ds";
        String userName = "simplehr";
        String password = "simplehr";
        return getJdbcOdbcConnection(odbcDataSourceName, userName, password);
    }

    public static Connection getJdbcOdbcConnection(String odbcDataSourceName,
            String userName, String password) throws SQLException,
            ClassNotFoundException {
 
        // Declare the class Driver for JDBC-ODBC Bridge
        // This is necessary with Java 5 (or older)
        // Java6 (or newer) automatically find the appropriate driver.
        // If you use Java> 5, then this line is not needed.        
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

        String connectionURL = "jdbc:odbc:" + odbcDataSourceName;

        Connection conn = DriverManager.getConnection(connectionURL, userName,
                password);
        return conn;
    }
}

public String getString(int columnIndex) throws SQLException;
public boolean getBoolean(int columnIndex) throws SQLException;
public int getInt(int columnIndex) throws SQLException;
public double getDouble(int columnIndex) throws SQLException;

...

public String getString(String columnLabel) throws SQLException;
public boolean getBoolean(String columnLabel) throws SQLException;
public int getInt(String columnLabel) throws SQLException;
public double getDouble(String columnLabel) throws SQLException;

....

package org.o7planning.tutorial.jdbc.basic;

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

import org.o7planning.tutorial.jdbc.ConnectionUtils;

public class QueryDataExample {

    public static void main(String[] args) throws ClassNotFoundException,
            SQLException {

        // Get Connection
        Connection connection = ConnectionUtils.getMyConnection();

        // Create statement
        Statement statement = connection.createStatement();

        String sql = "Select Emp_Id, Emp_No, Emp_Name from Employee";

        // Execute SQL statement returns a ResultSet object.
        ResultSet rs = statement.executeQuery(sql);

        // Fetch on the ResultSet        
        // Move the cursor to the next record.
        while (rs.next()) {
            int empId = rs.getInt(1);
            String empNo = rs.getString(2);
            String empName = rs.getString("Emp_Name");
            System.out.println("--------------------");
            System.out.println("EmpId:" + empId);
            System.out.println("EmpNo:" + empNo);
            System.out.println("EmpName:" + empName);
        }

        // Close connection.
        connection.close();
    }

}

public  Statement createStatement(int resultSetType, int resultSetConcurrency)
       throws SQLException;

// Example:
Statement statement = connection.createStatement(
               ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

// ResultSet can scroll & readonly
ResultSet rs = statement.executeQuery(sql);

package org.o7planning.tutorial.jdbc.basic;

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

import org.o7planning.tutorial.jdbc.ConnectionUtils;

public class ScrollableResultSetExample {

    public static void main(String[] args) throws ClassNotFoundException,
            SQLException {

        // Get a Connection
        Connection connection = ConnectionUtils.getMyConnection();


        // Create a Statement object
        // can be srolled, but not sensitive to changes under DB.
        // ResultSet is readonly (Cannot update)
        Statement statement = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        String sql = "Select Emp_Id, Emp_No, Emp_Name from Employee";


        // Execute statement, return ResultSet.
        ResultSet rs = statement.executeQuery(sql);

        
        // Jump the cursor to last record.
        boolean last = rs.last();
        
        System.out.println("last : "+ last);
        
        
        if(last) {
            // Print out data of last record
            System.out.println("EmpId:" + rs.getInt(1));
            System.out.println("EmpNo:" + rs.getString(2));
            System.out.println("EmpName:" + rs.getString(3));
        }
        
        
        System.out.println("--------------------");
        
        // Move cursor to previous record
        boolean previous =rs.previous();
        System.out.println("Previous 1: "+ previous);
        
        // Move cursor to previous record
        previous =rs.previous();
        System.out.println("Previous 2: "+ previous);       
        
        // Fetch in the ResultSet
        while (rs.next()) {
            // Get value of column 2
            String empNo = rs.getString(2);

            // Then get the value of column 1.
            int empId = rs.getInt(1);
            
            String empName = rs.getString("Emp_Name");
            
            System.out.println("--------------------");
            System.out.println("EmpId:" + empId);
            System.out.println("EmpNo:" + empNo);
            System.out.println("EmpName:" + empName);
        }

        // Close connection.
        connection.close();
    }

}

package org.o7planning.tutorial.jdbc.basic;

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

import org.o7planning.tutorial.jdbc.ConnectionUtils;

public class InsertDataExample {

    public static void main(String[] args) throws ClassNotFoundException,
            SQLException {

        // Get Connection
        Connection connection = ConnectionUtils.getMyConnection();

        Statement statement = connection.createStatement();

        String sql = "Insert into Salary_Grade (Grade, High_Salary, Low_Salary) "
                + " values (2, 20000, 10000) ";
        
        // Execute statement
        // executeUpdate(String) using for Insert, Update, Delete statement.
        int rowCount = statement.executeUpdate(sql);
            
        System.out.println("Row Count affected = " + rowCount);
    }
}

package org.o7planning.tutorial.jdbc.pareparedstatement;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.o7planning.tutorial.jdbc.ConnectionUtils;

public class PrepareStatementExample {

    public static void main(String[] args) throws ClassNotFoundException,
            SQLException {

        // Get a connection
        Connection connection = ConnectionUtils.getMyConnection();

        // Create a SQL statement with two parameters (?)
        String sql = "Select emp.Emp_Id, emp.Emp_No, emp.Emp_Name,"
                + " emp.Dept_Id from Employee emp "
                + " where emp.Emp_Name like ? and emp.Dept_Id = ? ";

        // Create a PreparedStatement object.
        PreparedStatement pstm = connection.prepareStatement(sql);

        // Set value for the first parameter (First '?')
        pstm.setString(1, "%S");

        // Set value for the second parameter (Second '?')
        pstm.setInt(2, 20);

        ResultSet rs = pstm.executeQuery();

        while (rs.next()) {
            System.out.println(" ---- ");
            System.out.println("EmpId : " + rs.getInt("Emp_Id"));
            System.out.println("EmpNo : " + rs.getString(2));
            System.out.println("EmpName : " + rs.getString("Emp_Name"));
        }
        
        System.out.println();
        System.out.println("Set other parameters ..");

        // Reuse PreparedStatement
        // Set other values
        pstm.setString(1, "KI%");
        pstm.setInt(2,10);
        
        // Execute statement.
        rs = pstm.executeQuery();
        
        while (rs.next()) {
            System.out.println(" ---- ");
            System.out.println("EmpId : " + rs.getInt("Emp_Id"));
            System.out.println("EmpNo : " + rs.getString(2));
            System.out.println("EmpName : " + rs.getString("Emp_Name"));
        }
    }
}

// Statement call procedure from Java
String sql = "{call procedure_name(?,?,?)}";

// Statement call function from Java.
String sql ="{? = call function_name(?,?,?)}";

-- This procedure retrieves information of an employee,
-- Input parameter: p_Emp_ID (Integer)
-- There are four output parameters v_Emp_No, v_First_Name, v_Last_Name, v_Hire_Date

Create Or Replace Procedure Get_Employee_Info(p_Emp_Id     Integer
                                           ,v_Emp_No     Out Varchar2
                                           ,v_First_Name Out Varchar2
                                           ,v_Last_Name  Out Varchar2
                                           ,v_Hire_Date  Out Date) Is
Begin
 v_Emp_No := 'E' || p_Emp_Id;
 --
 v_First_Name := 'Michael';
 v_Last_Name  := 'Smith';
 v_Hire_Date  := Sysdate;
End Get_Employee_Info;

-- This procedure retrieves information of an employee,
-- Input parameter: p_Emp_ID (Integer)
-- There are four output parameters v_Emp_No, v_First_Name, v_Last_Name, v_Hire_Date

CREATE PROCEDURE get_Employee_Info(p_Emp_ID     Integer,
                                   out       v_Emp_No        Varchar(50) ,
                                   out       v_First_Name    Varchar(50) ,
                                   Out       v_Last_name    Varchar(50) ,
                                   Out       v_Hire_date      Date)
BEGIN
 set v_Emp_No  = concat( 'E' , Cast(p_Emp_Id as char(15)) );
--
 set v_First_Name = 'Michael';
 set v_Last_Name  = 'Smith';
 set v_Hire_date  = curdate();
END

-- This procedure retrieves information of an employee,
-- Input parameter: p_Emp_ID (Integer)
-- There are four output parameters v_Emp_No, v_First_Name, v_Last_Name, v_Hire_Date

CREATE PROCEDURE Get_Employee_Info
     @p_Emp_Id       Integer ,
     @v_Emp_No      Varchar(50)   OUTPUT,
     @v_First_Name  Varchar(50)   OUTPUT,
     @v_Last_Name  Varchar(50)   OUTPUT,
     @v_Hire_Date    Date             OUTPUT
AS
BEGIN
     set @v_Emp_No  =   'E' + CAST( @p_Emp_Id as varchar)  ;
     --
     set @v_First_Name = 'Michael';
     set @v_Last_Name  = 'Smith';
     set @v_Hire_date  = getdate();
END

package org.o7planning.tutorial.jdbc.callablestatement;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;

import org.o7planning.tutorial.jdbc.ConnectionUtils;

public class CallableStatementExample {

    public static void main(String[] args) throws ClassNotFoundException,
            SQLException {

        // Get a connection
        Connection connection = ConnectionUtils.getMyConnection();


        String sql = "{call get_Employee_Info(?,?,?,?,?)}";

        // Create a CallableStatement object.
        CallableStatement cstm = connection.prepareCall(sql);

       
        // Set parameter value for first question mark
        // (p_Emp_ID)
        cstm.setInt(1, 10);

       
        // Registers the OUT parameter for 2st question mark.
        // (v_Emp_No)
        cstm.registerOutParameter(2, java.sql.Types.VARCHAR);        
       
        // Registers the OUT parameter for 3rd question mark.
        // (v_First_Name)        
        cstm.registerOutParameter(3, java.sql.Types.VARCHAR);

        // Registers the OUT parameter for 4th question mark.
        // (v_Last_Name)        
        cstm.registerOutParameter(4, java.sql.Types.VARCHAR);


        // Registers the OUT parameter for 5th question mark.
        // (v_Hire_Date)        
        cstm.registerOutParameter(5, java.sql.Types.DATE);

        // Execute statement.
        cstm.executeUpdate();

        String empNo = cstm.getString(2);
        String firstName = cstm.getString(3);
        String lastName = cstm.getString(4);
        Date hireDate = cstm.getDate(5);

        System.out.println("Emp No: " + empNo);
        System.out.println("First Name: " + firstName);
        System.out.println("Last Name: " + lastName);
        System.out.println("Hire Date: " + hireDate);

    }
}

package org.o7planning.tutorial.transaction;

import java.sql.Connection;
import java.sql.SQLException;

import org.o7planning.tutorial.jdbc.ConnectionUtils;

public class TransactionExample {

    private static void doJob1(Connection conn) {
        // Todo something here.
    }

    private static void doJob2(Connection conn) {
        // Todo something here.
    }

    public static void main(String[] args) throws ClassNotFoundException,
            SQLException {

        // Get a connection
        Connection connection = ConnectionUtils.getMyConnection();
           
        // Set auto-commit to false
        connection.setAutoCommit(false);

        try {
 
            // Do something related to the DB.
            doJob1(connection);
 
            doJob2(connection);
 
            // Explicitly commit statements to apply changes
            connection.commit();

        }
 
        // Handling exception
        catch (Exception e) {
            e.printStackTrace();
 
            // Rollback changes
            connection.rollback();
        }
 
        // Close connection.
        connection.close();
    }

}

package org.o7planning.tutorial.transaction;

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

import org.o7planning.tutorial.jdbc.ConnectionUtils;

public class BatchExample {

    public static void main(String[] args) throws SQLException,
            ClassNotFoundException {

        Connection conn = ConnectionUtils.getMyConnection();

        try {
            // Create statement object
            Statement stmt = conn.createStatement();

            // Set auto-commit to false
            conn.setAutoCommit(false);

            // Create SQL statement to insert into Employee table.
            String sql1 = "Update Employee emp set emp.Salary = emp.Salary + 100 "
                    + " where emp.Dept_Id = 10 ";
            
            // Add above SQL statement in the batch.
            stmt.addBatch(sql1);

            // Create one more SQL statement
            String sql2 = "Update Employee emp set emp.Salary = emp.Salary + 20 "
                    + " where emp.Dept_Id = 20 ";
            
            // Add above SQL statement in the batch.
            stmt.addBatch(sql2);

            // Create one more SQL statement
            String sql3 = "Update Employee emp set emp.Salary = emp.Salary + 30 "
                    + " where emp.Dept_Id = 30 ";
            
            // Add above SQL statement in the batch.
            stmt.addBatch(sql3);

            // Create an int[] to hold returned values
            int[] counts = stmt.executeBatch();

            System.out.println("Sql1 count = " + counts[0]);
            System.out.println("Sql2 count = " + counts[1]);
            System.out.println("Sql3 count = " + counts[2]);

            // Explicitly commit statements to apply changes
              conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            conn.rollback();
        }
    }

}

package org.o7planning.tutorial.transaction;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.UUID;

import org.o7planning.tutorial.jdbc.ConnectionUtils;

public class BatchExample2 {

    public static void main(String[] args) throws ClassNotFoundException,
            SQLException {

        Connection conn = ConnectionUtils.getMyConnection();

        try {
            String sql = "Insert into Timekeeper(Timekeeper_Id, Date_Time, In_Out, Emp_Id) "
                    + " values (?,?,?,?) ";
            // Create statement object
            PreparedStatement stmt = conn.prepareStatement(sql);

            // Set auto-commit to false
            conn.setAutoCommit(false);

            // Set values for parameters
            stmt.setString(1, UUID.randomUUID().toString());
            stmt.setDate(2, new Date(System.currentTimeMillis()));
            stmt.setString(3, "I");
            stmt.setInt(4, 7839);
            
            // Add to batch
            stmt.addBatch();


            // Set other values for parameters.
            stmt.setString(1, UUID.randomUUID().toString());
            stmt.setDate(2, new Date(System.currentTimeMillis()));
            stmt.setString(3, "I");
            stmt.setInt(4, 7566);

            // Add to batch.
            stmt.addBatch();
        

            // Create an int[] to hold returned values
            int[] counts = stmt.executeBatch();

            System.out.println("counts[0] = " + counts[0]);
            System.out.println("counts[1] = " + counts[1]);

            // Explicitly commit statements to apply changes
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            conn.rollback();
        }
    }

}