为避免数据库表过大,要求按月分表。当时是先创建了两年的数据库表,然后存储数据的时候,根据当前日期的年月确定存入的数据库表,xml中的插入语句如下:
<insert id="insertCloudSwitch" parameterType="CloudSwitch">
<selectKey keyProperty="id" resultType="String" order="BEFORE">
select REPLACE(UUID(),"-","")
</selectKey>
insert into
<if test="tableName!=null and tableName!=''">
${tableName}
</if>
(id,phone,poldswitch,pnowswitch,uoldswitch,unowswitch,timestamp)
values(#{id},#{cloudSwitch.phone},#{cloudSwitch.poldswitch},#{cloudSwitch.pnowswitch},#{cloudSwitch.uoldswitch},#{cloudSwitch.unowswitch},#{cloudSwitch.timestamp})
</insert>
由于表名tableName是作为参数传进来的,在这里需要用到${tableName}。
由于$存在sql注入的风险,但是表名无法通过#来传,通过查阅资料,决定利用存储过程,把sql语句写到数据库的存储过程中去。
1.创建存储过程语句如下:
CREATE PROCEDURE insert_cloudswitch(IN talbleName varchar(50),IN id VARCHAR(50),IN phone VARCHAR(50),IN poldswitch INT(3),IN pnowswitch INT(3),IN uoldswitch INT(3),IN unowswitch INT(3),IN timestamp VARCHAR(50))
BEGIN
set @tb = CONCAT("insert into ",talbleName,"(id,phone,poldswitch,pnowswitch,uoldswitch,unowswitch,timestamp)VALUES('",id,"','",phone,"',",poldswitch,",",pnowswitch,",",uoldswitch,",",unowswitch,",'",timestamp,"')");
prepare create_stmt from @tb;
execute create_stmt;
END;
创建存储过程时遇到了一个问题,开始我是这样写的:
CREATE PROCEDURE insert_cloudswitch(IN talbleName varchar(50),IN id VARCHAR(50),IN phone VARCHAR(50),IN poldswitch INT(3),IN pnowswitch INT(3),IN uoldswitch INT(3),IN unowswitch INT(3),IN timestamp VARCHAR(50))
BEGIN
set @tb = CONCAT('insert into ',talbleName,'(id,phone,poldswitch,pnowswitch,uoldswitch,unowswitch,timestamp)VALUES(',id,',',phone,',',poldswitch,',',pnowswitch,',',uoldswitch,',',unowswitch,',',timestamp,')');
prepare create_stmt from @tb;
execute create_stmt;
END;
这时varchar类型的字段只能传数字,否则会报“Unknown column '字段名' in 'field list'”错误,需要在两侧加上引号。
2.在mybatis的xml调用如下:
<insert id="insertCloudSwitch" parameterType="CloudSwitch">
<selectKey keyProperty="id" resultType="String" order="BEFORE">
select REPLACE(UUID(),"-","")
</selectKey>
{call insert_cloudswitch(
#{tableName,mode=IN},#{id,mode=IN},#{cloudSwitch.phone,mode=IN},#{cloudSwitch.poldswitch,mode=IN},#{cloudSwitch.pnowswitch,mode=IN},#{cloudSwitch.uoldswitch,mode=IN},#{cloudSwitch.unowswitch,mode=IN},#{cloudSwitch.timestamp,mode=IN})}
</insert>
下面的语句是为了生成唯一id,与上述内容无关
<selectKey keyProperty="id" resultType="String" order="BEFORE">
select REPLACE(UUID(),"-","")
</selectKey>
3.dao层的方法如下:
void insertCloudSwitch(@Param("tableName") String tableName,@Param("cloudSwitch") CloudSwitch cloudSwitch);