OA系统

开发平台：
Java

sqlcontrol.java：源码内容
							package control;
import java.lang.*;
import java.util.*;
import java.sql.*;
import sun.jdbc.odbc.*;
import sun.jdbc.odbc.JdbcOdbcDriver;
import javax.sql.*;
import javax.xml.xpath.*;
import org.xml.sax.*;
public class sqlcontrol {
    private int getCulm;
    private int getCon;
    private int maxnumber = 99;
    private Connection conn;
    private Statement stmt;
    private Statement del;
    private Statement runrs;
    private Statement change;
    private String Mousql="none";
    private ResultSet rs;
    private String res[][];
    //设置一次性处理的最大数量的方法
    public sqlcontrol() {
    }
    public void setMaxnumber(int getMax){
        this.maxnumber = getMax;
    }
    public Connection getSQL2005conn(String xmlname) throws Exception{
       String getpath=this.getClass().getClassLoader().getResource("/").getPath()+"\"+xmlname;
       String get[] = new String[5];
       XPath xpathEngine = XPathFactory.newInstance().newXPath();
       String xpathExpression = "/Root/port/text()";
       InputSource xmlSource = new InputSource(getpath);    // 假设 XML 文件路径为 C:a.xml
       get[0]=xpathEngine.evaluate(xpathExpression, xmlSource);//端口号
       xpathExpression = "/Root/database/text()";
       get[1]=xpathEngine.evaluate(xpathExpression, xmlSource);//数据库名
       xpathExpression = "/Root/name/text()";
       get[2]=xpathEngine.evaluate(xpathExpression, xmlSource);//用户名
       xpathExpression = "/Root/password/text()";
       get[3]=xpathEngine.evaluate(xpathExpression, xmlSource);//用户密码
       xpathExpression = "/Root/host/text()";
       get[4]=xpathEngine.evaluate(xpathExpression, xmlSource);//服务主机
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        this.conn = DriverManager.getConnection("jdbc:sqlserver://"+get[4]+":"+get[0]+";DatabaseName="+get[1],get[2],get[3]);
        return conn;
    }
    public Connection getMysqlconn_birdge(String server,String dbname,String user,String pass,String port){
         String MySqldriver;//MYSQL Server驱动程序字符串
         String MySqlURL; //MYSQL Server连接字符串
         MySqldriver = "org.gjt.mm.mysql.Driver";
         MySqlURL = "jdbc:mysql://";
         try{
             MySqlURL =MySqlURL+server+":"+port+"/"+dbname+"?user="+user+"&password="+pass+"&useUnicode=true&characterEncoding=GBK";
             Class.forName(MySqldriver);
             this.conn = DriverManager.getConnection(MySqlURL);
         }catch(Exception e){
             e.printStackTrace();
         }
    return conn;
    }
    //mysql连接数据库方法(数据源接法)
    public Connection getMysqlconn(String path){
        return conn;
    }
    //ACCESS数据库连接类,参数为数据库路径
    public Connection getConn(String path)
   {
     try {
       String forname = "sun.jdbc.odbc.JdbcOdbcDriver";
       Class.forName(forname);
       StringBuffer dbUrl = new StringBuffer(
               "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=");
       String s = path;//需要数据库的路径
       dbUrl.append(s);
       this.conn = DriverManager.getConnection(dbUrl.toString());
   }
    catch(Exception e)
   {
       e.printStackTrace( );
   }
     return conn;
   }
   //SQLSERVER数据库连接类
    public Connection getConn(String url,String name,String pwd){
        return conn;
    }
    //批量录入的方法，参数分别代表要操作的表名,要操作的字段,要添加的内容
    public String[] Autoinsert(String name,String ziduan[],String context[]){
        //获得要输入的字段数
        for(int helpget=0;helpget<ziduan.length;helpget++){
            try{
                if(ziduan[helpget].equals("")){
                    this.getCulm = helpget;
                    break;
                }else{
                    this.getCulm = ziduan.length;
                }
            }catch(Exception e){
                this.getCulm = helpget;
                break;
            }
        }
        //获得要输入的内容
        for(int helpgetCon=0;helpgetCon<context.length;helpgetCon++)
        { try{
           if(context[helpgetCon].equals(""))
           {this.getCon=helpgetCon;break;}
            else {this.getCon=context.length;}
           }catch(Exception e)
         {
           this.getCon=helpgetCon;break;
        }
      }
      //
      int n = getCon/getCulm;
      //设置数组变量，存要操作的SQL语句
      String AutoAllinsert[] = new String[n];
      StringBuffer inone = new StringBuffer();
      StringBuffer inonef = new StringBuffer();
      String getsql = "insert into "+name;
      //获得批量录入时字段组合和录入内容组合
      if(getCulm==getCon){
          for(int getziduan=0;getziduan<getCulm;getziduan++){
              if(getziduan == (getCulm-1)){
                  inone.append(ziduan[getziduan]);
              }else{
                  inone.append((ziduan[getziduan]+","));
              }
          }
          for(int getcontant=0;getcontant<getCon;getcontant++){
              if(getcontant ==(getCon-1)){
                  inonef.append(context[getcontant]);
              }else{
                  inonef.append((context[getcontant]+","));
              }
          }
          //拼装成所需要的SQL语句
          getsql = getsql+"("+inone.toString()+") values("+inonef.toString()+");";
          AutoAllinsert[0] = getsql;
          //向数据库中写入数据
          try{
              this.stmt = conn.createStatement();
              stmt.executeUpdate(getsql);
          }catch(Exception e){
              e.printStackTrace();
          }
      }else if(getCulm<getCon){   //多纪录批量录入部分
          for(int getziduan=0;getziduan<getCulm;getziduan++){
              if(getziduan == (getCulm-1)){
                  inone.append(ziduan[getziduan]);
              }else{
                  inone.append((ziduan[getziduan]+","));
              }
          }
          int bijiao=0,n4=0;
          for(int n1=0;n1<n;n1++){
              for(int n3=0;n3<getCulm;n3++){
                  if(bijiao==(getCulm-1)){
                      inonef.append(context[n4]);
                  }else{
                      inonef.append((context[n4]+","));
                  }
                  bijiao++;
                  n4++;
                  if(n4==getCon){
                      n4=n4-1;
                  }
              }
              AutoAllinsert[n1]="insert into "+name+"("+inone.toString()+") values("+inonef.toString()+");";
              bijiao = 0;
              inonef.delete(0,inonef.length());
          }
          //将组合好的SQL语句批量写入数据库
          try{
              this.stmt = conn.createStatement();
              for (int ins = 0; ins < AutoAllinsert.length; ins++) {
                 stmt.executeUpdate(AutoAllinsert[ins]);
              }
          }catch(Exception e){
              e.printStackTrace();
          }
      }
        return AutoAllinsert;
    }
    //执行外来SQL语句的方法,参数为外来SQL语句
    public String MouChoose(String mouchoose)
   {
       String MougetSql=mouchoose;
       this.Mousql=MougetSql;
       try
      {
      Statement stmt1=conn.createStatement();
      stmt1.executeUpdate(Mousql);
      }
      catch(Exception e)
      {
          e.printStackTrace();
      }
       return Mousql;
   }
   //执行自动删除纪录的方法,参数分别代表要操作的表名,及查询语句的条件
   public String AutoDeleteUpdate(String name,String forbiden)
  {    String getsql="";
       try
       {
           getsql="delete"+" "+"from"+" "+name+" ";
           StringBuffer FinallyDeleteSql=new StringBuffer();
           if (forbiden.equals("none")){
               FinallyDeleteSql.append((getsql+";"));
            }else{
                FinallyDeleteSql.append((getsql+"where "+forbiden+";"));
           }
           getsql = FinallyDeleteSql.toString();
           this.del=conn.createStatement();
           del.executeUpdate(getsql);
       }catch(Exception e)
       {
         e.printStackTrace();
       }
       return getsql;
   }
   //单一更新数据方法,第一个参数是要操作的表名.第二个参数是更新的字段和对应的更新内容,第三个字段为更新条件
   public String AutoModify(String name,String context[],String tiaojian){
       int pan = context.length/2;
       int get = pan*2;
       int shu = 0;
       StringBuffer pinzhuang = new StringBuffer();
       String getsql = "update "+name+" set ";
       for(int n=0;n<get;n+=2){
           if(n==(get-2)){
               pinzhuang.append((context[shu]+"="+context[(shu+1)]));
           }else{
               pinzhuang.append((context[shu]+"="+context[(shu+1)]+","));
           }
           shu+=2;
       }
       getsql=getsql+pinzhuang.toString()+" where "+tiaojian+";";
       try{
           this.change = conn.createStatement();
           change.executeUpdate(getsql);
       }catch(Exception e){
           e.printStackTrace();
       }
        return getsql;
    }
    //批量更新数据库的方法
    public String[] AutoModify(String name,String ziduan[],String context[],String[] key){
        //获得要输入的字段数
        for(int helpget=0;helpget<ziduan.length;helpget++){
            try{
                if(ziduan[helpget].equals("")){
                    this.getCulm = helpget;
                    break;
                }else{
                    this.getCulm = ziduan.length;
                }
            }catch(Exception e){
               this.getCulm = helpget;
                break;
            }
        }
        //获得要输入的内容
        for(int helpgetCon=0;helpgetCon<context.length;helpgetCon++)
        { try{
           if(context[helpgetCon].equals(""))
           {this.getCon=helpgetCon;break;}
            else {this.getCon=context.length;}
           }catch(Exception e)
         {
           this.getCon=helpgetCon;break;
        }
      }
      //
      int n = getCon/getCulm;
      //设置数组变量，存要操作的SQL语句
      String AutoAllModify[] = new String[n];
      StringBuffer inone = new StringBuffer();
      StringBuffer inonef = new StringBuffer();
      String getsql = "update "+name+" set ";
      //获得批量更新时字段组合和录入内容组合
      //多纪录批量更新部分
          int bijiao=0,n4=0;
        try{
          for(int n1=0;n1<n;n1++){
              for(int n3=0;n3<getCulm;n3++){
                  if(bijiao==(getCulm-1)){
                      inonef.append((ziduan[n3]+"="+context[n4]));
                  }else{
                      inonef.append((ziduan[n3]+"="+context[n4]+","));
                  }
                  bijiao++;
                  n4++;
                  if(n4==getCon){
                      n4=n4-1;
                  }
              }
              AutoAllModify[n1]="update "+name+" set "+inonef.toString()+" where "+key[n1]+";";
              bijiao = 0;
              inonef.delete(0,inonef.length());
          }
          //将组合好的SQL语句批量写入数据库
              this.change = conn.createStatement();
              for (int ins = 0; ins < AutoAllModify.length; ins++) {
                 change.executeUpdate(AutoAllModify[ins]);
              }
          }catch(Exception e){
              e.printStackTrace();
          }
        return AutoAllModify;
    }
      //执行查询返回结果集的方法,参数为查询语句
   public ResultSet getStatement(String select)
   { try{
         this.runrs = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
         this.rs=runrs.executeQuery(select);
       }catch(Exception e)
       {
           e.printStackTrace();
       }
       return rs;
   }
   //获得结果集中的数据的方法,并以二维数组纪录
   public String[][] getResultContant(){
       try{
           ResultSetMetaData meta = rs.getMetaData();
           rs.first();
           int n=1;
           while(rs.next()){
               n++;
           }
           rs.first();
           int getcount = meta.getColumnCount();
           String get[][]= new String[n][getcount];
           for(int h=0;h<n;h++){
               for(int h1=0;h1<getcount;h1++){
                   get[h][h1] = rs.getString((h1+1));
               }
               rs.next();
           }
           this.res=get;
           rs.first();
       }catch(Exception e){
           e.printStackTrace();
       }
       return res;
    }
    //关闭连接和相关,释放资源
    public void closeDB(){
        try{
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (runrs != null) {
                runrs.close();
                runrs = null;
            }
            if (del != null) {
                del.close();
                del = null;
            }
            if (change != null) {
               change.close();
               change = null;
           }
           if (conn != null) {
               conn.close();
               conn = null;
           }
        }catch(Exception e){
            e.printStackTrace();
        }
     }
     //此类到此结束,更待补充
}

						