我们在做程序的时候有事后会涉及到利用sql文件 直接执行,可是在sql文件中有很多注释,我们要一句一句的执行首先必须的得把sql文件解析
去除其中的注释,还有把每一句sql语句取出来,然后再利用各个平台中的数据库相关执行它。
接下来放代码!
java版本的
- package com.zz;
- import java.io.*;
- import java.util.ArrayList;
- import java.util.Enumeration;
- import java.util.List;
- import java.util.Vector;
- /*
- * 作者 祝君
- * 时间 2014年1月16号
- * java执行数据库脚本代码
- */
- public class SqlHelper {
- /**
- * @param args
- */
- public static void main(String[] args) {
- String path=new String("d:\\zzadmin.sql");
- String sql=GetText(path);
- String[] arr=getsql(sql);
- for(int i=0;i<arr.length;i++)
- System.out.println("第"+i+"句:"+arr[i]);
- }
- public static String GetText(String path){
- File file=new File(path);
- if(!file.exists()||file.isDirectory())
- return null;
- StringBuffer sb=new StringBuffer();
- try
- {
- FileInputStream fis = new FileInputStream(path);
- InputStreamReader isr = new InputStreamReader(fis, "UTF-8");
- BufferedReader br = new BufferedReader(isr);
- String temp=null;
- temp=br.readLine();
- while(temp!=null){
- sb.append(temp+"\r\n");
- temp=br.readLine();
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- return sb.toString();
- }
- /**
- * 获取sql文件中的sql语句数组
- * @param sql
- * @return 数组
- */
- public static String[] getsql(String sql)
- {
- String s=sql;
- s=s.replace("\r\n","\r");
- s=s.replace("\r", "\n");
- String[] ret=new String[1000];
- String[] sqlarray=s.split(";\n");
- sqlarray=filter(sqlarray);
- int num=0;
- for (String item : sqlarray)
- {
- String ret_item = "";
- String[] querys = item.trim().split("\n");
- querys = filter(querys);//去空
- for (String query : querys)
- {
- String str1 = query.substring(0, 1);
- String str2 = query.substring(0, 2);
- if (str1.equals("#") || str2.equals("--") || str2.equals("/*") || str2.equals("//"))//去除注释的关键步奏
- {
- continue;
- }
- ret_item += query;
- }
- ret[num] = ret_item;
- num++;
- }
- return filter(ret);
- }
- /// <summary>
- /// 去除空值数组
- /// </summary>
- /// <param name="ss">数组</param>
- /// <returns></returns>
- public static String[] filter(String[] ss)
- {
- List<String> strs = new ArrayList<String>();
- for (String s : ss) {
- if (s != null && !s.equals(""))
- strs.add(s);
- }
- String[] result=new String[strs.size()];
- for(int i=0;i<strs.size();i++)
- {
- result[i]=strs.get(i).toString();
- }
- return result;
- }
- //删除注释
- public void deletezs(String fileStr)
- {
- try{
- Vector<String> vec=new Vector<String>();
- String str="",tm="",mm="";
- BufferedReader br = new BufferedReader( new FileReader(fileStr));
- boolean bol=false;
- while( null != (str = br.readLine() ) )
- {
- if ((str.indexOf("/*")>=0)&&((bol==false)))
- {
- if (str.indexOf("*/")>0)
- {
- bol=false;
- vec.addElement(str.substring(0,str.indexOf("/*"))+str.substring(str.indexOf("*/")+2,str.length()));
- }
- else
- {
- bol=true;
- mm=str.substring(0,str.indexOf("/*"));
- if (!(mm.trim().equals("")))
- vec.addElement(mm);
- }
- }
- else if (bol==true)
- {
- if (str.indexOf("*/")>=0)
- {
- bol=false;
- mm=str.substring(str.indexOf("*/")+2,str.length());
- if (!mm.trim().equals(""))
- vec.addElement(mm);
- }
- }
- else if (str.indexOf("//")>=0)
- {
- tm=str.substring(0,str.indexOf("//"));
- if (!tm.trim().equals(""))
- vec.addElement(tm);
- }
- else
- {
- vec.addElement(str);
- }
- }
- br.close();
- File fName=new File(fileStr);
- FileWriter in=new FileWriter(fName);
- String ssss="";
- Enumeration<String> ew=vec.elements();
- while (ew.hasMoreElements()) {
- ssss= ew.nextElement().toString();
- in.write(ssss+"\n");
- }
- in.close();
- vec.clear();
- }catch(Exception ee){
- ee.printStackTrace();
- }
- }
- }
调用GetText就可以返回一个装满了sql语句的数组,循环执行其中的sql语句吧
c#版本的
- //-------------------------第一种-------------------------------------
- /// <summary>
- /// 获取sql文件中的sql语句数组 第一种方法
- /// </summary>
- /// <param name="sql"></param>
- /// <returns></returns>
- public static string[] sql_split(string sql)
- {
- string s = sql;
- Regex reg = new Regex("/TYPE=(InnoDB|MyISAM|MEMORY)( DEFAULT CHARSET=[^; ]+)?/");
- reg.Replace(sql, "ENGINE=\\1 DEFAULT CHARSET=utf8");
- s = s.Replace('\r', '\n');
- string[] ret = new string[10000];
- string[] sqlarray = StringSplit(s, ";\n");
- int num = 0;
- foreach (string item in sqlarray)
- {
- ret[num] = "";
- string[] queries = item.Split('\n');
- queries = filter(queries);
- foreach (string query in queries)
- {
- string str1 = query.Substring(0, 1);
- string str2 = query.Substring(0, 2);
- if (str1 != "#" && str2 != "--" && str2 != "/*" && str2 != "//")//去除注释的关键步奏
- {
- ret[num] += query;
- }
- }
- num++;
- }
- ret = filter(ret);
- return ret;
- }
- /// <summary>
- /// 去除空值数组
- /// </summary>
- /// <param name="ss"></param>
- /// <returns></returns>
- public static string[] filter(string[] ss)
- {
- List<string> strs = new List<string>();
- foreach (string s in ss)
- {
- if (!string.IsNullOrEmpty(s)) strs.Add(s);
- }
- string[] result = strs.ToArray();
- return result;
- }
- /// <summary>
- /// 将字符串分割成数组
- /// </summary>
- /// <param name="strSource"></param>
- /// <param name="strSplit"></param>
- /// <returns></returns>
- public static string[] StringSplit(string strSource, string strSplit)
- {
- string[] strtmp = new string[1];
- int index = strSource.IndexOf(strSplit, 0);
- if (index < 0)
- {
- strtmp[0] = strSource;
- return strtmp;
- }
- else
- {
- strtmp[0] = strSource.Substring(0, index);
- return StringSplit(strSource.Substring(index + strSplit.Length), strSplit, strtmp);
- }
- }
- /// <summary>
- /// 采用递归将字符串分割成数组
- /// </summary>
- /// <param name="strSource"></param>
- /// <param name="strSplit"></param>
- /// <param name="attachArray"></param>
- /// <returns></returns>
- private static string[] StringSplit(string strSource, string strSplit, string[] attachArray)
- {
- string[] strtmp = new string[attachArray.Length + 1];
- attachArray.CopyTo(strtmp, 0);
- int index = strSource.IndexOf(strSplit, 0);
- if (index < 0)
- {
- strtmp[attachArray.Length] = strSource;
- return strtmp;
- }
- else
- {
- strtmp[attachArray.Length] = strSource.Substring(0, index);
- return StringSplit(strSource.Substring(index + strSplit.Length), strSplit, strtmp);
- }
- }
- //-----------------------------------------------------
- //-----------------------第二种------------------------------
- /// <summary>
- /// 获取sql文件中的sql语句数组 第二种
- /// </summary>
- /// <param name="sql"></param>
- /// <returns></returns>
- public string[] getsqls(string sql)
- {
- string s = sql;
- s = s.Replace("\r\n", "\n");
- s = s.Replace("\r","\n").Trim();
- string[] ret = new string[1000];
- string[] sqlarray= StringSplit(s, ";\n");
- sqlarray = filter(sqlarray);//去空
- int num=0;
- foreach (string item in sqlarray)
- {
- string ret_item = "";
- string[] querys = item.Trim().Split('\n');
- querys = filter(querys);//去空
- foreach (string query in querys)
- {
- string str1 = query.Substring(0, 1);
- string str2 = query.Substring(0, 2);
- if (str1 == "#" || str2 == "--" || str2 == "/*" || str2 == "//")//去除注释的关键步奏
- {
- continue;
- }
- ret_item += query;
- }
- ret[num] = ret_item;
- num++;
- }
- return filter(ret);
- }
来源: http://www.phpxs.com/code/1002655/