自定义表操作

创建表

TableColumn：表的各项属性实体类

public class TableColumn {
    
    /** 表名 */
    private String tableName;

    /** 字段名 */
    private String name;

    /** 字段类型 */
    private String type;

    /** 字段长度 */
    private int length=0;

    /** decimalLength */
    private  int decimalLength=0;

    /** 是否为可以为null，true是可以，false是不可以，默认为true */
    private boolean nullValue=true;

    /** 是否是主键，默认false */
    private boolean key=false;

    /** 是否自动递增，默认false 只有主键才能使用 */
    private boolean autoIncrement=false;

    /** 默认值，默认为“” */
    private String defaultValue="";

    /** 注释 */
    private String comment="";

    public TableColumn() {
    }

    public TableColumn(String tableName, String name, String type, int length, int decimalLength, boolean nullValue, boolean key, boolean autoIncrement, String defaultValue, String comment) {
        this.tableName = tableName;
        this.name = name;
        this.type = type;
        this.length = length;
        this.decimalLength = decimalLength;
        this.nullValue = nullValue;
        this.key = key;
        this.autoIncrement = autoIncrement;
        this.defaultValue = defaultValue;
        this.comment = comment;
    }

    public String getTableName() {
        return tableName;
    }

    public void setTableName(String tableName) {
        this.tableName = tableName;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public int getLength() {
        return length;
    }

    public void setLength(int length) {
        this.length = length;
    }

    public int getDecimalLength() {
        return decimalLength;
    }

    public void setDecimalLength(int decimalLength) {
        this.decimalLength = decimalLength;
    }

    public boolean isNullValue() {
        return nullValue;
    }

    public void setNullValue(boolean nullValue) {
        this.nullValue = nullValue;
    }

    public boolean isKey() {
        return key;
    }

    public void setKey(boolean key) {
        this.key = key;
    }

    public boolean isAutoIncrement() {
        return autoIncrement;
    }

    public void setAutoIncrement(boolean autoIncrement) {
        this.autoIncrement = autoIncrement;
    }

    public String getDefaultValue() {
        return defaultValue;
    }

    public void setDefaultValue(String defaultValue) {
        this.defaultValue = defaultValue;
    }

    public String getComment() {
        return comment;
    }

    public void setComment(String comment) {
        this.comment = comment;
    }
}

	/**
     * @author:zyp
     * @description 创建表
     * @date: 2020/1/6 14:51
     * @return 
     */
public void createNewTable(String tableName) {
        List<TbTableColDefine> tbTableColDefines = getTbTableColDefines(tableName);
        List<TableColumn> lists = new ArrayList();
        tbTableColDefines.forEach(tbTableColDefine->{
            String fieldName = tbTableColDefine.getFieldName();
            String memo = tbTableColDefine.getMemo();
            Integer colType = tbTableColDefine.getColType();
            Integer colLength = tbTableColDefine.getColLength();
            Integer mustVal = tbTableColDefine.getMustVal();
            String defaultData = tbTableColDefine.getDefaultData();
            Integer autoAdd1 = tbTableColDefine.getAutoAdd1();
            String type = characterChange(colType);
            TableColumn tableColumn = new TableColumn("",fieldName,type,colLength,0,mustVal==0 ? true:false,false,autoAdd1==0 ? false:true,defaultData,memo);
            lists.add(tableColumn);
        });
        Map<String,Object> maps = new HashMap();
        maps.put("tableName",tableName);
        maps.put("tableColumns",lists);
        tableMapperVo.createNewTable(maps);
    }

    /**
     * @author:zyp
     * @description 根据表名获取表的所有字段进行创建表
     * @date: 2020/3/18 16:45
     * @return 
     */
    public List<TbTableColDefine> getTbTableColDefines(String tableName){
        TbTableDefineExample tbTableDefineExample = new TbTableDefineExample();
        TbTableDefineExample.Criteria criteria = tbTableDefineExample.createCriteria();
        criteria.andTableNmEqualTo(tableName);
        List<TbTableDefine> tbTableDefines = tbTableDefineMapper.selectByExample(tbTableDefineExample);
        TbTableDefine tbTableDefine = tbTableDefines.get(0);
        Integer id = tbTableDefine.getId();
        TbTableColDefineExample tbTableColDefineExample = new TbTableColDefineExample();
        TbTableColDefineExample.Criteria criteria1 = tbTableColDefineExample.createCriteria();
        criteria1.andTableidEqualTo(id);
        List<TbTableColDefine> tbTableColDefines = tbTableColDefineMapper.selectByExample(tbTableColDefineExample);
        return tbTableColDefines;
    }

xml：
	<!-- 创建新的表 -->
    <update id="createNewTable" parameterType="map">
        CREATE TABLE ${tableName} (
            <foreach collection="tableColumns" item="tableColumn" separator=",">
                ${tableColumn.name}
                ${tableColumn.type}
                <if test="tableColumn.type!='date' and tableColumn.type!='datetime' ">(${tableColumn.length}
                <if test="tableColumn.decimalLength!=0">,${tableColumn.decimalLength}</if> ) </if>
                <if test="tableColumn.nullValue==false">NOT NULL</if>
                <if test="tableColumn.autoIncrement==true">AUTO_INCREMENT</if>
                <if test="tableColumn.defaultValue!='' and tableColumn.defaultValue!=null"> DEFAULT ${tableColumn.defaultValue}</if>
                COMMENT '${tableColumn.comment}'
                <if test="tableColumn.key==true">,PRIMARY KEY (${tableColumn.name})</if>
            </foreach>
        )
    </update>

查询表

	/**
     * @author:zyp
     * @description 查询表是否存在
     * @date: 2020/1/6 16:10
     * @return 
     */
    @Override
    public int existTable(String tableName) {
        int i = tableMapperVo.existTable(tableName);
        return i;
    }
xml:
	<!-- 查看表是否存在 -->
    <select id="existTable" parameterType="String" resultType="Integer">
        select count(*)
        from information_schema.TABLES
        where LCASE(table_name)=#{tableName}
    </select>

增加表字段

	/**
     * @author:zyp
     * @description 增加表字段
     * @date: 2020/1/6 16:23
     * @return 
     */
    @Override
    public void addTableFields(Integer tableColDefineId) {
        TbTableColDefine tbTableColDefine = tbTableColDefineMapper.selectByPrimaryKey(tableColDefineId);
        Integer tableid = tbTableColDefine.getTableid();
        TbTableDefine tbTableDefine = tbTableDefineMapper.selectByPrimaryKey(tableid);
        String tableNm = tbTableDefine.getTableNm();
        String fieldName = tbTableColDefine.getFieldName();
        String memo = tbTableColDefine.getMemo();
        Integer colType = tbTableColDefine.getColType();
        Integer colLength = tbTableColDefine.getColLength();
        Integer mustVal = tbTableColDefine.getMustVal();
        String defaultData = tbTableColDefine.getDefaultData();
        Integer autoAdd1 = tbTableColDefine.getAutoAdd1();
        String type = characterChange(colType);
        TableColumn tableColumn = new TableColumn(tableNm,fieldName,type,colLength,0,mustVal==0 ? true:false,false,autoAdd1==0 ? false:true,defaultData,memo);
        tableMapperVo.addTableFields(tableColumn);
    }
xml:
	<!-- 增加表字段 -->
    <update id="addTableFields" parameterType="com.zyp.pojo.TableColumn">
        alter table ${tableName} add
        ${name}
        ${type}
        <if test="type!='date' and type!='datetime'">(${length}<if test="decimalLength!=0">,${decimalLength}</if>)</if>
        <if test="nullValue==false">NOT NULL</if>
        <if test="defaultValue!='' and defaultValue!=null"> DEFAULT ${defaultValue}</if>
        COMMENT '${comment}'
    </update>

修改表字段

	/**
     * @author:zyp
     * @description 修改表字段
     * @date: 2020/3/19 9:39
     * @return 
     */
    @Override
    public void updateTableFields(Integer tableColDefineId) {
        TbTableColDefine tbTableColDefine = tbTableColDefineMapper.selectByPrimaryKey(tableColDefineId);
        Integer tableid = tbTableColDefine.getTableid();
        TbTableDefine tbTableDefine = tbTableDefineMapper.selectByPrimaryKey(tableid);
        String tableNm = tbTableDefine.getTableNm();
        String fieldName = tbTableColDefine.getFieldName();
        String memo = tbTableColDefine.getMemo();
        Integer colType = tbTableColDefine.getColType();
        Integer colLength = tbTableColDefine.getColLength();
        Integer mustVal = tbTableColDefine.getMustVal();
        String defaultData = tbTableColDefine.getDefaultData();
        Integer autoAdd1 = tbTableColDefine.getAutoAdd1();
        String type = characterChange(colType);
        TableColumn tableColumn = new TableColumn(tableNm,fieldName,type,colLength,0,mustVal==0 ? true:false,false,autoAdd1==0 ? false:true,defaultData,memo);
        tableMapperVo.updateTableFields(tableColumn);
    }

	/**
     * @author:zyp
     * @description 字符转义
     * @date: 2020/4/1 9:34
     * @return
     */
    private String characterChange(int colType){
        String type ;
        switch (colType){
            case 0:
                type = "varchar";
                break;
            case 1:
                type = "int";
                break;
            case 2 :
                type = "double";
                break;
            case 3 :
                type = "date";
                break;
            default:
                type = "datetime";
        }
        return type;
    }
xml：
	<!-- 修改表字段 -->
    <update id="updateTableFields" parameterType="com.zyp.pojo.TableColumn">
        alter table ${tableName} MODIFY
        ${name}
        ${type}
        <if test="type!='date' and type!='datetime'">(${length}<if test="decimalLength!=0">,${decimalLength}</if>)</if>
        <if test="nullValue==false">NOT NULL</if>
        <if test="defaultValue!='' and defaultValue!=null"> DEFAULT ${defaultValue}</if>
    </update>

删除表字段

	/**
     * @author:zyp
     * @description 删除表字段
     * @date: 2020/3/30 15:57
     * @return
     */
    @Override
    public void deleteTableFields(Integer tableColDefineId) {
        TbTableColDefine tbTableColDefine = tbTableColDefineMapper.selectByPrimaryKey(tableColDefineId);
        Integer tableid = tbTableColDefine.getTableid();
        int i = tbTableColDefineMapper.deleteByPrimaryKey(tableColDefineId);
        TbTableDefine tbTableDefine = tbTableDefineMapper.selectByPrimaryKey(tableid);
        String tableNm = tbTableDefine.getTableNm();
        String fieldName = tbTableColDefine.getFieldName();
        TableColumn tableColumn = new TableColumn(tableNm,fieldName,"",0,0,false,false,false,null,null);
        tableMapperVo.deleteTableFields(tableColumn);
    }

xml:
	<!-- 删除表字段 -->
    <update id="deleteTableFields" parameterType="com.zyp.pojo.TableColumn">
        alter table ${tableName} drop ${name}
    </update>

删除表

	/**
     * @author:zyp
     * @description 删除表
     * @date: 2020/1/6 17:15
     * @return 
     */
    @Override
    public void dropTable(String tableName) {
        tableMapperVo.dropTable(tableName);
    }
xml:
	<!-- 删除表 -->
    <update id="dropTable" parameterType="string">
        DROP TABLE IF EXISTS ${tableName}
    </update>

查询实体表的字段属性

	/**
     * @author:zyp
     * @description 查询实体表的字段属性
     * @date: 2020/6/5 15:30
     * @return 
     */
    @Override
    public List<Map<String, Object>> selectTableColumn(String tableNm) {
        List<Map<String, Object>> maps = clusterTableMapperVo.selectTableColumn(tableNm);
        return maps;
    }
    
xml:
	<!-- 查看实体表的字段属性 -->
    <select id="selectTableColumn" parameterType="String" resultType="map">
        select column_name,column_comment,data_type
        from information_schema.columns
        where table_schema = 'innoking_docsys' and table_name=#{tableName}
    </select>

查询表的中文注释

	/**
     * @author:zyp
     * @description 查询表的中文注释
     * @date: 2020/6/9 18:06
     * @return
     */
    @Override
    public List<Map<String, Object>> selectTableComment(String tableNm ) {
        List<Map<String, Object>> maps = clusterTableMapperVo.selectTableComment(tableNm);
        return maps;
    }
    
xml:
	<!-- 查看实体表的字段属性 -->
    <select id="selectTableComment" parameterType="String" resultType="map">
        select table_name ,table_comment
        from information_schema.TABLES
        where table_schema = 'innoking_docsys' and table_name=#{tableName}
    </select>