Question

java mysql模糊查询有问题 请纠错

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class section17082{
public static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
public static final String DBURL = "jdbc:mysql://localhost:3306/leo";
public static final String DBUSER = "root";
public static final String DBPASS = "admin";

public static void main(String[] args) throws Exception{
String key = "凡";
String sql = "SELECT id,name,age,birthday FROM mytab" +
"WHERE name LIKE ?";
Class.forName(DBDRIVER);
Connection con = DriverManager.getConnection(DBURL,DBUSER,DBPASS);
PreparedStatement pre = con.prepareStatement(sql);
ResultSet res = pre.executeQuery();
pre.setString(1, "%" + key + "%");

while(res.next()){
int id = res.getInt(1);
String name = res.getString(2);
int age = res.getInt(3);
java.util.Date bir = res.getDate(4);
System.out.print("编号：" + id + "，");
System.out.print("姓名：" + name + "，");
System.out.print("年龄：" + age + "，");
System.out.println("生日：" + bir);
}
res.close();
pre.close();
con.close();
}
}

Exception in thread "main" java.sql.SQLException: No value specified for parameter 1
编译没问题，执行时出现了这个错误，求指出问题所在

Answer 1

您好，应该是你的sql有问题：
String sql = "SELECT id,name,age,birthday FROM mytab" +
"WHERE name LIKE ?";
这个拼出来的sql应该是：SELECT id,name,age,birthday FROM mytabWHERE name LIKE ? ；
在mytab和WHERE 之间没有空格。
修改为：
String sql = "SELECT id,name,age,birthday FROM mytab" +
" WHERE name LIKE ?";
希望对你有帮助

Answer 2

ResultSet res = pre.executeQuery();
pre.setString(1, "%" + key + "%");
异常报的是没有给参数设值,位置调换下,先给参数设值在执行sql
pre.setString(1, "%" + key + "%");
ResultSet res = pre.executeQuery();

追问

试了，改完出现 'LIKE '%·?%'' at line1，应该是语法问题

追答

mytab后面加个空格,你这样写mytab好像跟WHERE黏在一起了

Answer 3

pre.setString(1, " '% " + key + " %' ");你的百分号左右貌似少个单引号

追问

非也，改完还是老样子

Answer 4

我觉得也是，mytab 和 WHERE 之间少了个空格、
你可以把sql语句打印出来 System.out.println(sql)、
在WHERE前面或mytab后面加个空格就好了、

Answer 5

String sql = "SELECT id,name,age,birthday FROM mytab" +
"WHERE name LIKE % ? %";
pre.setString(1, key );

追问

改完出现这个问题
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 'LIKE %'·?'%' at line1

追答

String sql = "SELECT id,name,age,birthday FROM mytab" +
"WHERE name LIKE %‘“+ ? +”’%";
sql查询，字符串，需要加一个单引号，
pre.setString(1, key );