querydaoimpl.java

一套完整的工商12315的源程序jsp部分在12315里,后台JAVA部分在gs12315src里,没有打包数据库.

package com.gs.pageBuilder.dao;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2003</p>
 * <p>Company: </p>
 * @author not attributable
 * @version 1.0
 */
import java.lang.*;
import java.util.*;
import java.sql.*;
import java.text.DateFormat;
import com.gs.util.*;
import com.gs.util.PubFunc;
import com.gs.pageBuilder.util.*;
import com.gs.pageBuilder.model.*;

public class QueryDAOImpl implements QueryDAO{

    private Connection conn;
    private Statement stmt,stmt2;
    private ResultSet resu,rs;
    private PreparedStatement pstmt;

    public QueryDAOImpl(){
        super();
    }

    /**
     * 保存个体注册号
     * 参数BID：业务类型
     * 参数innerID：内部编号
     * 参数DJJY：登记机关
     * 参数regid：注册号
     * 应用: 变更查询
     * 编写人员：李
     * 日期： 2004-06-06
     */
    public int saveUnitRegister(String SN,String BID,String DJJG,String regid,String oldregid) throws AppException{
        String sql = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rsExplain = null;
        int excresult = 0;

        try{
            ///该方法缺少撤销原注册号的操作！
            conn = Common.getConnection();
          //  conn.setAutoCommit(false);
            stmt = conn.createStatement();
            int regs = 0;
            String oldregID=oldregid.substring(7);
            //查重注册号
            sql = "SELECT count(s.num) reg  FROM GS_REGISTER_SN_" + DJJG +
                " s,GS_REGISTER r WHERE s.register_id=r.register_id and s.used=1 AND s.num=" +
                regid + " and s.register_id='register_gt30' AND r.djjg='" + DJJG + "'";
            Debug.println("[QueryDAOImpl]saveUnitRegister===========================--->>>sql:" + sql+" oldregid="+oldregid);

            Debug.println("[QueryDAOImpl]getFieldExplain--->>>sql:" + sql);
            rsExplain = stmt.executeQuery(sql);
            rsExplain.next();
            excresult = rsExplain.getInt("reg");

            Debug.println("[QueryDAOImpl]getFieldExplain--->>>sql:" + sql + " excresult=" + excresult);
            if(excresult == 0){ //个体注册号没有被占用
                //更新管理网新的注册号
                sql = "UPDATE gsgsjin_basis02  SET field004='" + DJJG + "3" + regid + "' WHERE field002='020401' and field001='" + SN + "'";
                Debug.println("[QueryDAOImpl]update reggister-//更新管理网新的注册号-->>>sql:" + sql);
               int result = stmt.executeUpdate(sql);
                if(result != 1){
                    throw new AppException("更新管理网新的注册号失败!");
                }
                //更新红盾网新的注册号
                sql = "UPDATE gsgsjout_basis01  SET field101='" + DJJG + "3" + regid + "' WHERE field002='020401' and field001='" + SN + "'";
                Debug.println("[QueryDAOImpl]update reggister-//更新红盾网新的注册号-->>>sql:" + sql);
               result = stmt.executeUpdate(sql);
                if(result != 1){
                    throw new AppException("更新管理网新的注册号失败!");
                }

                //更新被使用注册号的标识位used=1
                sql = "UPDATE GS_REGISTER_SN_" + DJJG + " SET used=1 WHERE used=0 and num=" + regid + " and register_id='register_gt30'";
                Debug.println("[QueryDAOImpl]getFieldExplain-- 更新被使用注册号的标识位used=1->>>sql:" + sql);
                 result = stmt.executeUpdate(sql);
                if(result != 1){
                    throw new AppException("产生注册号失败!");
                }
                //更新被释放注册号的标识位used=0
                sql = "UPDATE GS_REGISTER_SN_" + DJJG + " SET used=0 WHERE used=1 and num=" + oldregID + " and register_id='register_gt30'";
                Debug.println("[QueryDAOImpl]getFieldExplain--更新被释放注册号的标识位used=0 ->>>sql:" + sql);
                result = stmt.executeUpdate(sql);
                if(result != 1){
                    throw new AppException("产生注册号失败!");
                }


            }
          //  conn.commit();
            /*
              rsExplain = stmt.executeQuery(sql);
                 rsExplain = stmtExplain.executeQuery(sql);
             */
        } catch(SQLException sqle){
            try{
                conn.rollback();
            } catch(Exception e){
                Debug.println("[QueryDAOImpl]撤消事物出错！" + e.getMessage());
            }
            Debug.println("[QueryDAOImpl]getFieldExplain--->>>Exception:" + sqle.getMessage());
        } finally{
            Common.clearUp(rs,stmt,conn);
        }
        return excresult;
    }

    /**
     * 通过对照表获得内网某个字段的解释
     * 参数BID：业务类型
     * 参数in_tableName：内网表名
     * 参数in_fieldName：内网字段名
     * 参数out_subTable：外网子表名
     * 应用: 变更查询
     * 编写人员：陈丹宇
     * 日期： 2004-04-03
     */
    public String getFieldExplain(String BID,String in_tableName,String in_fieldName,String out_subTable) throws
        AppException{

        String sql = null;
        String fieldExplain = "";
        if(!in_fieldName.startsWith("field")){
            return in_fieldName;
        }
        Connection connExplain = null;
        Statement stmtExplain = null;
        ResultSet rsExplain = null;
        String out_tableName = "";
        String out_fieldName = "";
        try{
            connExplain = Common.getConnection();
            stmtExplain = connExplain.createStatement();
            //取得对应的开业的BID
            String kyBID = "02" + BID.substring(2);

            //如果是子表则为in_change中的field011，并转换成对应的内网表名.比如：in_change.field011的值为sub01_03，则要转换为gsgsjin_sub02_03
            if(in_tableName.indexOf("sub") > -1){
                in_tableName = out_subTable;
                in_tableName = "gsgsjin_" + PubFunc.replace(in_tableName,"sub01","sub02");
            }

            sql = "select out_table,out_fieldname from "
                + TableNameUtil.GSGSJINOUT_RELATION
                + "gsgsjinout_relation where oid='" + kyBID + "' and in_table = '" + in_tableName +
                "' and in_fieldname='" + in_fieldName + "'";

            Debug.println("[QueryDAOImpl]getFieldExplain--->>>sql:" + sql);
            rsExplain = stmtExplain.executeQuery(sql);

            //如果在对照表中存在记录，则取出对应的外网的表名、字段名
            if(rsExplain.next()){
                out_tableName = rsExplain.getString("out_table");
                out_fieldName = rsExplain.getString("out_fieldname");
            }
            //如果在对照表中不存在记录，则对应的外网的表名、字段名与内网的表名、字段名对应
            else{
                //将gsgsjin替换成gsgsjout，将basis02替换成basis01，将sub02替换成sub01
                out_tableName = PubFunc.replace(in_tableName,"gsgsjin","gsgsjout");
                out_tableName = PubFunc.replace(out_tableName,"basis02","basis01");
                out_tableName = PubFunc.replace(out_tableName,"sub02","sub01");
                out_fieldName = in_fieldName;
            }
            //将gsgsjout_去掉，因为在解释表中是简写，如：basis01
            out_tableName = out_tableName.substring(9);

            sql = "select field004 from gsgsjout_explain01 where field001='" + kyBID + "' and field002='" + out_tableName + "' and field003='" +
                out_fieldName + "'";
            Debug.println("[QueryDAOImpl]getFieldExplain--->>>sql:" + sql);

            rsExplain = stmtExplain.executeQuery(sql);
            //如果存在数据则取出对应的解释
            if(rsExplain.next()){
                fieldExplain = rsExplain.getString("field004");
            } else{
                fieldExplain = "";
                Debug.println("[QueryDAOImpl]getFieldExplain--->>>BID=" + kyBID + ",表名=" + out_tableName + ",字段=" + out_fieldName + " 没有定义解释表");
            }

        } catch(SQLException sqle){
            Debug.println("[QueryDAOImpl]getFieldExplain--->>>Exception:" +
                          sqle.getMessage());
        }

        finally{

            try{
                if(rsExplain != null){
                    rsExplain.close();
                }
            } catch(Exception e){
            }
            try{
                if(stmtExplain != null){
                    stmtExplain.close();
                }
            } catch(Exception e){
            }
            try{
                if(connExplain != null && !connExplain.isClosed()){
                    connExplain.close();
                }
                Debug.print("conn已关闭：" + connExplain);

            } catch(Exception e){
            }
        }
        Debug.println("[QueryDAOImpl]getFieldExplain--->>>fieldExplain:" + fieldExplain);
        return fieldExplain;
    }

    /**
     * 获得企业信息
     * 应用:
     */
    public QuerytBusinessInfoModel getBusinessInfo(String innerID,String BID,String yearCheckupY) throws
        AppException{
        boolean flag = false;
        String sql = null;
        QuerytBusinessInfoModel querytBusinessInfoModel = new QuerytBusinessInfoModel();
        try{

            conn = Common.getConnection();
            stmt = conn.createStatement();

            //是否正常数据
            if(innerID == null || BID == null){
                Debug.println(
                    "[QueryDAOImpl]getBusinessInfo----->orderID or BID is null!");
                return querytBusinessInfoModel;
            }

            //如果是年检
            if(BID.length() > 2 && "07".equals(BID.substring(0,2))){
                sql = "SELECT field001,field002,field003,field004,field009  FROM  "
                    + TableNameUtil.IN_YEARVALI
                    + " WHERE field003='"
                    + innerID
                    //+ "' and field002='"
                    //+ BID
                    + "' and field007='"
                    + yearCheckupY
                    + "'";
            } else{

                sql = "SELECT field001,field002,field003,field004,field009  FROM  "
                    + TableNameUtil.GSGSJIN_BASIS02
                    + " WHERE field002='"
                    + BID
                    + "' and field003='"
                    + innerID
                    + "'";

            }

            Debug.println("[QueryDAOImpl]getBusinessInfo--->>>sql:" + sql);
            rs = stmt.executeQuery(sql);

            if(rs.next()){
                querytBusinessInfoModel.setBID(BID);
                querytBusinessInfoModel.setInnerID(innerID);

                querytBusinessInfoModel.setSN(rs.getString("field001"));
                querytBusinessInfoModel.setBusinessRegID(rs.getString("field004"));
                querytBusinessInfoModel.setBusinessName(rs.getString("field009"));
            }

        } catch(SQLException sqle){