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();
}
}
One comment