Step 1) First get mysql-connector-java-5.0.5.jar from jar finder website and put this on your class path. If you are using eclipse then configure this jar in your project as :
Right Click On Your Project-> Build Path->Configure Build Path->Libraries Tab-->Add External Jar choose your jar location and then jar and then Click on Open Then Click on Open
Step 2) Create a Procedure as follows for fetching data from Employee table as below blog in mysql. Procedure should be like below. Make sure inserts some data in Employee table
DELIMITER $$
DROP PROCEDURE IF EXISTS `getEmpName` $$
CREATE PROCEDURE `getEmpName`(IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN
SELECT name INTO EMP_FIRST
FROM Employee
WHERE ID = EMP_ID;
END $$
DELIMITER ;
Step 3) Call Procedure From Java Code as Follows
package test.myapp.jdbc;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
//In This call we use only interfaces to refere objects. JDBC Objects class exist in mysql jar file
public class CallableStatementExample {
public static void main(String[] args) {
// jdbc:mysql is protocol by which i can request for connect with
// database and 3306 is port number where my server can listen request
String dbUrl = "jdbc:mysql://localhost:3306/testdb";// testdb is
// database in mysql
Connection conn = null;
CallableStatement stmt = null;// this is CallableStatement reference
// variable
try {
// STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
// STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(dbUrl, "root", "root");
// STEP 4: Execute a query
System.out.println("Creating statement...");
String sql = "{call getEmpName (?, ?)}";
stmt = conn.prepareCall(sql);
// Bind IN parameter first, then bind OUT parameter
int empID = 1;
stmt.setInt(1, empID); // This would set ID as 102
// Because second parameter is OUT so register it
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
// Use execute method to run stored procedure.
System.out.println("Executing stored procedure...");
stmt.execute();
// Retrieve employee name with getXXX method
String empName = stmt.getString(2);
System.out.println("Emp Name with ID:" + empID + " is " + empName);
stmt.close();
conn.close();
} catch (SQLException se) {
// Handle errors for JDBC
se.printStackTrace();
} catch (Exception e) {
// Handle errors for Class.forName
e.printStackTrace();
} finally {
// finally block used to close resources
try {
if (stmt != null)
stmt.close();
} catch (SQLException se2) {
}// nothing we can do
try {
if (conn != null)
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}// end finally try
}// end try
System.out.println("Goodbye!");
}// end main
}
