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.*;
|
The code of the stored procedure is given below:
delimiter //
|
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)
If you are facing any programming issue, such as compilation errors or not able to find the code you are looking for.
Ask your questions, our development team will try to give answers to your questions.
Ask Questions? Discuss: Insert Data in Table Using Stored Procedure View All Comments
Post your Comment