| 动态sql

【mybatis】利用动态语句动态创建表和列

目的

为了动态从数据库中生成表以及定义属性

步骤

创建新的xml文件以及mapper接口类

TabelControllerMapper.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.me.mapper.TableControllerMapper">  


 <update id="createNewTable">  
  CREATE TABLE ${myMap.theTableName}(  
          id int(10) PRIMARY KEY AUTO_INCREMENT NOT NULL  
          <foreach collection="myMap" index="key" item="value">  
 <if test="key.toString() != 'theTableName'.toString()">  
  ,${key} ${value}  
 </if>  
 </foreach>  );  
    </update>  
</mapper>

注意第一句定义id后面不加逗号，在foreach循环里前面加上逗号是为了语句最后一次执行后面没有逗号，否则会报错

TableControllerMapper接口

package com.me.mapper;  

import org.apache.ibatis.annotations.Mapper;  
import org.apache.ibatis.annotations.Param;  

import java.util.HashMap;  

/**  
 * @author fenghere  
 */@Mapper  
public interface TableControllerMapper {  
    /**  
     * fetch data by rule id * * @param map mymap  
     * @return Result<XxxxDO>  
     */  

  void createNewTable(@Param(value = "myMap") HashMap map);  
}

输入的参数为hashmap类型，关于这种引入参数的方法另一篇博客有写

http://xfff.xyz/2019/01/07/Mybatis%E4%BC%A0%E5%8F%82%E6%96%B9%E6%B3%95/

测试

写一个测试类

package com.me.demo;  

import com.me.beans.User;  
import com.me.mapper.TableControllerMapper;  
import com.me.mapper.UserMapper;  
import com.me.security.Md5Utils;  
import com.me.service.UserService;  
import org.junit.Test;  
import org.springframework.beans.factory.annotation.Autowired;  

import java.io.UnsupportedEncodingException;  
import java.security.NoSuchAlgorithmException;  
import java.util.HashMap;  

/**  
 * @ClassName MyTest  
 * @Description TODO  
 * @Author xufeng  
 * @Data 2019/2/22 13:46  
 * @Version 1.0  
 **/
 public class MyTest extends DemoApplicationTests{  


  @Autowired  
  TableControllerMapper tableControllerMapper;  

  @Test  
  public void myTest(){  
          HashMap<String,String> mymap = new HashMap<>();  
          mymap.put("theTableName","dsafakshfdksahlfhdashdfh");  
          mymap.put("text","text");  
          mymap.put("username","varchar(10)");  
          mymap.put("ididiid","int");  
          tableControllerMapper.createNewTable(mymap);  

  }  
}

MyTest类继承DemoApplicationTests是为了引入springboot项目的数据库和mapper配置

执行完成，进入数据库查看

如下

另外，删除表等操作应为类似的操作.

<select id="existTable" parameterType="java.lang.String" resultType="java.lang.Integer">
    select count(*)
    from information_schema.TABLES
    where LCASE(table_name) = #{tableName}
</select>


<update id="dropTable">
    DROP TABLE ${tablename}
</update>
ESC