showdataaction.java

医院信息系统(Hospital Information System

/*
 * Copyright 2007 The Apache Software Foundation
 *
 * 该类用来显示数据查询的结果
 *
 */

package hospital.Controller.Actions.GeneralFunction;

import org.apache.struts.action.*;
import org.hibernate.Transaction;

import javax.servlet.http.*;
import java.sql.*;
import java.util.*;

import hospital.Controller.ActionForms.*;
import hospital.Foundation.DataFixing;
import hospital.Model.*;

import java.lang.reflect.Method; 
import java.lang.reflect.Field;

public class ShowDataAction extends Action {

    private String baseTableName = ""; //基本数据表
    private String superTableName = ""; //影射数据表
    private String keyNames = ""; //关键字名称
    private String menuID = ""; //菜单编号
    private String searchSQL = ""; //查询数据库的条件（SQL形式）
    private String searchText = ""; //查询数据库的条件（文本形式，便于用户阅读）
    private String orderColumn = ""; //单项排序的列名
    private String orderKind = ""; //表示单项排序是正序还是逆序
    private String multiSort = ""; //多项排序的条件
    private String modified = "0"; //双击后是否可以修改
    private String pageShowType = ""; //界面表现规则

    public ActionForward execute(ActionMapping mapping, ActionForm form,
                                 HttpServletRequest request,
                                 HttpServletResponse response) {
        String driverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
        String dbURL = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=hospital";
        String userName = "sa";
        String userPwd = "";
        Connection dbConn;

        //存储数据表每一列的标题
        ArrayList listColumnName = new ArrayList(20);
        //存储数据表每一行的内容
        ArrayList listTableContent = new ArrayList(20);

        try {
            Class.forName(driverName);
            dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
            Statement stmt = dbConn.createStatement();

            //初始化页面的所有参数
            initPageParameters(form, request, stmt);

            //用所得参数查询数据库，得出查询结果
            createTableContent(stmt, listColumnName, listTableContent);

            stmt.close();
            dbConn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

        //将参数和查询结果发往结果页面
        returnPageParameters(request, listColumnName, listTableContent);

        //转到查询结果页面
        return mapping.findForward("datatable");
    }

    //将所有成员变量归为初始值
    private void setPropertyToOrginate() {
        baseTableName = ""; //基本数据表
        superTableName = ""; //影射数据表
        keyNames = ""; //关键字名称
        menuID = ""; //菜单编号
        searchSQL = ""; //查询数据库的条件（SQL形式）
        searchText = ""; //查询数据库的条件（文本形式，便于用户阅读）
        orderColumn = ""; //单项排序的列名
        orderKind = ""; //表示单项排序是正序还是逆序
        multiSort = ""; //多项排序的条件
        modified = "0"; //双击后是否可以修改
        pageShowType = ""; //界面表现规则
    }

    private void initPageParameters(ActionForm form, HttpServletRequest request, Statement stmt) throws Exception {

        //将所有成员变量归为初始值
        setPropertyToOrginate();

        //从其他表单得到查询数据库所需的参数
        ParametersActionForm parametersActionForm = (ParametersActionForm) form;

        //如果从其他表单不能得到查询数据库所需的参数，则从地址栏得到最基本的两项参数
        if (parametersActionForm.getSuperTableName() == null || parametersActionForm.getMenuID() == null) {
            //superTableName = request.getParameter("tablename");
            //如果superTableName是从地址栏得到的，需要对其解码
            //superTableName = URLDecoder.decode(superTableName.replace('!', '%'), "UTF-8");
            menuID = request.getParameter("menuid");

            //当从地址栏得到了基本参数后，需要通过查询数据库得到更多的参数
            //取得baseTableName和keyNames两个参数
            getInfoFromMenuID(stmt);
        } else {
            //否则从表单中获得所有所需参数
            baseTableName = DataFixing.trimNULL(parametersActionForm.getBaseTableName());
            superTableName = DataFixing.trimNULL(parametersActionForm.getSuperTableName());
            menuID = DataFixing.trimNULL(parametersActionForm.getMenuID());
            keyNames = DataFixing.trimNULL(parametersActionForm.getKeyNames());
            searchSQL = DataFixing.trimNULL(parametersActionForm.getSearchSQL());
            searchText = DataFixing.trimNULL(parametersActionForm.getSearchText());
            orderColumn = DataFixing.trimNULL(parametersActionForm.getOrderColumn());
            orderKind = DataFixing.trimNULL(parametersActionForm.getOrderKind());
            multiSort = DataFixing.trimNULL(parametersActionForm.getMultiSort());
            modified = DataFixing.trimNULL(parametersActionForm.getModified());
            pageShowType=DataFixing.trimNULL(parametersActionForm.getPageShowType());
        }
    }

    //将参数和查询结果发往结果页面
    private void returnPageParameters(HttpServletRequest request,
                                      ArrayList listColumnName,
                                      ArrayList listTableContent) {
        request.setAttribute("listColumnName", listColumnName);
        request.setAttribute("listTableContent", listTableContent);
        request.setAttribute("baseTableName", baseTableName);
        request.setAttribute("keyNames", keyNames);
        request.setAttribute("superTableName", superTableName);
        request.setAttribute("menuID", menuID);
        request.setAttribute("searchSQL", searchSQL);
        request.setAttribute("searchText", searchText);
        request.setAttribute("orderColumn", orderColumn);
        request.setAttribute("orderKind", orderKind);
        request.setAttribute("multiSort", multiSort);
        request.setAttribute("modified", modified);
        request.setAttribute("pageShowType", pageShowType);
    }

    //从菜单的编号得到对应的表名和关键字名等
    private void getInfoFromMenuID(Statement stmt) throws SQLException {
        String sql = "select * from sys菜单基本表 where 菜单id='" + menuID + "'";
        ResultSet rs = stmt.executeQuery(sql);
        if (rs.next()) {
            baseTableName = rs.getString("表名");
            pageShowType = rs.getString("界面表现规则");
            superTableName = rs.getString("映射表名");
            keyNames = rs.getString("关键字名");
        }
    }

    //按条件查询数据库，得出要显示的整个结果
    private void createTableContent(Statement stmt, ArrayList listColumnName,
                                    ArrayList listTableContent) throws Exception {
        String sql = "select * from " + superTableName;
      
        /*Sys用户表DAO Sys用户表Obj=new Sys用户表DAO();
        Transaction tx=Sys用户表Obj.getSession().beginTransaction();
        ArrayList userList=(ArrayList)Sys用户表Obj.findData("");
        tx.commit();
        Sys用户表Obj.getSession().close();
        
        for(int i=0;i<userList.size();i++){
        	Object[] contentRowObj=(Object[])userList.get(i);
        	String[] contentRow = new String[contentRowObj.length];
        	for(int j=0;j<contentRow.length;j++){
        		contentRow[j]=contentRowObj[j].toString();
        	}
        	listTableContent.add(contentRow);
        }*/
        
        //拼查询条件
        if (searchSQL != null && !searchSQL.equals("")) {
            sql += " where " + searchSQL;
        }
        //如果有单项排序条件，则拼单项排序条件；否则拼多项排序条件
        if (orderColumn != null && orderKind != null &&
            !orderColumn.equals("") && !orderKind.equals("")) {
            sql += " order by " + orderColumn + " " + orderKind;
        } else if (multiSort != null && !multiSort.equals("")) {
            sql += " order by " + multiSort;
        }
        System.out.println(sql);
        ResultSet rs = stmt.executeQuery(sql);
        ResultSetMetaData rsmd = rs.getMetaData();
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
            listColumnName.add(rsmd.getColumnName(i));
        } while (rs.next()) {
            String[] contentRow = new String[rsmd.getColumnCount()];
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                contentRow[i - 1] = DataFixing.trimNULL(rs.getString(i));
                //如果日期项不为必填项，且用户未录入，则数据库会给该值默认赋为1900-01-01 00:00:00.0
                //而且即使用户输入了年月日，数据库也会默认补充时分秒 00:00:00.0
                //所以要去掉这些默认值
                if (contentRow[i - 1].startsWith("1900-01-01")) {
                    contentRow[i - 1] = "";
                } else if (contentRow[i - 1].endsWith(" 00:00:00.0")) {
                    contentRow[i - 1] = contentRow[i - 1].substring(0, 10);
                }
                contentRow[i - 1] = contentRow[i - 1].replace(" ", "&nbsp;"); //替换半角空格
                contentRow[i - 1] = contentRow[i - 1].replace("　", "&nbsp;"); //替换全角空格
                contentRow[i - 1] = contentRow[i - 1].replace("\n", "<br />"); //替换行符
            }
            //将一行的查询结果添加到链表中，作为即将显示出来的数据
            listTableContent.add(contentRow);
        }
        rs.close();
    }
}