I'm having a problem with select for update in jdbc. The table i'm trying to update is the smalldb table, i'm having problems-- i'm using select for update which does a lock on the selected row the update statement is --
String updateQ = "UPDATE libra.smalldb SET hIx = ? WHERE name = ?";
the select statement is --
rs = stmt1.executeQuery("SELECT hIx FROM libra.smalldb for update");
rs0 = stmt2.executeQuery("SELECT name,aff FROM libra.smalldb");
the second statement is because i need those fields as well.
Here is the complete code --
import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
public class Jdbcexample1 {
/**
* @param args
*/
public static void main(String[] args) {
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql:///test", "root",
"*****");
// String url = "jdbc:msql://200.210.220.1:1114/Demo";
// Connection conn = DriverManager.getConnection(url,"","");
Statement stmt1 = con.createStatement();
Statement stmt2 = con.createStatement();
Statement stmt3 = con.createStatement();
Statement stmt4 = con.createStatement();
ResultSet rs0;
ResultSet rs;
ResultSet rs1;
ResultSet rs2;
String name;
String hIx;
int hIxInt;
StringBuffer sb = new StringBuffer();
String affiliationSmall;
ArrayList<String> affiliation = new ArrayList<String>();
ArrayList<Float> matchValues = new ArrayList<Float>();
ArrayList<Integer> hixValues = new ArrayList<Integer>();
ArrayList<Integer> idValues = new ArrayList<Integer>();
boolean moreFlag = false;
String queryString;
int tmpIdx;
String name1;
// get the hix at that index where the similarity is maximum
int tmpHidx = 0;
int tmpHix = 0;
int id = 0;
int count;
int tmpidIdx = 0;
// rs =
// stmt.executeQuery("SELECT id FROM libra.researchint WHERE id = 910887");
// Get name, affiliation , hIx from smalldb
// rs =
// stmt1.executeQuery("SELECT name,aff,hIx FROM libra.smalldb");
// String cursorName = "OUR_CURSOR";
// stmt1.setCursorName(cursorName);
// rs =
// stmt1.executeQuery("SELECT name,aff,hIx FROM libra.smalldb for update");
rs = stmt1.executeQuery("SELECT hIx FROM libra.smalldb for update");
rs0 = stmt2.executeQuery("SELECT name,aff FROM libra.smalldb");
while (rs.next() && rs0.next()) {
// String lastName = rs.getString("id");
hIx = rs.getString("hIx");
hIxInt = Integer.parseInt(hIx);
// if hIx
if (hIxInt == -1)
continue;
// name = rs.getString("name");
name = rs0.getString("name");
name1 = new String(name);
System.out.println(name);
// affiliationSmall = rs.getString("aff");
affiliationSmall = rs0.getString("aff");
// name = "\"" +name+ "\"";
// Get matching names from large faculty table
// String queryString =
// "SELECT id,name,hIx FROM libra.faculty WHERE name = " +name;
// name = does not work names are similar but not same (in
// faculty and
// smalldb)
String query = "SELECT id,name,hIx FROM libra.faculty WHERE name like ?";
// String query =
// "SELECT id,name,hIx FROM libra.faculty for update of hIx WHERE name like ?";
PreparedStatement prepStmt = con.prepareStatement(query);
String[] nameArr = name.split(" ");
StringBuffer tmpSb = new StringBuffer();
for (int idx = 0; idx < nameArr.length; idx++) {
tmpSb.append(nameArr[idx] + "%");
}
name = tmpSb.toString();
prepStmt.setString(1, name);
rs1 = prepStmt.executeQuery();
// Try to get matching names from faculty big db
// Execute the query on faculty table
// rs1 = stmt2.executeQuery(queryString);
if (rs1.isClosed())
continue;
count = 0;
matchValues.clear();
affiliation.clear();
while (rs1.next()) {
// name = rs1.getString("name");
id = Integer.parseInt(rs1.getString("id"));
// idValues.add(id);
tmpHix = Integer.parseInt(rs1.getString("hIx"));
queryString = "SELECT aff FROM libra.affiliation WHERE id = "
+ id;
rs2 = stmt3.executeQuery(queryString);
// affiliation = rs1.getString("aff");
sb.delete(0, sb.length());
while (rs2.next()) {
// Concatenate it to the same string using a
// stringbuffer
sb.append(rs2.getString("aff"));
// affiliation.add(rs2.getString("aff"));
}
affiliation.add(sb.toString());
count++;
// if(count>1)
// {
// moreFlag = true;
// //Call fuzzy match function, store the distance values
// and select the
// //affiliation that has the minimum distance from
// affiliationSmall
//
// //problem is here, affiliation.get Index: 2, Size: 2
//
// matchValues.add(fuzzyMatch(affiliationSmall,affiliation.get(count)));
// hixValues.add(tmpHix);
// idValues.add(id);
// }
}// end of while rs1 -> faculty
rs1.close();
int idx = 0;
if (count > 1) {
moreFlag = true;
// Call fuzzy match function, store the distance values and
// select the
// affiliation that has the minimum distance from
// affiliationSmall
// problem is here, affiliation.get Index: 2, Size: 2
matchValues.add(fuzzyMatch(affiliationSmall, affiliation
.get(idx)));
hixValues.add(tmpHix);
idValues.add(id);
idx++;
}
if (moreFlag) {
Object obj = Collections.max(matchValues);
float maxVal = Float.parseFloat(obj.toString());
// int tmpIdx = matchValues.indexOf(new Float(maxVal));
// get the index at which similarity between affiliation
// strings is maximum,
// as returned by fuzzyMatch
// int tmpIdx = matchValues.indexOf(maxVal);
tmpIdx = matchValues.indexOf(maxVal);
// get the hix at that index where the similarity is maximum
// int tmpHidx = hixValues.get(tmpIdx);
tmpHidx = hixValues.get(tmpIdx);
tmpidIdx = idValues.get(tmpIdx);
// update the smalldb table
String updateQ = "UPDATE libra.smalldb SET hIx = ? WHERE name = ?";
// String updateQ =
// "UPDATE libra.smalldb SET hIx = ? WHERE current of "+cursorName;
// PreparedStatement prepStmt1 =
// con.prepareStatement("UPDATE libra.smalldb SET hIx = ? WHERE current of "+cursorName);
PreparedStatement prepStmt1 = con.prepareStatement(updateQ);
// PreparedStatement prepStmt1 =
// con.prepareStatement(updateQ);
prepStmt1.setString(2, name1);
prepStmt1.setString(1, Integer.toString(tmpHidx));
prepStmt1.executeUpdate(updateQ);
// prepStmt1.execute();
// stmt4.executeUpdate(updateQ);
}// end of if
// For matching names get the affiliation based on id from
// affiliation table
// con.close();
// System.out.println(lastName);
System.out.println(name);
}// end of while rs -> smalldb
rs.close();
// String updateQ =
// "UPDATE libra.smalldb1 SET hIx = "+Integer.toString(tmpHidx)+
// "WHERE id = "+Integer.toString(tmpidIdx);
// stmt4.executeUpdate(updateQ);
con.close();
} catch (Exception e) {
System.err.println("Got an exception! ");
System.err.println(e.getMessage());
e.printStackTrace();
}
}
public static float fuzzyMatch(String affiliationSmall, String affiliation) {
// float distance = 0;
String[] temp = null;
temp = affiliationSmall.split(" ");
int index;
// int index1 = affiliation.indexOf(affiliationSmall);
int matchCount = 0;
for (int idx = 0; idx < temp.length; idx++) {
index = affiliation.indexOf(temp[idx]);
if (index != -1) {
matchCount++;
}
}
float tmpFloat = matchCount / temp.length;
// int[] aff1= new int[affiliation1.length()];
// int[] aff2 = new int[affiliation2.length()];
return tmpFloat;
}
}
i think it is because of the second select statement (rs0)
Here is the error-
Got an exception!
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2690)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1648)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1567)
at Jdbcexample1.main(Jdbcexample1.java:184)
Ok, since the code is too much here's what i'm doing.
The outermost loop corresponds to rs0 and rs (both resultsets). One of them is executing the statement which says "select for update". The other one is on the same table, but it is not a select for update, it is just select.
The update statement updates the same field as in the rs(the resultset executing the statement with the update).
Initially i had one statement which read select multiple_fields from tablename for update
but I think that was causing problems because i had multiple fields in there and i needed to update only one field.
Hence i separated out the queries into two statements executed by two resultsets. but this is also causing problems because i think there is a lock on the row by the other resultset (the one without the update, rs0)
Any other suggestions??