saveaction.java

医院信息系统(Hospital Information System

package hospital.Controller.Actions.GeneralFunction;

import org.apache.struts.action.*;

import hospital.Foundation.DataFixing;

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

public class SaveAction extends Action {
    private static final String CONTENT_TYPE = "text/html; charset=GB2312";
    private String baseTableName = "";
    private String menuID = "";
    private String keyNames = "";
    private String keyValues = "";

    private String oldSaveStatus = "";
    private String newSaveStatus = "0";

    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;

        Hashtable hashRowContent = new Hashtable(30);

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

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

            //处理保存时的多种情况
            mutipleSavingConditions(stmt, request, hashRowContent);

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

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

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

        return mapping.findForward("showFormAction");
    }

    //处理保存时的多种情况
    private void mutipleSavingConditions(Statement stmt, HttpServletRequest request, Hashtable hashRowContent) throws SQLException {
        //如果keyValues为空值，说明保存时该值应由数据库自动生成，
        //如果keyValues不为空值，则应该进行查重等操作
        if (!keyValues.equals("")) {
            //oldSaveStatus=0表示初始状态，未进行任何保存验证，oldSaveStatus=2表示数据重复
            String selectSQL = "select * from " + baseTableName + " where " + keyNames + "='" + keyValues + "'";
            if (oldSaveStatus.equals("0") || oldSaveStatus.equals("2")) {
                //判断数据是否有重复
                System.out.println(selectSQL);
                ResultSet rs = stmt.executeQuery(selectSQL);
                if (rs.next()) {
                    //如果有重复，从数据库中查出完整数据，显示在页面上，并标示数据有重复
                    createRowContent(rs, hashRowContent);
                    newSaveStatus = "2";
                } else {
                    //如果没有重复，则将新数据插入数据库，并标示保存成功
                    String insertSQL = createInsertSQL(request);

                    //newSaveStatus=1表示保存成功，为-1表示保存失败
                    newSaveStatus = insert_UpdateItem(stmt, insertSQL, hashRowContent, "1");
                }
            }
            //oldSaveStatus=1原来是保存成功，oldSaveStatus=3原来是修改成功，oldSaveStatus=4表示此时为修改补录，这三种情况统一成修改成功
            else if (oldSaveStatus.equals("1") || oldSaveStatus.equals("3") || oldSaveStatus.equals("4")) {

                //不用再查重，拼出更新的SQL语句
                String updateSQL = createUpdateSQL(request);

                //newSaveStatus=3表示更新成功，为-3表示更新失败
                newSaveStatus = insert_UpdateItem(stmt, updateSQL, hashRowContent, "3");
            }
        } else {
            //如果没有重复，则将新数据插入数据库，并标示保存成功
            String insertSQL = createInsertSQL(request);

            //newSaveStatus=1表示保存成功，为-1表示保存失败
            newSaveStatus = insert_UpdateItem(stmt, insertSQL, hashRowContent, "1");
        }
    }

    private void setPropertyToOrginate() {
        baseTableName = "";
        menuID = "";
        keyNames = "";
        keyValues = "";
        oldSaveStatus = "";
        newSaveStatus = "0";
    }

    //初始化页面的所有参数
    private void initPageParameters(HttpServletRequest request) throws Exception {
        setPropertyToOrginate();

        baseTableName = request.getParameter("baseTableName");
        menuID = request.getParameter("menuID");
        keyNames = request.getParameter("keyNames");
        keyValues = request.getParameter("keyValues");

        oldSaveStatus = request.getParameter("saveStatus");
        newSaveStatus = "0";
    }

    private String createInsertSQL(HttpServletRequest request) {
        String columnNames = "";
        String columnValues = "";
        Enumeration enumParameterNames = request.getParameterNames();
        columnNames = "(";
        columnValues = "(";
        while (enumParameterNames.hasMoreElements()) {
            String parameterName = (String) enumParameterNames.nextElement();
            System.out.println(parameterName + ":" + request.getParameter(parameterName));
            if (!(
                parameterName.equals("baseTableName") ||
                parameterName.equals("menuID") ||
                parameterName.equals("keyNames") ||
                parameterName.equals("keyValues") ||
                parameterName.equals("saveStatus") ||
                parameterName.equals("pageShowType") ||
                parameterName.equals("superTableName") ||
                parameterName.equals("modified")
                )) {
                //如果关键字的值为空，说明关键字的值是自动生成的，
                //则拼接insert语句的时候，不能将关键字拼入
                if (!(keyValues.equals("") && parameterName.equals(keyNames))) {
                    columnValues += "'" + request.getParameter(parameterName) + "',";
                    columnNames += parameterName + ",";
                }
            }
        }
        columnNames = columnNames.substring(0, columnNames.length() - 1);
        columnValues = columnValues.substring(0, columnValues.length() - 1);
        columnNames += ")";
        columnValues += ")";

        return "insert into " + baseTableName + columnNames + " values " + columnValues;
    }

    private String createUpdateSQL(HttpServletRequest request) {
        String columnNames = "";
        String columnValues = "";
        String updateItem = "";
        String conditionSQL = " where " + keyNames + "='" + keyValues + "'";
        Enumeration enumParameterNames = request.getParameterNames();
        columnNames = "(";
        columnValues = "(";
        while (enumParameterNames.hasMoreElements()) {
            String parameterName = (String) enumParameterNames.nextElement();
            System.out.println(parameterName + ":" + request.getParameter(parameterName));
            if (!(
                parameterName.equals("baseTableName") ||
                parameterName.equals("menuID") ||
                parameterName.equals("keyNames") ||
                parameterName.equals("keyValues") ||
                parameterName.equals("saveStatus") ||
                parameterName.equals("pageShowType") ||
                parameterName.equals("superTableName") ||
                parameterName.equals("modified")
                )) {

                //拼接update语句的时候，不能将关键字拼入
                if (!parameterName.equals(keyNames)) {
                    columnNames = parameterName;
                    columnValues = request.getParameter(parameterName);
                    updateItem += columnNames + "= '" + columnValues + "' , ";
                }
            }
        }
        //去掉末尾的空格和逗号
        updateItem = updateItem.substring(0, updateItem.length() - 2);
        return "update " + baseTableName + " set " + updateItem + conditionSQL;
    }


    private String insert_UpdateItem(Statement stmt, String insertSQL, Hashtable hashRowContent,
                                     String successFlag) throws SQLException {
        String newSaveStatus = "";
        try {
            System.out.println(insertSQL);
            stmt.executeUpdate(insertSQL);
            newSaveStatus = successFlag;

            //保存之后还要进行“保存后处理”
            //插入数据库后，再从数据库中将其读出
            String sql = "select 执行SQL语句 from sys保存后处理表 where 保存表名='" + baseTableName + "'";
            ResultSet rs = stmt.executeQuery(sql);
            if(rs.next()){
                stmt.executeUpdate(rs.getString("执行SQL语句"));
            }
        } catch (SQLException ex) {
            //加负号表示操作失败
            newSaveStatus = "-" + successFlag;
            ex.printStackTrace();
        }

        if (keyValues.equals("")) {
            String maxSQL = "select max(" + keyNames + ")  from " + baseTableName;
            ResultSet rs = stmt.executeQuery(maxSQL);
            if (rs.next()) {
                keyValues = rs.getString(1);
            }
        }
        //插入数据库后，再从数据库中将其读出
        String selectSQL = "select * from " + baseTableName + " where " + keyNames + "='" + keyValues + "'";
        ResultSet rs = stmt.executeQuery(selectSQL);
        if (rs.next()) {
            createRowContent(rs, hashRowContent);
        }
        return newSaveStatus;
    }

    //讲查询到的一条数据转化成哈希表的形式
    private void createRowContent(ResultSet rs, Hashtable hashRowContent) throws SQLException {
        ResultSetMetaData rsmd = rs.getMetaData();
        String columnName = "";
        String columnValue = "";
        for (int i = 0; i < rsmd.getColumnCount(); i++) {
            columnName = rsmd.getColumnName(i + 1);
            columnValue = DataFixing.trimNULL(rs.getString(columnName));
            if (columnValue.endsWith(" 00:00:00.0")) {
                columnValue = columnValue.substring(0, columnValue.indexOf(" 00:00:00.0"));
            }
            if (columnValue.startsWith("1900-01-01")) {
                columnValue = "";
            }
            hashRowContent.put(columnName, columnValue);
        }
    }

    //将参数和查询结果发往结果页面
    private void returnPageParameters(HttpServletRequest request, Hashtable hashRowContent) {
        request.setAttribute("menuID", menuID);
        request.setAttribute("baseTableName", baseTableName);
        request.setAttribute("hashRowContent", hashRowContent);
        request.setAttribute("newSaveStatus", newSaveStatus);
    }
}