How to call MySql stored procedure form java application
There are 3 types of stored procedures
- system stored procedures
- extended stored procedure
- user defined stored procedures
Stored procedure allow modular programing, faster execution, reduce traffic, security to your data
Create a table named states
/*Table structure for table `tbl_states` */
DROP TABLE IF EXISTS `tbl_states`;
CREATE TABLE `tbl_states` (
`stateId` int(11) NOT NULL AUTO_INCREMENT,
`state` varchar(25) NOT NULL,
`status` varchar(10) NOT NULL DEFAULT 'Y',
PRIMARY KEY (`stateId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
Stored procedure for inserting values to states
Procedure name : p_states_insertstates
Input : statename and status of state
/* Procedure structure for procedure `p_states_insertstates` */
/*!50003 DROP PROCEDURE IF EXISTS `p_states_insertstates` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `p_states_insertstates`(In i_state VARCHAR(25),in i_status varchar(10))
BEGIN
insert into tbl_states(state,status) values (i_state,i_status);
Select last_insert_id();
END */$$
DELIMITER ;
insert query : insert into `tbl_states`(`stateId`,`state`,`status`) values (1,'Sakha Republic','N');
Java Code : callable insertion
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Main {
public static Connection connections()
{
try
{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost/whatsAppdatabase","root","root");
return con;
}
catch (Exception e)
{
e.printStackTrace();
}
return null;
}
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Main m = new Main();
m.insert("SakhaRepublic","Y");
}
private void insert(String stateName, String stateStatus) {
String state = stateName;
String status = stateStatus;
CallableStatement statement = null;
Connection connect = Main.connections();
System.out.println(" connecting .... ");
String sql = "{call p_states_insertstates (?, ?)}";
try {
statement = connect.prepareCall(sql);
statement.setString(1, state);
statement.setString(2, stateStatus);
//statement.registerOutParameter(3, java.sql.Types.VARCHAR);
statement.execute();
//int empName = statement.getInt(3);
System.out.println(" id : ");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally
{
try {
statement.close();
connect.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
Example of Mysql stored procedure with OUT parameter
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `serviceinformata`.`test`(out id int)
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
Select Max(stateId) into id from tbl_states;
END$$
DELIMITER ;
replace the above function with this
private void insert(String storedProcedure, String ,mysqlExample) {
String state = stateName;
String status = stateStatus;
CallableStatement statement = null;
Connection connect = Main.connections();
System.out.println(" connecting .... ");
String sql = "{call test (?)}";
try {
statement = connect.prepareCall(sql);
statement.registerOutParameter(1, java.sql.Types.INTEGER);
statement.execute();
int stateId= statement.getInt(1);
System.out.println(" id : "+stateId);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally
{
try {
statement.close();
connect.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
You have to download mysql connector.jar
download link
This is a small example for insertion stored procedure in mysql and how to call it from your java application
Advantage of stored procedures
- performance
- productivity and ease of use
- scalability
- maintainability
- interoperability
- security
- replication
Possible Exceptions
Data too long for column 'column_name'
http://javabelazy.blogspot.in/
