createsql.java

农业银行ATM对帐查询系统后台类包的源代码,实现了FTP,数据库管理等等功能

package boco.shabank.dbmanager;
//******************************************************************************
//:功能:据输入的数据参数,返回相应的SQL语句
//:时间:2002-07
//:作者:王建宾
//:特殊描述::test:为DEBUG时启用
//:程序描述:
// (1)返回增加当前行的SQL语句:区分数字型与非数字型数据
// (2)返回编辑当前行的SQL语句:区分数字型与非数字型数据
//******************************************************************************

import java.util.*;
import java.sql.*;

public class CreateSql {
  //定义变量
  String sql;
  String tmpSql;
  String columnType;
  String columnName;
  String columnValue;
  int i,j,len;
  ChineseTransfer C2A = new ChineseTransfer();
  //构造函数，初始化变量
  public CreateSql() {
    i=0;
    j=0;
    len=0;
    sql="";
    tmpSql="";
    columnType="";
    columnName="";
    columnValue="";
  }
  //返回增加SQL语句
  public String getInsertString(Object[] obj,String DBSource,String DBTName,Vector colType,Vector columns,Vector colValue){
    int j = obj.length ;
    String column = "";
    //sql="insert into dbsource ("+column+") values(";
    len=colType.size();
    //System.out.println("len="+len) ;
    for(i=0;i<len;i++){
        columnType=(String)colType.elementAt(i);
        columnValue=(String)colValue.elementAt(i);
        //System.out.println("type+value="+len+columnType+columnValue) ;;
        //最后一组数据
        if(i==len-1){
            if(columnType.equalsIgnoreCase("INTeger")||columnType.equalsIgnoreCase("INT")||columnType.equalsIgnoreCase("BIGINT")||columnType.equalsIgnoreCase("SMALLINT")||columnType.equalsIgnoreCase("DOUBLE")||columnType.equalsIgnoreCase("TINYINT")){
                tmpSql=tmpSql + " " + (C2A.ChineseStringToAscii(columnValue));
            }
            else{
                tmpSql=tmpSql + "'" + (C2A.ChineseStringToAscii(columnValue)) + "'";
            }
        }
        else{
            if(columnType.equalsIgnoreCase("INTeger")||columnType.equalsIgnoreCase("INT")||columnType.equalsIgnoreCase("BIGINT")||columnType.equalsIgnoreCase("SMALLINT")||columnType.equalsIgnoreCase("DOUBLE")||columnType.equalsIgnoreCase("TINYINT")){
                tmpSql=tmpSql + " " + (C2A.ChineseStringToAscii(columnValue)) + ",";
            }
            else{
                tmpSql=tmpSql + "'" + (C2A.ChineseStringToAscii(columnValue)) + "',";
            }
        }
    }
    for(int k =0;k<columns.size() ;k++){
        column = column+columns.elementAt(k).toString()+",";
    }
    //System.out.println("strcolumn=="+column) ;
    if(j==1){
        DBTableManager dbtm = new DBTableManager();
        int dbsid = dbtm.getMaxDBSid()+1;
        String strDBSid = Integer.toString(dbsid);
        tmpSql = strDBSid + tmpSql.substring(tmpSql.indexOf(","),tmpSql.length());
        sql="insert into dbsource ("+column.substring(0,column.length()-1)+") values ("+tmpSql+")";
    }
    else if(j==2 || j==3){
        String dbsname = obj[1].toString() ;
        DBSourceManager dbsm=new DBSourceManager(dbsname);
        String dbsid = dbsm.getDBSID() ;
        DBTableManager dbtm = new DBTableManager();
        int dbtid = dbtm.getMaxDBTid()+1;
        String strDBTid = Integer.toString(dbtid);
        tmpSql = strDBTid + tmpSql.substring(tmpSql.indexOf(","),tmpSql.length());
        sql="insert into dbtable ("+column+"dbsid) values ("+tmpSql+","+dbsid+")";
    }
    else if(j==4){
        String dbsname = obj[1].toString() ;
        String tablename=obj[3].toString() ;
        DBSourceManager dbsm=new DBSourceManager(dbsname);
        String dbsid = dbsm.getDBSID() ;
        DBTableManager dbtm = new DBTableManager(dbsid,tablename);
        String dbtid = Integer.toString(dbtm.getDBTID());
        int dbfid = dbtm.getMaxDBFid()+1;
        String strDBFid = Integer.toString(dbfid);
        tmpSql = strDBFid + tmpSql.substring(tmpSql.indexOf(","),tmpSql.length());
        sql="insert into DBField ("+column+"dbtid) values ("+tmpSql+","+dbtid+")";
    }
    //:test:System.out.println("Insert sql:"+sql);
    return sql;
  }

  //编辑当前行SQL
  public String getUpdateString(String DBSource,String DBTName,Vector colName,Vector colType,Vector colValue,String KeyValue,String Key,String KeyType){
    sql="update "+ DBTName + " set ";
    len=colType.size();
    //:test:System.out.println("DBTName:"+DBTName);
    //:test:System.out.println("sizeofName:" + colName.size());
    //:test:System.out.println("sizeofValue:" + colValue.size());
    //:test:System.out.println("sizeofType:" + colType.size());
    //:test:System.out.println("Key:" + Key);
    //:test:System.out.println("KeyValue:" + KeyValue);
    //:test:
    //System.out.println("KeyType:" + KeyType);
    for(i=0;i<len;i++){
      columnType=(String)colType.elementAt(i);
      columnName=(String)colName.elementAt(i);
      columnValue=(String)colValue.elementAt(i);
      //:test:System.out.println("columnType" + i + columnType);
      //:test:System.out.println("columnName" + i + columnName);
      //最后一组数据
      if(i==len-1){
          if(columnType.equalsIgnoreCase("INTeger")||columnType.equalsIgnoreCase("INT")||columnType.equalsIgnoreCase("BIGINT")||columnType.equalsIgnoreCase("SMALLINT")||columnType.equalsIgnoreCase("DOUBLE")||columnType.equalsIgnoreCase("TINYINT")){
          tmpSql=tmpSql + columnName + "=" + (DBSource.equalsIgnoreCase("sybaccess")?columnValue:C2A.ChineseStringToAscii(columnValue));
        }
      else{
          tmpSql=tmpSql + columnName + "='" + (DBSource.equalsIgnoreCase("sybaccess")?columnValue:C2A.ChineseStringToAscii(columnValue)) + "'";
        }
      }
      else{
          if(columnType.equalsIgnoreCase("INTeger")||columnType.equalsIgnoreCase("INT")||columnType.equalsIgnoreCase("BIGINT")||columnType.equalsIgnoreCase("SMALLINT")||columnType.equalsIgnoreCase("DOUBLE")||columnType.equalsIgnoreCase("TINYINT")){
          tmpSql=tmpSql + columnName + "=" + (DBSource.equalsIgnoreCase("sybaccess")?columnValue:C2A.ChineseStringToAscii(columnValue)) + ",";
        }
        else{
          tmpSql=tmpSql + columnName + "='" + (DBSource.equalsIgnoreCase("sybaccess")?columnValue:C2A.ChineseStringToAscii(columnValue)) + "',";
        }
      }
     }
     KeyType=KeyType.trim();
         if(KeyType.equalsIgnoreCase("INTeger")||KeyType.equalsIgnoreCase("INT")||KeyType.equalsIgnoreCase("BIGINT")||KeyType.equalsIgnoreCase("SMALLINT")||KeyType.equalsIgnoreCase("DOUBLE")||KeyType.equalsIgnoreCase("TINYINT")){
         sql = sql + tmpSql + " where " + Key.trim() + "=" + KeyValue;
      }
     else{
         sql = sql + tmpSql + " where " + Key.trim() + "='" + KeyValue + "'";
     }
     //:test:System.out.println("edit SQL:"+sql);
     return sql;
  }
    //测试主程序
    public static void main(String[] args){
      CreateSql ql = new CreateSql();
      Vector columnValue = new Vector();
      Vector columnType = new Vector();
      Vector columnName = new Vector();
      columnType.addElement("integer");
      columnType.addElement("char");
      columnType.addElement("date");
      columnType.addElement("integer");
      columnType.addElement("char");
      columnValue.addElement("11");
      columnValue.addElement("测试数据1");
      columnValue.addElement("20020203");
      columnValue.addElement("23");
      columnValue.addElement("9999");
      columnName.addElement("aaaa");
      columnName.addElement("bbbb");
      columnName.addElement("cccc");
      columnName.addElement("dddd");
      columnName.addElement("eeee");
      String sql=ql.getUpdateString("dbmng","dbtable",columnName,columnType,columnValue,"11","aaa","int");
      //System.out.println("mySql:"+sql);
    }
}