java 二维表_讲述对数据库中二维表格的操作![原创][struts框架]

package org.shine.action;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import javax.servlet.ServletContext;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.sql.DataSource;

import org.apache.struts.action.Action;

import org.apache.struts.action.ActionForm;

import org.apache.struts.action.ActionForward;

import org.apache.struts.action.ActionMapping;

public class QueryTeach extends Action {

public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {

ServletContext context =servlet.getServletContext();

DataSource source = (DataSource)context.getAttribute("SQLServer2k");

Connection cn = source.getConnection();//从数据库连接池中取得数据源

Statement st = cn.createStatement(); //实例一个statement对象用于执行查询语句

int i = 0;

int j = 0;

int iCourseCount = 0; //用于统计课程记录行数

int iTeacherCount = 0; //用于统计教师数量记录行数

String strSql = "select sum(1) from Course"; //查询出所有的课程的记录集行数

ResultSet rs = st.executeQuery(strSql);

while(rs.next()){

iCourseCount = rs.getInt(1); //统计课程的行数

}

String[] cName = new String[iCourseCount];

rs.close();//关闭记录集

strSql = "select cName from Course";

rs = st.executeQuery(strSql);

//使用ResultSet对象得到statement返回的记录集

while(rs.next()){

cName = rs.getString(1); //将取得到的课程名保存于cName这个字符数组中

i++;

}

i = 0; //复位计数器

strSql = "select sum(1) from teacher"; //记录集返回统计的老师的人数

rs = st.executeQuery(strSql); //执行查询返回结果集

while(rs.next()){

iTeacherCount = rs.getInt(1); //得到记录集的字段,即返回此记录集的行数

}

rs.close(); //关闭记录集

String tName[] = new String[iTeacherCount]; //定义一个字符串数组,长度为老师的人数,即用于保存老师的姓名

strSql = "select tName from teacher";

rs = st.executeQuery(strSql);

while(rs.next()){

tName = rs.getString(1);

i++;

}

rs.close(); //关闭记录集

int[][] nResult = new int[iTeacherCount][iCourseCount];//设置一个二维数组反映具体教师授课情况

for(i = 0;i < iTeacherCount;i++){

for(j = 0;j

strSql = "select cName from Course c,Teach tc,Teacher t where c.CID = tc.CID and t.TID = tc.TID and tName = '"+tName+"' and cName='"+cName[j]+"'";

rs = st.executeQuery(strSql);

if(rs.next()){ //查询如果有结果则表明教师和课程相交的那行的结果,是存在关系,即授课关系,则作上标记;否则不存在关系

nResult[j] = 1; //存在授课标记为1

}

else{

nResult[j] = 0; //存在授课标记为0

}

}

}

request.setAttribute("cName",cName); //将所有课程发送至前台

request.setAttribute("tName",tName); //将所有教师发送至前台

request.setAttribute("nResult",nResult);//将教师授课内容发送至前台

cn.close(); //关闭数据库连接

return mapping.findForward("ResultPage"); //跳转至XML里设置的前台JSP页面.详细见struts-config.xml里global-forwards的设置

}

}

/*这是struts-config.xml*/

authors.jsp

/*接受后台传来的数据,即课程名称,教师姓名,授课关系*/

String[] cName = (String[])request.getAttribute("cName");

String[] tName = (String[])request.getAttribute("tName");

int[][] nResult = (int[][])request.getAttribute("nResult");

/*定义累加器变量, 即i ,j*/

int i = 0;

int j = 0;

%>

for(i = 0;i <= tName.length;i++){

%>

for(j = 0;j <= cName.length;j++){

if(i == 0 && j == 0){

//左上角角标

%>

教师姓名\课程内容

}

if(i == 0 && j != 0){

//cName[j];读出课程名称

%>

}

if(j == 0 && i != 0){

//tName; //读出所有老师姓名

%>

}

if(i != 0 && j != 0){

if(nResult[i-1][j-1]==1){

//打印√

%>

√

}

else{

%>

}

}

}%>

}

%>