'I want to re-organize and re-order rows when any of the row is deleted." For eg: If i delete row with id=5, then row with 6th id should be changed to 5.

Java jsp and mysql are used on netbeans.

Edit method:

public void edit(int id, String title, String description, String detail, String category, String image){
     String sql = "update News set title = ?, description=?, Details=?, category=?, image=?" + " where id=? ";   
     PreparedStatement ps;
        try {
            ps = DBUtils.getPreparedStatement(sql);
            ps.setString(1,title);
            ps.setString(2,description );
            ps.setString(3,detail);
            ps.setString(4,category );
            ps.setString(5,image);
            ps.setInt(6,id);
            ps.executeUpdate();
        } catch (ClassNotFoundException | SQLException ex) {
            Logger.getLogger(DataAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

Delete method:

public void delete(int id){
    try {
        String sql = "delete from News where id = ?";
        PreparedStatement ps = DBUtils.getPreparedStatement(sql);
        ps.setInt(1, id);
        ps.executeUpdate();
    } catch (ClassNotFoundException | SQLException ex) {
        Logger.getLogger(DataAccess.class.getName()).log(Level.SEVERE, null, ex);
    }
}

2 Answers

0
Community On

You should not re order the ids. you can just use ROW_NUMBER for display purposes

0
Community On

In the delete method, you can run an additional query as

String query = "Update News set id = id-1 where id >" + id + ";";