Insertion into multiple tables using stored procedure

If you want to insert data into multiple tables at once, then you can do it by calling stored procedure by CallabaleStatement.Follow these steps:

Step 1: Create two table Comapny and Address.

CREATE TABLE company(company_id INT  PRIMARY KEY,
company_name VARCHAR(50) NOT NULL,company_type VARCHAR(20) NOT NULL);

CREATE TABLE address(company_id INT ,city VARCHAR(20),state VARCHAR(20),country VARCHAR(20),FOREIGN KEY(company_id) REFERENCES company(company_id) );

Step 2: Create stored procedure to insert into two tables:

DELIMITER //
CREATE  PROCEDURE insertion(IN company_id INT,company_name VARCHAR(50),company_type VARCHAR(20),city VARCHAR(20),state VARCHAR(20),country VARCHAR(20))
BEGIN
INSERT INTO company VALUES(company_id,company_name,company_type);
INSERT INTO address VALUES(company_id,city,state,country);
END //
DELIMITER ;

         
Step 3: Now write the java program to insert into multiple tables (here two tables) by using CallableStatement.Here is the Callable.java file

import java.sql.*;
pubic class Callable
{
           public static void main(String jtoc[]) throws Exception
          {
                   //Loading Driver
                   Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
                   //Establishing Connection
                   Connection con=DriverManager.getConnection(“jdbc:odbc:DSNNAME”);
                   //Get CallableStatement
                    CallableStatement cs=con.prepareCall(“{call insertion(?,?,?,?,?,?)}”);
                   //Set IN parameters
                   cs.setInt(1,101);
                   cs.setString(2,””Java2Career”);
                   cs.setString(3,”type”);
                   cs.setString(4,”delhi”);
                   cs.setString(5,”delhi”);
                   cs.setString(6,”India”);
 
                  cs.execute();
                  System.out.println(“Yipeee!! details submitted in multiple tables”);
                  con.close();
           }
}