你的位置:首页 > Java教程

[Java教程]JDBC之PreparedStatement模糊查询


今天要做一个关于模糊查询的需求,以前用JDBC做精确查询都是用 "SELECT * FROM test WHERE id = ?",所以用模糊查询时理所当然的也用了"SELECT * FROM test WHERE name = '%?%'",但是查询时一直提示java.sql.SQLException: Invalid parameter index 1.

Google一下原来ps不支持上面的写法,应该先用占位符表示 "SELECT * FROM test WHERE name = ?",然后在传参数的时候拼接ps.setString(1, "%"+参数+"%");

为了弄明白为什么PS不支持上面错误的表达方式,特意查看了一下源码(基于Jtds驱动,如下为简略代码),原来PS再解析SQL语句时,是先判断是否有 [ " ' 符号,如果有则把后面的字段都当做字符串处理,而后才判断占位符。

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; }
}