C#和Java中执行SQL文件脚本的代码(非常有用)-CSDN博客

我们在做程序的时候有事后会涉及到利用sql文件直接执行，可是在sql文件中有很多注释，我们要一句一句的执行首先必须的得把sql文件解析

去除其中的注释，还有把每一句sql语句取出来，然后再利用各个平台中的数据库相关执行它。

接下来放代码！

java版本的

 
    001package com.zz;
 
    002 
 
    003import java.io.*;
 
    004import java.util.ArrayList;
 
    005import java.util.Enumeration;
 
    006import java.util.List;
 
    007import java.util.Vector;
 
    008 
 
    009/*
 
    010 * 作者 祝君
 
    011 * 时间 2014年1月16号
 
    012 * java执行数据库脚本代码
 
    013 */
 
    014public class SqlHelper {
 
    015 
 
    016    /**
 
    017     * @param args
 
    018     */
 
    019    public static void main(String[] args) {
 
    020         
 
    021        String path=new String("d:\\zzadmin.sql");
 
    022        String sql=GetText(path);
 
    023        String[] arr=getsql(sql);
 
    024        for(int i=0;i<arr.length;i++)
 
    025            System.out.println("第"+i+"句:"+arr[i]);
 
    026 
 
    027    }
 
    028    public static String GetText(String path){
 
    029        File file=new File(path);
 
    030        if(!file.exists()||file.isDirectory())
 
    031            return null;
 
    032        StringBuffer sb=new StringBuffer();
 
    033        try
 
    034        {
 
    035            FileInputStream fis = new FileInputStream(path);
 
    036            InputStreamReader isr = new InputStreamReader(fis, "UTF-8");
 
    037            BufferedReader br = new BufferedReader(isr);
 
    038            String temp=null;
 
    039            temp=br.readLine();
 
    040            while(temp!=null){
 
    041            sb.append(temp+"\r\n");
 
    042            temp=br.readLine();
 
    043            }
 
    044        } catch (Exception e) {
 
    045            e.printStackTrace();
 
    046        }
 
    047        return sb.toString();
 
    048    }
 
    049 
 
    050    /**
 
    051     * 获取sql文件中的sql语句数组
 
    052     * @param sql
 
    053     * @return 数组
 
    054     */
 
    055    public static String[] getsql(String sql)
 
    056    {
 
    057        String s=sql;
 
    058        s=s.replace("\r\n","\r");
 
    059        s=s.replace("\r", "\n");
 
    060        String[] ret=new String[1000];
 
    061        String[] sqlarray=s.split(";\n");
 
    062        sqlarray=filter(sqlarray);
 
    063        int num=0;
 
    064        for (String item : sqlarray)
 
    065        {
 
    066            String ret_item = "";
 
    067            String[] querys = item.trim().split("\n");
 
    068            querys = filter(querys);//去空
 
    069            for (String query : querys)
 
    070            {
 
    071                String str1 = query.substring(0, 1);
 
    072                String str2 = query.substring(0, 2);
 
    073                if (str1.equals("#") || str2.equals("--") || str2.equals("/*") || str2.equals("//"))//去除注释的关键步奏
 
    074                {
 
    075                    continue;
 
    076                }
 
    077                ret_item += query;
 
    078            }
 
    079            ret[num] = ret_item;
 
    080            num++;
 
    081        }
 
    082        return filter(ret);
 
    083    }
 
    084    /// <summary>
 
    085    /// 去除空值数组
 
    086    /// </summary>
 
    087    /// <param name="ss">数组</param>
 
    088    /// <returns></returns>
 
    089    public static String[] filter(String[] ss)
 
    090    {
 
    091        List<String> strs = new ArrayList<String>();
 
    092        for (String s : ss) {
 
    093             if (s != null && !s.equals(""))
 
    094                 strs.add(s);
 
    095        }
 
    096        
 
    097        String[] result=new String[strs.size()];
 
    098        for(int i=0;i<strs.size();i++)
 
    099        {
 
    100            result[i]=strs.get(i).toString();
 
    101        }
 
    102        return result;
 
    103    }
 
    104     
 
    105    //删除注释
 
    106    public void deletezs(String fileStr)
 
    107    {
 
    108      try{
 
    109      Vector<String> vec=new Vector<String>();
 
    110      String str="",tm="",mm="";
 
    111      BufferedReader br = new BufferedReader( new FileReader(fileStr));
 
    112      boolean bol=false;
 
    113      while( null != (str = br.readLine() ) )
 
    114      {
 
    115        if ((str.indexOf("/*")>=0)&&((bol==false)))
 
    116        {
 
    117          if (str.indexOf("*/")>0)
 
    118          {
 
    119            bol=false;
 
    120            vec.addElement(str.substring(0,str.indexOf("/*"))+str.substring(str.indexOf("*/")+2,str.length()));
 
    121          }
 
    122          else
 
    123          {
 
    124             bol=true;
 
    125             mm=str.substring(0,str.indexOf("/*"));
 
    126             if (!(mm.trim().equals("")))
 
    127                 vec.addElement(mm);
 
    128          }
 
    129        }
 
    130        else if (bol==true)
 
    131        {
 
    132            if (str.indexOf("*/")>=0)
 
    133            {
 
    134                bol=false;
 
    135                mm=str.substring(str.indexOf("*/")+2,str.length());
 
    136                if (!mm.trim().equals(""))
 
    137                   vec.addElement(mm);
 
    138            }
 
    139        }
 
    140        else if (str.indexOf("//")>=0)
 
    141        {
 
    142                     tm=str.substring(0,str.indexOf("//"));
 
    143                     if (!tm.trim().equals(""))
 
    144                        vec.addElement(tm);
 
    145        }
 
    146        else
 
    147        {
 
    148            vec.addElement(str);
 
    149        }
 
    150        }
 
    151      br.close();
 
    152      File fName=new File(fileStr);
 
    153      FileWriter in=new  FileWriter(fName);
 
    154      String ssss="";
 
    155      Enumeration<String> ew=vec.elements();
 
    156 
 
    157             while (ew.hasMoreElements()) {
 
    158               ssss= ew.nextElement().toString();
 
    159               in.write(ssss+"\n");
 
    160             }
 
    161 
 
    162      in.close();
 
    163      vec.clear();
 
    164 
 
    165      }catch(Exception ee){
 
    166          ee.printStackTrace();
 
    167      }
 
    168 
 
    169    }
 
    170 
 
    171 
 
    172}

调用GetText就可以返回一个装满了sql语句的数组，循环执行其中的sql语句吧

c#版本的

 
    001//-------------------------第一种-------------------------------------
 
    002       /// <summary>
 
    003       /// 获取sql文件中的sql语句数组 第一种方法
 
    004       /// </summary>
 
    005       /// <param name="sql"></param>
 
    006       /// <returns></returns>
 
    007       public static string[] sql_split(string sql)
 
    008       {
 
    009           string s = sql;
 
    010           Regex reg = newRegex("/TYPE=(InnoDB|MyISAM|MEMORY)( DEFAULT CHARSET=[^; ]+)?/");
 
    011           reg.Replace(sql, "ENGINE=\\1 DEFAULT CHARSET=utf8");
 
    012           s = s.Replace('\r', '\n');
 
    013           string[] ret = new string[10000];
 
    014           string[] sqlarray = StringSplit(s, ";\n");
 
    015           int num = 0;
 
    016           foreach (string item in sqlarray)
 
    017           {
 
    018               ret[num] = "";
 
    019               string[] queries = item.Split('\n');
 
    020               queries = filter(queries);
 
    021               foreach (string query in queries)
 
    022               {
 
    023                   string str1 = query.Substring(0, 1);
 
    024                   string str2 = query.Substring(0, 2);
 
    025                   if (str1 != "#" && str2 != "--" && str2 != "/*"&& str2 != "//")//去除注释的关键步奏
 
    026                   {
 
    027                       ret[num] += query;
 
    028                   }
 
    029               }
 
    030               num++;
 
    031           }
 
    032           ret = filter(ret);
 
    033           return ret;
 
    034       }
 
    035 
 
    036       /// <summary>
 
    037       /// 去除空值数组
 
    038       /// </summary>
 
    039       /// <param name="ss"></param>
 
    040       /// <returns></returns>
 
    041       public static string[] filter(string[] ss)
 
    042       {
 
    043           List<string> strs = new List<string>();
 
    044           foreach (string s in ss)
 
    045           {
 
    046               if (!string.IsNullOrEmpty(s)) strs.Add(s);
 
    047           }
 
    048           string[] result = strs.ToArray();
 
    049           return result;
 
    050       }
 
    051       /// <summary>
 
    052       /// 将字符串分割成数组
 
    053       /// </summary>
 
    054       /// <param name="strSource"></param>
 
    055       /// <param name="strSplit"></param>
 
    056       /// <returns></returns>
 
    057       public static string[] StringSplit(string strSource, string strSplit)
 
    058       {
 
    059           string[] strtmp = new string[1];
 
    060           int index = strSource.IndexOf(strSplit, 0);
 
    061           if (index < 0)
 
    062           {
 
    063               strtmp[0] = strSource;
 
    064               return strtmp;
 
    065           }
 
    066           else
 
    067           {
 
    068               strtmp[0] = strSource.Substring(0, index);
 
    069               returnStringSplit(strSource.Substring(index + strSplit.Length), strSplit, strtmp);
 
    070           }
 
    071       }
 
    072 
 
    073       /// <summary>
 
    074       /// 采用递归将字符串分割成数组
 
    075       /// </summary>
 
    076       /// <param name="strSource"></param>
 
    077       /// <param name="strSplit"></param>
 
    078       /// <param name="attachArray"></param>
 
    079       /// <returns></returns>
 
    080       private static string[] StringSplit(string strSource, stringstrSplit, string[] attachArray)
 
    081       {
 
    082           string[] strtmp = new string[attachArray.Length + 1];
 
    083           attachArray.CopyTo(strtmp, 0);
 
    084 
 
    085           int index = strSource.IndexOf(strSplit, 0);
 
    086           if (index < 0)
 
    087           {
 
    088               strtmp[attachArray.Length] = strSource;
 
    089               return strtmp;
 
    090           }
 
    091           else
 
    092           {
 
    093               strtmp[attachArray.Length] = strSource.Substring(0, index);
 
    094               returnStringSplit(strSource.Substring(index + strSplit.Length), strSplit, strtmp);
 
    095           }
 
    096       }
 
    097 
 
    098       //-----------------------------------------------------
 
    099 
 
    100       //-----------------------第二种------------------------------
 
    101       /// <summary>
 
    102       /// 获取sql文件中的sql语句数组 第二种
 
    103       /// </summary>
 
    104       /// <param name="sql"></param>
 
    105       /// <returns></returns>
 
    106       public string[] getsqls(string sql)
 
    107       {
 
    108           string s = sql;
 
    109           s = s.Replace("\r\n", "\n");
 
    110           s = s.Replace("\r","\n").Trim();
 
    111           string[] ret = new string[1000];
 
    112 
 
    113           string[] sqlarray= StringSplit(s, ";\n");
 
    114           sqlarray = filter(sqlarray);//去空
 
    115 
 
    116           int num=0;
 
    117           foreach (string item in sqlarray)
 
    118           {
 
    119               string ret_item = "";
 
    120               string[] querys = item.Trim().Split('\n');
 
    121               querys = filter(querys);//去空
 
    122 
 
    123               foreach (string query in querys)
 
    124               {
 
    125                   string str1 = query.Substring(0, 1);
 
    126                   string str2 = query.Substring(0, 2);
 
    127                   if (str1 == "#" || str2 == "--" || str2 == "/*"|| str2 == "//")//去除注释的关键步奏
 
    128                   {
 
    129                       continue;
 
    130                   }
 
    131                   ret_item += query;
 
    132               }
 
    133               ret[num] = ret_item;
 
    134               num++;
 
    135           }
 
    136           return filter(ret);
 
    137       }

c#两个方法对sql文件解析都是一样的