<div class="image-550">
<img src="https://cdn.kahimyang.com/resources-22/jdbc.avif" loading="lazy" alt="Using JDBC in MYSQL stored procedures" />
</div>

In this blog we will demonstrate how to create stored procedures and call it from a Java application through JDBC.

<h3> 
Creating the Stored Procedure
</h3>


<pre class="brush: bash">
DELIMITER // 
CREATE PROCEDURE GetAllPosts() 
BEGIN 
SELECT blog_title,blog_content FROM blogs where blog_status=1;
END // 
DELIMITER ; 
</pre>



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.



<pre class="brush: bash">
DELIMITER // 
CREATE PROCEDURE GetAllPosts(IN dataSize int) 
BEGIN 
SELECT blog_title,blog_content FROM blogs where blog_status=1 limit 0,dataSize; 
END // 
DELIMITER ; 
</pre>



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.



<pre class="brush: bash">
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 ; 
</pre>



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.


<pre class="brush: bash">
drop procedure PROCEDURE_NAME
</pre>



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:


<pre class="brush: bash">
grant select on `mysql`.`proc` to 'user_id' identified by 'user_password';
</pre>


Please make sure to replace user_id and user_password with the approriate values. Issue the following command to update 

privileges.



<pre class="brush: bash">
flush privileges;
</pre>



To execute stored procedures from mysql command prompt, use the call command. The call command takes the following form


<pre class="brush: bash">
call stored_procedure (parameters)
</pre>



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 <a href="http://dev.mysql.com/doc/refman/5.6/en/stored-programs-defining.html" 

class="news_link" title="Stored procedures documentation" target="_blank">the documentation</a> to learn more about stored 

procedures.


<h3>
Using Stored Procedures in JDBC
</h3>


<pre class="brush: java;highlight:4">
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 ) {}
</pre>



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. 



<pre class="brush: java">
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) {}
</pre>



References to DatasourceConnection used in the JDBC snippet shown above is borrowed from Tomcat JDBC connection 

pool code from <a href="https://kahimyang.com/kauswagan/howto_blogs/1017-

using_tomcat_jdbc_connection_pool_as_standalone_instantiated_bean" class="news_link" title="Using Tomcat JDBC Connection Pool as 

standalone instantiated bean">this previous blog</a> about connection pool.



For non pooled connections, obtaining a connection to MySQL through JDBC should look like below.



<pre class="brush: java">
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()); 
</pre>



Replace DATABASE_NAME, DBUSER_NAME, and USER_PASSWORD with appropriate values.



That's it. Good luck.

Using JDBC with MySQL stored procedures