How to write stored procedure in mysql

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

1. performance
2. productivity and ease of use
3. scalability
4. maintainability
5. interoperability
6. security
7. replication

Possible Exceptions

Data too long for column 'column_name'

http://javabelazy.blogspot.in/