I had requirement to perform two insert queries in two different tables.
I am using Oracle/Java Combination.
What are the options available in this case?
6
-
hm, SQL using the JDBC API would be one option... What exactly is the problem? What do you use today?home– home2011-07-30 10:40:51 +00:00Commented Jul 30, 2011 at 10:40
-
I had a form. At submit of form, I need to insert data into two different tables at the same time. Could not understand yr second question....Ankit– Ankit2011-07-30 10:44:36 +00:00Commented Jul 30, 2011 at 10:44
-
Have you ever programmed in Java before?Michael-O– Michael-O2011-07-30 10:46:20 +00:00Commented Jul 30, 2011 at 10:46
-
Is this is really a dummy question? If yes, kindly tell the solution... and Programming in java for 1.5 yrs.Ankit– Ankit2011-07-30 10:49:14 +00:00Commented Jul 30, 2011 at 10:49
-
Ok. I didn't look into Batch Option in JDBC. If forums feels this is really a basic question, then i regret it..Ankit– Ankit2011-07-30 10:53:05 +00:00Commented Jul 30, 2011 at 10:53
|
Show 1 more comment
4 Answers
If you're trying to insert the same data into two separate tables you can use a multitable insert like this:
insert all
into table1(a, b)
into table2(a, b)
select 1 a, 2 b from dual;
Comments
The most straightforward method is to do two inserts sequentially using the same connection. Assuming that part of your point is that you want the two inserts to occur in the same transaction, then make sure you have disabled autocommit on the connection, and explicitly commit after the second insert.
Another option would be to write an Oracle stored procedure that does the inserts, and call it from Java with a PreparedStatement.
Comments
Sample from devdaily.
package com.devdaily.sqlprocessortests;
import java.sql.*;
public class BasicJDBCDemo
{
Connection conn;
public static void main(String[] args)
{
new BasicJDBCDemo();
}
public BasicJDBCDemo()
{
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
String url = "jdbc:mysql://localhost/coffeebreak";
conn = DriverManager.getConnection(url, "username", "password");
doTests();
conn.close();
}
catch (ClassNotFoundException ex) {System.err.println(ex.getMessage());}
catch (IllegalAccessException ex) {System.err.println(ex.getMessage());}
catch (InstantiationException ex) {System.err.println(ex.getMessage());}
catch (SQLException ex) {System.err.println(ex.getMessage());}
}
private void doTests()
{
doSelectTest();
doInsertTest(); doSelectTest();
doUpdateTest(); doSelectTest();
doDeleteTest(); doSelectTest();
}
private void doSelectTest()
{
System.out.println("[OUTPUT FROM SELECT]");
String query = "SELECT COF_NAME, PRICE FROM COFFEES";
try
{
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(query);
while (rs.next())
{
String s = rs.getString("COF_NAME");
float n = rs.getFloat("PRICE");
System.out.println(s + " " + n);
}
}
catch (SQLException ex)
{
System.err.println(ex.getMessage());
}
}
private void doInsertTest()
{
System.out.print("\n[Performing INSERT] ... ");
try
{
Statement st = conn.createStatement();
st.executeUpdate("INSERT INTO COFFEES " +
"VALUES ('BREAKFAST BLEND', 200, 7.99, 0, 0)");
}
catch (SQLException ex)
{
System.err.println(ex.getMessage());
}
}
private void doUpdateTest()
{
System.out.print("\n[Performing UPDATE] ... ");
try
{
Statement st = conn.createStatement();
st.executeUpdate("UPDATE COFFEES SET PRICE=4.99 WHERE COF_NAME='BREAKFAST BLEND'");
}
catch (SQLException ex)
{
System.err.println(ex.getMessage());
}
}
private void doDeleteTest()
{
System.out.print("\n[Performing DELETE] ... ");
try
{
Statement st = conn.createStatement();
st.executeUpdate("DELETE FROM COFFEES WHERE COF_NAME='BREAKFAST BLEND'");
}
catch (SQLException ex)
{
System.err.println(ex.getMessage());
}
}
}
6 Comments
Cris
the sample is with mysql but you should use an oracle driver
rsp
It is good practice to put both inserts in a transaction so that they succeed both or not at all.
Cris
agree...there are a lot of good practices that might apply to the above code...which is just a copy paste from a site for the sake of understanding via concrete simple sample...
Dave Costa
I don't think that copying code from another site, which is not even germane to the specifics of the question, is a very good answer.
|
You can check with StoredProcedures and execute with PreparedStatement in JDBC api. That intern will return you two resultSets , which you can get with a method getMoreResults(). You can then process the resultsets separately.
