将日志打印的SQL组装为可执行SQL_sublime版

java项目日志打印的sql和条件参数是分离的，使用起来很不方便，之前为了解决这种问题，使用notepad++录制宏解决了这个问题，但是后期不方便维护，遂转用sublime，这个跟notepad++比起来有些东西不是很习惯，但是好处是插件可以自己写，确实方便了很多，直接上代码，至于怎么安装，大家还是百度吧

直接上代码，注释写的很清楚，这里就不解释了

# 将java项目生成的sql改为可执行sql
class FillsqlCommand(sublime_plugin.TextCommand):
	def run (self,edit): 

		reg,source_str,lineBreak = getSel(self.view)  
		# 将==>和Parameters之间的空字符串删除，以免后面的匹配出问题
		source_str = re.sub("==>\s*Parameters:", "==>Parameters:", source_str)

		# 第一次出现的<==会包含完整的source_str以及参数信息，或是全没有这些信息，这正是符合我们预期
		# 每次将一份数据取出放到cut_str里面，同时source_str变量也要减去cut_str的数据，直接source_str消费干净为止
		cut_str = source_str[ :source_str.find("<==")]
		source_str = source_str[source_str.find("<==")+len("<=="):]

		result = ""
		while(len(cut_str.strip())!= 0):
			# 查询sql位于 preparing和parameters之间
			start_str = "Preparing:"
			statement_start_index = cut_str.find(start_str)
			if(statement_start_index ==  -1):
				# 我这里主要是查询sql，写这个也是为了兼容有些人没有复制preparing导致配不上的问题
				start_str = "SELECT"
				statement_start_index = cut_str.find(start_str)
				if (statement_start_index == -1):
					break
			statement_start_index = statement_start_index+len(start_str)

			end_str = "==>Parameters:"
			# 没找到查询sql说明后面的数据没有价值了，直接跳出循环
			if cut_str.find(end_str) == -1:
				break

			statement_end_index = cut_str.find(end_str) 

			preparing_sql = cut_str[statement_start_index:statement_end_index]

			# 查询条件位于 Parameters 和<==之间  
			parameter_sql = cut_str[statement_end_index+len(end_str):cut_str.find("<==")]

			# 条件为空时，就说明数据少了，就结束循环
			if(parameter_sql.strip() is None):
				break
			# parameter_sql 格式为  1(String),2(Integer)  我们需要根据类型决定条件里面的参数要加引号还是不加，虽然统一加引号，mysql也是可以隐匿转换
			parameter_list = parameter_sql.split(",")

			for index in range(len(parameter_list)):
				param_sub_list = parameter_list[index].split("(")
				param_prefix = param_sub_list[0]
				if(len(param_sub_list) == 2):
					param_suffix = param_sub_list[1].replace(")","")
					# 这里用了一个取巧方式可以少写很多if elif
					if param_suffix in "String,LocalDate,Date,TimeStamp":
						param_prefix = "\"" +param_prefix.strip()+ "\""

				# 每次只替换一个问号字符
				preparing_sql = preparing_sql.replace("?",param_prefix,1)

			result += preparing_sql.replace("\n",";")
			result += "\n\n===============================>\n\n"

			# 找不到有效数据就退出 循环
			if(source_str.find("<==") == -1):
				break
			cut_str = source_str[:source_str.find("<==")]
			source_str = source_str[source_str.find("<==") + len("<=="):]


		# 有数据时才刷到view里面
		if len(result) != 0:
			self.view.replace(edit,reg,result)


 

从项目里面拿的测试文本

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6911a3c2] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@739455108 wrapping com.mysql.cj.jdbc.ConnectionImpl@32f1afc4] will not be managed by Spring
==>  Preparing: SELECT max(biz_date) as biz_date FROM ttbi_order_hf WHERE (biz_date >= ? AND biz_date <= ?) GROUP BY null
==> Parameters: 2022-05-22(LocalDate), 2022-08-26(LocalDate)
<==      Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6911a3c2]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1df5f8a3] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@2040416108 wrapping com.mysql.cj.jdbc.ConnectionImpl@32f1afc4] will not be managed by Spring
==>  Preparing: select biz_date,round(sum(fz) /sum(fm),7) as dataNumber from ( select biz_date,recom_type_code, case when recom_type_code in ( ? , ? ) then sum(bigorder_new_1w) else 0 end as fz, sum(bigorder_new_1w) as fm from ttbi_order_hf WHERE biz_date in ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) group by biz_date,recom_type_code ) t1 group by biz_date order by biz_date asc
==> Parameters: 1(String), 2(String), 2022-05-22(String), 2022-05-29(String), 2022-06-05(String), 2022-06-12(String), 2022-06-19(String), 2022-06-26(String), 2022-07-03(String), 2022-07-10(String), 2022-07-17(String), 2022-07-24(String), 2022-07-31(String), 2022-08-07(String), 2022-08-14(String), 2022-08-21(String), 2022-08-26(String), null
<==      Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1df5f8a3]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7eed8a78] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@332896599 wrapping com.mysql.cj.jdbc.ConnectionImpl@32f1afc4] will not be managed by Spring
==>  Preparing: select code,name,parent_code,category_code,is_delete as isDeleted from t_conf_info WHERE is_delete = 0 and category_code in ( ? ) and code in ( ? , ? ) order by category_code,code
==> Parameters: car_type(String), 2(String), 7(String)
<==    Columns: code, name, parent_code, category_code, isDeleted
<==        Row: 2, WE86, , car_type, 0
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7eed8a78]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@613de06a] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1476495627 wrapping com.mysql.cj.jdbc.ConnectionImpl@32f1afc4] will not be managed by Spring
==>  Preparing: SELECT max(biz_date) biz_date FROM ttbi_order_hf GROUP BY null
==> Parameters: 
<==      Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@613de06a]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7e864b7c] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1416401560 wrapping com.mysql.cj.jdbc.ConnectionImpl@32f1afc4] will not be managed by Spring
==>  Preparing: SELECT biz_date,sum(bigorder_add_1y) as dataNum,car_type FROM ttbi_order_hf WHERE (car_type IN (?,?) AND biz_date IN (?,?)) GROUP BY biz_date,car_type order BY biz_date ASC
==> Parameters: 2(String), 7(String), 2022-08-26(LocalDate), null
<==      Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7e864b7c]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1347aa58] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1586936640 wrapping com.mysql.cj.jdbc.ConnectionImpl@32f1afc4] will not be managed by Spring
==>  Preparing: SELECT max(biz_date) biz_date FROM ttbi_order_hf GROUP BY null
==> Parameters: 
<==      Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1347aa58]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5a78eec9] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1950587572 wrapping com.mysql.cj.jdbc.ConnectionImpl@32f1afc4] will not be managed by Spring
==>  Preparing: SELECT biz_date,sum(bigorder_add_1y) as dataNum FROM ttbi_order_hf WHERE (biz_date IN (?,?)) GROUP BY biz_date order BY biz_date ASC
==> Parameters: 2022-08-26(LocalDate), null
<==      Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5a78eec9]

效果图