Please help on distributed transaction with JSP-Servlet
Hi
I am trying to develop an application that will involve two oracle databases(db_a and db_b) on different servers. I need to query a table(tbl_db_a) from db_a and insert into a table(tbl_db_b) in db_b. The information in the table in db_a is loaded automatically by some external means. Both tables on both databases have same structure.
I am using JNDI with tomcat 5.5 to connect to these two dbs. I am able to connect to each one of them individually. In both tables on two databases, I have a field called "status". In tbl_db_a its intitial value is '0' but when I query the table and after confirmation that data is inserted in tbl_db_b, its status changes to '1' so that to indicate that the record has been transfered. I insert records with this "status" value '1' into tbl_db_b.
Please help me with some ideas or links on the way to implement this. After some reading and researching,I have decided to use JSP for purely data presentation and servlets and other classes to handle the logic. So I am trying to have the selection and insertion handled by some some logic and later just present the view with JSPs when required.
Just to clarify a bit here is the code am connecting the databases with.
DbConnection
package transaction_test;
import java.sql.*;
import javax.naming.NamingException;
import javax.sql.*;
import javax.naming.InitialContext;
publicclass DbConnection{
publicstatic Connection getDbConnection(String jndiDS)
throws SQLException{
DataSource ds =null;
Connection conn =null;
InitialContext initCxt =null;
try{
initCxt =new InitialContext();
ds = (DataSource)initCxt.lookup(jndiDS);
conn = ds.getConnection();
}catch (SQLException ex){
ex.printStackTrace();
}catch (NamingException ex){
ex.printStackTrace();
}
return conn;
}
}
And the one loading records in an ArrayList so as to later insert into tbl_db_b
RecordsDataAccess
package trancaction_test;
import java.util.*;
import trancaction_test.ReordsBean;
import java.sql.*;
publicclass RecordsDataAccess{
private String _db_a ="java:comp/env/jdbc/DB_A";//for connecting to db_a
private String _db_b ="java:comp/env/jdbc/DB_B";//for connecting to db_b
public ArrayList<RecordsBean > getRecords(){
String _sqlQuery ="SELECT * FROM tbl_db_a";
Connection dbConn =null;
Statement statmnt =null;
ResultSet rstset =null;
ArrayList<RecordsBean > records =new ArrayList<RecordsBean >();
RecordsBean record;
try{
dbConn = DbConnection.getDbConnection(_db_a);
statmnt = dbConn.createStatement();
rstset = statmnt.executeQuery(_sqlQuery);
while(rstset.next()){
record =new RecordsBean();
//Get records
record1.setRecord1(rstset.getInt(1));
record2.setRecord1(rstset.getInt(2));
//load records into ArrayList
records.add(record);
}
}catch (NumberFormatException ex){
ex.printStackTrace();
}catch (SQLException ex){
ex.printStackTrace();
}
return records;
}
}
Truly speaking am stuck from here on how to think of loading the records in table tbl_db_b.What is confusing me is the transaction stuff I have read of on which am not so clear.
I am new to JSP and Servlets so please bare with me.
Thank you in advance for any ideas.
[5564 byte] By [
aiExa] at [2007-11-27 6:35:52]
# 1
Didn't really understand what the problem is. Can you rephrase?
# 2
Ok.I would like to query tbl_db_a and load the records into tbl_db_d. These tables are in different databases db_a and db_b respectively, on different servers. How do I do this?i seem to have confused somehow after reading on transactions etc....Thnx
aiExa at 2007-7-12 18:03:09 >
# 3
I'm no expert at this and I've certainly never done this on my own but what I would try is this:
1. You'd have connections from both the databases
2. From what I know, using Connection.setAutocommit(false) is equivalent to starting transaction; so you'd do that on both to get locks on you tables.
3. Then, its basic select and insert statements, I think you know that part already.
# 4
Something like this I guess
Connection dbConnA = null;
Connection dbConnB = null;
> try {
>
dbConnA = DbConnection.getDbConnection(_db_a);
dbConnB = DbConnection.getDbConnection(_db_b);
dbConnA.setAutoCommit(false);
dbConnB.setAutoCommit(false);
> while(rstset.next()){
//get the records from database A and insert into database B
>}
//at the end, before you close the connection, setAutoCommit(true) again to end the transaction.
Edit: Forgot the code tags :P
Message was edited by:
nogoodatcoding
# 5
Thank you once more for the reply. yeah I think 1 and 3 I can handle. On the issue of the "status" field (its on both tables) which I need to update upon successful transferring of records to another table, how would you suggest to change it withing the transaction process or after afterwards?I thought to include this field(in tbl_db_a) to show that a particular record has been transfered or not.
thnx
aiExa at 2007-7-12 18:03:09 >
# 6
It should be within the transaction itself; you're setting this flag for each record in the table A independently right? Or do you want to set the flag for all the records you've transferred, only if all the records have transferred and not set it if even one had errors? As in, all or none?
If it's to be set independently, then you should update that row right after you successfully transfer. Consider
while ( rsA.next() )
{
//read the fields from table A
try
{
//try to insert into table B
try
{
//if you reached here, there was no exception so try to update the flag field in table A for this particular record
//if this step was also done successfully, then
connA.commit(); //commit the current changes to the db
connB.commit();
}
catch //etc...
{
//if there was some error, then
connA.rollback();
connB.rollback(); //undo any changes to the dbs after the last insert
}
}
catch ( Exception e )
{
//if there is some error while entering data handle it and do not do anything to the flag in A
connA.rollback();
connB.rollback(); //undo changes
}
}
On the other hand, if it's to be all or none:
try
{
while ( rsA.next() )
{
//read the fields from table A
try
{
//try to insert into table B
try
{
//if you reached here, there was no exception so try to update the flag field in table A for this particular record
//this is where I'm not too certain about good practices; since you already have a ResultSet on the very rows you're going to update within the loop that reads those rows; but from what I know, the ResultSet rsA which you're using will not reflect the changes till you run the same query again.
}
catch ( Exception e )
{
//if there was some error, then
connA.rollback();
connB.rollback(); //undo any changes to the dbs after the last insert
throw e; //throw e to avoid continuing the loop;
}
}
catch ( Exception e )
{
//if there is some error while entering data handle it and do not do anything to the flag in A
connA.rollback();
connB.rollback(); //undo changes
throw e; //throw e to avoid continuing the loop since we only want all or none at all
}
}
}
catch ( Exception e )
{
}
finally
{
//after the while loop is completely done and there were no errors for any record, commit the changes
connA.commit();
connB.commit();
}
# 7
Sory it was late alread here so I left.Yes. I am setting this flag for each record in the table A independently .Thnx a lot.Let me work it out now....though I don't know if you'll be visiting this thread when you have time just in case... :-) Thnx
aiExa at 2007-7-12 18:03:09 >
# 8
No problem, you're welcome :)Like I mentioned, I'm no expert, this is what I would try out myself from what I've understood till now.I'll keep this topic on my watch list in case you post anything again.All the best!
# 9
hi,
Sorry for this question.Am learning and trying to implement at the same time. I have decided to create a method that will do the transaction. I want it to use the Arraylist am returning in the method abovegetRecords()
since I have already queried the database.
I thought to use the returned ArrayList because I have a lot of fields to select from one table and insert into the other table (Over 100). So my code be too long if I put everything in one method.
The question is how do i get individual records I populated the the ArrayList with using setRecors1()
and setRecord2()
and use them in the "sql insert" query...?
thank you.
aiExa at 2007-7-12 18:03:09 >
# 10
I didn't understand you too well.
And I'm not sure if this is a good way to go about it (not sure though).
You want to use the data from the ArrayList and put that into an SQL insert query right?
Look up the PreparedStatement, I think this is what you're looking for. http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html and http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html.
You'll do something like this:
PreparedStatement ps = con.prepareStatement("insert into mytable values (?, ?)");
while ( /*there are records in the ArrayList*/ )
{
//read the next records from ArrayList, record1 and record2, assuming ints
ps.setInt(1, record1);
ps.setInt(2, record2);
ps.executeUpdate();
}
Basically, PreparedStatement starts off with placeholders ( ? ) that will be substitued with the actual values using setXXX() methods, similar to getXXX() methods of ResultSet, like getString() etc.
This makes it more efficient for multiple, similar queries and also protects against SQL injection attacks by making sure the data you're setting the placeholder to is valid.
Hope this helps.
# 11
Thank you very much for the links!They helped a lot.Thats part of what i needed.Yes I need to get the data from my ArrayList and use it to insert into mytable2.
Sorry I made a typo error in the code I presented earlier on....it should be
while(rstset.next()){
record = new RecordsBean();
//Get records
record.setRecord1(rstset.getInt(1));
record.setRecord2(rstset.getInt(2));
//load records into ArrayList
records.add(record);//I later return this
}
My problem is I can't figure out how to get the individual "record" from my ArrayList and use it in my PreparedStatement in my "while" loop.
for example if it was a "ResultSet" from what I understand, I could do something like(right?):
String _sqlQuery = "SELECT * FROM mytable1";
Statement stat = dbConn1.createStatement();
PreparedStatement ps = dbConn2.prepareStatement("insert into mytable2 values(?, ?)");
ResultSet rstset = stat.executeQuery(_sqlQuery);
while (rstset.next()){
ps.setInt(1, rstset.getInt(1));
ps.setInt(2, rstset.getInt(2));
ps.executeUpdate();
}
but in case of using an ArrayList in the while loop.....
while ( /*there are records in the ArrayList*/ )
{
//read the next records from ArrayList, record1 and record2, assuming ints
ps.setInt(1, record1);
ps.setInt(2, record2);
ps.executeUpdate();
}
Which mechanism do I use to read right data from mytable1 and insert it into a right field in mytable2?
Thanx a lot for your help
aiExa at 2007-7-12 18:03:09 >
# 12
> Which mechanism do I use to read right data from mytable1 and
> insert it into a right field in mytable2?
Well, you've got an ArrayList and you've put your records in that right? From what I see, you've made a 'RecordBean' object and you put that in the ArrayList.
Now you can use an Iterator to get the objects from the ArrayList:
Iterator it = records.iterator();
RecordBean currentRecordBean = null;
while ( it.hasNext())
{
currentRecordBean = (RecordBean) it.next();
ps.setInt(1, currentRecordBean.getRecord1());
ps.setInt(2, currentRecordBean.getRecord2());
}
I'm assuming you also have getter methods like you had your setters, setRecord1() and setRecord2()
This what you wanted?
# 13
Yes I have all my getter and setter methods.Thats exactly thats what I wanted... :-)Thnx a lot...i could've gotten you a beer or something if u were in zambia :-)I have some confidence now.Have a nice weekend.my work time has ended for 2day.THNX A LOT!!!
aiExa at 2007-7-12 18:03:09 >
# 14
> i could've gotten you a beer or something if u were in zambia :-)
:)) Thanks for the offer though :)
Good luck and make sure you read up on the documentation of the class you're working with coz this wasn't that tough, just a matter of knowing which methods are available :)
# 15
Thank you.. :-) I am doing some reading and research atleast till friday while trying it out.Thnx
aiExa at 2007-7-21 21:58:59 >
