Please consider the following code. Since, my main question revolves around the usage of transaction, I haven't bothered to include full code here. The following code just gives an overwiew of what I am doing and what results I am getting after running the program. If you would like to look at an actual code, please take a look here. However, I feel that the following code snippet is sufficient for understanding my question. Thanks
So, I have something like the following inside the try block:
try{ Connection connRemote = DriverManager.getConnection("jdbc:mysql:// connecting to MySQL database located at 11.11.1.111"); connRemote.setAutoCommit(false); // Starting Transaction here maindbsql = "SELECT IP_vch FROM mytable "; // These are the possible IP's that //gets selected :22.22.2.222 , 33.33.3.333,44.44.4.444 Map<String,Connection> connections = new HashMap<>(); DeviceStmt = connRemote.createStatement(); DeviceRS = DeviceStmt.executeQuery(maindbsql); while(DeviceRS.next()){ final String ip_address = DeviceRS.getString("IP_vch"); System.out.println("Value of IP_vch Field:"+ip_address); connections.put(ip_address,DriverManager.getConnection("jdbc:mysql://" + ip_address + ":3306/test",User,Pass)); // I am connecting to the following connections above 22.22.2.222 , 33.33.3.333,44.44.4.444 }//END Of while(DeviceRS.next()) for(final String ip : connections.keySet()) { String QueryString = "SELECT DTSId_int & more fields from test.selectiontable WHERE DTSStatustype_int = 1 // here I am selecting some data (10 records at a time) from the MySQL located at 11.11.1.111 " String QueryInsertRemote = " INSERT INTO test.insertiontable // MySQL table located at each of the following locations 22.22.2.222 , 33.33.3.333,44.44.4.444 // Here I am updating one specific field called `DTSStatustype_int of the 10 records I selected in `QueryString` to number `3` so that next time when my loop starts, I don't select already copied records and t // insert into test.insertiontable StringBuilder sqlSelect = new StringBuilder(1024); sqlSelect.append("UPDATE test.selectiontable "); sqlSelect.append("SET DTSStatusType_ti = 3,"); sqlSelect.append("Queued_DialerIP_vch = ? "); sqlSelect.append("WHERE DTSId_int IN ( "); PreparedStatement pst = connRemote.prepareStatement( sqlSelect.toString() ); pst.executeUpdate()' connRemote.commit(); // Transaction Commited System.out.println("Checking Prepared Statement:"+pst); } // END Of For each loop }// end of try block
More information:
- DTSId_int is a field that exists in the MySQL table for all the IP addresses above. IT's a primary key and set to autoincrement.
- DTSStatusType_ti is initially set to 1 in test.selectiontable and then getting updated to 3
**My Question:**
When I check the results of prepared statement, I get the following, when the for loop runs for the first time:
When for loop runs for the second time, I get the following results:Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1052ebec:UPDATE test.selectiontable SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '22.22.2.222' WHERE DTSId_int IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10) Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1052ebec:UPDATE test.selectiontable SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '33.33.3.333' WHERE DTSId_int IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10) Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1052ebec:UPDATE test.selectiontable SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '44.44.4.444' WHERE DTSId_int IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1052ebec:UPDATE test.selectiontable SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '22.22.2.222' WHERE DTSId_int IN ( 11,12, 13, 14, 15, 16, 17, 18, 19, 20) Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1052ebec:UPDATE test.selectiontable SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '33.33.3.333' WHERE DTSId_int IN ( 11,12, 13, 14, 15, 16, 17, 18, 19, 20) Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1052ebec:UPDATE test.selectiontable SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '44.44.4.444' WHERE DTSId_int IN ( 11,12, 13, 14, 15, 16, 17, 18, 19, 20)
**Problem I am facing:**
The problem here is, the first 10 values in `test.selectiontable` is getting updated three times at a time. Eventually, it get's updated with the most recent value, which is 44.44.4.444.
I want something like following results which makes sense:
Please let me know what am I doing wrong in my transaction related code.Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1052ebec:UPDATE test.selectiontable SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '22.22.2.222' WHERE DTSId_int IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10) Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1052ebec:UPDATE test.selectiontable SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '33.33.3.333' WHERE DTSId_int IN ( 11,12, 13, 14, 15, 16, 17, 18, 19, 20) Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1052ebec:UPDATE test.selectiontable SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '44.44.4.444' WHERE DTSId_int IN ( 21, 22, 23, 24, 25, 26, 27, 28, 29, 30)
Thanks