Question

jsp mysql 模糊查询中文字

前面省略.....

String sql = "select * from BOOK where userId= ? and name LIKE BINARY '%?%'"; //这里
try {

conn = DBConnection.getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, userId);//这里
ps.setString(2, name);//这里
rs = ps.executeQuery();

while (rs.next()) {
Book book = new Book();
book.setId(rs.getInt("id"));
book.setName(rs.getString("name"));
book.setSex(rs.getString("sex"));
book.setPhone(rs.getString("phone"));
book.setAddress(rs.getString("address"));
book.setMobilePhone(rs.getString("mobilePhone"));
book.setCompany(rs.getString("company"));
book.setComPhone(rs.getString("comPhone"));
book.setComAddress(rs.getString("comAddress"));
後面省略...
查不出数据,提示:
java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3326)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3310)
at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4146)
at y2javaee.xmal1.operation.BookBo.SearchBook(BookBo.java:168)
at y2javaee.xmal1.servlet.SearchServlet.doPost(SearchServlet.java:54)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:261)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:581)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Unknown Source)
sql语句应该是正确的,在cmd里把问号变成中文就能查询出,但是代入程序就不行,哪位大神能明白哪里出问题 在线等

Answer 1

估计是 '%?%'
这个地方的问题 他应该去匹配带问号的

这样 就没有第二个参数让你去赋值
改成这样
sql = "select * from BOOK where userId= ? and name LIKE BINARY '%"+name+"%'";
然后后面 只需赋值一个 这样肯定没问题

追问

还要把这句删掉:ps.setString(2, name);
厉害啊,我弄了两天都没好,大神一说就可以了,谢谢