PreparedStatement fuzzy query for JDBC, preparedstatement
Today, we need to make a fuzzy query request. In the past, for exact queries using JDBC, we used "SELECT * FROM test WHERE id =? ", So when fuzzy queries are used, the" SELECT * FROM test WHERE name = '%? % '", But always prompt java. SQL. SQLException: Invalid parameter index 1.
Google ps does not support the above statement. It should first use a placeholder to represent "SELECT * FROM test WHERE name =? ", And then splice ps. setString (1," % "+ parameter +" % ") when passing parameters ");
To understand why PS does not support the above incorrect expression, I checked the source code (based on the Jtds driver, the following is the simple code). When PS parses the SQL statement again, it is to first determine whether there is a ["'symbol. If yes, the following fields are treated as strings before the placeholder is determined.
String[] parse(boolean extractTable) throws SQLException {
switch (c) { case '{': escape(); isModified = true; break; case '[': case '"': case '\'': copyString(); break; case '?': copyParam(null, d); break; case '/': if (s+1 < len && in[s+1] == '*') { skipMultiComments(); } else { out[d++] = c; s++; } break; case '-': if (s+1 < len && in[s+1] == '-') { skipSingleComments(); } else { out[d++] = c; s++; } break; default: if (isSlowScan && Character.isLetter(c)) { if (keyWord == null) { keyWord = copyKeyWord(); if ("select".equals(keyWord)) { isSelect = true; } isSlowScan = extractTable && isSelect; break; } if (extractTable && isSelect) { String sqlWord = copyKeyWord(); if ("from".equals(sqlWord)) { // Ensure only first 'from' is processed isSlowScan = false; tableName = getTableName(); } break; } } out[d++] = c; s++; break; }
}