最近,小弟用到mysql模糊查询时碰到了一个难以解决的问题,在网上找了很多了资料,也没能解决,希望在这里碰到高手,帮小弟度过难关,小弟我感激涕零~~~
好的,看下面这段代码吧:mysql> use mingxuan;
Database changed
mysql> select count(id) from product where ptype like '%联想%' and bid=(
-> select id from brand where bname='联想' and cid=(
-> select id from category where id=4
-> )
-> );
+-----------+
| count(id) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)在mysql里面可以用这个sql语句进行模糊查询,但是到了Java程序里面用con.prepareStatement(sql);问题就来了
代码如下:String sql = "select count(id) from product "
+ "where ptype like ? and bid=( "
+ "select id from brand where bname=? and cid=( "
+ "select id from category where id=?));";
con = DBUtil.getConnect();
try {
ps = con.prepareStatement(sql);
ps.setString(1, "%" + type + "%");
ps.setString(2, bname);
ps.setInt(3, cid);
rs = ps.executeQuery();
if (rs.next()) {
count = rs.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(rs);
DBUtil.close(ps);
DBUtil.close(con);
}这时MyEclipse就报错java.sql.SQLException: 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 '%联想% and bid=( select id from brand where bname='联想' and cid=( select id fro' at line 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:930)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1024)据我在网上找的资料所知,问题出在%?%这里,但是我不会解决,哎,能力有限啊,希望高人解决,多谢~~~~
好的,看下面这段代码吧:mysql> use mingxuan;
Database changed
mysql> select count(id) from product where ptype like '%联想%' and bid=(
-> select id from brand where bname='联想' and cid=(
-> select id from category where id=4
-> )
-> );
+-----------+
| count(id) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)在mysql里面可以用这个sql语句进行模糊查询,但是到了Java程序里面用con.prepareStatement(sql);问题就来了
代码如下:String sql = "select count(id) from product "
+ "where ptype like ? and bid=( "
+ "select id from brand where bname=? and cid=( "
+ "select id from category where id=?));";
con = DBUtil.getConnect();
try {
ps = con.prepareStatement(sql);
ps.setString(1, "%" + type + "%");
ps.setString(2, bname);
ps.setInt(3, cid);
rs = ps.executeQuery();
if (rs.next()) {
count = rs.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(rs);
DBUtil.close(ps);
DBUtil.close(con);
}这时MyEclipse就报错java.sql.SQLException: 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 '%联想% and bid=( select id from brand where bname='联想' and cid=( select id fro' at line 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:930)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1024)据我在网上找的资料所知,问题出在%?%这里,但是我不会解决,哎,能力有限啊,希望高人解决,多谢~~~~
这样试试,如果还不行
不如String sql = "select count(id) from product "
+ "where ptype like ? and bid=( "
+ "select id from brand where bname=? and cid=( "
+ "select id from category where id=?));";
直接写成
String sql = "select count(id) from product "
+ "where ptype like '%" + type + "%' and bid=( "
+ "select id from brand where bname='"+bname+"' and cid=( "
+ "select id from category where id='"+cid+"'));";
我上面的代码没有问题,应该是IDE工具的问题吧,我用的是MyEclipse7.0的,做WEB开发时,几分钟的时间javaw就吃了800多M内存,然后就要重启,不然IDE就很缓慢,我很无语
问题解决了,但是我还在郁闷中
另JF