Jdbc Procedure Tutorial

 

What is PL/SQL?

PL/SQL is designed for seamless processing of SQL statements enhancing the security, portability, and robustness of the database. Similar to other database languages, it gives more control to the programmers by the use of loops, conditions and object-oriented concepts. Full form of PL/SQL is "Procedural Language extensions to SQL".

Why Stored Procedures?

• Stored procedures are fast.
• Stored procedures are portable.
• Stored procedures are safe because in this case query is written in database layer.
• High Productivity
• Tight integration with SQL
• Tight Security
• Support Object Oriented Programming concepts.

In JDBC we have three type of statement that is used for different type of query or procedure as below : 

Statement	PreparedStatement	CallabaleStatement
It is parent interface of PreparedStatement.	It is parent of CallableStatement.	It is a sub class of PreparedStatement.
It is used for non-parameterized query.	It is used for parameterized query.	It is used for procedure or function.
It is not pre compiled statement.	It is pre compiled statement.	It is also pre compiled statement.
Its performance is slow because its compile all the time.	Its performance is faster than Statement because its pre compiled.	Its performance is faster than PreparedStatement.
It is used for DDl query.	It is used for any type of query.	It is used for procedure and function.
It is used when sql query execute at once.	It is used when sql query execute multiple times.	It is used for procedure and function.

We can divide the above CREATE PROCEDURE statement in the following ways :

1. CREATE PROCEDURE pro_name () ...

In this case when you don’t want to get any input from user.

2. CREATE PROCEDURE pro_name ([IN] param_name type)...

In this case when you want to get input from user.

3. CREATE PROCEDURE pro_name ([OUT] param_name type)...

In this case when you want get value from this procedure.

4. CREATE PROCEDURE pro_name ([INOUT] param_name type)...

In this case when you want get input and output value.

What is the difference between procedure and function :

Procedure	Function
It does not have any return type.	It must have a return type.
It is used for business logic.	It is used for calculation.
A function can be call from procedure.	Procedure can never call from function.
It supports IN or OUT parameters.	It supports only IN parameters.
Try catch block can be used in procedure.	Try catch block can not be used in function.
It can return 0 or more values.	It returns one value.

1.How to create a procedure in mysql database for insert content and call in using java program.

• mysql> DELIMITER $$
• mysql> create procedure myinsert(IN roll int,IN name varchar(20),IN marks float)
•  -> BEGIN -> insert into student values(roll,name,marks);
•  -> END $$
• Query OK, 0 rows affected (0.07 sec)
• mysql> DELIMITER ;

Create a class name is Dao to define connection here :

Dao.java

import java.sql.Connection;
import java.sql.DriverManager;

public class Dao {
private static Connection con;

public static Connection getConnection() {

try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "root", "root");
} catch (Exception e) {
System.out.println(e);
}
return con;
}

}

CallableInsert.java

import java.sql.CallableStatement;

import java.sql.Connection;

import java.util.Scanner;

public class CallableInsert {

public void insert(int roll, String name, float marks) {

try (Connection con = Dao.getConnection();) {

CallableStatement cs = con.prepareCall("call myinsert(?,?,?)");

cs.setInt(1, roll);

cs.setString(2, name);

cs.setFloat(3, marks);

cs.executeUpdate();

System.out.println("success");

} catch (Exception e) {

System.out.println(e);

}

}

public static void main(String[] args) {

Scanner scanner = new Scanner(System.in);

System.out.println("Enter roll number");

int roll = scanner.nextInt();

System.out.println("Enter name");

String name = scanner.next();

System.out.println("Enter marks");

float marks = scanner.nextFloat();

new CallableInsert().insert(roll, name, marks);

scanner.close();

}

}

Output is :

Enter roll number

2

Enter name

ramesh

Enter marks

78.7

success

2.How to create a procedure in mysql database for update content and call in using java program.

• mysql> DELIMITER $$
• mysql> create procedure myupdte(IN roll int,IN name varchar(20),IN marks float)
•     -> BEGIN
•     -> update student set name=name,marks=marks where roll=roll;
•     -> END $$
• Query OK, 0 rows affected (0.00 sec)
• mysql> DELIMITER ;

CallableUpdate.java

import java.sql.CallableStatement;

import java.sql.Connection;

import java.util.Scanner;

public class CallableUpdate {

public void update(int roll, String name, float marks) {

try (Connection con = Dao.getConnection();) {

CallableStatement cs = con.prepareCall("call myupdate(?,?,?)");

cs.setInt(1, roll);

cs.setString(2, name);

cs.setFloat(3, marks);

cs.executeUpdate();

System.out.println("success");

} catch (Exception e) {

System.out.println(e);

}

}

public static void main(String[] args) {

Scanner scanner = new Scanner(System.in);

System.out.println("Enter roll number");

int roll = scanner.nextInt();

System.out.println("Enter name");

String name = scanner.next();

System.out.println("Enter marks");

float marks = scanner.nextFloat();

new CallableUpdate().update(roll, name, marks);

scanner.close();

}

}

Output is :

Enter roll number

2

Enter name

uu

Enter marks

76

success

3.How to create a procedure in mysql database for delete content and call in using java program.

• mysql> DELIMITER $$
• mysql> create procedure mydelete(IN r int)
•     -> BEGIN
•     -> delete from student where roll=r;
•     -> END $$
• Query OK, 0 rows affected (0.12 sec)
• mysql> DELIMITER ;

CallableDelete.java 

import java.sql.CallableStatement;

import java.sql.Connection;

import java.util.Scanner;

public class CallableDelete {

public void delete(int roll) {

try (Connection con = Dao.getConnection();) {

CallableStatement cs = con.prepareCall("call mydelete(?)");

cs.setInt(1, roll);

cs.executeUpdate();

System.out.println("success");

} catch (Exception e) {

System.out.println(e);

}

}

public static void main(String[] args) {

Scanner scanner = new Scanner(System.in);

System.out.println("Enter roll number");

int roll = scanner.nextInt();

new CallableDelete().delete(roll);

scanner.close();

}

}

Output is :

Enter roll number

2

success

4.How to create a procedure in mysql database for fetching all content and call in using java program.

• mysql> DELIMITER $$
• mysql> create procedure myfetchall()
•     -> BEGIN
•     -> select * from student;
•     -> END $$
• Query OK, 0 rows affected (0.04 sec)
• 
  
• mysql> DELIMITER ;

CallableFetchAll.java

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Types;

import java.util.Scanner;

public class CallableFetchAll {

public void fetchAll() {

try (Connection con = Dao.getConnection();) {

CallableStatement cs = con.prepareCall("call myfetchall()");

ResultSet rs = cs.executeQuery();

while(rs.next()){

System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getFloat(3));

}

} catch (Exception e) {

System.out.println(e);

}

}

public static void main(String[] args) {

new CallableFetchAll().fetchAll();

}

}

Output is :

1 deep 56.7

5.How to create a procedure in mysql database for fetching single content and call in using java program.

• mysql> DELIMITER $$
• mysql> create procedure myfetchone(IN r int)
•     -> BEGIN
•     -> select * from student where roll=r;
•     -> END $$
• Query OK, 0 rows affected (0.00 sec)
• mysql> DELIMITER ;

CallableFetchOne.java

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Types;

import java.util.Scanner;

public class CallableFetchOne {

public void fetchOne(int roll) {

try (Connection con = Dao.getConnection();) {

CallableStatement cs = con.prepareCall("call myfetchone(?)");

cs.setInt(1, roll);

ResultSet rs = cs.executeQuery();

if (rs.next()) {

System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getFloat(3));

}

} catch (Exception e) {

System.out.println(e);

}

}

public static void main(String[] args) {

Scanner scanner = new Scanner(System.in);

System.out.println("Enter roll number");

int roll = scanner.nextInt();

new CallableFetchOne().fetchOne(roll);

}

}

Output is :

Enter roll number

1

1 deep 56.7