java - mysql中模糊查询的应用问题？

Question

首先来一下代码

StringBuffer sql = new StringBuffer();
        sql.append("select");
        sql.append("        f.id,");
        sql.append("        f.foodName,");
        sql.append("        f.price,");
        sql.append("        f.mprice,");
        sql.append("        f.intro,");
        sql.append("        f.img,");
        sql.append("        t.id,");
        sql.append("        t.typeName ");
        sql.append("from ");
        sql.append("        OS_foodInfo f, ");
        sql.append("        OS_foodType t ");
        sql.append("where 1=1");
        sql.append("        and f.foodType_id=t.id ");
        List<Object> list = new ArrayList<Object>();
        if (foodType_id > 0) {
            sql.append("and f.foodType_id = ? ");
            list.add(foodType_id);
        }
        if (foodName != null && !"".equals(foodName.trim())) {
            sql.append("and f.foodName like '%");
            sql.append("?");
            sql.append("%' ");
            list.add(foodName);
        }
        sql.append("limit ?,?");

然后来运行后的错误

 java.sql.SQLException: Wrong number of parameters: expected 3, was given 4 Query: select        f.id,        f.foodName,        f.price,        f.mprice,        f.intro,        f.img,        t.id,        t.typeName from         OS_foodInfo f,         OS_foodType t where 1=1        and f.foodType_id=t.id and f.foodType_id = ? and f.foodName like '%?%' limit ?,? Parameters: [1, 虾, 0, 6]

是模糊拼接有问题吗？

Answer 1

三个问号你给了四个参数

Answer 2

你输出一下拼接的 sql 字符串不就能发现了么

Answer 3

很明显，是你SQL错误啊。f.foodType_id = f.id AND f.foodType_id = 1 ？

SELECT
    f.id,
    f.foodName,
    f.price,
    f.mprice,
    f.intro,
    f.img,
    t.id,
    t.typeName
FROM
    OS_foodInfo f,
    OS_foodType t
WHERE
    1 = 1
AND f.foodType_id = t.id
AND f.foodType_id = 1
AND f.foodName LIKE '%虾%'
LIMIT 0,6

Answer 4

为什么不用mybatis框架,还用这种蛋疼的方式拼sql