FAQ

Java

JSP

Servlet


Advertisement



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 Printer-friendly version | Send this 
article to a friend Mail this to a friend

Previous Next vertical dots separating previous/next from contents/index/pdf Contents

  |   |