how to pass sql commands from Java

Question

I am trying to pass a SQL command where a row has to be deleted from the database. I m using setString(index, value) and invoking the method in the main class. But when I pass the parameter to the method in the main class nothing happens in the database. Is there a form to pass the value of "jobnumber=?" in the main method so it deletes that row from the database? Ps: jobnumber is declared as serial and is the primary key in the database.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DeleteUserDao {
    Connection conDB2 = Connect.connectDB();    
    public void deleteDb(String y) {
        String sqlDelete = ("DELETE FROM bookings WHERE jobnumber=?");      
        try {
            PreparedStatement deleter = conDB2.prepareStatement(sqlDelete);
            deleter.setString(1, y);            
            System.out.println("Item deleted from Bookings.");
        } catch (SQLException e) {
            System.out.println("Coud not delete item. " + e.getMessage());
            e.printStackTrace();
        }
    }
}

import ***.jdbc.DeleteUserDao;

public class DeleteTest {
    public static void main(String[] args) {
        DeleteUserDao del = new DeleteUserDao();        
        del.deleteDb("4");
    }
}

Answer 1

If I understand your question, you're missing executeUpdate() for the Statement,

PreparedStatement deleter = conDB2.prepareStatement(sqlDelete);
deleter.setString(1, y);
deleter.executeUpdate(); // <-- like so

From the linked Javadoc,

Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

Edit

As noted in the comments below, you should probably be using an int type and you need to close your PreparedStatement -

public void deleteDb(int y) {
  String sqlDelete = ("DELETE FROM bookings WHERE jobnumber=?");     
  PreparedStatement deleter = null; 
  try {
    deleter = conDB2.prepareStatement(sqlDelete);
    deleter.setInt(1, y);            
    System.out.println("Item deleted from Bookings.");
  } catch (SQLException e) {
    System.out.println("Coud not delete item. " + e.getMessage());
    e.printStackTrace();
  } finally {
    if (deleter != null) {
      try {
        deleter.close();
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
  }
}

Then to call it, pass it an int -

del.deleteDb(4);

Answer 2

You haven't actually told the program to run the command.

In the prepareStatement call, you told the program "This is the format of the command I'll run, and I may run it multiple times with different parameters in place of the question marks", and in the setString call you said "At the moment, I think I'll want to run the command with parameter 1 set to the value of y", but you never actually said "execute the statement". Which you'd do by running:

deleter.execute();

The reasons for this are manifold, but the most facile example would be if you were running a prepared statement with two parameters, and you planned on running that statement 400 times.

Say you had "DELETE FROM bookings WHERE jobnumber=? AND personid=?", and you ran:

deleter.setString(1, y);
deleter.setString(2, y);

Then, you wanted to set parameter 1 to some value x, and parameter 2 to some value z. So you run:

deleter.setString(1, x);
deleter.setString(2, z);

If things worked the way you assumed in your question, this would have unexpected behavior, because parameters 1 and 2 are already both set to y, so setting parameter 1 to x would make parameter 1 become x and 2 remain y, and both parameters would be set which would prompt an automatic execute of the SQL on both those calls to setString. Which wouldn't be what you'd want.

On the other hand, if you did just want to change parameter 1, and didn't want to change parameter 2, it would be more convenient to simply be able to say:

deleter.setString(1, x);
deleter.execute();

Instead of:

deleter.setString(1, x);
deleter.setString(2, y);

In that particular example, it doesn't save much effort. But if you had a prepared statement with 30 parameters, and between each successive call you only change 1 or 2 of them on average, this paradigm saves enormous effort.