I have the following snippet of a function that queries a PostgreSQL DB:

private void setQuery(String col1, String col2, String col3) {
   StringBuilder stringBuilder = new StringBuilder();
   stringBuilder.append(
      " SELECT  t1.id,t1.uuid, t2.client_id,t1.start_time, t1.end_time, t1.state FROM t1, t2 WHERE  t1.id=t2.id"
   );

   if (col1 != null && !col1.isEmpty()) {
     stringBuilder.append(" AND t1.col1 = ?");
   }
   if (col2 != null && !col2.isEmpty()) {
     stringBuilder.append(" AND t1.col2 = ?");
   }
   if (col3 != null && !col3.isEmpty()) {
     stringBuilder.append(" AND t1.col3 = ?");
   }
   PreparedStatement pstmt = null;
   try (Connection connection = connectionProvider.getConnection()) {
     pstmt = connection.prepareStatement(stringBuilder.toString());
     // how can I set the ? values in the stringBuilder, since they can be empty?
     // pstmt.setString(1, col1);
     // pstmt.setString(2, col2);
     // pstmt.setString(3, col3);
   } finally {
     if (pstmt != null) pstmt.close();
   }
}

Notice col1, col2, col3 can be empty or null, I need to add/remove those variables from the SQL query in case they are empty/null or not. Ideally, I would like to be allowed to append to the PreparedStatement, but I don't know how that's possible. What would be the best way to achieve this? How can I properly set the values into the prepared statement?

CodePudding user response：

You can try this approach:

private void setQuery(String col1, String col2, String col3) throws SQLException {
    StringBuilder stringBuilder = new StringBuilder();
    stringBuilder.append(
        " SELECT t1.id, t1.uuid, t2.client_id, t1.start_time, t1.end_time, t1.state FROM t1, t2 WHERE t1.id = t2.id "
    );

    List<String> optionalParams = new ArrayList<>();

    if (col1 != null && !col1.isEmpty()) {
        stringBuilder.append(" AND t1.col1 = ?");
        optionalParams.add(col1);
    }
    if (col2 != null && !col2.isEmpty()) {
        stringBuilder.append(" AND t1.col2 = ?");
        optionalParams.add(col2);
    }
    if (col3 != null && !col3.isEmpty()) {
        stringBuilder.append(" AND t1.col3 = ?");
        optionalParams.add(col3);
    }

    try (Connection connection = connectionProvider.getConnection();
        PreparedStatement pstmt = connection.prepareStatement(stringBuilder.toString()) {
        for(int i = 1; i < optionalParams.size(); i  ) {
            pstmt.setString(i, optionalParams.get(i));
        }
    }
}

Some tips:

1. You can also put PreparedStatement in try-with-resource statement, and then remove finally block.
2. If your setQuery is not in a loop, you can just simply use operator instead of StringBuilder to shorten the code.