Question

执行SQL：SELECT count(*) FROM tb_person 时发生异常:Before start of result set 。 怎么办？

<%@ page language="java" pageEncoding="UTF-8" contentType="text/html;charset=UTF-8"%>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.ResultSet" %>
<jsp:directive.page import="java.sql.Date"/>
<jsp:directive.page import="java.sql.Timestamp"/>
<jsp:directive.page import="java.sql.SQLException"/>
<jsp:directive.page import="com.helloweenvsfei.util.DbManager"/>
<jsp:directive.page import="java.sql.PreparedStatement"/>
<jsp:directive.page import="com.helloweenvsfei.util.Pagination"/>

<%
final int pageSize = 10; //一页显示10条记录
int pageNum = 1; //当前的页数
int pageCount = 1; //总页数
int recordCount = 0; //总记录数

try {
pageNum = Integer.parseInt(request.getParameter("pageNum"));
} catch(Exception e) {}

String sql = null;

Connection conn = null;
PreparedStatement preStmt = null;
ResultSet rs = null;

try {
sql = "SELECT count(*) FROM tb_person ";
recordCount = DbManager.getCount(sql);
pageCount = (recordCount + pageSize - 1) / pageSize;

int startRecord = (pageNum - 1) * pageSize;

sql = "SELECT * FROM tb_person LIMIT ?, ? ";

conn = DbManager.getConnection();
preStmt = conn.prepareStatement(sql);
DbManager.setParams(preStmt,startRecord,pageSize);

rs = preStmt.executeQuery();

%>

package com.helloweenvsfei.util;
import java.sql.*;
public class DbManager {
public static Connection getConnection() throws SQLException {
return getConnection("databaseWeb","root","admin");
}

public static Connection getConnection(String dbName,String userName,String password) //getConnection（）函数重载
throws SQLException {
String url = "jdbc:mysql://localhost:3306/" + dbName + "?useUnicode=true&characterEncoding=utf-8";

DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //注册驱动

return DriverManager.getConnection(url, userName, password); //建立数据库连接
}

public static int getCount(String sql) throws SQLException {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
return rs.getInt(1);
} finally {
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(conn != null) conn.close();
}
}
}
异常信息：
java.sql.SQLException: Before start of result set
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:841)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2674)
at com.helloweenvsfei.util.DbManager.getCount(DbManager.java:68)
at org.apache.jsp.listPagedPerson_jsp._jspService(listPagedPerson_jsp.java:90)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at java.lang.Thread.run(Thread.java:619)

Answer 1

try {
conn = getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
return rs.getInt(1);
} finally {
里的return rs.getInt(1);没用
一般都是先在外面定义一个变量 int a；
然后if(rs.next()){a = rs.getInt(1);}
然后在finally里 renturn a;

Answer 2

最好能把异常信息也贴出来