sql:使用druid做sql校验

项目有个需求需要使mysql的语句可以格式化，并且还要校验成功，查了很多资料阿里巴巴的druid下面有几个工具包正好能满足这些需求所以以下做一个总结。

引入maven依赖包

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
	<version>1.1.0</version>
</dependency>

sql校验

public static boolean verifySql(String sql){
	com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser
	parser=new com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser(sql);

	boolean flag=true;
	try{
		//只要解析不出现错误就算成功
		List<com.alibaba.druid.sql.ast.SQLStatement> smtList=parser.parseStatementList();
	}catch(Exception e){
		flag=false;
		e.printStackTrace();
	}
	return flag;
}

sql格式化

public static String formatSql(String sql){
	//先校验sql
	verifySql(sql);

	String formatSql=com.alibaba.druid.sql.SQLUtils.formatMySql(sql);

	//判断是否已经格式化过
	if(!sql.equals(formatSql)){
		return formatSql;
	}
	return null;
}

test 输入

sELECT * FROM JJJ WHERE LI=1 And moile='13261' And id In(selecT jj_id from jk where l=1 group by j having count(jj_id)>1) LIMIT 10,33

输出

SELECT *
FROM JJJ
WHERE LI = 1
	AND moile = '13261'
	AND id IN (
		SELECT jj_id
		FROM jk
		WHERE l = 1
		GROUP BY j
		HAVING COUNT(jj_id) > 1
	)
LIMIT 10, 33

赏

谢谢你的激励

微信