How to Use JDBC Java to Dynamically Create a Stored Procedure?

This example demonstrates how to create a stored procedure in JDBC in MySQL database. Assume that we have a table created by the following schema script:

CREATE TABLE  `mydb`.`employees` (
  `EmployeeID` int(10) unsigned NOT NULL default '0',
  `Name` varchar(45) collate utf8_unicode_ci NOT NULL default '',
  `Office` varchar(10) 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;       

We are going to dynamically add one stored procedure to mydb database

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

public class JDBCCreateTable {

    private static final String DBURL = 
             "jdbc:mysql://localhost:3306/mydb?user=usr&password=sql&" +
             "useUnicode=true&characterEncoding=UTF-8";
    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 main(String[] args) {
        Connection con = getConnection();
        Statement stmt =null;
        try {
            stmt = con.createStatement();
            stmt.execute("CREATE PROCEDURE `WhoAreThey`(" +
                   "OUT error VARCHAR(128)," +
                   "IN office VARCHAR(10)) " +
                   "BEGIN "+
                   "SET error = NULL; "+
                   "IF office IS NULL THEN "+
                   "SET error = 'You need to pass in an office number'; "+
                   "ELSE "+
                   "  SELECT EmployeeID, Name FROM " + 
                   " employees WHERE office = office; "+
                   "END IF; "+
                   "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());
                }
            }
        }
    }

}

Printer-friendly version | Mail this to a friend