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)