生成Mapper类名称： generate_class_mapperClass_name
CREATE DEFINER=`admin`@`%` PROCEDURE `generate_class_mapperClass_name`(in t_name varchar(255),in split_joint_str varchar(255), out property_name varchar(255))
BEGIN -- 原来的 procedure - sql5java,现在改名为 generate_class_mapperClass_name,用于生成类名称或者是映射类的名称
DECLARE n int DEFAULT 0; -- 特殊字符总个数
DECLARE i int DEFAULT 0; -- 循环变量
DECLARE ind int DEFAULT 0; -- 得到特殊字符的下标
DECLARE left_str varchar(255); -- 左边的字符串
DECLARE right_str varchar(255); -- 右边的字符串
DECLARE midd_char varchar(255); -- 中间的那个需要修改的字符
DECLARE str varchar(1024); -- 用于拼接的变量
DECLARE first_str varchar(255); -- 第一个字符
DECLARE join_str varchar(255); -- 拼接进来的字符串
SET i = 1;
SET str = t_name;
SET join_str = split_joint_str;
SET n = length(str)-length(REPLACE(str,"_",""))+1; -- 总共有多少次需要进行替换以及大小写变化
WHILE i < n DO
SET ind = LENGTH(SUBSTRING_INDEX(str,'_',i)); -- 特殊字符的下标
SET left_str = CONCAT(SUBSTRING_INDEX(str,'_',i),'_'); -- 特殊字符左边的字符串
SET right_str =
SUBSTRING(SUBSTRING_INDEX(str,'_',i-(n)),2,LENGTH(SUBSTRING_INDEX(str,'_',i-(n)))); -- 特殊字符右边的字符串
SET midd_char = UPPER(SUBSTRING(SUBSTRING_INDEX(str,'_',i-(n)),1,1)); -- 需要变化成大写的字符
SET str = concat(left_str,midd_char,right_str); -- 对str字符串变量进行重新赋值
SET i = i+1;
END WHILE;
SET first_str = UPPER(SUBSTRING(str,1,1));
SET str = SUBSTRING(REPLACE(str,"_",""),2);
SET property_name = CONCAT(first_str,str,join_str);
END
通过逗号分隔开字段取值：fieldValue_split_by_comma
CREATE DEFINER=`admin`@`%` PROCEDURE `fieldValue_split_by_comma`(in splitContent varchar(255))
BEGIN -- 原来的procedure - sql9java,现在改名为 field_split_by_comma
DECLARE n int DEFAULT 0; -- 特殊字符总个数
DECLARE test_n int DEFAULT 0; -- 特殊字符总个数
DECLARE i int DEFAULT 0; -- 循环变量
DECLARE ind int DEFAULT 0; -- 得到特殊字符的下标
DECLARE left_str varchar(30); -- 左边的字符串
DECLARE str_value varchar(30); -- value变量
DECLARE str varchar(255); -- 用于拼接的变量
create temporary table if not exists tmp_sql5java(num int,s_id varchar(255));
delete from tmp_sql5java;-- 将拆分出来的那个供应商编号新增入该表
SET i = 1;
SET str = splitContent;
SET n = length(str)-length(REPLACE(str,",",""))+1; -- 总共有多少次需要进行替换以及大小写变化
SET max_sp_recursion_depth = 3000;
WHILE i <= n DO
SET ind = LENGTH(SUBSTRING_INDEX(str,',',i)); -- 特殊字符的下标
SET left_str = SUBSTRING_INDEX(str,',',i); -- 特殊字符左边的字符串
SET test_n = LENGTH(SUBSTRING_INDEX(left_str,',',i)); -- 查看截取了的内容是否还有特殊字符
if(test_n<0) then
SET str_value = left_str;
else
SET str_value = SUBSTRING_INDEX(left_str,',',-1);
end if;
insert into tmp_sql5java values(i,str_value);
SET i = i+1;
END WHILE;
END
生成Mapper文件内容：generate_base_mapper
存储过程
generate_property_name
在这里
CREATE DEFINER=`admin`@`%` PROCEDURE `generate_base_mapper`(in t_name char(255))
BEGIN -- 原来的 procedure - sql6java,现在改名为 generate_base_mapper
-- t_name 即table_name 上送参数为表名称
DECLARE columnName varchar(5000); -- 原型字段名称
DECLARE property varchar(255); -- 大小写驼峰字段名称
DECLARE mapper varchar(255); -- 表映射名称
DECLARE class varchar(255); -- 类名称
DECLARE varClass varchar(255); -- 变量类名称
DECLARE str text(50000); -- 字符串变量
DECLARE columnsStr varchar(5000); -- 列字符串变量
DECLARE insertColumnsStr varchar(5000); -- 列字符串变量
DECLARE propertyStr varchar(5000); -- 属性字符串变量
DECLARE columnPropertyStr varchar(5000); -- 列属性字符串变量
DECLARE findByIdStr varchar(5000); -- 通过主键查找对象方法
DECLARE insertRecord varchar(5000); -- 新增记录
DECLARE selectCount varchar(5000); -- 新增记录
DECLARE findAll varchar(5000); -- 新增记录
DECLARE head varchar(5000); -- 新增记录
DECLARE resultMapper text(10000); -- 新增记录
DECLARE n int DEFAULT 0 ; -- 总共的字段数
DECLARE i int DEFAULT 0; -- 循环时的变量
create temporary table if not exists tmp_sql2java(property_name varchar(255));
delete from tmp_sql2java;
SET i = 1;
SET str = '';
SET columnsStr = '';
SET insertColumnsStr = '';
SET propertyStr = '';
SET columnPropertyStr = '';
SET findByIdStr = '';
SET insertRecord = '';
SET selectCount =''; -- 新增记录
SET findAll ='';
SET property = '';
SET mapper = '';
SET class = '';
SET varClass = '';
SET resultMapper = '';
CALL generate_class_mapperClass_name(t_name,'Mapper',@mapperName); -- 通过表名称获取得到Mapper类名称
CALL generate_class_mapperClass_name(t_name,'',@className); -- 通过表名称获取得到Model类名称
SET mapper = @mapperName;
SET class = @className;
SET head = CONCAT(
'<?xml version="1.0" encoding="UTF-8"?>\n',
'<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">\n',
'<mapper namespace="com.yourshares.entity.dao.',
mapper,
'">\n',
'<resultMap id="BaseResultMap" type="com.yourshares.entity.model.',
class,
'">\n'
);
select COUNT(*) into n from information_schema.columns where table_name = t_name; -- 总共的字段
WHILE i <= n DO -- 循环
select column_name into columnName from information_schema.`columns` where table_name = t_name and ordinal_position=i;
CALL generate_property_name(columnName,@propertyName); -- 通过表字段获取得到属性名称
SET property = @propertyName; -- 赋值
CALL generate_class_mapperClass_name(columnName,'Mapper',@varClassName);
SET varClass = @varClassName;
select
CONCAT(
columnPropertyStr,
case
when column_key in ('PRI') then '<id column="'
when column_key not in ('PRI','MUL') then '<result column="'
when column_key in ('MUL') then '<association column="'
end,
column_name,
CONCAT('" property="', property, '" '),
case
when column_key in ('PRI') then
CONCAT(
'jdbcType="' ,
case
when data_type in ('int','smallint') then 'INTEGER'
else UPPER(data_type)
end,
'" />\n'
)
when column_key not in ('PRI','MUL') then
CONCAT(
'jdbcType="' ,
case
when data_type in ('int','smallint') then 'INTEGER'
else UPPER(data_type)
end,
'" />\n'
)
when column_key in ('MUL') then
CONCAT(
'select="com.yourshares.entity.dao.',
@varClassName,
'.findById"></association>\n'
)
else ''
end
) into columnPropertyStr
from
information_schema.columns
where
table_name = t_name and ordinal_position=i;
SET str = CONCAT(str,columnPropertyStr); -- 拼接resultMap节点当中的子节点
select CONCAT(columnsStr,'a.',column_name,',') into columnsStr from information_schema.columns where table_name = t_name and ordinal_position=i; -- 所有字段列
select CONCAT(insertColumnsStr,column_name,',') into insertColumnsStr from information_schema.columns where table_name = t_name and ordinal_position=i; -- 所有字段列
select CONCAT(propertyStr,'#{',property,'},') into propertyStr from information_schema.columns where table_name = t_name and ordinal_position=i; -- 所有属性列
SET i = i+1;
END WHILE;
SET columnsStr = SUBSTRING(columnsStr,1,LENGTH(columnsStr)-1);
SET propertyStr = SUBSTRING(propertyStr,1,LENGTH(propertyStr)-1);
SET findByIdStr = CONCAT(
'<select id="findById" parameterType="java.lang.Integer" resultMap="BaseResultMap">\n',
'select ', columnsStr, ' from ', t_name, ' a where a.id=#{value} \n',
'</select>\n'
); -- 查询语句
SET insertRecord = CONCAT(
'<insert id="insertRecord" parameterType="com.yourshares.entity.model.', class, '">\n',
' insert into ',t_name, '\n','(',insertColumnsStr,')\n', 'values\n', '(',propertyStr,')\n',
'</insert>\n'
); -- 新增语句
SET selectCount = CONCAT(
'','',''
);
SET selectCount = CONCAT(
'','',''
);
SET resultMapper = CONCAT(
head,
columnPropertyStr,
'</resultMap>\n',
findByIdStr,
insertRecord,
'</mapper>'
);
select resultMapper;
END
生成原型Model层内容不区分大小写
CREATE DEFINER=`admin`@`%` PROCEDURE `generate_model_not_case`(in t_name char(30))
begin -- 原来的procedure - sql2java,现在改名为 generate_model_not_case,用于生成model层的注释以及属性，属性名称未做驼峰处理
select
concat(
'/*',
column_comment,
'*/\n',
'private ',
case
when data_type in ('varchar', 'char', 'text') then
'String'
when data_type in ('int', 'tinyint') then
'Integer'
when data_type in ('bigint') then
'Long'
when data_type in ('datetime') then
'Date'
when data_type in ('bit', 'boolean') then
'Boolean'
else
'类型不确定'
end,
' ',
column_name,
';'
) as java
from
information_schema. columns
where
table_name = t_name;
end
下面这个存储过程是david让给前段弄的，也是model的差不多的意思
CREATE DEFINER=`admin`@`%` PROCEDURE `generate_view_interface_model`(in t_name char(125))
BEGIN -- 原来的procedure - sql7java, 用于前端生成基本属性映射，现在改名为 generate_view_interface_model
DECLARE columnName varchar(1000); -- 字段名称
DECLARE class varchar(125);
DECLARE property varchar(5000); -- 字段名称
DECLARE columnPropertyStr varchar(5000); -- 列属性字符串变量
DECLARE resultInterface varchar(5000); -- 新增记录
DECLARE str varchar(5000); -- 字段名称
DECLARE n int DEFAULT 0 ; -- 总共的字段数
DECLARE i int DEFAULT 0; -- 循环时的变量
create temporary table if not exists tmp_sql2java(property_name varchar(5000));
delete from tmp_sql2java;
SET i = 1;
SET columnPropertyStr = '';
SET str = '';
SET resultInterface = '';
CALL generate_class_mapperClass_name(t_name,'',@className);
SET class = @className;
select count(*) into n from information_schema.columns where table_name = t_name;
WHILE i <= n DO
select column_name into columnName from information_schema.`columns` where table_name = t_name and ordinal_position=i;
CALL generate_property_name(columnName,@propertyName);
SET property = @propertyName;
select
concat(
'/*',
column_comment,
'*/\n',
property,
case
when is_nullable in ('YES') then
'?'
else
''
end,
":",
case
when data_type in ('varchar', 'char', 'text') then
'string'
else
'number'
end,
';\n'
) as java into columnPropertyStr
from
information_schema. columns
where
table_name = t_name and ordinal_position=i;
SET str = CONCAT(str,columnPropertyStr);
SET i = i+1;
END WHILE;
SET resultInterface = CONCAT('export interface ',class,' {\n',str,'}');
select resultInterface;
END
后面就是一些关于业务逻辑的一些存储过程，这里就不贴了；
贴出来也不太好；
存储过程一般最好不去用在业务上面；
别问，问就是我不知道；
版权声明：本文为qq_43409111原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。