/**
CREATE TABLE `test_table` (
  `id` int(11) NOT NULL,
  `min_age` float NOT NULL default '0',
  `max_age` float NOT NULL default '1200',
  `order_num` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`id`,`min_age`,`max_age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

INSERT INTO `test_table` VALUES (131,0,130,1),(131,1,12,3),(131,3,18,0),(131,4,16,1),(131,5,10,0),(131,5.5,51.45,2),(131,12,144,0),(131,13,25,4),(131,36,47,2),(131,48,204,1);

 */
import java.sql.*;
import java.util.Random;

public class UpdateTest
{
    public static void main(String[] args)
        throws Exception
    {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance(); // Load driver

            conn = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "");

            conn.setAutoCommit(false); // BEGIN
            ps = conn.prepareStatement("SELECT id, min_age, max_age, order_num FROM test_table WHERE id=131 FOR UPDATE",
                                       ResultSet.TYPE_FORWARD_ONLY,
                                       ResultSet.CONCUR_UPDATABLE);

            rs = ps.executeQuery();

            Random random = new Random(System.currentTimeMillis());
            while(rs.next())
            {
                rs.updateInt("order_num", (random.nextInt() & 0xff)); // not PK

                rs.updateRow();
            }

            conn.commit();
        }
        catch (SQLException se)
        {
            if(null != conn) try { conn.rollback(); } catch (SQLException sqle)
            { sqle.printStackTrace(); }

            throw se;
        }
        catch (RuntimeException rte)
        {
            if(null != conn) try { conn.rollback(); } catch (SQLException sqle)
            { sqle.printStackTrace(); }

            throw rte;
        }
        catch (Error e)
        {
            if(null != conn) try { conn.rollback(); } catch (SQLException sqle)
            { sqle.printStackTrace(); }

            throw e;
        }
        finally
        {
            if(null != rs) try { rs.close(); } catch (SQLException sqle)
            { sqle.printStackTrace(); }

            if(null != ps) try { ps.close(); } catch (SQLException sqle)
            { sqle.printStackTrace(); }

            if(null != conn) try { conn.close(); } catch (SQLException sqle)
            { sqle.printStackTrace(); }
        }
    }
}