Java SQL Update ExecuteUpdateStmt(Connection conn, PreparedStatement pstmt, PreparedStatement lockStatement, String tablename)

Description

Execute Update Stmt

License

Open Source License

Declaration

public static boolean ExecuteUpdateStmt(Connection conn, PreparedStatement pstmt,
            PreparedStatement lockStatement, String tablename) throws SQLException 

Method Source Code

//package com.java2s;
/**                                                                                                                                                                                
 * Copyright (c) 2012 USC Database Laboratory All rights reserved. 
 *
 * Authors:  Sumita Barahmand and Shahram Ghandeharizadeh                                                                                                                            
 *                                                                                                                                                                                 
 * Licensed under the Apache License, Version 2.0 (the "License"); you                                                                                                             
 * may not use this file except in compliance with the License. You                                                                                                                
 * may obtain a copy of the License at                                                                                                                                             
 *                                                                                                                                                                                 
 * http://www.apache.org/licenses/LICENSE-2.0                                                                                                                                      
 *                                                                                                                                                                                 
 * Unless required by applicable law or agreed to in writing, software                                                                                                             
 * distributed under the License is distributed on an "AS IS" BASIS,                                                                                                               
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or                                                                                                                 
 * implied. See the License for the specific language governing                                                                                                                    
 * permissions and limitations under the License. See accompanying                                                                                                                 
 * LICENSE file.                                                                                                                                                                   
 *//*from  w ww  .  ja  v  a2  s  .co  m*/

import java.sql.Connection;
import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.Random;

public class Main {
    public static final int MAX_UPDATE_RETRIES = 100;
    public static final int UPDATE_RETRY_SLEEP_TIME = 10;
    public static final boolean LOCK_TABLE_EXPLICIT = false;

    public static boolean ExecuteUpdateStmt(Connection conn, PreparedStatement pstmt,
            PreparedStatement lockStatement, String tablename) throws SQLException {
        String name_array[] = { tablename };
        return ExecuteUpdateStmt(conn, pstmt, lockStatement, name_array);
    }

    public static boolean ExecuteUpdateStmt(Connection conn, PreparedStatement pstmt,
            PreparedStatement lockStatement, String tablenames[]) throws SQLException {
        boolean retVal = false;
        int num_retries = 0;
        int sleep_time = 1;
        Random rand = new Random();

        boolean prev_autocommit_val = conn.getAutoCommit();
        conn.setAutoCommit(false);
        while (retVal == false) {
            try {
                if (LOCK_TABLE_EXPLICIT) {
                    for (String tablename : tablenames) {
                        LockRow(lockStatement, tablename, true);
                    }
                }
                pstmt.executeUpdate();
                conn.commit();
                retVal = true;
                //System.out.println("Successfully updated");
            } catch (SQLException e) {
                conn.rollback();
                if (e.getMessage().indexOf("ORA-08177") >= 0 && num_retries < MAX_UPDATE_RETRIES) {
                    // Sleep for awhile to avoid constantly deadlocking with other transactions
                    try {
                        int temp = rand.nextInt(sleep_time);
                        Thread.sleep(temp);
                        //System.out.println("Sleep for: " + temp + " ms, " + num_retries + " time");
                    } catch (InterruptedException e1) {
                        // TODO Auto-generated catch block
                        e1.printStackTrace();
                    }

                    // Retry the execution
                    num_retries++;
                    sleep_time += UPDATE_RETRY_SLEEP_TIME;
                } else {
                    conn.setAutoCommit(prev_autocommit_val);
                    throw e;
                }
            }
        }
        conn.setAutoCommit(prev_autocommit_val);
        return retVal;
    }

    public static void LockRow(PreparedStatement pstmt, String tablename, boolean exclusiveMode)
            throws SQLException {
        pstmt.executeUpdate(getLockStatement(tablename, exclusiveMode));
    }

    public static String getLockStatement(String tablename, boolean exclusiveMode) {
        if (exclusiveMode) {
            return "LOCK TABLE " + tablename + " IN EXCLUSIVE MODE";
        } else {
            return "LOCK TABLE " + tablename + " IN ROW SHARE MODE";
        }
    }
}

Related

1. ExecuteUpdate(String sql, List paras)
2. executeUpdateNoCommit(Connection c, String query)
3. executeUpdatePreparedStm(Connection conn, String preparedStm, Object... sqlParameters)
4. ExecuteUpdateQuery(String query, Connection con)
5. executeUpdateSP(Connection conn, String spName, Object... sqlParameters)