java - Spring mvc - jsp MySQLSyntaxErrorException

Question

我正在尝试从 MYSQL DB 捕获参数并通过 spring mvc 将其打印在 JSP 页面上。

我的 Controller 中有以下功能:

@RequestMapping(value = "/showentry")
public ModelAndView showentry(@RequestParam("id") String id){
    System.out.println("xxxxxxxxxxxxxxxxxxxxxxxx" + id);
    entries = dao.search(id);
    ModelAndView mav = new ModelAndView();
    mav.addObject("list", entries);
    return mav;
}

从以下形式获取参数“Id”:

  <form action="showentry">
    <input type=  "hidden" name="id" value = "${item.id}">
    <button> Show Entry </button>
  </form>

调用showentry方法的函数位于DAO类中:

@Override
public List<Entry> search(String id) {
    List<Entry> res = new ArrayList<Entry>();
    String sql = "SELECT * FROM Person WHERE Id = ? ;";
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet resultSet = null;
    try {
        //open connection
        conn = dataSource.getConnection();

        //prepare the statement
        ps = conn.prepareStatement(sql);

        //bind parameters to preparedstatement
        ps.setString(1, id);

        //execute the statement
        resultSet = ps.executeQuery(sql);

        while (resultSet.next()) {
            Entry entry = new Entry();
            entry.setId(resultSet.getInt("id"));
            entry.setCn(resultSet.getString("cn"));
            entry.setSn(resultSet.getString("sn"));
            entry.setPn(resultSet.getString("pn"));
            res.add(entry);
        }
    } catch (SQLException ex) {
        //[...]
    }
    return res;
}

我可以在日志上打印带有与条目相关的 id 的字符串，例如: xxxxxxxxxxxxxxxxxxxxxxxx 32

但是它不会打印在jsp上，并且返回错误:

net.tirasa.springaddressbook.SpringEntryDAO search
GRAVE: null
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

sql字符串有问题吗？

Answer 1

您必须删除 resultSet = ps.executeQuery(sql); 中的参数

一定是

resultSet = ps.executeQuery();

请参阅Javadoc来自方法executeQuery(String sql):

Note:This method cannot be called on a PreparedStatement or CallableStatement

同时删除语句末尾的 ;:

String sql = "SELECT * FROM Person WHERE Id = ?";