Inserting variable into SQL query from Java

Question

How do you insert variables into an SQL Query?

This is what I have so far ...

public String getBugList(int amount) {
    Connection con  = DatabaseConnection.getConnection();
    try (PreparedStatement ps = con.prepareStatement("SELECT submitter, report FROM bugs_log ORDER BY id DESC limit ))
}

I'm trying to get "amount" bugs to list. So if I input 2, then only the top 2 will get listed.

Answer 1

Try this code:

public String getBugList(int amount) {
    Connection con  = DatabaseConnection.getConnection();
    String query = "SELECT submitter, report FROM bugs_log ORDER BY id DESC limit ?";
    try(PreparedStatement ps = con.prepareStatement(query)) {
        ps.setInt(1, amount);
    }
}

Answer 2

Put a ? at the desired variable location. Then, from this API, call the set method for the variable type.

http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html

In your case you want an int, so call ps.setInt(1,x). If you want multiple variables, - or in SQL terms a "parameter" - , just use multiple ?'s. The PreparedStatement setter methods requires the parameters index. The max index is equal to the amount of ?'s you have in your query.