上学期做了一个简单数据库管理系统,判断SQL语法时百度了发现很少关于SQL的正则,所以分享一下,写得一般般,多多指教
//建表
public static boolean isCreateTAB(String sql) {
String str="^[\\s]*CREATE[\\s]*+TABLE+[\\s]*[A-Z][A-Z]*[\\s]*"
+ "[(][A-Z][A-Z]*[\\s]*+[CHAR[(][0-9][0-9]*[)]|"
+ "VARCHAR[(][0-9][0-9]*[)]|INT][,[A-Z][A-Z]*[\\s]*"
+ "(CHAR[(][0-9][0-9]*[)]|[VARCHAR][(][0-9][0-9]*[)]|[INT])]*"
+ "[,PRIMARY[\\s]*KEY][(][A-Z][A-Z]*[,[A-Z][A-Z]*]*[)][)][\\s]*$";
Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(sql);
boolean flag = m.matches();
return flag;
}
//测试语句:create TABLE A(ID INT,NAME CHAR(5),age int,D VARCHAr(12)),PRIMARY KEY(ID)
//删表
public static boolean isDropTAB(String sql) {
String str="^[\\s]*DROP[\\s]+TABLE+[\\s][A-Z][A-Z]*[\\s]*";
Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(sql);
boolean flag = m.matches();
return flag;
}
//建库
public static boolean isCreateDB(String sql) {
String str="^[\\s]*CREATE[\\s]*+DATABASE+[\\s]*[A-Z][A-Z]*[\\s]*$";
Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(sql);
boolean flag = m.matches();
return flag;
}
//删库
public static boolean isDropDB(String sql) {
String str="^[\\s]*DROP[\\s]+DATABASE+[\\s][A-Z][A-Z]*[\\s]*$";
Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(sql);
boolean flag = m.matches();
return flag;
}
/*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;
}
//insert
public static boolean isInsert(String sql) {
String str="^[\\s]*INSERT[\\s]*INTO[\\s]*[A-Z][A-Z]*"
+ "[\\s]*[(][A-Z][A-Z]*(,[A-Z][A-Z]*)*[)][\\s]*+"
+ "VALUES+[\\s]*[(](([0-9][0-9]*|[\\'][A-Z0-9][A-Z0-9]*[\\']))"
+ "(,([0-9][0-9]*|[\\'][A-Z0-9][A-Z0-9]*[\\']))*[)]$";
Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(sql);
boolean flag = m.matches();
if(flag) {
flag=matchAttribute(sql);//如果符合sql语法,进一步判断属性的个数跟值的个数是否一样
}
return flag;
}
//属性列和值列匹配
public static boolean matchAttribute(String sql){
boolean flat=true;
String sqlArr[]=sql.split(" ");//按空格分,第三列对应属性列,第四个对应值列
String sqlArr0=sqlArr[2].substring(sqlArr[2].indexOf("(") + 1, sqlArr[2].indexOf(")"));//属性列()中内容
String sqlArr1=sqlArr[3].substring(sqlArr[3].indexOf("(") + 1, sqlArr[3].indexOf(")"));//值列()中内容
String sqlArr01[]=sqlArr0.split(",");//()里按逗号分,匹配属性列数和值列数
String sqlArr11[]=sqlArr1.split(",");
if(sqlArr01.length!=sqlArr11.length) {
System.out.println("属性列数和值列数不一致");
flag=false;
}
return flag;
}
//权限
public static boolean isGrant(String sql) {
boolean flag=false;
String str="^[\\s]*GRANT[\\s]*+([A-Z][A-Z]*)(,[A-Z][A-Z]*)*"
+ "+[\\s]*+ON+[\\s]*[([A-Z.][A-Z.]*)(,[A-Z.][A-Z.]*)*+[\\s]]*"
+ "TO+[\\s]*([A-Z][A-Z]*)(,[A-Z][A-Z]*)*$";
Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(sql);
flag = m.matches();
return flag;
}
//测试语句:GRANT SELecT ON D.B,M.DT TO YJL,YXB
public static boolean isGrantCreate(String sql)
{
boolean flag=false;
String str="^[\\s]*GRANT[\\s]*+([A-Z][A-Z]*[\\s]*[A-Z][A-Z]*)"
+ "(,[A-Z][A-Z]*[\\s]*[A-Z][A-Z]*)*+[\\s]*+TO+[\\s]*"
+ "([A-Z][A-Z]*)(,[A-Z][A-Z]*)*$";
Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(sql);
flag = m.matches();
return flag;
}
//撤销权限
public static boolean isRevoke(String sql) {
boolean flag=false;
String str="^[\\s]*REVOKE[\\s]*+([A-Z][A-Z]*)(,[A-Z][A-Z]*)*+[\\s]*"
+ "ON+[\\s]*[([A-Z.][A-Z.]*)(,[A-Z.][A-Z.]*)*+[\\s]]*"
+ "FROM+[\\s]*([A-Z][A-Z]*)(,[A-Z][A-Z]*)*$";
Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(sql);
flag = m.matches();
return flag;
}
//测试语句:REVOKE SELecT,DELETE ON D,M.DT FROM YJL,YXB
public static boolean isRevokeCreate(String sql)
{
boolean flag=false;
String str="^[\\s]*REVOKE[\\s]*+([A-Z][A-Z]*[\\s]*[A-Z][A-Z]*)"
+ "(,[A-Z][A-Z]*[\\s]*[A-Z][A-Z]*)*+[\\s]*+FROM+[\\s]*"
+ "([A-Z][A-Z]*)(,[A-Z][A-Z]*)*$";
Pattern p=Pattern.compile(str,Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(sql);
flag = m.matches();
return flag;
}