Java SQL UPDATE command not working

Question

I'm having an issue where my GUI program is showing no error and also not updating the jTable with the UPDATE statement I'm giving it. INSERT and DELETE are working fine, but this isn't for some reason.

I'm using UCanAccess so I can use an MSAccess DB and rs2xml for resultset stuff.

The table I'm modifying here is a junction table with 2 primary keys for director_id and movie_id which are linked to separate tables so this is a many-to-many relationship.

 private void jButtonEditActionPerformed(java.awt.event.ActionEvent evt) {                                            
        DefaultTableModel tableModel = (DefaultTableModel) jTableDBView.getModel();

        // If there's an issue...
        if(error handling snipped out...)
        {
        }
        else
        {
            runQuery(Integer.parseInt(jTextFieldDirectorID.getText()), Integer.parseInt(jTextFieldMovieID.getText()), "UPDATE");
            jTextAreaDBView.append("Updated row: " + (jTableDBView.getSelectedRow() + 1) + " (Director ID: " + jTextFieldDirectorID.getText() + " & Movie ID: " + jTextFieldMovieID.getText() +")\n");
            updateDB();
        }

Which calls...

public void runQuery(int movieID, int directorID, String typeOfCommand) throws SQLException
{
    Connection conn = DriverManager.getConnection(jdbcURL);
    ResultSet rs = null;
    PreparedStatement stat = null;

    try
    {
        //snipped insert/delete

        else if(typeOfCommand.equals("UPDATE"))
        {
            stat = conn.prepareStatement("UPDATE Movie_Directors SET director_id = ?, movie_id = ? WHERE (director_id = ?) AND (movie_id = ?)"); 
            stat.setInt(1, directorID);
            stat.setInt(2, movieID);
            stat.setInt(3, directorID);
            stat.setInt(4, movieID);

            int result = stat.executeUpdate();
        }

And

private void updateDB()
{
    try
    {
        Connection conn = DriverManager.getConnection(jdbcURL);
        PreparedStatement pst = conn.prepareStatement(query);
        Class.forName(driver);
        ResultSet rs = pst.executeQuery();
        jTableDBView.setModel(DbUtils.resultSetToTableModel(rs)); // Imported lib function
        conn.close();
        pst.close();
        rs.close();
    }
    catch (Exception ex)
    {
        // Add a window here
    }
}

UPDATE - Fixed the runQuery block with this: I added 2 new jTextFields to input the new data and I understand how it all works now. Thank you @jan

            int result;
            int newDirectorID = Integer.parseInt(jTextFieldNewDirectorID.getText());
            int newMovieID = Integer.parseInt(jTextFieldNewMovieID.getText());

            stat = conn.prepareStatement("UPDATE Movie_Directors SET director_id = ?, movie_id = ? WHERE director_id = ? AND movie_id = ?"); 
            stat.setInt(1, newDirectorID);
            stat.setInt(2, newMovieID);
            stat.setInt(3, directorID);
            stat.setInt(4, movieID);
            result = stat.executeUpdate();

can you remove parentheses likewise, WHERE director_id = ? AND movie_id = ? — Vishal Gajera
– Vishal Gajera, Commented Dec 13, 2015 at 19:23

Jan · Accepted Answer · 2015-12-13 19:53:37Z

1

I might be mistaken - but your update does nothing. It assigns (movieId, directorId) to that row which already has exactly that (movieId,directorId)

So if there is already a row - nothing happens. And if there's no matching row - nothing happens as well.

An update would make sense if for instance a movie's director did change at some time.

In that case, you'd need a variable newDirectorID and put it in as new value:

        int newDirectorID; // YOU NEED TO PROVIDE A VALUE
        stat = conn.prepareStatement("UPDATE Movie_Directors SET director_id = ?, movie_id = ? WHERE director_id = ? AND movie_id = ?"); 
        stat.setInt(1, newDirectorID);
        stat.setInt(2, movieID);
        stat.setInt(3, directorID);
        stat.setInt(4, movieID);

        int result = stat.executeUpdate();

This would infact change some data in your table, so number of updated rows should be 1 if (directorID, movieID) existed before.

edited Dec 13, 2015 at 19:53

answered Dec 13, 2015 at 19:26

Jan

13.9k3 gold badges35 silver badges58 bronze badges

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

2 Comments

Arte Vandelay Over a year ago

I'm pretty new at this but isn't update supposed to modify the existing data? I have spent hours looking up how to do an update to a junction table with more than 1 primary key in MSAccess in Java and have found nothing. I'm just adding it for a just-in-case scenario where a director would change because sometimes it does happen. But really I just chose a subject and put it as movies/directors instead of car/owner or something else.

Jan Over a year ago

See my edit - you'd need both the newDirectorID and the existing directorID in that case.

Collectives™ on Stack Overflow

Java SQL UPDATE command not working

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related