创建表
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>