How do I delete a row from database table in Java

Question

I am trying to develop a simple Java DVD library console app in Java. I have created a database table that contains a list of DVD's. I have managed to get the adding a new DVD to the database functionally working, but I am struggling to delete a row from the database. When I use a SQL statement to select a row (row 7) then run the line 'rs.delete' I get the following exception:-

Invalid cursor state - no current row.

Below is my database table:-

ID   Film Name    Genre   Rating
-------------------------------
1    Robocop      Sci-fi  18
2    Terminator   Sci-fi  18
3    Alien        Sci-fi  18
4    Big Fish     Fantasy PG
5    The Pianist  Drama   18
6    Total Recall  Sci-fi 18
7    Carnage Comedy       18

Below is copy of my code. Please could somebody help?

/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package dvdlibrary;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import javax.swing.JOptionPane;
import java.util.Scanner;
/**
*
* @author Andy
*/
public class DVDLibrary {

    Connection con;
    Statement stmt;
    ResultSet rs;

    String selection = "";
    int id_num =0;
    String film_name ="";
    String genre ="";
    String rating="";


        public DVDLibrary()
        {
            DoConnect();
        }

        public void DoConnect() { 

            try
            {
                String host = "jdbc:derby://localhost:1527/DVDLibrary";
                String username = "andyshort";
                String password = "Pa55word";`enter code here`

                con = DriverManager.getConnection(host, username, password);
                stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

                String SQL = "SELECT * FROM ANDYSHORT.DVDS";

                rs = stmt.executeQuery(SQL);

                /*
                System.out.println("ID   Film Name   Genre   Rating");
                System.out.println("-------------------------------");

                while (rs.next())                
                {   
                    int id_col = rs.getInt("ID");
                    String film_name_col = rs.getString("Film_Name");
                    String genre_col = rs.getString("Genre");
                    String rating_col = rs.getString("Rating");

                    String p = id_col + "    " + film_name_col + "    " + genre_col + "    " + rating_col;

                    System.out.println(p);     
                    //System.out.format("%32s%10d%16s", id_col , film_name_col, genre_col, rating_col);
                }
                */
            }
            catch (SQLException err)
            {
                System.out.println(err.getMessage());
            }

        }

        public void GetUserInput()
        { 
            System.out.println(); 
            System.out.println("What would you like to do? Choose one of the following options.");
            System.out.println("1. Display DVD library list");
            System.out.println("2. Add a new film to database.");        
            System.out.println("3. Delete a film from the database.");               

            System.out.println();  

            Scanner user_option_selection = new Scanner(System.in);

            selection = user_option_selection.next();

            if(selection.equalsIgnoreCase("1"))
            {
                DisplayDVDList();
            }
            else if(selection.equalsIgnoreCase("2"))
            {
                AddRecord();
            }
            else if(selection.equalsIgnoreCase("3"))
            {
                DeleteRecord();
            }
            else
            {
                System.out.println("Incorrect option entered. Please try again.");   
            }
        }

        public void DisplayDVDList()
        {
             try
            {                
                String SQL = "SELECT * FROM ANDYSHORT.DVDS";
                rs = stmt.executeQuery(SQL);    

                System.out.println("ID   Film Name   Genre   Rating");
                System.out.println("-------------------------------");

                while (rs.next())                
                {   
                    int id_col = rs.getInt("ID");
                    String film_name_col = rs.getString("Film_Name");
                    String genre_col = rs.getString("Genre");
                    String rating_col = rs.getString("Rating");

                    String p = id_col + "    " + film_name_col + " " + genre_col + " " + rating_col;

                    System.out.println(p);     
                    //System.out.format("%32s%10d%16s", id_col , film_name_col, genre_col, rating_col);
                }
            }
            catch (SQLException err)
            {
                System.out.println(err.getMessage());
            }
            GetUserInput();
        }

        public void AddRecord()
        {
            Scanner new_film_details = new Scanner(System.in);

            System.out.println("Please enter film name: ");
            film_name = new_film_details.next();

            System.out.println("Please enter film genre: ");
            genre = new_film_details.next();

            System.out.println("Please enter film rating: ");
            rating = new_film_details.next();

            try
            {
                rs.last();

                id_num = rs.getRow();
                id_num = id_num + 1;

                rs.moveToInsertRow();

                rs.updateInt("ID", id_num);
                rs.updateString("FILM_NAME", film_name);
                rs.updateString("GENRE", genre);
                rs.updateString("RATING", rating);

                rs.insertRow();

                //stmt.close( );
                //rs.close( );
            }
            catch(SQLException err)
            {
                System.out.println(err.getMessage());
            }
            GetUserInput();
        }



        public void DeleteRecord()
        {
            String id = "";
            Scanner id_of_film_to_delete= new Scanner(System.in);

            System.out.println("Enter ID of film you want to delete.");
            id = id_of_film_to_delete.next();    
            int idInt = Integer.parseInt(id);

            try
            {
                stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
                String sql = "SELECT * FROM ANDYSHORT.DVDS WHERE ID =" + idInt;
                rs = stmt.executeQuery(sql);

                rs.deleteRow();

            }
            catch(SQLException err)
            {
                System.out.println(err.getMessage());
            }
            GetUserInput();
        }



    }

Patrick · Accepted Answer · 2014-12-01 12:03:18Z

4

use directly this query

 "DELETE FROM ANDYSHORT.DVDS WHERE ID =" + idInt;

edited Dec 1, 2014 at 12:03

Patrick

18k6 gold badges73 silver badges85 bronze badges

answered Dec 1, 2014 at 12:00

Danyal Sandeelo

12.4k10 gold badges57 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ED209 Over a year ago

This did not work. After I reran the 'DisplayDVDList' method and the database still contained the row I tried to delete.

Danyal Sandeelo Over a year ago

preparedStmt.execute(); instead of executeUpdate(sql);

Harshit · Accepted Answer · 2014-12-02 06:38:29Z

2

String sql = "DELETE FROM ANDYSHORT.DVDS WHERE ID=?";

PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1, "+ idInt+");

int rowsDeleted = statement.executeUpdate();
if (rowsDeleted > 0) {
System.out.println(" delete successfully!");
}

edited Dec 2, 2014 at 6:38

answered Dec 1, 2014 at 12:45

Harshit

1632 silver badges11 bronze badges

2 Comments

ED209 Over a year ago

When I put this code into my Netbeans project I am getting a red squiggly line under 'rs = stmt.executeUpdate(sql);' and the error message says - "incompatible types:int can not be converted to ResultSet"

Harshit Over a year ago

write like this int rs = stmt.executeUpdate(sql);

mHouses · Accepted Answer · 2014-12-01 12:51:41Z

0

Use prepared statements to avoid SQL injection:

PreparedStatement statement;
statement = con.prepareStatement("DELETE FROM andyshort.dvds WHERE id = ?");
statement.setInt(1, idToDelete);
statement.executeUpdate();

answered Dec 1, 2014 at 12:51

mHouses

9191 gold badge17 silver badges37 bronze badges

1 Comment

ED209 Over a year ago

I tried this:- String query = "DELETE FROM ANDYSHORT.DVDS WHERE ID = "+ idInt; PreparedStatement preparedStatement = con.prepareCall(query); preparedStatement.setInt(1, idInt); preparedStatement.executeUpdate(); But go the following exception message - "The column position '1' is out of range. The number of columns for this ResultSet is '0'."

aravindaM · Accepted Answer · 2014-12-01 13:07:06Z

0

You can directly use delete query if you have the Id before hand.

String sql = "DELETE FROM ANDYSHORT.DVDS WHERE ID =" + idInt;
stmt.executeUpdate(sql);

But, it's better to use prepared statements instead of statements in order to avoid sql injection attacks.

String query= "DELETE FROM ANDYSHORT.DVDS WHERE ID = ? ";
PreparedStatement preparedStatement = con.prepareStatement(query);
preparedStatement.setInt(1,idInt);
preparedStatement.executeUpdate();

You need to move the cursor to the first row before deleting the row, if you want to use deleteRow() method.

rs.first();
rs.deleteRow();

answered Dec 1, 2014 at 13:07

aravindaM

1,08810 silver badges16 bronze badges

2 Comments

ED209 Over a year ago

I tried this:- String query = "DELETE FROM ANDYSHORT.DVDS WHERE ID = "+ idInt; PreparedStatement preparedStatement = con.prepareCall(query); preparedStatement.setInt(1, idInt); preparedStatement.executeUpdate(); But go the following exception message - "The column position '1' is out of range. The number of columns for this ResultSet is '0'."

aravindaM Over a year ago

Check your code again. There should be some mistake. query like, String query= "DELETE FROM ANDYSHORT.DVDS WHERE ID = '?' "; will not work at all.

Collectives™ on Stack Overflow

How do I delete a row from database table in Java

4 Answers 4

2 Comments

2 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related