Callable Statement in JDBC:-
This statement is used to call stored procedure of database, store procedure means precompile block of code of an application that will be directly executed hence procedure performance is better as compare to normal SQL Query.
Syntax to create a Stored Procedure?
Create Procedure Procedurename() as
begin
Statement1
Statement2
end;
/
Procedurename()
The procedure will link with the table, which means without the table, the procedure can not be created.
Now I am explaining an example of the Select Procedure on MySQL Database?
DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
SELECT * FROM tbl_student;
END //
DELIMITER ;
How to execute
call GetAppProducts()
How to use Callable Statement using Java Code?
package jdbc;
import java.sql.*;
public class JDBCEXAMPLE {
public static void main(String[] args) {
try
{
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/schooldb","root","");
CallableStatement st = conn.prepareCall("{call GetAllProducts()}");
ResultSet x = st.executeQuery();
while(x.next()){
System.out.println(x.getString(1) + " "+ x.getString(2));
}
}
catch(Exception ex)
{
System.out.println(ex.getMessage());
}
}
}
............................................................................................................
Now Create a procedure for Data Insertion on MYSQL:-
DELIMITER //
CREATE PROCEDURE InsertStudent(a int,b varchar(50),c int)
BEGIN
insert into student values(a,b,c);
END //
DELIMITER ;
Code for JDBC:-
package jdbc;
import java.sql.*;
public class JDBCEXAMPLE {
public static void main(String[] args) {
try
{
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/schooldb","root","");
CallableStatement st = conn.prepareCall("{call InsertStudent(?,?,?,?)}");
st.setInt(1, 1212);
st.setString(2,"ravi");
st.setString(3,"CS");
st.setInt(4,45000);
int x = st.executeUpdate();
if(x!=0)
{
System.out.print("data inserted successfully");
}
}
catch(Exception ex)
{
System.out.println(ex.getMessage());
}
}
}
Post a Comment
POST Answer of Questions and ASK to Doubt