一、起因
在开发过程中，需要使用Mybatis频繁的生成SQL查询数据库，但是因为Mybatis打印出的SQL分为两部分，一部分是Parameters，另一部分是Preparing。
其中Parameters为具体的条件，而Preparing为SQL的架子，自己在开发过程中，无法直接在SQL编辑器中运行，每次都需要手动替换，费时费力，故想写一个小程序，只需要传入Parameters和Preparing即可生成真实的SQL。
image.png
如上图所示：Parameters与Preparing分离，故很难组装好了SQL自己测试，验证SQL正确与否。还需要手动拼接条件，将Preparing中的问号替换出来。
Parameters:
江苏(String), 松下(String), 16.01(String), 200(String), 大连锁(String), 波轮(String), 滚筒(String), 3400~3599(String), 4300~4399(String), 2000~2199(String), 1600~1799(String), 8.0-8.9kg(String), 7.0-7.9kg(String), 6.0-6.9kg(String), 变频(String), 定频(String), 江苏(String), 16.01(String), 200(String), 大连锁(String), 波轮(String), 滚筒(String), 3400~3599(String), 4300~4399(String), 2000~2199(String), 1600~1799(String), 8.0-8.9kg(String), 7.0-7.9kg(String), 6.0-6.9kg(String), 变频(String), 定频(String)
Preparing:
SELECT brand_sum.brand_current_sum_volume AS "volume", brand_sum.brand_current_sum_sales AS "sales", ROUND(brand_sum.brand_current_sum_sales / brand_sum.brand_current_sum_volume, 2) AS avgPrice, ROUND(brand_sum.brand_current_sum_volume::NUMERIC / industry_sum.industry_current_sum_volume::NUMERIC, 4) * 100 AS "salesMarketShare", ROUND(brand_sum.brand_current_sum_sales::NUMERIC / industry_sum.industry_current_sum_sales::NUMERIC, 4) * 100 AS "volumeMarketShare" FROM ( SELECT '1' AS type, SUM(volume) AS brand_current_sum_volume, SUM(sales) AS brand_current_sum_sales FROM wm_offline_monthly_fct WHERE province = ? AND brand_name = ? AND month_code = ? AND price_segment_group = ? AND channel_type = ? AND product_type in(?,?) AND price_segment in (?,?,?,?) AND capacity_segment in (?,?,?) AND product_tech in (?,?) ) AS brand_sum JOIN( SELECT '1' AS type, SUM(volume) AS industry_current_sum_volume, SUM(sales) AS industry_current_sum_sales FROM wm_offline_monthly_fct WHERE province = ? AND month_code = ? AND price_segment_group = ? AND channel_type = ? AND product_type in(?,?) AND price_segment in (?,?,?,?) AND capacity_segment in (?,?,?) AND product_tech in (?,?) ) AS industry_sum ON brand_sum.type = industry_sum.type
代码如下：
from tkinter import *
import tkinter.messagebox
import re
class MainWindow:
def buttonListener1(self, event):
data1 = self.text_parameter.get("0.0", "end")
data2 = self.text_preparing.get("0.0", "end")
data1 = re.split('\([\w\s]+\),\s', data1)
data1[len(data1)-1] = re.sub('\(.*\)\\n', "", data1[len(data1)-1])
print(data1)
print(data2)
j = 0
ttsql = ''
for s in data2:
if s != '?':
ttsql = ttsql + s
else:
ttsql = ttsql + '\'' + data1[j] + '\''
j = j + 1
print(ttsql)
self.text_result.delete(0.0, tkinter.END)
self.text_result.insert(INSERT, ttsql)
def selectText_preparing(self, event):
self.text_preparing.tag_add(tkinter.SEL, "1.0", tkinter.END)
return 'break'
def selectText_parameter(self, event):
self.text_parameter.tag_add(tkinter.SEL, "1.0", tkinter.END)
return 'break'
def selectText_result(self, event):
self.text_result.tag_add(tkinter.SEL, "1.0", tkinter.END)
return 'break'
def __init__(self):
self.frame = Tk()
self.frame.geometry("1024x768")
self.label_parameter = Label(self.frame, text="Parameters:")
self.label_preparing = Label(self.frame, text="Preparing:")
self.label_result = Label(self.frame, text="Result:")
self.text_parameter = Text(self.frame, height="8", width=100, background="grey", highlightbackground="orange")
self.text_preparing = Text(self.frame, height="8", width=100, background="grey", highlightbackground="orange")
self.text_result = Text(self.frame, height="14", width=100, background="grey")
self.label_parameter.grid(row=0, column=0)
self.label_preparing.grid(row=1, column=0)
self.button_ok = Button(self.frame, text="ok", width=10)
self.button_cancel = Button(self.frame, text="cancel", width=10)
self.text_parameter.grid(row=0, column=1)
self.text_preparing.grid(row=1, column=1)
self.button_ok.grid(row=3, column=0)
self.button_cancel.grid(row=3, column=1)
self.label_result.grid(row=4, column=0)
self.text_result.grid(row=4, column=1)
self.button_ok.bind("<ButtonRelease-1>", self.buttonListener1)
self.text_preparing.bind("<Command-Key-a>", self.selectText_preparing)
self.text_result.bind("<Command-Key-a>", self.selectText_result)
self.text_parameter.bind("<Command-Key-a>", self.selectText_parameter)
self.frame.mainloop()
frame = MainWindow()
此为Python3的脚本，因为本人用的是Mac，故只有Mac用户才可以Command+A进行全选，若为Windows用户需要手动将代码中的Command替换为Control即可实现全选。
image.png
如上图所示：传入Parameters和Preparing，点击ok，即可生成真实的SQL。
image.png
Format之后，可以看到整体的轮廓，其中绿色的为替换的条件。
