Passing Parameters to a Jdbc Preparedstatement

Passing parameters to a JDBC PreparedStatement

You should use the setString() method to set the userID. This both ensures that the statement is formatted properly, and prevents SQL injection:

statement =con.prepareStatement("SELECT * from employee WHERE  userID = ?");
statement.setString(1, userID);

There is a nice tutorial on how to use PreparedStatements properly in the Java Tutorials.

JDBC pass parameters to sql query

in that case your query should be

String query = "select * from employee where id = ?";

instead of Statement you need to create PreparedStatement

PreparedStatement preparedStatement = conn.prepareStatement(query);

and then set your id to the prepared statement

preparedStatment.setInt(1, id);

finally execute the query

resultSet = preparedStatement.executeQuery();

JDBC PreparedStatement and parameters (?) in select query

It is not possible to create dynamic queries this way, you have to use the normal string operations. Parameters can only be used for values, like Strings, Numbers, etc., not for names.

In your case it would be possible to do something like

String sqlTemplate = "SELECT <id_column>,supplier_name FROM supplier WHERE supplier_id = ?";
String selectSQL = sqlTemplate.replace("<id_column>", "supplier_id");

How to pass multiple parameters to a PreparedStatement in java?

Each ? in the PrepareStatement should be assigned a value. Here is an example adopted from here :

   String updateString =
        "update " + dbName + ".COFFEES " +
        "set SALES = ? where COF_NAME = ?";

   PreparedStatement updateSales = con.prepareStatement(updateString);
   updateSales.setInt(1, 500); //set value to first `?`
   updateSales.setString(2, "roasted"); //set value to second `?` 

PreparedStatement with list of parameters in a IN clause

What I do is to add a "?" for each possible value.

var stmt = String.format("select * from test where field in (%s)",
                         values.stream()
                         .map(v -> "?")
                         .collect(Collectors.joining(", ")));

Alternative using StringBuilder (which was the original answer 10+ years ago)

List values = ... 
StringBuilder builder = new StringBuilder();

for( int i = 0 ; i < values.size(); i++ ) {
    builder.append("?,");
}

String placeHolders =  builder.deleteCharAt( builder.length() -1 ).toString();
String stmt = "select * from test where field in ("+ placeHolders + ")";
PreparedStatement pstmt = ... 

And then happily set the params

int index = 1;
for( Object o : values ) {
   pstmt.setObject(  index++, o ); // or whatever it applies 
}

JDBC PreparedStatement - Using the same argument, is it possible?

Using a local variable, you can make the code less ugly and error-prone. But the shortcoming of JDBC that it does not support named parameters still holds. There will be again multiple lines for the same parameter.

    statement = connection.prepareStatement(sql);

    long time = i_RequestStats.GetResponseTime();
    long bytes = i_RequestStats.GetBytes();

    statement.setString(1, i_ServletModel.GetPath());
    statement.setInt(2, i_ServletModel.GetApplicationId());
    statement.setLong(3,time);
    statement.setLong(4, bytes);
    statement.setLong(5, time);
    statement.setLong(6, bytes);

Is it safe to pass a query with no parameters to PreparedStatement?

Since you are not passing any parameter to your query, you do not have the risk of SQL Injection. Also, you do not need PreparedStatement for your case. You can use Statement instead.

String query = "SELECT * from Table where col1 = 123 and col2 = 'abc'";

try (Statement st = conn.createStatement()) {
    ResultSet rset = stmt.executeQuery(query);

    while (rs.next()) {
        //...
    }
} 

Apart from this, as you can see in the code above, you should try using try-with-resorces statement which closes the resource automatically.

how to pass a variable number of parameters using a jdbc prepared statement?

If the number of ? parameter-markers varies per run then you must re-prepare if the number of parameter-markers changes. I would use a Declared Global Temporary Table (DGTT) especially if there are very large numbers of rows. Yes, more statements, but easier to scale because you can dynamically index the DGTT.

For more information on temporary tables in DB2, see this question.

---

Related Topics