Insert Data in Table Using Stored Procedure
In this example we are inserting data into a table
using stored procedure.
Steps:
1.Create database:
To create database we use syntax: create database database_name. We
can create database with the name "Employee" as
mysql> create database Employee;
Query OK, 1 row affected (0.09 sec) |
2.Change the database:
We are using use database_name;
to change the database
mysql> use employee;
Database changed |
3.Create the table:
mysql> create table emp(name char(12),fathername char(12),password char(12));
Query OK, 0 rows affected (0.55 sec)
|
4.Create procedure :
1.We can use delimiter to create multiple statements. To create
delimiter we can use following syntax.
mysql> DELIMITER //
2.To create procedure we use following syntax:
mysql>create
procedure procedure_name(IN |OUT | INOUT) param_name
type)
3.We write sql statement into begin......end body. To start begin use
mysql>begin
and to end use: mysql>end;
4.To finish the procedure use mysql>//
delimiter.
mysql> delimiter //
mysql> create procedure empproc(in name char(12),in fathername char(12),in password char(12))
-> begin
-> insert into emp values(name,fathername,password);
-> end;
-> //
Query OK, 0 rows affected (0.22 sec) |
5.Step to load driver:
To load the driver we are using syntax:
Class.forName("com.mysql.jdbc.Driver").newInstance();
6.Steps to make connection:
We are using getConnection("url/database","user","password")
method to create a connection.
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/employee","root","root");
7.Step to call procedure:
We are using CallableStatement to
execute a stored procedure into java code. The following syntax is used to create an
object of CallableStatement interface.
CallableStatement calstat=conn.prepareCall("{call empproc(?,?,?)}");
8.Step to pass the values into procedure:
To set the values we are using setXXX(). Here XXX is data type
Object class name, e.g setString() to pass string in procedure.
9.Step to execute query :
To execute query we can use:
ResultSet rs = calstat.executeQuery();
10.Close connection :
calstat.close();
The code of the program is given below:
import java.sql.*;
public class Insert_EMP{
public static void main(String a[])throws Exception {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn=DriverManager.getConnection("jdbc:mysql:
//localhost:3306/employee","root","root");
CallableStatement calstat=conn.prepareCall("{call
empproc(?,?,?)}");
calstat.setString(1,a[0]);
calstat.setString(2,a[1]);
calstat.setString(3,a[2]);
ResultSet rs = calstat.executeQuery();
conn.close();
calstat.close();
System.out.println("Your data has been inserted into table.");
}
}
|
The code of the stored procedure is given below:
delimiter //
create procedure empproc(in name char(12),in fathername
char(12),in password char(12))
begin
insert into emp values(name,fathername,password);
end;
//
|
The output of the program is given below:
C:\rajesh\mysql>javac Insert_EMP.java
C:\rajesh\mysql>java Insert_EMP Rajesh Ram Raju
Your data has been inserted into table.
|
Download this example.(Java
Code)
Download this example.(Procedure)
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
