Select item from JCombobox and delete the row in d

Question

I am working on a project in which I have a SQLite database with a table called Table1 and values title/location without an ID as column etc...

My form has a combobox which I've managed to get it display my DB each entry in one row . Now I want with a button "Delete" to delete the row that I have selected in the combobox and I am kind of lost.

Here is my code for the combobox (I think it's fine):

private void FillCombo(){
    try {
        String newLine = System.getProperty("line.separator");        
        Class.forName("org.sqlite.JDBC");
      connection = DriverManager.getConnection("jdbc:sqlite:C:\\users\\Kajou\\momentsdb.sqlite");
        statement = connection.createStatement();
        resultSet = statement.executeQuery("SELECT * FROM Table1");

        while (resultSet.next()) {    
            comboBox1.addItem(resultSet.getString("Title") + " " + "Date:" + resultSet.getString("Day")+"/"+ resultSet.getString("Month") +"/" + resultSet.getString("Year") + " " + resultSet.getString("Location")+ " " + resultSet.getString("Mood"));
        }

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            resultSet.close();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

And now about the button delete. I've tried few things but looks like not going well:

private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {                                         
    try {
        Class.forName("org.sqlite.JDBC");                
        connection = DriverManager.getConnection("jdbc:sqlite:C:\\users\\Kajou\\momentsdb.sqlite");
        statement = connection.createStatement();
        String sql = "DELETE FROM Table1 WHERE col_string=Title";
        int deleteCount = statement.executeUpdate(sql);
        sql = "DELETE FROM Table1 WHERE col_string=?";
        pst = connection.prepareStatement(sql);
        pst.setString(1,"a string");
        deleteCount=pst.executeUpdate();
    } catch (ClassNotFoundException ex) {
        Logger.getLogger(DeleteImportantMomentsForm.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
        Logger.getLogger(DeleteImportantMomentsForm.class.getName()).log(Level.SEVERE, null, ex);
    }            
}

Answer 1

First off, sorry for the extension but I have several advices for you :)

1) My form has a combobox which i managed to get it display my DB each entry in one row

Here is the way you're adding an entry to the combo box:

comboBox1.addItem(resultSet.getString("Title") + " " + "Date:" + 
                   resultSet.getString("Day")+"/"+ 
                   resultSet.getString("Month") +"/" + 
                   resultSet.getString("Year") + " " + 
                   resultSet.getString("Location")+ " " + 
                   resultSet.getString("Mood"));

This way you'll get a very large string added to your combo box, which is not bad but can be highly improved if you map this info into a domain class. For instance:

class MyClass {
    int day, month, year;
    String title, location, mood;

    public MyClass(int day, int month, int year, 
                   String title, String location, String mood) {
        this.day = day;
        this.month = month;
        this.year = year,
        this.title = title;
        this.location = location;
        this.mood = mood;
    }

    // getters and setters are up to you :)
}

Next step is adding new MyClass objects to your combo box:

while (resultSet.next()) {
    MyClass myClass = new MyClass(resultSet.getInt("Day"),
                                  resultSet.getInt("Month"),
                                  resultSet.getInt("Year"),
                                  resultSet.getString("Title"),
                                  resultSet.getString("Location"),
                                  resultSet.getString("Mood"));
    comboBox1.addItem(myClass); // see note below
}

Note: Swing components (such as JComboBox) must be created and updated in the Event Dispatching Thread (a.k.a. EDT) which is a single and special thread. If you have a heavy task running in the EDT (for instance database connections) then this task may block the EDT and your GUI will freeze and Swing components won't be able to work (or even display) untill this task is done. To avoid this issue there are some tools you can use to make the heavy tasks in a separate thread and update Swing components in the EDT, for instance SwingWorker. For further information take a look to Concurrency in Swing.

So I would suggest you make database calls using a SwingWorker:

SwingWorker<Void,MyClass> worker = new SwingWorker<Void,MyClass>() {
    @Override
    protected Void doInBackground() throws Exception { // this take place in a background thread
        // connect to the database and execute the query here
        while (resultSet.next()) {
            MyClass myClass = new MyClass(resultSet.getInt("Day"), ...);
            publish(myClass);
        }
        return null;
    }

    @Override
    protected void process(List<MyClass> chunks){ // this take place in the EDT
        for(MyClass myClass : chunks){
            comboBox1.addItem(myClass);
        }
    }
}

Well now you have the items properly added to your combo box. Next step is make the combo box display the text as you wish. To do so you'll need to implement a ListCellRenderer. Take a look to Providing a Custom Renderer section in How to Use Combo Boxes tutorial. Take a look to this example. It's about setting a custom renderer to a JList but exactly the same applies to JComboBox.

2) Now i want with a button "delete" to delete the row that i have selected in the combobox and i am kind of lost

Please note you're trying to execute two different delete queries:

String sql = "DELETE FROM Table1 WHERE col_string=Title"; // This doesn't make sense
int deleteCount = statement.executeUpdate(sql); 
sql = "DELETE FROM Table1 WHERE col_string=?"; // This looks better I guess
pst = connection.prepareStatement(sql);

I suspect one of these queries (if not both of them) throws an Exception.

As your combo box will contain MyClass objects, you can make this change at the action performed method:

MyClass myClass = (MyClass)comboBox1.getSelectedItem();
String sql = "DELETE FROM Table1 WHERE Title = ?"; //If Title is the name of the column, of course
PreparedStatement pst = connection.prepareStatement(sql);
pst.setString(1,myClass.getTitle());

Note: Once again, the action performed method is executed in the EDT. You should move the database call to a background thread to avoid blocking the EDT.

Answer 2

You need something like:

Object item = comboBox.getSeletedItem();
pst.setString(1, item.toString());