Mysql+MyIbatis3 利用存储过程创建 更

mysql存储过程如下
//增加分表
DELIMITER $$
DROP PROCEDURE IF EXISTS pro_create_table $$
CREATE PROCEDURE pro_create_table(in i_table_name varchar(50),in fields varchar(1000))
BEGIN
SET @csql=concat("create table if not exists ",i_table_name ,fields);
PREPARE create_stmt from @csql;
EXECUTE create_stmt;
END $$
DELIMITER ;
//删除分表
DELIMITER $$
DROP PROCEDURE IF EXISTS pro_delete_table $$
CREATE PROCEDURE pro_delete_table(in i_table_name varchar(50))
BEGIN
SET @csql=concat("DROP table if exists ",i_table_name);
PREPARE create_stmt from @csql;
EXECUTE create_stmt;
END $$
DELIMITER ;
//修改分表
DELIMITER $$
DROP PROCEDURE IF EXISTS pro_update_table $$
CREATE PROCEDURE pro_update_table(in i_table_oldname varchar(50),in i_table_newname varchar(50))
BEGIN
SET @csql=CONCAT("rename table ",i_table_oldname," to ",i_table_newname);
PREPARE create_stmt from @csql;
EXECUTE create_stmt;
END $$
DELIMITER ;
//显示存储过程状态
show procedure status
//重命名表名
rename table t_ms_version to t_ms_version1
myibatis3 mapping脚本 Submeter.xml
<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.huawei.tds.syncInfoManager.dao.IConfigInfoDao">
<!-- 终端信息表字段 -->
<sql id="terminerInfoFileds">(
id INTEGER not null auto_increment,
IMEI varchar(32) not null,
IMSI varchar(32) not null,
clientIP varchar(64) not null,
checkTime timestamp,
result numeric(3,0),
updateType numeric(3,0),
deviceName varchar(64),
firmwareVersion varchar(64),
hardwareVersion varchar(64),
dashboardVersion varchar(64),
dashboardFlashVersion varchar(64),
appName varchar(64),
clientOS varchar(32),
osLanguage varchar(32),
clientLanguage varchar(32),
cVersionValue varchar(32),
network varchar(32),
createTime timestamp,
checkTimes int not null default 1,
primary key (id)
)
</sql>
<!-- 终端信息表分表存储过程 -->
<select id="createTerminalInfoTable" parameterType="map"
statementType="CALLABLE">
{call
pro_create_table(#{tableName,mode=IN,javaType=String,jdbcType=VARCHAR},
'
<include refid="terminerInfoFileds" />
'
)}
</select>
<!-- 下载信息表字段 -->
<sql id="updateFileds">
(
ID INTEGER not null auto_increment,
userID varchar(32) not null,
clientIP varchar(128),
clientVersion varchar(64),
versionID varchar(32),
eventId numeric(3,0),
eventTime timestamp default CURRENT_TIMESTAMP,
description varchar(256),
primary key (ID)
)
</sql>
<!-- 版本下载信息表分表存储过程 -->
<select id="createUpdateTable" parameterType="map"
statementType="CALLABLE">
{call
pro_create_table(#{tableName,mode=IN,javaType=String,jdbcType=VARCHAR},
'
<include refid="updateFileds" />
'
)}
</select>
<!-- 删除分表 -->
<select id="dropSubmeterTable" parameterType="map"
statementType="CALLABLE">
{call
pro_delete_table(#{tableName,mode=IN,javaType=String,jdbcType=VARCHAR})}
</select>
<!-- 更新分表 -->
<select id="updateSubmeterTable" parameterType="map" statementType="CALLABLE">
{call pro_update_table(#{oldTableName,mode=IN,javaType=String,jdbcType=VARCHAR},#{newTableName,mode=IN,javaType=String,jdbcType=VARCHAR})}
</select>
<update id="updateVersionQueryStatic" parameterType="map" >
update T_QS_VERSIONQUERY set sitecode=#{newSitecode} where sitecode = #{oldSitecode}
</update>
</mapper>

延伸阅读