java.sql.SQLException: Wrong number of parameters: expected 0, was given 3 Query dbutil的模糊查询插入问题

原创已于 2022-04-18 21:06:43 修改 · 1.7k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql

于 2022-04-15 13:58:56 首次发布

mysql 专栏收录该内容

2 篇文章

订阅专栏

这篇博客讨论了在Java中使用QueryRunner进行SQL查询时，如何通过预编译占位符提高效率，避免了字符串拼接导致的问题。文章通过示例展示了错误的使用方式，即直接在SQL语句中包含百分号，以及正确的解决方案，即使用问号占位符并手动添加百分号。作者还展示了如何进行参数绑定和执行查询，确保了SQL注入的安全性。

  public List<Book> findBook(String search) throws SQLException {

        Connection conn = DbUtil.getDataSource().getConnection();

        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();
/*
        String sql = "SELECT\n" +
                "    book.*\n" +
                "FROM\n" +
                "    book\n" +
                "WHERE\n" +
                "        book.book_name LIKE \'%?%\' OR\n" +
                "        book.book_author LIKE \'%?%\'  OR\n" +
                "        book.book_commit LIKE \'%?%\' \n" +
                "LIMIT 0, 100\n";
                //把\'%?%\' 识别成了一个字符串而不是占位符
*/
//-------------------------------解决 -------------------------------
        String sql = "SELECT\n" +
                "    book.*\n" +
                "FROM\n" +
                "    book\n" +
                "WHERE\n" +
                "        book.book_name LIKE ? OR\n" +
                "        book.book_author LIKE  ? OR\n" +
                "        book.book_commit LIKE ? \n" +
                "LIMIT 0, 100\n";
       
        search='%'+search+'%';//把要插入的变量进行修改
//--------------------------------------------------------------
         LOGGER.debug("    search:"+search);
        List<Book> query = queryRunner.query(conn, sql, new BeanListHandler<>(Book.class),search,search,search);

        conn.close();
        return query;
    }