Home Jdbc Jdbc-mysql Insert Data in Table Using Stored Procedure

Insert Data in Table Using Stored Procedure
Posted on: July 7, 2007 at 12:00 AM

In this example, we are inserting data into a MySQL database table using stored procedure.

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)

Related Tags for Insert Data in Table Using Stored Procedure:
sql, mysql, c, database, query, syntax, table, data, insert, name, change, using, create, tab, row, for, cte, with, to, base, ws, e, it, eps, ax, can, hang, use, in, as, m, nt, ps, ca, tax, step, em, ase, me, s, ee, at, my, k, ha, ll, mpl, follow, ea, ar, rt, wing, s, s, th, steps, st, ab, abl, af, fe, pl, ol, o

«Previous Index Next»