wanziz

导航

SQL语句的正则表达式

上学期做了一个简单数据库管理系统，判断SQL语法时百度了发现很少关于SQL的正则，所以分享一下，写得一般般，多多指教

 1 //建表
 2   public static boolean isCreateTAB(String sql) {  
 3    String str="^[\\s]*CREATE[\\s]*+TABLE+[\\s]*[A-Z][A-Z]*[\\s]*"
 4      + "[(][A-Z][A-Z]*[\\s]*+[CHAR[(][0-9][0-9]*[)]|"
 5      + "VARCHAR[(][0-9][0-9]*[)]|INT][,[A-Z][A-Z]*[\\s]*"
 6      + "(CHAR[(][0-9][0-9]*[)]|[VARCHAR][(][0-9][0-9]*[)]|[INT])]*"
 7      + "[,PRIMARY[\\s]*KEY][(][A-Z][A-Z]*[,[A-Z][A-Z]*]*[)][)][\\s]*$";
 8    Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
 9    Matcher m = p.matcher(sql);
10    boolean flag = m.matches();
11    return flag;
12   }
13   
14 //测试语句：create TABLE A(ID INT,NAME CHAR(5),age int,D VARCHAr(12)),PRIMARY KEY(ID)
15 
16 //删表
17    public static boolean isDropTAB(String sql) {
18     String str="^[\\s]*DROP[\\s]+TABLE+[\\s][A-Z][A-Z]*[\\s]*";
19     Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
20     Matcher m = p.matcher(sql);
21     boolean flag = m.matches();
22     return flag;
23    }
 1 //建库
 2  public static boolean isCreateDB(String sql) {
 3   String str="^[\\s]*CREATE[\\s]*+DATABASE+[\\s]*[A-Z][A-Z]*[\\s]*$";
 4   Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
 5   Matcher m = p.matcher(sql);
 6   boolean flag = m.matches();
 7   return flag;
 8  }
 9  
10  //删库
11   public static boolean isDropDB(String sql) {
12    String str="^[\\s]*DROP[\\s]+DATABASE+[\\s][A-Z][A-Z]*[\\s]*$";
13    Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
14    Matcher m = p.matcher(sql);
15    boolean flag = m.matches();
16    return flag;
17   }
/*select*/
 public static boolean isSelect(String sql) {  
  String str="^[\\s]*SELECT[\\s]*+([\\*]|[[a-z][a-z]*(,[A-Z][A-Z]*)*])"
    + "+[\\s]*+FROM[\\s]+([A-Z][A-Z]*)[\\s]*+(WHERE[\\s]*"
    + "+([A-Z][A-Z]*[=<>][^\\s]+[\\s]*(OR|AND)[\\s]*)*"
    + "([A-Z][A-Z]*[=<>][^\\s]+[\\s]*)[\\s]*)*$"; 
  Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
  Matcher m = p.matcher(sql);
  boolean flag = m.matches();
  return flag;
 }
//delete（删除记录行）  DELETE FROM 表名称 WHERE 列名称 = 值
 public static boolean isDelete(String sql) {
  boolean flag=false;
  String str="^[\\s]*DELETE[\\s]+FROM[\\s]+([A-Z][A-Z]*)"
    + "[\\s]+(WHERE[\\s]+([A-Z][A-Z]*[=<>]([0-9]|[\\'][A-Z][\\']))"
    + "(,[A-Z][A-Z]*[=<>][^\\s]+[\\s]*)*[\\s]*)*$";
  Pattern p=Pattern.compile(str);
  Matcher m = p.matcher(sql);
  flag = m.matches();
  return flag;
 }
//update      UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
  public static boolean isUpdate(String sql) {
   String str="^[\\s]*UPDATE[\\s][A-Z][A-Z]*[\\s]"
     + "SET[\\s][A-Z][A-Z]*=([0-9]|[\\'][A-Z][A-Z]*[\\'])"
     + "(,[A-Z][A-Z]*=([0-9]|[\\'][A-Z][A-Z]*[\\']))*[\\s]"
     + "WHERE[\\s][A-Z][A-Z]*[=<>]([0-9]|[\\'][A-Z][A-Z]*[\\'])"
     + "([\\s](AND|OR)[\\s][A-Z][A-Z]*[=<>]([0-9]|[\\'][A-Z][A-Z]*[\\']))*$";
   Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
   Matcher m = p.matcher(sql);
   boolean flag = m.matches();
   return flag;
  }
 1 //insert
 2 public static boolean isInsert(String sql) {
 3   String str="^[\\s]*INSERT[\\s]*INTO[\\s]*[A-Z][A-Z]*"
 4     + "[\\s]*[(][A-Z][A-Z]*(,[A-Z][A-Z]*)*[)][\\s]*+"
 5     + "VALUES+[\\s]*[(](([0-9][0-9]*|[\\'][A-Z0-9][A-Z0-9]*[\\']))"
 6     + "(,([0-9][0-9]*|[\\'][A-Z0-9][A-Z0-9]*[\\']))*[)]$";
 7   
 8   Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
 9   Matcher m = p.matcher(sql);
10   boolean flag = m.matches();
11   if(flag) {
12    flag=matchAttribute(sql);//如果符合sql语法，进一步判断属性的个数跟值的个数是否一样
13   }
14    return flag;
15  }
16 //属性列和值列匹配
17 public static boolean matchAttribute(String sql){
18     boolean flat=true;
19     String sqlArr[]=sql.split(" ");//按空格分，第三列对应属性列，第四个对应值列
20     String sqlArr0=sqlArr[2].substring(sqlArr[2].indexOf("(") + 1, sqlArr[2].indexOf(")"));//属性列()中内容
21      String sqlArr1=sqlArr[3].substring(sqlArr[3].indexOf("(") + 1, sqlArr[3].indexOf(")"));//值列()中内容
22      String sqlArr01[]=sqlArr0.split(",");//()里按逗号分，匹配属性列数和值列数
23       String sqlArr11[]=sqlArr1.split(",");
24     if(sqlArr01.length!=sqlArr11.length) {
25             System.out.println("属性列数和值列数不一致");
26             flag=false;
27       }
28   return flag;
29 
30 
31 }
 1 //权限
 2 public static boolean isGrant(String sql) {
 3   boolean flag=false;
 4   String str="^[\\s]*GRANT[\\s]*+([A-Z][A-Z]*)(,[A-Z][A-Z]*)*"
 5     + "+[\\s]*+ON+[\\s]*[([A-Z.][A-Z.]*)(,[A-Z.][A-Z.]*)*+[\\s]]*"
 6     + "TO+[\\s]*([A-Z][A-Z]*)(,[A-Z][A-Z]*)*$";
 7   
 8   Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
 9   Matcher m = p.matcher(sql);
10   flag = m.matches();
11   return flag;
12  }
13  
14  //测试语句：GRANT SELecT ON  D.B,M.DT TO  YJL,YXB
15  public static boolean isGrantCreate(String sql)
16  {
17   boolean flag=false;
18   String str="^[\\s]*GRANT[\\s]*+([A-Z][A-Z]*[\\s]*[A-Z][A-Z]*)"
19     + "(,[A-Z][A-Z]*[\\s]*[A-Z][A-Z]*)*+[\\s]*+TO+[\\s]*"
20     + "([A-Z][A-Z]*)(,[A-Z][A-Z]*)*$";
21   
22   Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
23   Matcher m = p.matcher(sql);
24   flag = m.matches();
25   return flag;
26  }
27  
 1 //撤销权限
 2 public static boolean isRevoke(String sql) {
 3   boolean flag=false;
 4   String str="^[\\s]*REVOKE[\\s]*+([A-Z][A-Z]*)(,[A-Z][A-Z]*)*+[\\s]*"
 5     + "ON+[\\s]*[([A-Z.][A-Z.]*)(,[A-Z.][A-Z.]*)*+[\\s]]*"
 6     + "FROM+[\\s]*([A-Z][A-Z]*)(,[A-Z][A-Z]*)*$";
 7   Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
 8   Matcher m = p.matcher(sql);
 9   flag = m.matches();
10   return flag;
11  }
12 
13 //测试语句：REVOKE    SELecT,DELETE ON  D,M.DT FROM  YJL,YXB
14 public static boolean isRevokeCreate(String sql)
15  {
16   boolean flag=false;
17   String str="^[\\s]*REVOKE[\\s]*+([A-Z][A-Z]*[\\s]*[A-Z][A-Z]*)"
18     + "(,[A-Z][A-Z]*[\\s]*[A-Z][A-Z]*)*+[\\s]*+FROM+[\\s]*"
19     + "([A-Z][A-Z]*)(,[A-Z][A-Z]*)*$";
20   
21   Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
22   Matcher m = p.matcher(sql);
23   flag = m.matches();
24   return flag;
25  }
26 ```

 

posted on 2019-07-07 22:19  wanziz  阅读(1439)  评论(0)    收藏  举报