How to Use JDBC Statement Objecct?

Once a connection to a particular database is established, that Connection object can be used to send SQL statements. The Java JDBC's Statement objects are JDBC's way of executing SQL statements to the database.A Statement object is used to send SQL statements to a database.

There are three main types of Statement objects: the base class Statement, the PreparedStatement, and the CallableStatement. These objects are instantiated from your JDBC Connection object.

• Statement: Execute simple sql statement without parameters. For example, to creates an SQL Statement object with DBConnection object which is a database Connection object.
  

      ....
      
      Statement s = DBConnection.createStatement(); 
  //Creates an SQL Statement object
      ....
  

• PreparedStatement: Execute a pre-compiled SQL statement with or without IN parameters. It is useful because it avoids manual conversion of Java types to SQL types. PreparedStatement objects are precompiled
  

      ...
      PreparedStatement ps = DBConnection.prepareStatement(

            "INSERT INTO Table1 VALUES (?, ?, ?)");
    ...

• CallableStatement: Execute a call to a database stored procedure. CallableStatement objects are SQL stored procedure call statements
  

     ...
     CallableStatement cs = DBConnection.prepareCall("CALL myStoredProcedure");
     ...
  

The following is a simple example, for MySQL, which including

• How to use these three kinds of Statement in Java
• How to dynamically create MySQL table by using JDBC in Java
• How to dynamically create MySQL procdure by using JDBC in Java

package com.xyzws;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

public class JDBCSample {
    
    private static final String DBURL = 
              "jdbc:mysql://localhost:3306/mydb?user=usr&password=sql";
    private static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
  
    static {
        try {
            Class.forName(DBDRIVER).newInstance();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    private static Connection getConnection() 
    {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(DBURL);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void createEmployees()
    {
        Connection con = getConnection();
        Statement stmt =null;
        String createString;
        createString = "CREATE TABLE  `mydb`.`employees` ("+
           "`EmployeeID` int(10) unsigned NOT NULL default '0',"+
           "`Name` varchar(45) collate utf8_unicode_ci NOT NULL default '',"+
           "`CreateTime` timestamp NOT NULL default CURRENT_TIMESTAMP,"+
           "PRIMARY KEY  (`EmployeeID`)"+
           ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";            
        try {
            stmt = con.createStatement();
            stmt.executeUpdate(createString);
        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    }
    private static void dropEmployees()
    {
        Connection con = getConnection();
        Statement stmt =null;
        String createString;
        createString = "DROP TABLE IF EXISTS `mydb`.`employees`;";            
        try {
            stmt = con.createStatement();
            stmt.executeUpdate(createString);
        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    }
    
    private static void insertEmployee() 
    {
        Connection con = getConnection();
        PreparedStatement ps = null;

        try {
            ps = con.prepareStatement(
                    "INSERT INTO Employees VALUES (?, ?, ?)");
            ps.setInt(1, 1000);
            ps.setString(2, "David Walker");
            ps.setNull(3, Types.TIMESTAMP);
            ps.executeUpdate();
            ps.clearParameters();
            ps.setInt(1, 1001);
            ps.setString(2, "Paul Martin");
            ps.setTimestamp(3, new java.sql.Timestamp(
                      System.currentTimeMillis()));
            ps.executeUpdate();
        } catch (SQLException e) {
            System.err.println("SQLException: " + e.getMessage());
        }
        finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    }
    
    private    static void createProcedure() {
        Connection con = getConnection();
        Statement stmt =null;
        try {
            stmt = con.createStatement();
            stmt.execute("CREATE PROCEDURE  `mydb`.`SayHello`(IN id INT) " +
                       "BEGIN " +
                       "SELECT name FROM employees WHERE EmployeeID = id; " +
                       "END");

        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
        
    }
    private    static void dropProcedure() {
        Connection con = getConnection();
        Statement stmt =null;
        try {
            stmt = con.createStatement();
            stmt.execute("DROP PROCEDURE IF EXISTS `mydb`.`SayHello`");
        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
        
    }
    private    static void callProcedure() {
        Connection con = getConnection();
        CallableStatement cs = null;
        try {
            cs = con.prepareCall("{call SayHello(?)}");
            cs.setInt(1, 1001);
            cs.execute();
            ResultSet rs = cs.getResultSet();
            if (rs.next()) {
                System.out.println("Name : " + rs.getString(1));
            }
        } catch (SQLException e) {
            System.err.println("SQLException: " + e.getMessage());
        }
        finally {
            if (cs != null) {
                try {
                    cs.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    }
    public static void main(String args[]){
        createEmployees();
        insertEmployee();
        createProcedure();
        callProcedure();
        dropProcedure();
        dropEmployees();
    }
}

Printer-friendly version | Mail this to a friend