MySql sample stored procedures

if else update insert stored procedure

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `cfedapps`.`p_save_branch`(in branchId int(50),in branchCode int(5),in branchName varchar(55),in address varchar(55),in district varchar(33),in pincode bigint(10),in landline bigint(15),in startDate date,in lat long,in lon long, in branchStatus varchar(5), in branchDesc varchar(55),in userId int(4), in imgUrl varchar(55),in branchEmail varchar(55),in branchType int(5))
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
 
if (branchId is not null) then

update tbl_unit_details set firmCode = branchCode,unitName = branchName where unitId = branchId;

else

insert into tbl_unit_details (firmCode,unitName,address,district,pincode,landline,startedDate,unitLat,unitLon,status,description,userId,unitImg,branchEmailid,unittype) values (branchCode,branchName,address,district,pincode,landline,startDate,lat,lon,branchStatus,branchDesc,userId,imgUrl,branchEmail,branchType);
select last_insert_id();

end if;

    END$$

DELIMITER ;

Important notes

Setting default value of a column to CURRENT_TIMESTAMP while creating a database allows to update current date time in that field automatically

sample query
create table consumerfed (itsection varchar(32),createdDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP);


Thanks

http://javabelazy.blogspot.in/

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/