STORED PROCEDURE PROGRAMMING IN JAVA USING MYSQL

Some of the benefits of stored procedure programming are faster execution of sql statements and also preventing sql injection. So whenever the application logic of your application is data centric, it is better to use stored procedure.

To access mysql stored procedure in your java application, you first create the stored procedure in mysql. Below is a sample created stored procedure in mysql.

   1: DELIMITER $$

   2: DROP PROCEDURE IF EXISTS `test`.`cdinsert`$$

   3: CREATE DEFINER=`root`@`localhost` PROCEDURE `cdinsert`(cID varchar(15), tdate varchar(15),cdNo int)

   4: BEGIN

   5:  if exists(select ID from cddetail where cdID=cID) then

   6:   update cddetail set transdate=tdate,cdNum=cdNo where cdID=cID;

   7:  else

   8:   insert into cddetail(cdID,transdate,cdNum) values (cID,tdate,cNo);

   9:  end if;

  10: END$$

  11: DELIMITER ;

You then create your java application  to access the stored procedure as shown below

   1: import java.sql.*;

   2: public class storproc {

   3:  

   4:     Connection conn = null;

   5:     Statement stm = null;

   6:     static final String url = "jdbc:mysql://localhost/test";

   7:     static final String user = "root";

   8:     static final String pswd = "password";

   9:  

  10:     //connecting to the mysql database

  11:     public Connection getConnection() {

  12:         try {

  13:             conn = DriverManager.getConnection(url, user, pswd);

  14:         } catch (Exception ex) {

  15:             System.out.println("error occured " + ex.toString());

  16:         }

  17:         return conn;

  18:     }

  19:  

  20:     public void insertNew(String line) {

  21:         try {

  22:             String input[] = line.split(",");

  23:             //calling the created storedprocedure 

  24:             //and passing the required parameters

  25:             CallableStatement cs = this.getConnection().

  26:             prepareCall("{call insertnew(?,?,?)}");

  27:             

  28:             cs.setString(1, input[0]);

  29:             cs.setString(2, input[1]);

  30:             cs.setInt(3, Integer.parseInt(input[2]));

  31:             cs.execute();

  32:         } catch (Exception e) {

  33:             System.out.println("errr  " + e.toString());

  34:         } finally {

  35:             try {

  36:                 conn.close();

  37:             } catch (SQLException ex) {

  38:                 System.out.println("error occured " + ex.toString());

  39:             }

  40:         }

  41:  

  42:     }

  43: }

Advertisements