Conditions in prepared statements in postgres
This query will work for you.
select * from users where
case
when coalesce($1, '') = '' then true
else (name ~ $1)
end;
PreparedStatement IN clause alternatives?
An analysis of the various options available, and the pros and cons of each is available in Jeanne Boyarsky's Batching Select Statements in JDBC entry on JavaRanch Journal.
The suggested options are:
- Prepare
SELECT my_column FROM my_table WHERE search_column = ?
, execute it for each value and UNION the results client-side. Requires only one prepared statement. Slow and painful. - Prepare
SELECT my_column FROM my_table WHERE search_column IN (?,?,?)
and execute it. Requires one prepared statement per size-of-IN-list. Fast and obvious. - Prepare
SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ...
and execute it. [Or useUNION ALL
in place of those semicolons. --ed] Requires one prepared statement per size-of-IN-list. Stupidly slow, strictly worse thanWHERE search_column IN (?,?,?)
, so I don't know why the blogger even suggested it. - Use a stored procedure to construct the result set.
- Prepare N different size-of-IN-list queries; say, with 2, 10, and 50 values. To search for an IN-list with 6 different values, populate the size-10 query so that it looks like
SELECT my_column FROM my_table WHERE search_column IN (1,2,3,4,5,6,6,6,6,6)
. Any decent server will optimize out the duplicate values before running the query.
None of these options are ideal.
The best option if you are using JDBC4 and a server that supports x = ANY(y)
, is to use PreparedStatement.setArray
as described in Boris's anwser.
There doesn't seem to be any way to make setArray
work with IN-lists, though.
Sometimes SQL statements are loaded at runtime (e.g., from a properties file) but require a variable number of parameters. In such cases, first define the query:
query=SELECT * FROM table t WHERE t.column IN (?)
Next, load the query. Then determine the number of parameters prior to running it. Once the parameter count is known, run:
sql = any( sql, count );
For example:
/**
* Converts a SQL statement containing exactly one IN clause to an IN clause
* using multiple comma-delimited parameters.
*
* @param sql The SQL statement string with one IN clause.
* @param params The number of parameters the SQL statement requires.
* @return The SQL statement with (?) replaced with multiple parameter
* placeholders.
*/
public static String any(String sql, final int params) {
// Create a comma-delimited list based on the number of parameters.
final StringBuilder sb = new StringBuilder(
String.join(", ", Collections.nCopies(possibleValue.size(), "?")));
// For more than 1 parameter, replace the single parameter with
// multiple parameter placeholders.
if (sb.length() > 1) {
sql = sql.replace("(?)", "(" + sb + ")");
}
// Return the modified comma-delimited list of parameters.
return sql;
}
For certain databases where passing an array via the JDBC 4 specification is unsupported, this method can facilitate transforming the slow = ?
into the faster IN (?)
clause condition, which can then be expanded by calling the any
method.
How to use prepared statements in DO statements?
You cannot use a DO
statement as a prepared statement.
I recommend that you use two statements:
one get the three results that you need to determine if there is an error condition
one to run the
INSERT
statement
The second of these would be a regular prepared statement.
It seems to me that you are mixing up transactions and BEGIN ... END
blocks in PL/pgSQL.
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
}
Postgres SQL in clause and setArray()
When your database field is of type array
, then you can use the PreparedStatement.setArray()
to send an array to the query. But, in your case, it's not really an array, rather is a variable no of arguments, and you can't do that. i.e.
PreparedStatement ptmt = connection.prepareStatement("select * from foo where id in (?)");
can take only one parameter. If you want 3 parameters to be passed, you have to do
PreparedStatement ptmt = connection.prepareStatement("select * from foo where id in (?, ?, ?)");
And do ptmt.setString(n, "String")
thrice.
If your no of arguments aren't constant, then construct the query dynamically, although, you loose the efficiency.
prepared statement - using parameter to specify table name
It is not possible. The prepare statement is persistent execution plan - and execution plan contains pined source of data - so tables, column names cannot be mutable there.
When you change table, columns, then you change the semantic of query - you will got different execution plan and then this behave is not possible in prepared statements. The main use case of prepared statements is reusing of execution plans - plan once, execute more. But there are some principal limits - only some parameters can be changed.
Related Topics
Java.Sql.Sqlexception: Ora-03115: Unsupported Network Datatype or Representation
Group by Every N Records in T-Sql
Get the Number of Affected Rows in a MySQL Update Statement
How to Replace Null Values with a Text
How to Migrate Datetime Values to Datetimeoffset in SQL Server
Swap Values for Two Rows in the Same Table in SQL Server
How to Get Time Part from SQL Server 2005 Datetime in 'Hh:Mm Tt' Format
SQL Do Inner Join If Condition Met
Sqlite3 (Or General SQL) Retrieve Nth Row of a Query Result
Sql: Select Top 3 Records + Sum of Quantity
How to Use Array_Agg() for Varchar[]
T-SQL Get Number of Working Days Between 2 Dates