数据统计

alalee 2003-04-04 10:36:56

我需要做一个统计查询系统：
研究生大学大专中专高中初中小学
单位1 8 7 6 5
单位2 0 6
单位3
.
.
.
所有数据都是从一人事档案库中统计得出，由于学历标题和单位也是从数据库中得来的，这样需多次连接数据库，且有嵌套循环，搞得网页较慢，有时还死机（连数据库死），请那位高手出出主意，帮助解决，50分。
JAVABEAN程序：
package mydb;
import java.sql.*;
import java.lang.*;

public class mydb {
String sDBDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
String sUSERNAME = "hr";
String sPASSWORD = "hr";
String sConnStr = "jdbc:odbc:hr";
private Connection conn = null;
private Statement stmt = null;
ResultSet rs = null;
String sql;

StringBuffer dest = new StringBuffer();
int beginIndex = 0;
int endIndex = 0;
public mydb() {
try {
Class.forName(sDBDriver);
}
catch(java.lang.ClassNotFoundException e) {
System.err.println("mydb(): " + e.getMessage());
}
}

public ResultSet executeQuery(String sql) {
rs = null;
try {
conn = DriverManager.getConnection(sConnStr,sUSERNAME,sPASSWORD);
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
}
catch(SQLException ex) {
System.err.println("aq.executeQuery: " + ex.getMessage());
}
return rs;
}

public void executeUpdate(String sql) {
stmt = null;
rs=null;
try {
conn = DriverManager.getConnection(sConnStr,sUSERNAME,sPASSWORD);
stmt = conn.createStatement();
stmt.executeQuery(sql);
stmt.close();
conn.close();
}
catch(SQLException ex) {
System.err.println("aq.executeQuery: " + ex.getMessage());
}
}

public void closeStmt(){
try{
stmt.close();
}
catch(SQLException e){
e.printStackTrace();
}
}
public void closeConn(){
try{
conn.close();
}
catch(SQLException e){
e.printStackTrace();
}
}
}

JSP程序：
<%@ page contentType="text/html;charset=gb2312" %>
<%@ page language="java" import="java.lang.*,java.sql.*,java.sql.Date"%>
<jsp:useBean id="sqlbean" scope="page" class="mydb.mydb"/>

<html>
<head>
<link rel=stylesheet href="../public/FORUM.css" type=text/css>
<title>学历查询</title>
</head>
<body leftmargin="9" topmargin="8" background="../imges/bg1.gif">
<%
String datarange = request.getParameter("datarange"),dm="",mc="",deptlevel1="",pl="";
String deptlevel = request.getParameter("deptlevel");
String xuelidm[]= new String[10],xuelimc[]= new String[10];
String danweidm[]= new String[50],danweimc[]= new String[50];
int userlevel = Integer.parseInt(deptlevel.substring(1));
int ll,l,ii=0,jj=0;
userlevel++;
//sqlbean.init();
ResultSet rs = sqlbean.executeQuery("Select MC,dm FROM TS_DMMX WHERE LBDM='WHCD'");
while(rs.next())
{
mc=rs.getString(1);
dm=rs.getString(2);
xuelidm[ii]=dm;
xuelimc[ii]=mc;
ii++;
}
rs.close();
String sql="select distinct l"+userlevel+",dname"+userlevel+" from info_UNIT_CODE where "+deptlevel+"='"+datarange+"' order by l"+userlevel+" asc";
rs = sqlbean.executeQuery(sql);
while(rs.next())
{
mc=rs.getString(2);
dm=rs.getString(1);
danweidm[jj]=dm;
danweimc[jj]=mc;
jj++;
}
rs.close();
deptlevel1="l"+userlevel;
%>
<div align="center"><b><font size="4" color="#000066" face="宋体">学历情况统计表</font></b>
</div>
<br>
<table border="1" cellspacing="0" cellpadding="0" bordercolorlight="#3399CC" bordercolordark="#FFFFFF" align="center" bgcolor="#FFFFCC">
<tr bgcolor="#FFCC66">
<Td>
<div align="center"><font color="#CC1166">单位</font></div>
</Td>
<%
for(ll=0;ll<ii;ll++)
{%>
<Td><font color="#CC116"><%=xuelimc[ll]%></font></Td>
<% }%>
<TD><font color="#CC1166">合计</font></TD>
</tr>
<%

for(ll=0;ll<jj;ll++)
{
pl=danweidm[ll];
if (pl!=null){
if (userlevel<4){
%>
<tr><td><a href="zhcx_xueli.jsp?datarange=<%=pl%>&deptlevel=<%=deptlevel1%>"><%=danweimc[ll]%></a></td>
<%
}
else {
%>
<tr><td><%=danweimc[ll]%></td>
<%
}
for(l=0;l<ii;l++)
{
rs = sqlbean.executeQuery("Select count(*) FROM info_basic WHERE l"+userlevel+"='"+pl+"' and edulevel='"+xuelidm[l]+"'");
if (rs.next())
{
%>
<td><%=rs.getString(1)%></td>
<%
}
rs.close();
}

ResultSet rs3 = sqlbean.executeQuery("Select count(*) FROM info_basic WHERE l"+userlevel+"='"+pl+"'");
if (rs3.next())
{
%>
<td><%=rs3.getString(1)%></td>
</tr>
<%
}
rs3.close();
}
}
%>
<tr><TD>合计</TD>
<%
for(ll=0;ll<ii;ll++)
{
ResultSet rs4 = sqlbean.executeQuery("Select count(*) FROM info_basic WHERE "+deptlevel+"='"+datarange+"' and edulevel='"+xuelidm[ll]+"'");

if (rs4.next())
{
%>
<td><%=rs4.getString(1)%></td>
<%
}
rs4.close();
}
ResultSet rs5 = sqlbean.executeQuery("Select count(*) FROM info_basic WHERE "+deptlevel+"='"+datarange+"'");
if (rs5.next())
{
%>
<td><%=rs5.getString(1)%></td>
<%
}
rs5.close();
%>
</tr>
</table>
<%
sqlbean.closeStmt();
sqlbean.closeConn();
%>
</body>
</html>

...全文