DBHelper代码:public PreparedStatement getPstm(String sql,Object[] params) {
try{
if(this.pstm != null){
this.pstm.close();
this.pstm = null;
}
}catch(SQLException sqle){
System.out.println("DBHelper.getPstm:PreparedStatement对象资源关闭出错。");
sqle.printStackTrace();
}
try{
this.pstm = this.getConn().prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
if(params != null){
for(int i = 0;i < params.length;i++){
this.pstm.setObject(i + 1,params[i]);
}
}
}catch(SQLException sqle){
System.out.println("DBHelper.getPstm:创建PreparedStatement对象出错。");
sqle.printStackTrace();
}
return this.pstm;
}
private Connection getConn() {
try{
if(this.conn == null || this.conn.isClosed()){
this.conn = DriverManager.getConnection(url, user, password);
}
}catch(SQLException sqle){
System.out.println("DBHelper.getConn:创建Connection对象出错。");
sqle.printStackTrace();
}
return this.conn;
}
private void execute(String sql,Object[] params){
if(sql != null && !sql.equals("")){
try{
this.pstm = this.getPstm(sql,params);
this.pstm.execute();
}catch(SQLException sqle){
System.out.println("DBHelper.execute:数据库操作出错。");
sqle.printStackTrace();
}
}
}
public ResultSet executeQuery(String sql,Object[] params){
ResultSet rs = null;
execute(sql,params);
try{
rs = this.pstm.getResultSet();
}catch(SQLException sqle){
rs = null;
System.out.println("DBHelper.executeQuery:数据库查询失败。");
sqle.printStackTrace();
}
return rs;
}
执行查询的代码public List<News> getNewsByCategory(int categoryID,int offset,int num){
String sql = "SELECT TOP ?newsID,categoryID,isRecommend,canComment FROM t_news WHERE categoryID=? AND newsID NOT IN(SELECT TOP ? newsID FROM t_news WHERE categoryID=? ORDER BY newsID DESC) ORDER BY newsID DESC";
Object[] params = {num,categoryID,offset,categoryID};
return getNewsList(sql,params);
}
private List<News> getNewsList(String sql,Object[] params){
List<News> newsList = new LinkedList<News>();
ResultSet rs = db.executeQuery(sql, params);
try {
while(rs.next()){
newsList.add(News.convertToNews(rs));
}
} catch (SQLException sqle) {
sqle.printStackTrace();
}finally{
db.close(rs);
}
return newsList;
} 数据库用的是access,CategoryID为自增字段。
用Access查询分析器运行该SQL代码都没有错,但是在Java中调用就错误。错误堆栈信息
DBHelper.execute:数据库操作出错。
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] SELECT 子句中包含一个保留字、拼写错误或丢失的参数,或标点符号不正确。
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
at com.tbin.news.dataService.DBHelper.execute(DBHelper.java:88)
at com.tbin.news.dataService.DBHelper.executeQuery(DBHelper.java:113)
at com.tbin.news.dataService.ModelService.getNewsList(ModelService.java:206)
at com.tbin.news.dataService.ModelService.getNewsByCategory(ModelService.java:197)
at com.tbin.news.models.Category.getNewsInCategory(Category.java:102)
at com.tbin.news.models.Category.main(Category.java:131)
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] SELECT 子句中包含一个保留字、拼写错误或丢失的参数,或标点符号不正确。
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLNumResultCols(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.getColumnCount(Unknown Source)DBHelper.executeQuery:数据库查询失败。 at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet(Unknown Source)
at com.tbin.news.dataService.DBHelper.executeQuery(DBHelper.java:115)
at com.tbin.news.dataService.ModelService.getNewsList(ModelService.java:206)
at com.tbin.news.dataService.ModelService.getNewsByCategory(ModelService.java:197)
at com.tbin.news.models.Category.getNewsInCategory(Category.java:102)
at com.tbin.news.models.Category.main(Category.java:131)
Exception in thread "main" java.lang.NullPointerException
at com.tbin.news.dataService.ModelService.getNewsList(ModelService.java:208)
at com.tbin.news.dataService.ModelService.getNewsByCategory(ModelService.java:197)
at com.tbin.news.models.Category.getNewsInCategory(Category.java:102)
at com.tbin.news.models.Category.main(Category.java:131)
try{
if(this.pstm != null){
this.pstm.close();
this.pstm = null;
}
}catch(SQLException sqle){
System.out.println("DBHelper.getPstm:PreparedStatement对象资源关闭出错。");
sqle.printStackTrace();
}
try{
this.pstm = this.getConn().prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
if(params != null){
for(int i = 0;i < params.length;i++){
this.pstm.setObject(i + 1,params[i]);
}
}
}catch(SQLException sqle){
System.out.println("DBHelper.getPstm:创建PreparedStatement对象出错。");
sqle.printStackTrace();
}
return this.pstm;
}
private Connection getConn() {
try{
if(this.conn == null || this.conn.isClosed()){
this.conn = DriverManager.getConnection(url, user, password);
}
}catch(SQLException sqle){
System.out.println("DBHelper.getConn:创建Connection对象出错。");
sqle.printStackTrace();
}
return this.conn;
}
private void execute(String sql,Object[] params){
if(sql != null && !sql.equals("")){
try{
this.pstm = this.getPstm(sql,params);
this.pstm.execute();
}catch(SQLException sqle){
System.out.println("DBHelper.execute:数据库操作出错。");
sqle.printStackTrace();
}
}
}
public ResultSet executeQuery(String sql,Object[] params){
ResultSet rs = null;
execute(sql,params);
try{
rs = this.pstm.getResultSet();
}catch(SQLException sqle){
rs = null;
System.out.println("DBHelper.executeQuery:数据库查询失败。");
sqle.printStackTrace();
}
return rs;
}
执行查询的代码public List<News> getNewsByCategory(int categoryID,int offset,int num){
String sql = "SELECT TOP ?newsID,categoryID,isRecommend,canComment FROM t_news WHERE categoryID=? AND newsID NOT IN(SELECT TOP ? newsID FROM t_news WHERE categoryID=? ORDER BY newsID DESC) ORDER BY newsID DESC";
Object[] params = {num,categoryID,offset,categoryID};
return getNewsList(sql,params);
}
private List<News> getNewsList(String sql,Object[] params){
List<News> newsList = new LinkedList<News>();
ResultSet rs = db.executeQuery(sql, params);
try {
while(rs.next()){
newsList.add(News.convertToNews(rs));
}
} catch (SQLException sqle) {
sqle.printStackTrace();
}finally{
db.close(rs);
}
return newsList;
} 数据库用的是access,CategoryID为自增字段。
用Access查询分析器运行该SQL代码都没有错,但是在Java中调用就错误。错误堆栈信息
DBHelper.execute:数据库操作出错。
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] SELECT 子句中包含一个保留字、拼写错误或丢失的参数,或标点符号不正确。
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
at com.tbin.news.dataService.DBHelper.execute(DBHelper.java:88)
at com.tbin.news.dataService.DBHelper.executeQuery(DBHelper.java:113)
at com.tbin.news.dataService.ModelService.getNewsList(ModelService.java:206)
at com.tbin.news.dataService.ModelService.getNewsByCategory(ModelService.java:197)
at com.tbin.news.models.Category.getNewsInCategory(Category.java:102)
at com.tbin.news.models.Category.main(Category.java:131)
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] SELECT 子句中包含一个保留字、拼写错误或丢失的参数,或标点符号不正确。
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLNumResultCols(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.getColumnCount(Unknown Source)DBHelper.executeQuery:数据库查询失败。 at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet(Unknown Source)
at com.tbin.news.dataService.DBHelper.executeQuery(DBHelper.java:115)
at com.tbin.news.dataService.ModelService.getNewsList(ModelService.java:206)
at com.tbin.news.dataService.ModelService.getNewsByCategory(ModelService.java:197)
at com.tbin.news.models.Category.getNewsInCategory(Category.java:102)
at com.tbin.news.models.Category.main(Category.java:131)
Exception in thread "main" java.lang.NullPointerException
at com.tbin.news.dataService.ModelService.getNewsList(ModelService.java:208)
at com.tbin.news.dataService.ModelService.getNewsByCategory(ModelService.java:197)
at com.tbin.news.models.Category.getNewsInCategory(Category.java:102)
at com.tbin.news.models.Category.main(Category.java:131)
2.问号是我在发帖编辑的时候加的,所以是全角的,在源码中是英文的问号。
3.如果我不用preparedstatement,直接用字符串拼接,则可以正常。
4.即使SELECT TOP 5newsID,categoryID,isRecommend,canComment FROM t_news WHERE categoryID=? AND newsID NOT IN(SELECT TOP 1 newsID FROM t_news WHERE categoryID=? ORDER BY newsID DESC) ORDER BY newsID DESC,进行查询,categoryID这个参数照样出错。该语句如果将子查询去掉,竟然就可以。
DBHelper.execute:数据库操作出错。
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] 语法错误。 在查询表达式 'categoryID=Pa_RaM000 AND newsID NOT IN(SELECT TOP 0 newsID FROM t_news WHERE categoryID=Pa_RaM001 ORDER BY newsRTime DESC)' 中。
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
at com.tbin.news.dataService.DBHelper.execute(DBHelper.java:88)
at com.tbin.news.dataService.DBHelper.executeQuery(DBHelper.java:113)
at com.tbin.news.dataService.ModelService.getNewsList(ModelService.java:207)
at com.tbin.news.dataService.ModelService.getNewsByCategory(ModelService.java:197)
at com.tbin.news.models.Category.getNewsInCategory(Category.java:102)
at com.tbin.news.models.Category.main(Category.java:131)
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] 语法错误。 在查询表达式 'categoryID=Pa_RaM000 AND newsID NOT IN(SELECT TOP 0 newsID FROM t_news WHERE categoryID=Pa_RaM001 ORDER BY newsRTime DESC)' 中。
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLNumResultCols(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.getColumnCount(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet(Unknown Source)
at com.tbin.news.dataService.DBHelper.executeQuery(DBHelper.java:115)
at com.tbin.news.dataService.ModelService.getNewsList(ModelService.java:207)
at com.tbin.news.dataService.ModelService.getNewsByCategory(ModelService.java:197)
at com.tbin.news.models.Category.getNewsInCategory(Category.java:102)
at com.tbin.news.models.Category.main(Category.java:131)DBHelper.executeQuery:数据库查询失败。Exception in thread "main" java.lang.NullPointerException
at com.tbin.news.dataService.ModelService.getNewsList(ModelService.java:209)
at com.tbin.news.dataService.ModelService.getNewsByCategory(ModelService.java:197)
at com.tbin.news.models.Category.getNewsInCategory(Category.java:102)
at com.tbin.news.models.Category.main(Category.java:131)
,预编译参数只有一个categoryID,此时就可以正确获取数据,不会再报错。多了一个子查询就莫名其妙的报错,是何原因呢?
1.想办法将生成的SQL串输出
2.在查询分析器里构造相同的语句验证
3.检查not in后的子查询,确认要有结果集。