Using JDBC with MySQL stored procedures

Thursday April 19, 2012 ()

Stored procedures are declarative SQL code which can be called by a trigger, another stored procedure or by an application code. In MySQL, they are compiled and stored in table proc of system database mysql. In this blog we will demonstrate how to create stored procedures and call it from a Java application through JDBC.

Creating the Stored Procedure

DELIMITER // 
CREATE PROCEDURE GetAllPosts() 
BEGIN 
SELECT blog_title,blog_content FROM blogs where blog_status=1;
END // 
DELIMITER ; 

Our stored procedure above returns the usual result set. The DELIMITER command changes the standard semicolon delimiter to //. This enables us to enter sql statements in a single procedure terminated by the standard semicolor delimiter. The body of the procedure is enclosed by BEGIN/END pair. We terminated END with our delimiter (//) to signal the end of the stored procedure body. Line 6 above restores back the default semicolon delimiter.

DELIMITER may not always be //. Use pattern that suits your taste.

Stored procedure can also take parameters. Consider the stored procedure below.

DELIMITER // 
CREATE PROCEDURE GetAllPosts(IN dataSize int) 
BEGIN 
SELECT blog_title,blog_content FROM blogs where blog_status=1 limit 0,dataSize; 
END // 
DELIMITER ; 

We used the same SQL statement as in the previous snippet, this time we limit the number of rows returned through dataSize parameter which is of type int. Any valid SQL type can be used as parameter type.

The parameter mode can be IN, OUT or INOUT depending on the purpose of the parameter. In the example above, we used IN to indicate that our parameter is to be used as input only. Below is an example using both IN and OUT parameters.

DELIMITER // 
CREATE PROCEDURE GetPostCount(IN blogType varchar (32), OUT recordCount int) 
BEGIN 
SELECT count(*) into recordCount FROM blogs where blog_type=blogType; 
SELECT recordCount;
END // 
DELIMITER ; 

In the stored procedure above, we use SELECT statement to actually return the OUT parameter.

You can enter the preceeding stored procedures from mysql command line prompt to actually create our stored procedures.

In cases where you want to delete your saved store procedure, issue a drop command like the one shown below.

drop procedure PROCEDURE_NAME

As mentioned earlier, stored procedures in MySQL are saved in mysql.proc table. We make sure that user have SELECT access to this table by granting access to it and avoid access errors. At the mysql command prompt enter the following:

grant select on `mysql`.`proc` to 'user_id' identified by 'user_password';

Please make sure to replace user_id and user_password with the approriate values. Issue the following command to update privileges.

flush privileges;

To execute stored procedures from mysql command prompt, use the call command. The call command takes the following form

call stored_procedure (parameters)

Our stored procedures are simple SQL queries. Stored procedures however can contain loops, conditional statements, declare and manipulate variables like every other programming language. Please see the documentation to learn more about stored procedures.

Using Stored Procedures in JDBC

try (
        Connection conn = DatasourceConnection.getConnection();
        CallableStatement proc = 
                conn.prepareCall("{call GetAllPosts()}");
        ResultSet set = proc.executeQuery()) {

    if (set.first()) {        
		while (!set.isAfterLast ()) {
			// do the job;
		}
    }
}
catch (Exception exception ) {}

The snippet shown above executes our parameterless stored procedure. java.sql.CallableStatement is used to call stored procedures. It is an interface that extends java.sql.PreparedStatement. The stored procedure returns a resultset, similar to the result set returned by a regular SQL query using java.sql.Statement (or PreparedStatement). Another way to use CallableStatement is demonstrated below. This example uses IN and OUT parameter from our stored procedure GetPostCount.

String worldNews ="world";

try (
        Connection conn = DatasourceConnection.getConnection();
        CallableStatement stmt = 
                conn.prepareCall("{call GetAllPosts(?, ?)}")) {                

    stmt.setString (1,worldNews);
    stmt.registerOutParameter(2, java.sql.Types.INTEGER);

    ResultSet set = stmt.executeQuery();            
    int count = set.getInt(2);

    // continue with count

    set.close();
}
catch (java.sql.SQLException exception) {}

References to DatasourceConnection used in the JDBC snippet shown above is borrowed from Tomcat JDBC connection pool code from this previous blog about connection pool.

For non pooled connections, obtaining a connection to MySQL through JDBC should look like below.

StringBuilder url = 
        new StringBuilder ("jdbc:mysql://localhost:3306/DATABASE_NAME");
url.append("?user=DBUSER_NAME");
url.append ("&password=USER_PASSWORD");

Class.forName("com.mysql.jdbc.Driver").newInstance();
java.sql.Connection connection = 
        java.sql.DriverManager.getConnection(url.toString()); 

Replace DATABASE_NAME, DBUSER_NAME, and USER_PASSWORD with appropriate values.

That's it. Good luck.


3,799

Comments (Using JDBC with MySQL stored procedures )