select for update problem in jdbc
- by kartiku
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(count1)
// {
// 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)
