MySQL Stored Procedure Tutorial 1

MySQL Stored Procedure Tutorial

                1. Create Procedure in MySQL As Below: (Here I am 

              using MySQL Query Browser).

              

                      Procedure In MySQL 'getData' on table 'one' of database 

               'test' with structer (id int, surname varchar(30))

_______________________________________________________________________________               

                DELIMITER $$

                            DROP PROCEDURE IF EXISTS `test`.`getData` $$

                            CREATE DEFINER=`root`@`localhost` PROCEDURE `getData`(IN tname         VARCHAR(25))

                            BEGIN

                                        SELECT * FROM one WHERE name=tname;

                            END $$

               DELIMITER ;

_________________________________________________________________________________

           

              So This was the code of procedure creation.. Now we will call it from java code...

              2. Following is the java code to call Procedure From 

              MySQL Database. 

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class testProcedure {

public static void main(String s[])

{

Connection cn;

Statement st;

            try

{

cn=DriverManager.getConnection("jdbc:mysql://localhost:3306/" +

"test","root", "root");

st=cn.createStatement();

st.execute("call getData('Ankit')");

ResultSet rs=st.getResultSet();

while(rs.next())

{

System.out.println("id= "+rs.getString(1));

System.out.println("surname= "+rs.getString(2));

}

}catch(Exception e){

e.printStackTrace();

}

}