mysql DBUtils 存储过程代码生成
-- call p_CreateTableDal ('UAlarmData','IndustryParkForm','告警配置','Alarm')
DROP PROCEDURE IF EXISTS p_CreateTableDal;
CREATE DEFINER=`root`@`%` PROCEDURE `p_CreateTableDal`( TableName VARCHAR(50), DataBaseName VARCHAR(50), FDescribe VARCHAR(50),Controller VARCHAR(50))
BEGIN
#编辑条件
set @FType='';
#主键类型
set @PrimaryType='';
#方法参数
set @ParaStr='';
#修改的列表
set @UpdateColumn='';
#修改的条件
set @UpdateWhere ='';
#追加的条件
set @UpdateObjectKey='';
#修改的Object
set @UpdateObject ='';
#修改的参数
set @UpdatePara ='';
#新增列
set @InserColumn ='';
#新增的参数
set @InserObject ='';
#新增FGUID
set @InserFGUID ='';
#新增的Value
set @InserValue ='';
#参数类型1
set @DataString ='char,varchar,datetime,text';
#参数类型2
set @DataInt ='int,long,bit';
#参数类型3
set @DataFloat ='float';
#参数类型3
set @DataTime ='datetime,date,time';
#存放Dal
set @SelectData='';
set @DeleteData='';
set @AddUpdate='';
#存放server
set @SelectServer='';
set @DeleteServer='';
set @AddUpdateServer='';
#存放server
set @SelectController='';
set @DeleteController='';
set @AddUpdateController='';
#存放json
set @SelectJson='';
set @DeleteJson='';
set @AddUpdateJson='';
#存放Controller参数
set @jsonObject='';
#存放Token
set @FTokenID='';
SELECT FTokenID into @FTokenID from TToken ORDER BY FCreateTime desc limit 1;
#新增修改数据
BEGIN
#生成参数 COLUMN_NAME,DATA_TYPE
SELECT GROUP_CONCAT((case
when @DataString LIKE CONCAT('%',DATA_TYPE,'%') then 'String'
when @DataInt LIKE CONCAT('%',DATA_TYPE,'%') then 'int'
when @DataFloat LIKE CONCAT('%',DATA_TYPE,'%') then 'float'
else 'String' end),' ',
COLUMN_NAME),
GROUP_CONCAT('\n',(case
when @DataString LIKE CONCAT('%',DATA_TYPE,'%') then 'jsonObject.getString("'
when @DataInt LIKE CONCAT('%',DATA_TYPE,'%') then 'jsonObject.getIntValue("'
when @DataFloat LIKE CONCAT('%',DATA_TYPE,'%') then 'jsonObject.getFloatValue("'
else 'jsonObject.getString("' end),
COLUMN_NAME,'")'),
GROUP_CONCAT((case
when @DataTime LIKE CONCAT('%',DATA_TYPE,'%') then CONCAT('"',COLUMN_NAME,'":"',NOW(),'"')
when @DataString LIKE CONCAT('%',DATA_TYPE,'%') then CONCAT('"',COLUMN_NAME,'":"测试"')
when @DataInt LIKE CONCAT('%',DATA_TYPE,'%') then CONCAT('"',COLUMN_NAME,'":1')
when @DataFloat LIKE CONCAT('%',DATA_TYPE,'%') then CONCAT('"',COLUMN_NAME,'":1')
else CONCAT('"',COLUMN_NAME,'":"测试"') end))
into @ParaStr,
@jsonObject,
@AddUpdateJson
from INFORMATION_SCHEMA.COLUMNS where table_name = TableName and TABLE_SCHEMA=DataBaseName;
#修改参数生成
BEGIN
#生成修改条件
SELECT
GROUP_CONCAT( kc.COLUMN_NAME,'=? ' SEPARATOR ' and '),
GROUP_CONCAT(kc.COLUMN_NAME),
GROUP_CONCAT((case
when @DataString LIKE CONCAT('%',DATA_TYPE,'%') then CONCAT('StringUtils.isEmpty(',kc.COLUMN_NAME,')')
when @DataInt LIKE CONCAT('%',DATA_TYPE,'%') then CONCAT(kc.COLUMN_NAME,'==0')
when @DataFloat LIKE CONCAT('%',DATA_TYPE,'%') then CONCAT(kc.COLUMN_NAME,'==0')
else CONCAT('StringUtils.isEmpty(',kc.COLUMN_NAME,')') end)
) ,
GROUP_CONCAT((case
when @DataString LIKE CONCAT('%',DATA_TYPE,'%') then 'String'
when @DataInt LIKE CONCAT('%',DATA_TYPE,'%') then 'int'
when @DataFloat LIKE CONCAT('%',DATA_TYPE,'%') then 'int'
else 'String' end)
)
into
@UpdateWhere,
@UpdateObjectKey,
@FType,
@PrimaryType
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc
LEFT JOIN INFORMATION_SCHEMA.COLUMNS sc on kc.TABLE_SCHEMA=sc.TABLE_SCHEMA and kc.table_name=sc.table_name and kc.COLUMN_NAME=sc.COLUMN_NAME
WHERE kc.table_name = TableName AND constraint_name = 'PRIMARY' and kc.TABLE_SCHEMA=DataBaseName;
#生成修改参数
SELECT GROUP_CONCAT(sc.COLUMN_NAME ,'=? '),GROUP_CONCAT(sc.COLUMN_NAME) into @UpdateColumn,@UpdateObject FROM INFORMATION_SCHEMA.COLUMNS sc
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc on kc.TABLE_SCHEMA=sc.TABLE_SCHEMA and kc.table_name=sc.table_name and kc.COLUMN_NAME=sc.COLUMN_NAME
WHERE sc.table_name = TableName AND ifnull(constraint_name,'') != 'PRIMARY' and sc.TABLE_SCHEMA=DataBaseName and sc.COLUMN_NAME not in ('FCreatorGUID','FCreateTime');
END;
#方法参数
#SELECT @ParaStr;
#拼接修改语句
#SELECT CONCAT('update ',TableName ,' set ',@UpdateColumn,' where 1=1 ',@UpdateWhere);
#拼接object
#SELECT CONCAT('Object[] params = new Object[] { ',@UpdateObject,',',@UpdateObjectKey,'}');
#新增参数生成
BEGIN
if(@PrimaryType='String') THEN
set @InserFGUID=CONCAT(@UpdateObjectKey,' = UUID.randomUUID().toString();');
SELECT GROUP_CONCAT(sc.COLUMN_NAME),GROUP_CONCAT(sc.COLUMN_NAME),GROUP_CONCAT('?') into @InserColumn,@InserObject,@InserValue FROM INFORMATION_SCHEMA.COLUMNS sc
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc on kc.TABLE_SCHEMA=sc.TABLE_SCHEMA and kc.table_name=sc.table_name and kc.COLUMN_NAME=sc.COLUMN_NAME
WHERE sc.table_name = TableName and sc.TABLE_SCHEMA=DataBaseName;
ELSE
SELECT GROUP_CONCAT(sc.COLUMN_NAME),GROUP_CONCAT(sc.COLUMN_NAME),GROUP_CONCAT('?') into @InserColumn,@InserObject,@InserValue FROM INFORMATION_SCHEMA.COLUMNS sc
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc on kc.TABLE_SCHEMA=sc.TABLE_SCHEMA and kc.table_name=sc.table_name and kc.COLUMN_NAME=sc.COLUMN_NAME
WHERE sc.table_name = TableName AND ifnull(constraint_name,'') != 'PRIMARY' and sc.TABLE_SCHEMA=DataBaseName;
END if;
end;
SELECT CONCAT('/**
* 新增修改',FDescribe,'
* @return
*/
public int DAddOrUpdate',TableName,'(',@ParaStr,') {
Connection conn = DBUtils.Instance().getMainConnection();
try {
int index = 0;
String sql="";
if (',@FType,') {
',@InserFGUID,'
Object[] params = new Object[] { ',@InserObject,' };
sql = "INSERT INTO ',TableName,'(',@InserColumn,') VALUES (',@InserValue,');";
index = runner.update(conn, sql, params);
} else {
Object[] params = new Object[] { ',@UpdateObject,',',@UpdateObjectKey,'};','
sql = "','update ',TableName ,' set ',@UpdateColumn,' where ',@UpdateWhere,'";
index = runner.update(conn, sql, params);
}
return index;
} catch (SQLException ex) {
General.Instance().Log("Error", java.util.logging.Level.WARNING,
"D',Controller,'.DAddOrUpdate',TableName,':" + ex.getMessage());
return 0;
} finally {
DBUtils.Instance().close(null, null, conn);
}
}') into @AddUpdate;
#生成server
SELECT CONCAT('/**
* 新增修改',FDescribe,'
* @return
*/
public int SAddOrUpdate',TableName,'(',@ParaStr,'){
return d',Controller,'.DAddOrUpdate',TableName,'(',@InserObject,');
}') into @AddUpdateServer;
#生成Controller
SELECT CONCAT('/**
* 新增/修改',FDescribe,'
*
* @param jsonObject
* @return
*/
@RequestMapping("/AddOrUpdate',TableName,'")
@ResponseBody
public MBackResult AddOrUpdate',TableName,'(@RequestBody JSONObject jsonObject) {
if (CheckToken(jsonObject)) {
S',Controller,' s',Controller,' = new S',Controller,'();
this._BackResult.setFobject(s',Controller,'.SAddOrUpdate',TableName,'(',@jsonObject,'));
}
return this._BackResult;
}') into @AddUpdateController;
#生成JSON
SET @AddUpdateJson=CONCAT('{"FTokenID":"',@FTokenID,'",',@AddUpdateJson,'}');
END;
#查询条件
set @SelectWhere='';
#排序
set @SelectOrder='';
#分页查询数据
BEGIN
SELECT ifnull(GROUP_CONCAT(sc.COLUMN_NAME,' like ''%"+SearchKey+"%''' SEPARATOR ' or '),'') into @SelectWhere FROM INFORMATION_SCHEMA.COLUMNS sc
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc on kc.TABLE_SCHEMA=sc.TABLE_SCHEMA and kc.table_name=sc.table_name and kc.COLUMN_NAME=sc.COLUMN_NAME
WHERE sc.table_name = TableName and sc.TABLE_SCHEMA=DataBaseName and sc.COLUMN_NAME like '%name%';
if(@PrimaryType='String') THEN
set @SelectOrder=' FLastUpdateTime desc';
ELSE
SELECT GROUP_CONCAT(sc.COLUMN_NAME,' desc') into @SelectOrder FROM INFORMATION_SCHEMA.COLUMNS sc
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc on kc.TABLE_SCHEMA=sc.TABLE_SCHEMA and kc.table_name=sc.table_name and kc.COLUMN_NAME=sc.COLUMN_NAME
WHERE sc.table_name = TableName AND ifnull(constraint_name,'') = 'PRIMARY' and sc.TABLE_SCHEMA=DataBaseName;
END if;
#生成查询Dal
SELECT CONCAT('/**
* 分页查询',FDescribe,'
*
* @param SearchKey
* @param PageIndex
* @param PageSize
* @return
*/
public Map<String, Object> DQueryPage',TableName,'(String SearchKey,int PageIndex,int PageSize) {
Connection conn = DBUtils.Instance().getMainConnection();
try {
String whereSQL = "";
if (!SearchKey.isEmpty()){
whereSQL = whereSQL +" and (',@SelectWhere,')";
}
String countsql = "SELECT COUNT(1) FTotalCount FROM ',TableName,' WHERE 1=1 "+ whereSQL;
Map<String, Object> mapInfo = runner.query(conn, countsql, new MapHandler());
String sql = "SELECT * FROM ',TableName,' WHERE 1=1 "
+ whereSQL + " ORDER BY ',@SelectOrder,' limit " + (PageIndex - 1) * PageSize + "," + PageSize;
List<Map<String, Object>> list = runner.query(conn, sql, new MapListHandler());
mapInfo.put("Data", General.Instance().GetPageListAddRowNum(list, PageIndex, PageSize));
return mapInfo;
} catch (SQLException ex) {
General.Instance().Log("Error", java.util.logging.Level.WARNING,
"D',Controller,'.DQueryPage',TableName,':" + ex.getMessage());
return null;
} finally {
DBUtils.Instance().close(null, null, conn);
}
}') into @SelectData;
#生成server
SELECT CONCAT('/**
* 分页查询',FDescribe,'
* @param PageIndex
* @param PageSize
* @return
*/
public Map<String, Object> SQueryPage',TableName,'(String SearchKey, int PageIndex, int PageSize) {
PageIndex = PageIndex > 0 ? PageIndex : 1;
PageSize = PageSize > 0 ? PageSize : 10;
return d',Controller,'.DQueryPage',TableName,'(SearchKey,PageIndex, PageSize);
}') into @SelectServer;
#生成Controller
SELECT CONCAT('/**
* 分页查询',FDescribe,'
* @param jsonObject
* @return
*/
@RequestMapping("/QueryPage',TableName,'")
@ResponseBody
public MBackResult QueryPage',TableName,'(@RequestBody JSONObject jsonObject) {
if (CheckToken(jsonObject)) {
S',Controller,' s',Controller,' = new S',Controller,'();
this._BackResult.setFobject(
sMeetingRoom.SQueryPage',TableName,'(
jsonObject.getString("SearchKey"),
jsonObject.getIntValue("PageIndex"),
jsonObject.getIntValue("PageSize")
));
}
return this._BackResult;
}') into @SelectController;
#生成JSON
SET @SelectJson=CONCAT('{"FTokenID":"',@FTokenID,'","SearchKey":"","PageIndex":1,"PageSize":10}');
END;
#删除条件
set @DeleteWhere='';
#删除对象
set @DeleteObject='';
set @DeleteJsonObject='';
set @DeletePara='';
#删除数据
BEGIN
SELECT
GROUP_CONCAT(sc.COLUMN_NAME ,'=? ' SEPARATOR ' and '),
GROUP_CONCAT(sc.COLUMN_NAME) ,
GROUP_CONCAT((case
when @DataString LIKE CONCAT('%',DATA_TYPE,'%') then 'String'
when @DataInt LIKE CONCAT('%',DATA_TYPE,'%') then 'int'
when @DataFloat LIKE CONCAT('%',DATA_TYPE,'%') then 'float'
else 'String' end),' ',
sc.COLUMN_NAME),
GROUP_CONCAT('\n',(case
when @DataString LIKE CONCAT('%',DATA_TYPE,'%') then 'jsonObject.getString("'
when @DataInt LIKE CONCAT('%',DATA_TYPE,'%') then 'jsonObject.getIntValue("'
when @DataFloat LIKE CONCAT('%',DATA_TYPE,'%') then 'jsonObject.getFloatValue("'
else 'jsonObject.getString("' end),
sc.COLUMN_NAME,'")'),
GROUP_CONCAT((case
when @DataTime LIKE CONCAT('%',DATA_TYPE,'%') then CONCAT('"',sc.COLUMN_NAME,'":"',NOW(),'"')
when @DataString LIKE CONCAT('%',DATA_TYPE,'%') then CONCAT('"',sc.COLUMN_NAME,'":"测试"')
when @DataInt LIKE CONCAT('%',DATA_TYPE,'%') then CONCAT('"',sc.COLUMN_NAME,'":1')
when @DataFloat LIKE CONCAT('%',DATA_TYPE,'%') then CONCAT('"',sc.COLUMN_NAME,'":1')
else CONCAT('"',sc.COLUMN_NAME,'":"测试"') end))
into
@DeleteWhere,
@DeleteObject,
@DeletePara ,
@DeleteJsonObject,
@DeleteJson
FROM INFORMATION_SCHEMA.COLUMNS sc
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc on kc.TABLE_SCHEMA=sc.TABLE_SCHEMA and kc.table_name=sc.table_name and kc.COLUMN_NAME=sc.COLUMN_NAME
WHERE sc.table_name = TableName AND ifnull(constraint_name,'') = 'PRIMARY' and sc.TABLE_SCHEMA=DataBaseName and sc.COLUMN_NAME not in ('FCreatorGUID','FCreateTime');
#生成Dal
SELECT CONCAT(' /**
* 删除',FDescribe,'
* @return
*/
public int DDelete',TableName,'(',@DeletePara,') {
Connection conn = DBUtils.Instance().getMainConnection();
try {
String sql = "DELETE FROM ',TableName,' where ',@DeleteWhere,'";
return runner.update(conn, sql, ',@DeleteObject,');
} catch (SQLException ex) {
General.Instance().Log("Error", java.util.logging.Level.WARNING,
"D',Controller,'.DDelete',TableName,':" + ex.getMessage());
return 0;
} finally {
DBUtils.Instance().close(null, null, conn);
}
}') into @DeleteData;
#生成Server
SELECT CONCAT('/**
* 删除',FDescribe,'
* @return
*/
public int SDelete',TableName,'(',@DeletePara,') {
return d',Controller,'.DDelete',TableName,'(',@DeleteObject,');
}') into @DeleteServer;
#生成Controller
SELECT CONCAT('/**
* 删除',FDescribe,'
*
* @param jsonObject
* @return
*/
@RequestMapping("/Delete',TableName,'")
@ResponseBody
public MBackResult Delete',TableName,'(@RequestBody JSONObject jsonObject) {
if (CheckToken(jsonObject)) {
S',Controller,' s',Controller,' = new S',Controller,'();
this._BackResult.setFobject(s',Controller,'.SDelete',TableName,'(',@DeleteJsonObject,'));
}
return this._BackResult;
}') into @DeleteController;
#生成Json
set @DeleteJson=CONCAT('{"FTokenID":"',@FTokenID,'",',@DeleteJson,'}');
END;
SELECT @SelectData as 'dal',@SelectServer as 'Server',@SelectController as 'Controller',@SelectJson as 'json',CONCAT('/',Controller,'/QueryPage',TableName) 'api'
UNION all
SELECT @AddUpdate as 'dal', @AddUpdateServer as 'Server',@AddUpdateController as 'Controller' ,@AddUpdateJson as 'json',CONCAT('/',Controller,'/AddOrUpdate',TableName) 'api'
UNION all
SELECT @DeleteData as 'dal',@DeleteServer as 'Server',@DeleteController as 'Controller',@DeleteJson as 'json',CONCAT('/',Controller,'/Delete',TableName) 'api' ;
END;