java.sql.SQLException: '@P0' 附近有语法错误

转载于 2018-01-22 09:30:54 发布 · 739 阅读

日常小问题专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一种SQL语句中使用预编译参数时遇到的问题及其两种解决方案。一种是通过动态字符串拼接来实现，另一种是在预编译参数位置添加括号。这两种方法都能有效解决SQL不支持为select top预编译的问题。

SQL语句:

String sql = "select * FROM mybooks a," +
      "(select top ? id from" +
      "(select top ? * from mybooks where  price >= ? AND price <= ? order by id desc) b " +
      "order by id desc) c " +
      "where a.id = c.id order by c.id desc"

原因：sql不支持为select top ? 预编译

解决办法：1.换成动态拼接

String sql = "select * FROM mybooks a," +
      "(select top %s id from" +
      "(select top %s * from mybooks where  price >= ? AND price <= ? order by id desc) b " +
 "order by id desc) c " +
 "where a.id = c.id order by c.id desc";
sql = String.format(sql, 3, 3);

2.在？号加括号

String sql = "select * FROM mybooks a," +
      "(select top (?) id from" +
      "(select top (?) * from mybooks where  price >= ? AND price <= ? order by id desc) b " +
      "order by id desc) c " +
      "where a.id = c.id order by c.id desc";

原作者处：http://www.blogjava.net/jzone/articles/305388.html