getsqlstring.java

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

package boco.DBManager.Tree;

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

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

public class getSQLstring {
  //定义变量
  String sql;
  String tmpSql;
  String columnType;
  String columnName;
  String columnValue;
  int i,j,len;
  ChineseTransfer C2A = new ChineseTransfer();
  //构造函数，初始化变量
  public getSQLstring() {
    i=0;
    j=0;
    len=0;
    sql="";
    tmpSql="";
    columnType="";
    columnName="";
    columnValue="";
  }
  //返回增加SQL语句
  public String getInsertString(String DBSource,String DBTName,Vector colType,Vector colValue){
    sql="insert into " + DBTName + " values(";
    len=colType.size();
    for(i=0;i<len;i++){
      columnType=(String)colType.elementAt(i);
      columnValue=((String)colValue.elementAt(i)).trim();
      //最后一组数据
      if(i==len-1){
        if(columnType.equalsIgnoreCase("INTeger")||columnType.equalsIgnoreCase("INT")||columnType.equalsIgnoreCase("BIGINT")||columnType.equalsIgnoreCase("SMALLINT")||columnType.equalsIgnoreCase("DOUBLE")||columnType.equalsIgnoreCase("TINYINT")||columnType.equalsIgnoreCase("DECIMAL")){
          tmpSql=tmpSql + " " + (DBSource.equalsIgnoreCase("sybaccess")?columnValue:C2A.ChineseStringToAscii(columnValue));
        }
      else{
          tmpSql=tmpSql + "'" + (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")||columnType.equalsIgnoreCase("DECIMAL")){
          tmpSql=tmpSql + " " + (DBSource.equalsIgnoreCase("sybaccess")?columnValue:C2A.ChineseStringToAscii(columnValue)) + ",";
        }
        else{
          tmpSql=tmpSql + "'" + (DBSource.equalsIgnoreCase("sybaccess")?columnValue:C2A.ChineseStringToAscii(columnValue)) + "',";
        }
      }
     }
     sql=sql+tmpSql+")";
    //:test:System.out.println("Insert sql:"+sql);
    return sql;
  }
  //编辑当前行SQL
  public String getUpdateString(String DBSource,String DBTName,Vector colName,Vector colType,Vector colValue,Vector KeyValue,Vector Key,Vector KeyType){
    sql="update "+ DBTName + " set ";
    len=colType.size();
    String type="";
    String name="";
    String value="";
    //: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)).trim();
      //: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")||columnType.equalsIgnoreCase("DECIMAL")){
            if(columnValue.trim().equals(""))
              tmpSql=tmpSql + columnName + "=null";
            else
              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")||columnType.equalsIgnoreCase("DECIMAL")){
            if(columnValue.trim().equals(""))
              tmpSql=tmpSql + columnName + "=null"+",";
            else
              tmpSql=tmpSql + columnName + "=" + (DBSource.equalsIgnoreCase("sybaccess")?columnValue:C2A.ChineseStringToAscii(columnValue)) + ",";
        }
        else{
          tmpSql=tmpSql + columnName + "='" + (DBSource.equalsIgnoreCase("sybaccess")?columnValue:C2A.ChineseStringToAscii(columnValue)) + "',";
        }
      }
     }
     sql = sql + tmpSql + " where ";
     for(int i=0;i<KeyType.size();i++){
       type=KeyType.elementAt(i).toString();
       name=Key.elementAt(i).toString();
       value=KeyValue.elementAt(i).toString().trim();
       if(i==KeyType.size()-1){
         if(type.equalsIgnoreCase("INTeger")||type.equalsIgnoreCase("INT")||type.equalsIgnoreCase("BIGINT")||type.equalsIgnoreCase("SMALLINT")||type.equalsIgnoreCase("DOUBLE")||type.equalsIgnoreCase("TINYINT")||type.equalsIgnoreCase("DECIMAL")){
           sql = sql + name + "=" + value;
         }
         else{
           sql = sql + name + "='" + value + "'";
         }
       }
       else{
         if(type.equalsIgnoreCase("INTeger")||type.equalsIgnoreCase("INT")||type.equalsIgnoreCase("BIGINT")||type.equalsIgnoreCase("SMALLINT")||type.equalsIgnoreCase("DOUBLE")||type.equalsIgnoreCase("TINYINT")||type.equalsIgnoreCase("DECIMAL")){
           sql = sql + name + "=" + value + " and ";
         }
         else{
           sql = sql + name + "='" + value + "'" + " and ";
         }
       }
     }
     //:test:System.out.println("edit SQL:"+sql);
     return sql;
  }
  //查询当前行SQL
  public String getQueryString(String DBSource,String DBTName,Vector Field,Vector FName,Vector FType,Vector FOperator){
    String FieldValue="";
    String FieldName="";
    String FieldType="";
    String FieldOper="";
    tmpSql="";
    String tSql = "";
    sql="select * from " + DBTName + " ";
    len=FName.size();
    j=0;
    Vector MemI = new Vector();
    //:test:System.out.println("DBTSource:"+DBTSource);
    //:test:System.out.println("DBTName:"+DBTName);
    //:test:System.out.println("Field value size:" + Field.size());
    //:test:System.out.println("FName size:" + FName.size());
    //:test:System.out.println("FType size:" + FType.size());
    for(i=0;i<len;i++){
      FieldValue=(String)Field.elementAt(i);
      if(FieldValue.trim().equals("")||FieldValue.trim().equals("null")){
       }
      else{
      MemI.addElement(Integer.toString(i));
      }
    }
    j=MemI.size();
    for(i=0;i<j;i++){
      FieldValue=(String)Field.elementAt(Integer.parseInt(MemI.elementAt(i).toString()));
      FieldName=(String)FName.elementAt(Integer.parseInt(MemI.elementAt(i).toString()));
      FieldType=(String)FType.elementAt(Integer.parseInt(MemI.elementAt(i).toString()));
      FieldOper=(String)FOperator.elementAt(Integer.parseInt(MemI.elementAt(i).toString()));
      //最后一组数据
      if(i==j-1){
          tSql =  " where ";
          if(FieldType.equalsIgnoreCase("INTeger")||FieldType.equalsIgnoreCase("INT")||FieldType.equalsIgnoreCase("BIGINT")||FieldType.equalsIgnoreCase("SMALLINT")||FieldType.equalsIgnoreCase("DOUBLE")||FieldType.equalsIgnoreCase("TINYINT")||columnType.equalsIgnoreCase("DECIMAL")){
          tmpSql=tmpSql + FieldName + " " + FieldOper + " " + (FieldValue.equalsIgnoreCase("sybaccess")?columnValue:C2A.ChineseStringToAscii(FieldValue));
        }
      else{
        if(FieldOper.equalsIgnoreCase("like")){
          tmpSql=tmpSql + FieldName + " " + FieldOper + " '%" + (FieldValue.equalsIgnoreCase("sybaccess")?columnValue:C2A.ChineseStringToAscii(FieldValue)) + "%'";
        }
        else{
          tmpSql=tmpSql + FieldName + " " + FieldOper + " '" + (FieldValue.equalsIgnoreCase("sybaccess")?columnValue:C2A.ChineseStringToAscii(FieldValue)) + "'";
        }
        }
      }
      else{
          if(FieldType.equalsIgnoreCase("INTeger")||FieldType.equalsIgnoreCase("INT")||FieldType.equalsIgnoreCase("BIGINT")||FieldType.equalsIgnoreCase("SMALLINT")||FieldType.equalsIgnoreCase("DOUBLE")||FieldType.equalsIgnoreCase("TINYINT")||columnType.equalsIgnoreCase("DECIMAL")){
          tmpSql=tmpSql + FieldName + " " + FieldOper + " " + (FieldValue.equalsIgnoreCase("sybaccess")?columnValue:C2A.ChineseStringToAscii(FieldValue)) + " and ";
        }
        else{
          if(FieldOper.equalsIgnoreCase("like")){
          tmpSql=tmpSql + FieldName + " " + FieldOper + " '%" + (FieldValue.equalsIgnoreCase("sybaccess")?columnValue:C2A.ChineseStringToAscii(FieldValue)) + "%' and ";
          }
          else{
            tmpSql=tmpSql + FieldName + " " + FieldOper + " '" + (FieldValue.equalsIgnoreCase("sybaccess")?columnValue:C2A.ChineseStringToAscii(FieldValue)) + "' and ";
          }
        }
      }
     }
     //:test:System.out.println("tSql:"+tSql);
     sql = sql + tSql + tmpSql;
     //:test:System.out.println("query SQL:"+sql);
     return sql;
  }

    //测试主程序
    public static void main(String[] args){
      getSQLstring ql = new getSQLstring();
      Vector Field = new Vector();
      Vector FType = new Vector();
      Vector FName = new Vector();
      Vector FOper = new Vector();
      FType.addElement("integer");
      FType.addElement("char");
      FType.addElement("date");
      FType.addElement("integer");
      FType.addElement("char");
      Field.addElement("11");
      Field.addElement("aaa");
      Field.addElement("aaa");
      Field.addElement("aaa");
      Field.addElement("aaa");
      FName.addElement("aaaa");
      FName.addElement("bbbb");
      FName.addElement("cccc");
      FName.addElement("dddd");
      FName.addElement("eeee");
      FOper.addElement(">  ");
      FOper.addElement("=  ");
      FOper.addElement("like");
      FOper.addElement("=");
      FOper.addElement("like");
      String sql=ql.getUpdateString("dbmng","dbtable",FName,FType,FOper,FName,FType,FOper);
      System.out.println("mySql:"+sql);
    }
}