Question

java模糊查询sql语句问题

ublic class Shbk{
public static final String DBDRIVER="org.gjt.mm.mysql.Driver";
public static final String dburl = "jdbc:mysql://localhost:3306/wang";
public static final String dbuser = "root";
public static final String dbpass = "wang";
public Shbk(String name,String book,String writer)throws Exception{
Connection coon=null; //连接数据库
PreparedStatement psmt=null; //数据库操作
ResultSet rs =null; //保存查询结果
JFrame frame =new JFrame("查询");
String[] titles={"id","姓名","书名","作者"};
Object[][] userinfo=new Object[10][4];
JButton but=new JButton("确定");
String sql="SELECT*"+" FROM library WHRERE name LIKE ? OR book LIKE ? OR writer LIKE ?";
Class.forName(DBDRIVER);
coon=DriverManager.getConnection(dburl,dbuser,dbpass);
psmt=coon.prepareStatement(sql);
psmt.setString(1,"%"+name+"%");
psmt.setString(2,"%"+book+"%");
psmt.setString(3,"%"+writer+"%");
rs=psmt.executeQuery();
for(int i=0;rs.next();i++){
userinfo[i][0]=rs.getInt(1);
userinfo[i][1]=rs.getString(2);
userinfo[i][2]=rs.getString(3);
userinfo[i][3]=rs.getString(4);
}
rs.close();
psmt.close();
coon.close();
frame.addWindowListener(new WindowAdapter(){
public void windowClosing(WindowEvent arg0){
System.exit(1);
}
});
JTable table =new JTable(userinfo,titles);
JScrollPane sp=new JScrollPane(table);
frame.add(sp);
frame.pack();
frame.setLocation(500,350);
frame.setVisible(true);
}
}

编译无问题 运行后出错
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 'name LIKE '%ssss%' OR book LIKE '%123%' OR wri
ter LIKE '%xxx%'' at line 1

Answer 1

1.psmt.setString(1,"%"+name+"%");
psmt.setString(2,"%"+book+"%");
psmt.setString(3,"%"+writer+"%");

这边拼接字符串少了单引号。 "'%"+name+"'%"
2.SELECT* 最好select和*之间加一个空格

Answer 2

like 后面不需要加‘’ 可能跟你用的方法有关 psmt.setString(1,"%"+name+"%"); setString可能是自动添加引号了

追问

我like后没加"啊

我like后没加"啊

追答

我不是说了么 可能是你用的setString自动加上去的

追问

那我该怎么改呢

追答

你试试setObject 或者干脆拼sql好了 不要用方法了

Answer 3

你那个 在拼接 like语句是 要在外面加 单引号 如 ....like ‘%..%’

也就是 psmt.setString(1,"‘%"+name+"%’");

注意是 在 两边的 % 要在 单引号里面 其他改的方法一样

Answer 4

select * 里面的*前后加个空格