2

While executing update by jdbc on Oracle dbms, my program hangs. I think it is waiting for another process/user to release lock on the rows or table that I am trying to update. So what are the possible causes for this problem and how can I solve it? I am making calls to the dbms through jdbc as show here:

  public static void updateEmployee(String name,int id) throws ClassNotFoundException

{
    Connection con=null;
    PreparedStatement st=null;
    String driver= "oracle.jdbc.driver.OracleDriver";
    String username="someuser";
    String password="pwd";
    String url="jdbc:oracle:thin:@hostname:1521:ORAJAVADB";
    Class.forName(driver);
    try
    {
        con=DriverManager.getConnection(url,username,password);
        st=con.prepareStatement("update employee set employeeName=? where 
        employeeId = ? ");
        st.setString(1,name);
        st.setInt(2,id);
        st.executeUpdate();
        st.close();
        con.close();
    }
    catch(SQLException ex)
    {

    }

}
peak
  • 105,803
  • 17
  • 152
  • 177
varun kumar dogra
  • 82
  • 3
  • 3
    Trying add `ex.printStackTrace();` in your `catch` block, maybe that will help. You should also use a [The try-with-resources Statement](https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html) to close the `PreparedStatement` (and possibly the `Connection`) – MadProgrammer Jan 11 '16 at 04:23
  • 2
    There is nothing *you* can do, if someone *else* has a active transaction with an update pending for the same record. You (or rather a DBA) will have to find the blocking session, and kill it if necessary, then kick the person leaving an open transaction hanging. – Andreas Jan 11 '16 at 04:23
  • *Side note:* `Class.forName()` is not necessary anymore, and you should use try-with-resources. – Andreas Jan 11 '16 at 04:25
  • see [this](http://stackoverflow.com/questions/7501776/oracle-database-is-hanging-infinitly-in-update-queries) . You need to find out what else is running on DB , post your table size and how frequent transactions get committed in your environment ? – Sabir Khan Jan 11 '16 at 05:40
  • Why do you think the update statement is waiting for the some locks for be released? If you are familiar with the tools, try them while your program is executing to check that your connection is waiting for locks to be released. It may also help to step through your code in a debugger to verify that it is indeed the `executeUpdate` that is blocked. – Miserable Variable Jan 11 '16 at 23:38

1 Answers1

0

I made this code for update function using java with oracle database. I hope it will help you. Good Luck

package updatemethod;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.pool.OracleDataSource;


public class UpdateMethod {

public static String url = "jdbc:oracle:thin:@mohammed:1521:XE";
public static String user = "md";
public static String password = "md";
public static String query;

public static Connection conn;
public static Statement smt = null;




// CREATE DATABASE CONNECTION
 public static void getDBConnection() throws SQLException{

    OracleDataSource ds;
    ds = new OracleDataSource();
    ds.setURL(url);
    conn = ds.getConnection(user, password);

    System.out.println("DataBase Accessed!");
}


public static void updateEmployee(String nID, String newFirst, String newLast, String newJob)throws SQLException{

    try{
        smt = conn.createStatement();
        smt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

        ResultSet uprs = smt.executeQuery("SELECT ID, FIRSTNAME, LASTNAME, JOB FROM MD.EMPLOYEE1");

        while(uprs.next()){



             int newID = Integer.parseInt(nID);

             uprs.updateInt("ID", newID);
             uprs.updateString("FIRSTNAME", newFirst);
             uprs.updateString("LASTNAME", newLast);
             uprs.updateString("JOB", newJob);
             uprs.updateRow();

             System.out.println("DataBase Updated\n");
             System.out.println("ID " + newID + "    " + "FIRSTNAME " + newFirst + "  " + "LASTNAME  " + newLast + "     " + "JOB  " + newJob);

        }
    }
    catch(SQLException er){
        System.out.println(er);
    }

}




public static void main(String[] args) throws SQLException {

    // CREATE CONNECTION BY CALLING getDBConnection();
    getDBConnection();

    // NOW, CALL OUR updateEmployee(String,String,String,String) FUNCTION
    updateEmployee("123", "mohammed", "Jamal", "Computer Technique Engineer");

}

}
mohamad Jamal
  • 28
  • 6