jsp access

kite_KW 发布于 2014/02/03 10:14
阅读 577
收藏 1

【领华为电脑包】容器化时代到来!跳转机分配问题终于“有救”了!>>>

各位,新年好!

  我有一个jsp文件连接access数据库,jsp文件如下:

 

<%@page contentType="text/html;charset=gb2312" language="java" import="java.sql.*"%> 

<%
try
   { 
       String spath = "/data/andon.mdb"; 
       String dbpath = application.getRealPath(spath);        
       String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=" + dbpath;
       Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
       Connection conn = DriverManager.getConnection(url);
       Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 
    
       String sql = "SELECT * FROM tblBreakdownInfo ORDER BY reactionTime DESC"; 
       
       ResultSet rs = stmt.executeQuery(sql);
       while (rs.next())
          {
             out.print(rs.getRow()+"  ");
             out.print(rs.getString("faultTypeID") + " ID:");
             out.print(rs.getString("reactionTime") + "<br>");
          } 
        out.print("<br>恭喜你!数据库连接成功!");
        rs.close(); 
        stmt.close();
        conn.close();
     }
 catch (Exception e)
    { 
       out.print("数据可连接错误!,错误信息如下:<br>");
       out.print(e.getMessage());
    }
%>

以上文件运行正常,查询结果也正常;

 

但是,如果将下面这句改为另外一句:

String sql = "SELECT * FROM tblBreakdownInfo ORDER BY reactionTime DESC"; 

改成如下:

String sql="SELECT faultTypeID,  sum(tblBreakdownInfo.reactionTime) as reactionTime "+
                   "FROM tblBreakdownInfo "+
                   "GROUP BY  faultTypeID ";

此查询语句在access中调试正常,并且能输出正常结果

 

 

 

但通过网页文件,会出现以下结果:

 

本来只有4条记录,但常游标会移到5,并且会报如下错误:

[Microsoft][ODBC 驱动程序管理器] 无效的游标状态,

请见附件文件,为什么呀,,,各位??

 

 

如果将jsp文件修改如下(红色部分修改)

 

<%@page contentType="text/html;charset=gb2312" language="java" import="java.sql.*"%> 

<%
try
   { 
       String spath = "/data/andon.mdb"; 
       String dbpath = application.getRealPath(spath);        
       String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=" + dbpath;
       Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
       Connection conn = DriverManager.getConnection(url);
       Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 
    
       //String sql = "SELECT * FROM tblBreakdownInfo ORDER BY reactionTime DESC";
        String sql="SELECT faultTypeID,  sum(tblBreakdownInfo.reactionTime) as reactionTime "+
                   "FROM tblBreakdownInfo "+
                   "GROUP BY  faultTypeID ";
       //          "ORDER BY sum(tblBreakdownInfo.reactionTime) DESC";
     
       ResultSet rs = stmt.executeQuery(sql);
       rs.last();
       int iTotalRow=rs.getRow();
       out.print("iTotalRow="+iTotalRow);
       while (rs.next())
          {
             out.print(rs.getRow()+"  ");
          //   out.print(rs.getString("faultTypeID") + " ID:");
           //  out.print(rs.getString("reactionTime") + "<br>");
          } 
        out.print("<br>恭喜你!数据库连接成功!");
        rs.close(); 
        stmt.close();
        conn.close();
     }
 catch (Exception e)
    { 
       out.print("数据可连接错误!,错误信息如下:<br>");
       out.print(e.getMessage());
    }
%>

 运行文件,结果如下:

 

现在的问题是:本来分组后resultSet应只有4条记录,但游标移动了7次(此时数据库中表中的记录也是7行,如果修改表中记录的行数,游标移动次数也会跟着变,与行数一致),分组好象没用。。。。

为什么呀。。。。

我试了,只要不用聚合函数,就没有问题,,,,???

加载中
0
Timco
Timco
确实奇怪,没看出来问题-.-
0
k
kite_KW

我修改了一下jsp文件如下红色区域,

<%@page contentType="text/html;charset=gb2312" language="java" import="java.sql.*"%> 

<%
try
   { 
       String spath = "/data/andon.mdb"; 
       String dbpath = application.getRealPath(spath);        
       String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=" + dbpath;
       Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
       Connection conn = DriverManager.getConnection(url);
       Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 
    
       //String sql = "SELECT * FROM tblBreakdownInfo ORDER BY reactionTime DESC";
        String sql="SELECT faultTypeID,  sum(tblBreakdownInfo.reactionTime) as reactionTime "+
                   "FROM tblBreakdownInfo "+
                   "GROUP BY  faultTypeID ";
       //          "ORDER BY sum(tblBreakdownInfo.reactionTime) DESC";
     
       ResultSet rs = stmt.executeQuery(sql);
       rs.last();
       int iTotalRow=rs.getRow();
       out.print("iTotalRow="+iTotalRow);
       while (rs.next())
          {
             out.print(rs.getRow()+"  ");
          //   out.print(rs.getString("faultTypeID") + " ID:");
           //  out.print(rs.getString("reactionTime") + "<br>");
          } 
        out.print("<br>恭喜你!数据库连接成功!");
        rs.close(); 
        stmt.close();
        conn.close();
     }
 catch (Exception e)
    { 
       out.print("数据可连接错误!,错误信息如下:<br>");
       out.print(e.getMessage());
    }
%>

运行结果如下:

从结果可以看出,本来应只有4条记录,但resultSet确有7条,不知什么原因??

0
k
kite_KW
也就是说,这种查询方式,resultSet的行数与表中的行数是一致,分组对resultSet的行数没影响,,,感觉是这样(我增加表中的行中,)iTotalRow会跟着变。。。

 

0
k
kite_KW
现在的问题有点明显了,只要在sql语句中不用聚合函数,就不会出错。。。。为什么??
返回顶部
顶部