sybasechangedao.java

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

package com.gs.component.change.dao;

import java.sql.*;
import java.util.*;
import javax.naming.*;
import javax.sql.*;

import com.gs.component.change.model.*;
import com.gs.component.change.util.ChangeUtil;
import com.gs.component.change.util.Debug;
import com.gs.util.Common;
import com.gs.util.*;

/**
 *类名：SybaseChangeDAO
 *功能描述：数据库操作类
 *创建人：冯瑞龙
 *创建时间：2003-8-20
 *修改人：
 *修改时间：
 */

public class SybaseChangeDAO implements ChangeDAO{

    //生成变化记录SQL语句
    private static String INSERT_CHANGERECORD_STATEMENT =
        " insert into out_change (field001,field002,field003,field004,field005,field006,field007,field008,field009,field010,field011) values (?,?,?,?,?,?,?,?,?,?,?) ";

    //删除变化记录SQL语句
    private static String DELETE_CHANGERECORD_STATEMENT = " delete from out_change where field001=? and field011=?";

    //删除所有变化记录SQL语句
    private static String DELETE_ALL_CHANGERECORD_STATEMENT = " delete from out_change where field001=? ";

    //得到变化记录SQL语句
    private static String[] GET_CHANGERECORD_STATEMENT = {
        "  select field001,field002,field003,field004,field005,field006,field007,field008,field009,field010 from out_change where field001=? ",
        " and field011=?  ","  and field005=? "};

    //拷贝变化记录
    private static String COPY_CHANGERECORD_STATEMENT = " insert into in_change  select * from out_change where field001=? ";

    //记录操作员
    private static String UPDATE_USERID_STATEMENT = " update in_change set field010=? where field001=?";

    //数据复制的时间
    private static String UPDATE_COPYTIME_STATEMENT = " update in_change set field006=? where field001=?";

    /**
     *方法名称：insertChangeRecord
     *功能描述：生成变化记录
     *参数：（具体参见变化组件变量说明）
     *返回值：无
     *创建人：冯瑞龙
     *创建时间：2003-10-11
     */

    public void insertChangeRecord(String SN,String BID,String INNERID,String changeDateTime,String changeItem,String oldValue,String newValue,
                                   String tableSign,String recordID,String operateUser,String SUB){

        Connection conn = null;
        PreparedStatement ps = null;

        try{
            conn = Common.getConnection();

            ps = conn.prepareStatement(INSERT_CHANGERECORD_STATEMENT,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

            ps.setString(1,SN);
            ps.setString(2,BID);
            ps.setString(3,INNERID);
            ps.setString(4,tableSign);
            ps.setString(5,recordID);
            ps.setString(6,changeDateTime);
            ps.setString(7,changeItem);
            ps.setString(8,oldValue);
            ps.setString(9,newValue);
            ps.setString(10,operateUser);
            ps.setString(11,SUB);

            Debug.println("[SybaseChangeDAO-->>sql]:" + INSERT_CHANGERECORD_STATEMENT);

            ps.executeUpdate();
            Debug.println("[SybaseChangeDAO-->>sql]:" + INSERT_CHANGERECORD_STATEMENT);
        } catch(Exception e){
            Debug.println("[SybaseChangeDAO-->>insertChangeRecord]:" + e.toString());

        } finally{
            try{

                ps.close();
                conn.close();
            } catch(Exception e){
            }
        }

    }

    /**
     * @author add by xiedejin 20040519
     * @param NBXH
     * @return stringQymc
     * @throws AppException
     * 通过参数内部序号在gsgsjin_basis02查询开业时的企业名称
     */
    public String getQYMC(String SN) throws AppException{

        Connection conn = Common.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "";

        String changeQymc = "";
        String nowQymc = "";
        try{
            sql = "select field009 from " + TableNameUtil.GSGSJIN_BASIS02 +
                " where field002 like '02%' and field003 = (select field003 from gsgsjout_basis01 where field001 = '" + SN + "')";
            Debug.println("[SybaseChangeDAO-->>getQYMC --> sql]:" + sql);
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                nowQymc = rs.getString("field009"); //开业企业的名称
            }

            if(!(nowQymc == null || nowQymc.equals(""))){
                sql = "select field009 from " + TableNameUtil.GSGSJIN_BASIS02 + " where field009 != '" + nowQymc +
                    "' and field159 = 1 and field002 = '0102' and field003 = (select field003 from gsgsjout_basis01 where field001 = '" + SN + "')";
                ps = conn.prepareStatement(sql);
                rs = ps.executeQuery();
                if(rs.next()){
                    changeQymc = rs.getString("field009"); //开业企业的名称
                }

            }
        } catch(Exception e){
            Debug.println("[SybaseChangeDAO-->>getQYMC]:" + e.toString());
        } finally{
            try{
                rs.close();
                ps.close();
                conn.close();
            } catch(Exception e){
            }
        }

        return changeQymc;

    }

    /**
     *方法名称：deleteChangeRecord
     *功能描述：删除变化记录
     *参数：（具体参见变化组件变量说明）
     *返回值：无
     *创建人：冯瑞龙
     *创建时间：2003-10-11
     */

    public void deleteChangeRecord(String SN,String SUB){

        Connection conn = null;

        PreparedStatement ps = null;

        try{
            conn = Common.getConnection();
            ps = conn.prepareStatement(DELETE_CHANGERECORD_STATEMENT,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            ps.setString(1,SN);
            ps.setString(2,SUB);
            ps.executeUpdate();

        } catch(Exception e){
            Debug.println("[SybaseChangeDAO-->>deleteChangeRecord]:" + e.toString());
        } finally{
            try{

                ps.close();
                conn.close();
            } catch(Exception e){
            }
        }

    }

    /**
     *方法名称：deleteAllChangeRecord
     *功能描述：删除所有变化记录
     *参数：（具体参见变化组件变量说明）
     *返回值：无
     *创建人：冯瑞龙
     *创建时间：2003-10-11
     */

    public void deleteAllChangeRecord(String SN){

        Connection conn = null;

        PreparedStatement ps = null;

        try{
            conn = Common.getConnection();
            ps = conn.prepareStatement(DELETE_ALL_CHANGERECORD_STATEMENT,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            ps.setString(1,SN);
            ps.executeUpdate();

            Debug.print("conn已关闭：" + conn);
        } catch(Exception e){
            Debug.println("[SybaseChangeDAO-->>deleteChangeRecord]:" + e.toString());
        } finally{
            try{

                ps.close();
                conn.close();
            } catch(Exception e){
            }
        }

    }

    /**
     *方法名称：getChangeRecord
     *功能描述：得到变化记录
     *参数：（具体参见变化组件变量说明）
     *返回值：ArrayList类型，变化记录的数据集合
     *创建人：冯瑞龙
     *创建时间：2003-10-11
     */

    public ArrayList getChangeRecord(String SN,String BID,String INNERID,String SUB){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuffer sb = new StringBuffer();
        ArrayList al = new ArrayList();
        try{

            conn = Common.getConnection();

            sb.append(GET_CHANGERECORD_STATEMENT[0]);

            if(SUB != null && !SUB.trim().equals("")){
                sb.append(GET_CHANGERECORD_STATEMENT[1]);

            }
            Debug.println("[SybaseChangeDAO---------->>>>tableSign]" + SUB);

            ps = conn.prepareStatement(sb.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

            ps.setString(1,SN);

            if(SUB != null && !SUB.trim().equals("")){
                ps.setString(2,SUB);

            }
            rs = ps.executeQuery();

            Debug.println("select sql:" + sb.toString());

            while(rs.next()){

                ChangeRecordModel changeRecord = new ChangeRecordModel();

                changeRecord.setSN(rs.getString("field001"));
                changeRecord.setBID(rs.getString("field002"));
                changeRecord.setINNERID(rs.getString("field003"));

                changeRecord.setChangeDateTime(rs.getString("field006"));
                changeRecord.setChangeItem(rs.getString("field007"));
                changeRecord.setOldValue(rs.getString("field008"));
                changeRecord.setNewValue(rs.getString("field009"));

                changeRecord.setTableSign(rs.getString("field004"));
                changeRecord.setRecordID(rs.getString("field005"));
                changeRecord.setOperateUser(rs.getString("field010"));

                Debug.println("fieldname:" + changeRecord.getNewValue());

                al.add(changeRecord);
            }
        } catch(Exception e){
            Debug.println("[SybaseChangeDAO-->>getChangeRecord]:" + e.toString());
        } finally{
            try{
                if(rs != null){
                    rs.close();
                }
            } catch(Exception e){
            }

            try{
                if(ps != null){
                    ps.close();
                }
            } catch(Exception e){
            }
            try{
                if(conn != null){
                    conn.close();
                    Debug.print("conn已关闭：" + conn);
                }
            } catch(Exception e){
            }
            return al;
        }
    }

    /**
     * 方法名称:updateSubchange
     * 功能描述：这个方法比较特殊，只为了处理030205-sub01_46
     * @param SN
     * @param SUB
     * @param al
     */
    public void updateSubchange(String SN,String SUB,ArrayList al){
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "";

        try{

            conn = Common.getConnection();

            //ArrayList
            sql = "update " + TableNameUtil.GSGSJOUT_SUB01 + " set ";

            for(int i = 0;i < al.size();i++){

                String[] paraName = (String[]) al.get(i);
                sql = sql + paraName[0] + "=" + "'"+ paraName[2] +"'"+",";

            }
            sql = sql.substring(0,sql.length()-1);

            sql = sql + " where field001 = '"+ SN +"' and field002 = '"+SUB+"'";

            Debug.println("[updateSubchange]-->sql " + sql);

            ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

            ps.executeUpdate();

        } catch(Exception e){

            Debug.println("[SybaseChangeDAO-->>copyChangeRecord]");

        } finally{
            try{

                ps.close();
                conn.close();
            } catch(Exception e){
            }
        }

    }

    /**
     *方法名称：copyChangeRecord
     *功能描述：拷贝变化记录
     *参数：（具体参见变化组件变量说明）
     *创建人：冯瑞龙
     *创建时间：2003-10-16
     */
    public void copyChangeRecord(String SN,String operateUser){

        Connection conn = null;

        PreparedStatement ps = null;

        try{

            conn = Common.getConnection();

            ps = conn.prepareStatement(COPY_CHANGERECORD_STATEMENT,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

            ps.setString(1,SN);

            ps.executeUpdate();

            //记录操作员
            ps = conn.prepareStatement(UPDATE_USERID_STATEMENT,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            ps.setString(1,operateUser);
            ps.setString(2,SN);
            ps.executeUpdate();

            //add (hgw 2004-03-12 19:33)
            //数据导入（核准）时间
            ps = conn.prepareStatement(UPDATE_COPYTIME_STATEMENT,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            ps.setString(1,ChangeUtil.getCurrentTime());
            ps.setString(2,SN);
            ps.executeUpdate();
            //End add (hgw 2004-03-12 19:33)
        } catch(Exception e){

            Debug.println("[SybaseChangeDAO-->>copyChangeRecord]");

        } finally{
            try{

                ps.close();
                conn.close();
            } catch(Exception e){
            }
        }

    }

}