Prepared Statement

[supriya ratnakar]

I have created two tables in my database each one is dependent with each other. Now i need to insert the values into the tables simultaneously. For this condition how to make use of the executeBatch() method of prepared statement.

[Jeanne Boyarsky]

Supriya,
Forgetting about JDBC for a moment, wouldn't this be a problem? If you insert into either one, there would be a constraint error. While you could drop the constraints, add the records and re-create the constraints, this is quite inefficient.

Can you give an example of why you need circular dependencies between two tables?

[supriya ratnakar]

I have more than a table with empid as primary key and it is auto increment.
i have two more tables. those also have empid so i made them as foreign key as these table are dependent .

my problem is when i try to submit my page fields belong to different tables but for that particular emp id only i have to insert those values . when i am using triggers i am getting foreign key constrait violated trigger.

so how to insert the value.



try
{
con=getConnection();
try{
con.setAutoCommit(false);

String sQuery = "insert into Student(Sname,Smedium,sex,total) values(?,?,?,?)";
String Query1 = "insert into English(m1,m2,m3,avg1,sid) values (?,?,?,?,?)";
String Query2 = "insert into Lang(m4,m5,m6,avg2,sid) values (?,?,?,?,?)";
String Query3 = "insert into Maths(m7,m8,m9,avg3,sid) values (?,?,?,?,?)";
String Query4 = "insert into Science(m10,m11,m12,avg4,sid) values (?,?,?,?,?)";
String Query5 = "insert into Social(m13,m14,m15,avg5,sid) values (?,?,?,?,?)";
String Query6 = "select sid from student";
System.out.println("Inside Dao : Abt to get Connection" +con);

PreparedStatement ps=con.prepareStatement(sQuery);
ps.setString(1,b.getSname());
ps.setString(2,b.getSmedium());
ps.setString(3,b.getsex());
ps.setInt(4,b.gettxtTot());
//ps.addBatch();
//ps.executeBatch();
ps.executeUpdate();

ps=con.prepareStatement(Query6);
ResultSet rs = ps.executeQuery();
while (rs.next())
{
b.setsId(rs.getInt(1));
}

System.out.println("Inside Dao : Abt to submit " +b.getsId() );
ps=con.prepareStatement(Query1);
ps.setInt(1,b.gettxtM1());
ps.setInt(2,b.gettxtM2());
ps.setInt(3,b.gettxtM3());
ps.setInt(4,b.gettxtAvg1());
ps.setInt(5,b.getsId());
//ps.addBatch();
//ps.executeBatch();
ps.executeUpdate();
System.out.println("Inside Dao : Abt to submit1 " +ps );
ps=con.prepareStatement(Query2);
ps.setInt(1,b.gettxtM4());
ps.setInt(2,b.gettxtM5());
ps.setInt(3,b.gettxtM6());
ps.setInt(4,b.gettxtAvg2());
ps.setInt(5,b.getsId());
//ps.addBatch();
//ps.executeBatch();
ps.executeUpdate();
System.out.println("Inside Dao : Abt to submit2 " +ps );
ps=con.prepareStatement(Query3);
ps.setInt(1,b.gettxtM7());
ps.setInt(2,b.gettxtM8());
ps.setInt(3,b.gettxtM9());
ps.setInt(4,b.gettxtAvg3());
ps.setInt(5,b.getsId());
//ps.addBatch();
//ps.executeBatch();
ps.executeUpdate();
System.out.println("Inside Dao : Abt to submit3 " +ps );
ps=con.prepareStatement(Query4);
ps.setInt(1,b.gettxtM10());
ps.setInt(2,b.gettxtM11());
ps.setInt(3,b.gettxtM12());
ps.setInt(4,b.gettxtAvg4());
ps.setInt(5,b.getsId());
//ps.addBatch();
//ps.executeBatch();
ps.executeUpdate();
System.out.println("Inside Dao : Abt to submit4 " +ps );
ps=con.prepareStatement(Query5);
ps.setInt(1,b.gettxtM13());
ps.setInt(2,b.gettxtM14());
ps.setInt(3,b.gettxtM15());
ps.setInt(4,b.gettxtAvg5());
ps.setInt(5,b.getsId());
//ps.addBatch();
//ps.executeBatch();
ps.executeUpdate();
System.out.println("Inside Dao : Abt to submit5 " +ps );
System.out.println("Inside Dao : After executing the Batch Querry" );
con.commit();
con.close();
}
catch (BatchUpdateException s){
System.out.println("SQL statement is not executed!");
}