Deleting Data From Database Through JavaFX GUI

Question

What I want to be able to do is load my database through a table view select an item and have it deleted into the database. I do not have users entering the id of a particular song so it makes it harder for me to accomplish this. I have the GUI set up and all the code that I have so far.

GUI CODE:

SongContent Code:

  /*
 * 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 playmymusic;

import javafx.beans.property.IntegerProperty;
import javafx.beans.property.SimpleIntegerProperty;
import javafx.beans.property.SimpleStringProperty;
import javafx.beans.property.StringProperty;

/**
 *
 * @author man
 */
public class SongContent 
{
    private final StringProperty artist;
    private final StringProperty title;
    private final StringProperty genre;
    private final IntegerProperty id;

   public SongContent(int id, String artist, String title, String genre)
   {
       this.artist = new SimpleStringProperty(artist);
       this.title = new SimpleStringProperty(title);
       this.genre = new SimpleStringProperty(genre);
       this.id = new SimpleIntegerProperty(id);
   }

   public Integer getId()
   {
       return id.get();
   }
   public void setID(int paramId)
   {
       id.set(paramId);
   }

   public String getArtist()
   {
       return artist.get();
   }
   public void setArtist(String paramArtist)
   {
       artist.set(paramArtist);
   }

   public String getTitle()
   {
       return title.get();
   }
   public void setTitle(String paramTitle)
   {
       title.set(paramTitle);
   }

   public String getGenre()
   {
       return genre.get();
   }
   public void setGenre(String paramGenre)
   {
       genre.set(paramGenre);
   }

   public StringProperty artistProperty(){return artist;}
   public StringProperty titleProperty(){return title;}
   public StringProperty genreProperty(){return genre;}
   public IntegerProperty idProperty() { return id;}
}

Controller Code:

/*
 * 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 playmymusic;

import java.io.IOException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;
import javafx.beans.property.IntegerProperty;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.fxml.FXMLLoader;
import javafx.fxml.Initializable;
import javafx.scene.Scene;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.TextField;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.scene.layout.Pane;
import javafx.stage.Stage;
import javax.swing.JOptionPane;
import org.apache.derby.jdbc.ClientDriver;
/**
 *
 * @author man
 */
public class FXMLDocumentController implements Initializable {
    public LoginModel loginModel = new LoginModel();

    @FXML
    private TextField txtUsername;
    @FXML
    private TextField txtPassword;

    @FXML
    private TextField txtArtist;
    @FXML
    private TextField fxTitle;
    @FXML 
    private TextField fxGenre;

    @FXML
    private TableView<SongContent> tableView;

    @FXML
    private TableColumn<SongContent, Integer> id;

    @FXML
    private TableColumn<SongContent, String> artist;
    @FXML
    private TableColumn<SongContent, String> title;
    @FXML
    private TableColumn<SongContent, String> genre;

    private ObservableList<SongContent> data;


    @FXML
    private void Login(ActionEvent event) throws SQLException {
        try {
            if(loginModel.isLogin(txtUsername.getText(), txtPassword.getText()))
            {
                Stage primaryStage = new Stage();
                                FXMLLoader loader = new FXMLLoader();
                Pane root = loader.load(getClass().getResource("PopUpWindow.fxml").openStream());

                Scene scene = new Scene(root, 785, 809);
                primaryStage.setScene(scene);
                primaryStage.show();

                PlayMyMusic.primaryStage.close();
            }else 
            {
                            System.out.println("WOOPS");
                        }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
                }
    }


    @FXML
    private void songs(ActionEvent e) throws SQLException, ClassNotFoundException 
    {

        loginModel.insertSongs(txtArtist.getText(), fxTitle.getText(), fxGenre.getText());    
        try
        {
            int i = 1;
            Connection conn = DriverManager.getConnection("jdbc:derby://localhost:1527/PlayMyMusicDB;user=test;password=test");
            data = FXCollections.observableArrayList();

            ResultSet rs = conn.createStatement().executeQuery("select * from Song");
            while(rs.next())
            {
                data.add(new SongContent(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4)));
                i++;
            }
        }catch(SQLException ex)       {
            System.err.println("Error"  + ex);
        }
        id.setCellValueFactory(new PropertyValueFactory<>("id"));
        artist.setCellValueFactory(new PropertyValueFactory<>("artist"));
        title.setCellValueFactory(new PropertyValueFactory<>("title"));
        genre.setCellValueFactory(new PropertyValueFactory<>("genre"));

        tableView.setItems(null);
        tableView.setItems(data);
        txtArtist.clear();
        fxTitle.clear();
        fxGenre.clear();

    }

    @FXML
    public void deleteItems(ActionEvent e) throws SQLException, ClassNotFoundException
    {
        Connection c = DriverManager.getConnection("jdbc:derby://localhost:1527/PlayMyMusicDB;user=test;password=test");
        int action = JOptionPane.showConfirmDialog(null, "Are you sure you want to delete this item?");
        if(action == 0)
        {
            try
            {
               IntegerProperty i = SongContent.idProperty();

               ResultSet rs = c.createStatement().executeQuery("DELETE FROM Song where i = " + i);


            }catch(Exception e1)
            {
                e1.printStackTrace();
            }
        }
    }


    @Override
    public void initialize(URL url, ResourceBundle rb)
    {

    }    
}
`

Any explination of why this could not be deleting my data? I would also love it if someone explained to me a strategy of resetting the SongNumberID every time the GUI opened and closed. But, the main goal for me is to figure out how to delete songs.

Thanks so much -Aaron

Answer 1

The result of calling toString on a SimpleIntegerProperty is something like IntegerProperty [value: 10]. You should use the value, not the IntegerProperty. Furthermore it's better to use a PreparedStatement to create the query. Also you should get the selected item from the table instead of trying to reference a instance method as if it was static:

SongContent song = tableView.getSelectionModel().getSelectedItem();
if (song != null) {
    // there is a selection -> delete
    ...

    PreparedStatement statement = c.prepareStatement("DELETE FROM Song WHERE i = ?");
    statement.setInt(1, song.getId());
    statement.executeUpdate();
    ...
}

Furthermore you should make sure i is actually the column name of the id column (and not id).