I got the insert and Delete Data/Row working for my DBMS.
The deleteRow method finds the row that contains the same primary key from the database, passed on to the method, and deletes the row.
With using the insertRow method, the data I added shows on up on the last row as I wanted.

My problem is when I do a combination of calling the insert, delete multiple times the row inserted shows up on the position where the last deleted row was**

**when I delete the newly inserted row positioned at the deleted row and inserts it again it now shows up on the last row

My Questions is:
How do I make sure that the inserted row will always be at the last row no matter how many times I delete,insert or update the data.

Here's my current code:

    public void insertRow(String Mname, String Genre, int price,
                        String date) throws SQLException {
    Statement stmt = null;
    stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    ResultSet uprs = stmt.executeQuery("SELECT MOVIE_NAME, GENRE, FILM_BUDGET, RELEASE_DATE FROM MOVIE");
    try {

      uprs.moveToInsertRow();
      uprs.updateString("MOVIE_NAME", Mname);
      uprs.updateString("GENRE", Genre);
      uprs.updateInt("FILM_BUDGET", price);
      uprs.updateString("RELEASE_DATE", date);
      uprs.insertRow();
      uprs.moveToCurrentRow();

    } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
    } finally {
      if (uprs != null) { uprs.close(); }  
      if (stmt != null) { stmt.close(); }
    }
  }


    void deleteRow(String Mname) throws SQLException{
        Statement stmt = null;
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet uprs = stmt.executeQuery("SELECT MOVIE_NAME, GENRE, FILM_BUDGET, RELEASE_DATE FROM MOVIE");
       try {
        uprs.last();
        while( !(uprs.getString("MOVIE_NAME").equals(Mname)) ){
            uprs.previous();
        }
        uprs.deleteRow();

        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        } finally {
        if (stmt != null) { stmt.close(); }
        if (uprs != null) { uprs.close(); }
        } 
    }

Edited 4 Years Ago by zeroliken

It is obvious that you do not understand how internal database works. This may be a problem dealing with database because it is not exactly the same as programming.

In SQL, there usually is a unique identification number (ID) created with a table (when someone creates a table), so that you could uniquely identify and access each row data by giving only one obvious value -- the ID. The ID field usually has special properties set to it -- integer, primary key, auto-increment, and may be unique. If you delete a row, the unique identification (ID) which is being used is gone. To ensure that the same ID won't be duplicated, in SQL, it automatically generates a new ID by increasing from the last value of the ID used in the database table. As a result, the new inserted row is added to the table -- last row in the table.

There are 2 ways to deal with this problem, but they both are depended on certain criteria.

One method is to use "insert" with specified ID value. Then, when you call for data rows, sort it by ID. This will ensure that your data will go into the same row you deleted. In order to insert with the same ID, you must keep the ID from the deleted row before you delete the row.

The other method is to use "update" instead of "delete" and "insert" commands. This way, the same record row will never be removed but changed to whatever data you want.

PS: This question is not really about Java but SQL...

Edited 4 Years Ago by Taywin

This article has been dead for over six months. Start a new discussion instead.