简介
- 我一般数据表结构是用PowerDesigner来建, 建完就需要建Model也即一堆javaBean, 如果没有辅助工具挨个建要累死, 于是用python写了这样一个辅助小工具, 仅需复制下PowerDesigner中Preview中的sql语句, 然后双击下工具就直接生成出所有数据表的javaBean了
分析
- 原理很简单, 解析SQL语句用了正则表达式, 将关键信息封装起来, 解析完返回后即可以进行拼凑成JavaBean了
- SQL的解析如下, 其中get_tables即解析函数, 解析完最终返回一个Table对象的列表, 每张表对应一个Table对象, Table对象中有个Field列表, Field其实就是表里面的列
class Field:
def __init__(self, name='', type='', comment=''):
self.name = name
self.type = type
self.comment = comment
self.is_not_null = False
self.is_key = False
self.is_auto_increase = False
def __repr__(self):
return "Field(name=%s, type=%s, comment=%s)" % (self.name, self.type, self.comment)
class Table:
def __init__(self):
self.name = ''
self.comment = ''
self.fields = []
def __repr__(self):
return "Table(name=%s, comment=%s, field=%s)" % (self.name, self.comment, repr(self.fields))
def get_tables(sql_text):
"""
sql文本转Table对象列表
:param sql_text: sql文本
:return: list of Table
"""
tables = []
ret = re.findall(r'(create\stable\s(.+)[^(]+\(([^;]+)\);)', sql_text)
for per_table_ret in ret:
table_name = per_table_ret[1].replace('\r', '').replace('\n', '')
table_body = per_table_ret[2]
table_body_lines = list(map(lambda x: x.strip(), table_body.strip().splitlines()))
new_table = Table()
# 遍历( ... )里面的每一行
key_names = []
for line in table_body_lines:
if 'primary key' in line:
key_names.append(line[line.find('(') + 1:line.find(')')])
if 'key ' not in line:
ret_line = re.search(r'([\w_]+)\s+([^\s,]+)\s?(.*)', line)
field_name = ret_line.group(1)
field_type = ret_line.group(2)
field_tail = ret_line.group(3)
field_comment = '--'
if 'comment ' in field_tail:
field_comment = re.search(r'[\w\s]+\'(.+)\'', line).group(1)
new_field = Field(field_name, field_type, field_comment)
if 'not null' in line:
new_field.is_not_null = True
if 'auto_increment' in line:
new_field.is_auto_increase = True
new_table.fields.append(new_field)
# 处理primary key
for per_field in new_table.fields:
if per_field.name in key_names:
per_field.is_key = True
table_comment = 'null'
ret_comment = re.search(
r'alter\stable\s%s.+\'(.+)\'' % table_name.strip(), sql_text)
if ret_comment is not None:
table_comment = ret_comment.group(1)
new_table.comment = table_comment
new_table.name = table_name
tables.append(new_table)
return tables
- 解析完得到Table的列表后, 剩下的就非常简单了, toString也好getter setter也好最终也仅仅是字符串的拼拼凑凑而已; 比较麻烦也就toString, 参照IDEA的toString格式的话, String类型在toString里面两边还挂着对单引号, 加上各种缩进转义啥的折腾起来有点乱. 代码如下
TO_STRING_TEMPLATE = """
@Override
public String toString() {{
return {}
}}
"""
def gen_tostring(class_name, typefield_list):
"""
生成并返回tostring
:param class_name
:param typefield_list: 字段类型+名的元组列表 eg:[(int,id), (String,name), (Date,createTime)]
:return: str
"""
tostring = '\"' + class_name + '{\" +\n'
tostring += '\t\t\"' + \
typefield_list[0][1] + '=\" + ' + typefield_list[0][1] + ' +\n'
typefield_list_ext_first = typefield_list[1:]
for type_field in typefield_list_ext_first:
single_quote_left = r"'" if type_field[0] == 'String' else ""
single_quote_right = r" + '\''" if type_field[0] == 'String' else ""
tostring += '\t\t\", ' + type_field[1] + '=' + single_quote_left + '\" + ' + type_field[
1] + single_quote_right + ' + \n'
tostring += "\t\t\'}\';"
return TO_STRING_TEMPLATE.format(tostring)
@Override
public String toString() {
return "Job{" +
"id=" + id +
", name='" + name + '\'' +
", description='" + description + '\'' +
", cronExpression='" + cronExpression + '\'' +
", startTime='" + startTime + '\'' +
", endTime='" + endTime + '\'' +
", exeCount=" + exeCount +
", maxExeCount=" + maxExeCount +
", status='" + status + '\'' +
", className='" + className + '\'' +
", methodName='" + methodName + '\'' +
", delayTime=" + delayTime +
", intervalTime=" + intervalTime +
'}';
}
- 至于复制完直接双击就可以生成JavaBean, 这个则直接去拿剪贴板的数据然后判断是否是属于sql语句, 如果是则直接生成JavaBean
Ps
- Sql2Bean本来给自己用的, 所以夹了点私货, 如多了@DescAnnotation注解等, 只要把Sql2Bean里面额IS_SIMBA变量置为False即可生成不带私货的JavaBean 🙂
