JDBC Update on Oracle failed to commit

Question

I have JDBC Dao Object, and used PreparedStatements to do UPDATE a row at a table in my DB.

I have other methods such as SELECT and INSERT which are successful (insert-commit works). But the update, just does not commit the changes (does not work at all). While the same UPDATE statement works from Oracle SQLServer directly.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class StaffDAO {
    private Connection conn;

    public StaffDAO() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            System.out.println("Oracle Driver not found");
            System.exit(0);
        }
        try {
            conn = DriverManager.getConnection(
                    "jdbc:oracle:thin:@db01.xxxdev.com:1521:training",
                    "training", "training");
        } catch (SQLException e) {
            System.out.println("Driver manager failed");
        }
    }

    public ResultSet getAllResultSet() {
        String sql = "select * from ben_staff";
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(sql);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return rs;

    }

    public Staff viewEmployee(String id) throws Exception {
        Staff st = new Staff();
        String sql = "SELECT * from BEN_STAFF where BEN_STAFF.id =\'" + id
                + "\'";
        // String psql = "SELECT * FROM BEN_STAFF WHERE ID = ?";

        Statement statement = null;
        // PreparedStatement pstatement = null;
        try {
            statement = conn.createStatement();
            // pstatement = conn.prepareStatement(psql);
            // pstatement.setString(1, id);
        } catch (SQLException e) {
            System.out.println("Create Statement failed");
            System.exit(1);
        }
        ResultSet rs = null;
        try {
            rs = statement.executeQuery(sql);
            // rs = pstatement.executeQuery();

            while (rs.next()) {
                st.setId(rs.getString("ID"));
                st.setLastName(rs.getString("LASTNAME"));
                st.setFirstName(rs.getString("FIRSTNAME"));
                st.setMi(rs.getString("MI"));
                st.setAddress(rs.getString("ADDRESS"));
                st.setCity(rs.getString("CITY"));
                st.setState(rs.getString("STATE"));
                st.setTelephone(rs.getString("TELEPHONE"));
                st.setEmail(rs.getString("EMAIL"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                statement.close();
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return st;
    }

    public boolean insert(String id, String last, String first, String mi,
            String address, String city, String state, String telephone,
            String email) {
        PreparedStatement pstmt = null;
        String psql = "insert into ben_staff (id, lastname, firstname, mi, address, city, state, telephone, email)"
                + "values (?,?,?,?,?,?,?,?,?)";

        try {
            pstmt = conn.prepareStatement(psql);
            // pstmt.setString(1,st.getId());
            // pstmt.setString(2, st.getLastName());
            // pstmt.setString(3, st.getFirstName());
            pstmt.setString(1, id);
            pstmt.setString(2, last);
            pstmt.setString(3, first);
            pstmt.setString(4, mi);
            pstmt.setString(5, address);
            pstmt.setString(6, city);
            pstmt.setString(7, state);
            pstmt.setString(8, telephone);
            pstmt.setString(9, email);
            pstmt.executeUpdate();
            conn.commit();
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        } finally {
            try {
                pstmt.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        return true;
    }

    public boolean update(String id, String last, String first, String mi,
            String address, String city, String state, String telephone,
            String email) {
        PreparedStatement pstmt = null;
        String psql = "update ben_staff set lastname=?, firstname=?, mi=?, address=?, city=?, state=?,"
                + " telephone=?, email=? where id=?";

        try {
            pstmt = conn.prepareStatement(psql);
            pstmt.setString(1, last);
            pstmt.setString(2, first);
            pstmt.setString(3, mi);
            pstmt.setString(4, address);
            pstmt.setString(5, city);
            pstmt.setString(6, state);
            pstmt.setString(7, telephone);
            pstmt.setString(8, email);
            pstmt.setString(9, id);
            pstmt.executeUpdate();
            conn.commit();
        } catch (SQLException ex) {
            ex.printStackTrace();
            try{
                conn.rollback();
            } catch (SQLException exx){
                System.out.println("Update Rollback Failed");
            }
            return false;
        } finally {
            try {
                pstmt.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        return true;
    }

    public void close() {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

Answer 1

What does pstmt.executeUpdate(); return? That would tell you how many rows are being updated. Something like

int numRows = pstmt.executeUpdate();
System.out.println( "Update modified " + numRows + " rows." );

My guess is that your update isn't actually modifying any rows. That would imply that the id being passed in was incorrect. Remember that string comparisons in SQL Server are case insensitive by default while they are case sensitive by default in Oracle.