目录
一、(业务表)数据表管理mtyxs_business_table
介绍:
需要无实体类的增删改查功能,并且可以对表结构做修改,针对一个业务为一张数据库表实现外部动态修改表的结构和数据。(在恶势力的摧残下,终于可以实现了这个动态数据增删改查的功能,实现了用mysql就可以修改表结构表字段,同时假删除数据表和已有表的数据)
三张数据表:
id | int | 主键ID |
business_name | varchar(255) | 表名称(业务名称) |
business_keys | varchar(255) | 表keys(业务唯一标识) |
status | char(1) | 判断数据是否已删除(1是0否) |
delete_time | datetime | 删除日期 |
create_time | datetime | 创建时间 |
create_by | varchar(255) | 创建人 |
update_time | datetime | 修改时间 |
update_by | varchar(255) | 修改人 |
remark | varchar(255) | 备注 |
业务逻辑:先创建数据表,再修改表结构,根据字段business_keys创建数据库表,business_keys字段内容不可以修改
id | int | 主键ID |
business_keys | varchar(255) | 关联业务表key |
column_name | varchar(255) | 字段名称 |
column_describe | varchar(255) | 字段描述 |
column_type | varchar(255) | 字段类型 |
is_required | char(1) | 是否必填1是0否 |
select_type | varchar(255) | 查询方式 |
is_insert | char(1) | 是否插入1是0否 |
is_update | char(1) | 是否修改1是0否 |
delete_time | datetime | 删除时间 |
status | char(1) | 删除状态 1是0否 |
create_time | datetime | 创建时间 |
create_by | varchar(255) | 创建人 |
update_time | datetime | 修改时间 |
update_by | varchar(255) | 修改人 |
remark | varchar(255) | 备注 |
业务逻辑:记录保留表结构 进行增删改查 对字段增删改查设置限制
第三张数据表根据“数据表管理”生成,表的数据为“表结构管理”,每一个business_keys绑定一个表,可实现数据表假删除,id、status字段是必包含的特殊字段(不可以在表结构管理中修改)
id | int | 主键ID |
status | char(1) | 判断数据是否已删除(1是0否) |
一、(业务表)数据表管理mtyxs_business_table
1.domain.java 就是普通的实体类
2.mapper.xml 普普通通的增删改查 只有删除做了一个假删除处理
3.mapper.java 普普通通的增删改查
3.service.java 普普通通的增删改查
4.serviceImpl.java 普普通通的增删改查 和一个创建表方法 和一个假删除表方法
/**
* 新增业务管理
*
* @param mtyxsBusinessTable 业务管理
* @return 结果
*/
@Override
public int insertMtyxsBusinessTable(MtyxsBusinessTable mtyxsBusinessTable)
{
mtyxsBusinessTable.setCreateTime(DateUtils.getNowDate());
mtyxsBusinessTable.setCreateBy(getUsername());
int i = mtyxsBusinessTableMapper.insertMtyxsBusinessTable(mtyxsBusinessTable);
//创建表
mtyxsTableMapper.createTableNameBase("mtyxs_gen_"+mtyxsBusinessTable.getBusinessKeys()+"_data",mtyxsBusinessTable.getBusinessKeys());
//新增id字段
MtyxsTableStructure mtyxsTableStructure = new MtyxsTableStructure();
mtyxsTableStructure.setBusinessKeys(mtyxsBusinessTable.getBusinessKeys());
mtyxsTableStructure.setColumnName("id");
mtyxsTableStructure.setColumnDescribe("主键ID");
mtyxsTableStructure.setColumnType("int");
mtyxsTableStructure.setIsRequired("0");
mtyxsTableStructure.setIsApi("1");
mtyxsTableStructure.setSelectType("=");
mtyxsTableStructure.setIsInsert("0");
mtyxsTableStructure.setIsUpdate("0");
mtyxsTableStructure.setCreateBy(getUsername());
mtyxsTableStructure.setCreateTime(DateUtils.getNowDate());
mtyxsTableStructureMapper.insertMtyxsTableStructure(mtyxsTableStructure);
return i;
}
/**
* 批量删除业务管理
*
* @param ids 需要删除的业务管理主键
* @return 结果
*/
@Override
public int deleteMtyxsBusinessTableByIds(Long[] ids)
{
//关联假删除表结构管理数据
for (int i = 0; i < ids.length; i++) {
MtyxsBusinessTable mtyxsBusinessTable =selectMtyxsBusinessTableById(ids[0]);
mtyxsTableStructureMapper.deleteMtyxsTableStructureBybusinessKeys(mtyxsBusinessTable.getBusinessKeys());
//时间截
Date date = new Date();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
String format = simpleDateFormat.format(date);
mtyxsTableMapper.updateTableName("mtyxs_gen_"+mtyxsBusinessTable.getBusinessKeys()+"_data","deprecated_gen_"+mtyxsBusinessTable.getBusinessKeys()+format);
}
return mtyxsBusinessTableMapper.deleteMtyxsBusinessTableByIds(ids);
}
5.controller.java 普普通通的增删改查 添加有一个校验
/**
* 新增业务管理
*/
@PreAuthorize("@ss.hasPermi('mtyxs:business:add')")
@Log(title = "业务管理", businessType = BusinessType.INSERT)
@PostMapping
public AjaxResult add(@RequestBody MtyxsBusinessTable mtyxsBusinessTable)
{
//校验businessKeys是否重复
MtyxsBusinessTable mtyxsBusinessTableNew = new MtyxsBusinessTable();
mtyxsBusinessTableNew.setBusinessKeys(mtyxsBusinessTable.getBusinessKeys());
mtyxsBusinessTableNew.setStatus("0");
List<MtyxsBusinessTable> list = mtyxsBusinessTableService.selectMtyxsBusinessTableList(mtyxsBusinessTableNew);
if (list.size()>0){
return AjaxResult.error(mtyxsBusinessTable.getBusinessKeys()+"业务唯一标识重复");
}
return toAjax(mtyxsBusinessTableService.insertMtyxsBusinessTable(mtyxsBusinessTable));
}
二、表结构管理mtyxs_table_structure
1.domain.java 就是普通的实体类
2.(1).mapper.xml 普普通通的增删改查 只有删除做了一个假删除处理
(2).mapper.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.ruoyi.mtyxs.mapper.MtyxsTableMapper">
<update id="createTableField">
alter table ${tableName} ADD ${fieldName} ${fieldType} ${isNull} ${comments};
</update>
<update id="updateTableName">
alter table ${oldTableName} RENAME ${newTableName}
</update>
<update id="updateTableField">
alter table ${tableName} change ${oldFieldName} ${newFieldName} ${fieldType} ${comments};
</update>
<update id="createTableNameBase">
CREATE TABLE ${tableName} (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`status` char(2) NOT NULL DEFAULT '0' COMMENT '删除状态 1是0否',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
</update>
</mapper>
3.(1).mapper.java 普普通通的增删改查
(2).mapper.java 表结构 新增表字段 修改表名 修改表字段 创建表
/**
* 表结构管理Mapper接口
*
* @author abab
* @date 2023-04-20
*/
public interface MtyxsTableMapper
{
/**
* 新增表字段
*
* @param tableName 表名
* @param fieldName 字段名
* @param fieldType 字段类型
* @param isNull 是否为空
* @param comments 备注
* @return 表结构管理
*/
int createTableField(@Param("tableName") String tableName,
@Param("fieldName") String fieldName,
@Param("fieldType") String fieldType,
@Param("isNull") String isNull,
@Param("comments") String comments);
/**
* 创建表
*
* @param tableName 表名
* @param fieldName 字段名
* @return 表结构管理
*/
int createTableNameBase(@Param("tableName") String tableName,
@Param("fieldName") String fieldName);
/**
* 修改列名
*
* @param tableName 表名
* @param oldFieldName 旧列名
* @param newFieldName 新列名
* @param fieldType 字段类型
* @param comments 备注
* @return 表结构管理
*/
int updateTableField(@Param("tableName") String tableName,
@Param("oldFieldName") String oldFieldName,
@Param("newFieldName") String newFieldName,
@Param("fieldType") String fieldType,
@Param("comments") String comments);
/**
* 修改表名
*
* @param oldTableName 旧表名
* @param newTableName 新表名
* @return 表结构管理
*/
int updateTableName(@Param("oldTableName") String oldTableName,
@Param("newTableName") String newTableName);
}
3.service.java 普普通通的增删改查
4.serviceImpl.java 普普通通的增删改查 和一个新增字段方法 和一个修改字段方法 删除是假删除
/**
* 新增结构管理
*
* @param mtyxsTableStructure 结构管理
* @return 结果
*/
@Override
public AjaxResult insertMtyxsTableStructureNew(MtyxsTableStructure mtyxsTableStructure)
{
if (mtyxsTableStructure.getColumnName().equals("id")||mtyxsTableStructure.getColumnName().equals("group_by_field") || mtyxsTableStructure.getColumnName().equals("status")|| mtyxsTableStructure.getColumnName().equals("businessKeys")){
return AjaxResult.error("字段名称:"+mtyxsTableStructure.getColumnName()+"为特殊字符,不可以命名");
}
MtyxsTableStructure mtyxsTableStructureNew = new MtyxsTableStructure();
mtyxsTableStructureNew.setBusinessKeys(mtyxsTableStructure.getBusinessKeys());
mtyxsTableStructureNew.setColumnName(mtyxsTableStructure.getColumnName());
mtyxsTableStructure.setStatus("0");
List<MtyxsTableStructure> list = selectMtyxsTableStructureList(mtyxsTableStructure);
if (list.size()>0){
return AjaxResult.error("字段名称:"+mtyxsTableStructure.getColumnName()+"已存在");
}
MtyxsTableStructure mtyxsTableStructureNewOne = new MtyxsTableStructure();
mtyxsTableStructureNewOne.setBusinessKeys(mtyxsTableStructure.getBusinessKeys());
mtyxsTableStructureNewOne.setColumnDescribe(mtyxsTableStructure.getColumnDescribe());
mtyxsTableStructure.setStatus("0");
List<MtyxsTableStructure> listNew = selectMtyxsTableStructureList(mtyxsTableStructure);
if (listNew.size()>0){
return AjaxResult.error("字段描述:"+mtyxsTableStructure.getColumnName()+"已存在");
}
if (mtyxsTableStructure.getColumnName().substring(mtyxsTableStructure.getColumnName().length()-1).equals("_")){
return AjaxResult.error("禁止已下划线结尾");
}
//回参
//判断是否被假删除
MtyxsTableStructure mtyxsTableStructure1 = new MtyxsTableStructure();
mtyxsTableStructure1.setBusinessKeys(mtyxsTableStructure.getBusinessKeys());
mtyxsTableStructure1.setColumnName(mtyxsTableStructure.getColumnName());
List<MtyxsTableStructure> mtyxsTableStructures = selectMtyxsTableStructureList(mtyxsTableStructure1);
if (mtyxsTableStructures.size() > 0) {
mtyxsTableStructure1.setStatus("0");
updateMtyxsTableStructure(mtyxsTableStructure1);
}else {
insertMtyxsTableStructure(mtyxsTableStructure);
//添加字段到表
String tableName="mtyxs_gen_"+mtyxsTableStructure.getBusinessKeys()+"_data";
String fieldName=mtyxsTableStructure.getColumnName();
String fieldType=mtyxsTableStructure.getColumnType();
String isNull="DEFAULT NULL";
String comments="COMMENT'"+mtyxsTableStructure.getColumnDescribe()+"'";
mtyxsTableMapper.createTableField(tableName,fieldName,fieldType,isNull,comments);
}
return AjaxResult.success();
}
/**
* 修改结构管理
*
* @param mtyxsTableStructure 结构管理
* @return 结果
*/
@Override
public AjaxResult updateMtyxsTableStructureNew(MtyxsTableStructure mtyxsTableStructure)
{
mtyxsTableStructure.setUpdateTime(DateUtils.getNowDate());
mtyxsTableStructure.setUpdateBy(getUsername());
if (mtyxsTableStructure.getColumnName().equals("id")){
return AjaxResult.error("禁止修改主键属性");
}
if (mtyxsTableStructure.getColumnName().substring(mtyxsTableStructure.getColumnName().length()-1).equals("_")){
return AjaxResult.error("禁止已下划线结尾");
}
MtyxsTableStructure mtyxsTableStructure1 = selectMtyxsTableStructureById(mtyxsTableStructure.getId());
//修改字段到表
String tableName="mtyxs_gen_"+mtyxsTableStructure1.getBusinessKeys()+"_data";
String oldFieldName= mtyxsTableStructure1.getColumnName();
String newFieldName= mtyxsTableStructure.getColumnName();
String fieldType=mtyxsTableStructure.getColumnType();
String comments;
if (mtyxsTableStructure.getColumnDescribe()==null ||mtyxsTableStructure.getColumnDescribe().equals("")){
comments="COMMENT'"+mtyxsTableStructure1.getColumnDescribe()+"'";
}else {
comments="COMMENT'"+mtyxsTableStructure.getColumnDescribe()+"'";
}
mtyxsTableMapper.updateTableField(tableName,oldFieldName,newFieldName,fieldType,comments);
int i = mtyxsTableStructureMapper.updateMtyxsTableStructure(mtyxsTableStructure);
return AjaxResult.success(i);
}
5.controller.java 普普通通的增删改查 删除有一个校验
/**
* 删除结构管理
*/
@PreAuthorize("@ss.hasPermi('mtyxs:structure:remove')")
@Log(title = "结构管理", businessType = BusinessType.DELETE)
@DeleteMapping("/{ids}")
public AjaxResult remove(@PathVariable Long[] ids)
{
for (int i = 0; i < ids.length; i++) {
MtyxsTableStructure mtyxsTableStructure = mtyxsTableStructureService.selectMtyxsTableStructureById(ids[i]);
if (mtyxsTableStructure.getColumnName().equals("id") || mtyxsTableStructure.getColumnName().equals("status")){
return AjaxResult.error("特殊字段不可以删除");
}
}
return toAjax(mtyxsTableStructureService.deleteMtyxsTableStructureByIds(ids));
}
三、表数据管理mtyxs_gen_**_data
1.没有实体类
2.mapper.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.ruoyi.mtyxs.mapper.MtyxsTableDateMapper">
<select id="selectTableDateList" resultType="Map" >
select ${fieldName} from ${tableName}
<where>
status=0
${selectString}
</where>
</select>
<update id="updateTableDateList" parameterType="Map">
update ${tableName} set ${fieldName} where id = ${fieldValue}
</update>
<insert id="insertTableDateList" parameterType="Map">
insert into ${tableName} (${fieldName}) values (${fieldValue})
</insert>
<update id="deleteTableDateList" parameterType="Map">
update ${tableName} set status='1' where id = #{fieldValue}
</update>
</mapper>
3.mapper.java 增删改查
/**
* 表数据管理Mapper接口
*
* @author abab
* @date 2023-04-20
*/
public interface MtyxsTableDateMapper
{
/**
* 查询表数据
*
* @param fieldName 字段名
* @param tableName 表名
* @param selectString 查询语句
* @return 表数据管理
*/
List<Map<String,String>> selectTableDateList(@Param("fieldName") String fieldName,
@Param("tableName") String tableName,
@Param("selectString") String selectString);
/**
* 修改表数据
* @param tableName 表名
* @param fieldName 字段名
* @param fieldValue 判断条件
* @return 表结构管理
*/
int updateTableDateList(@Param("tableName") String tableName,
@Param("fieldName") String fieldName,
@Param("fieldValue") String fieldValue);
/**
* 新增表数据
* @param tableName 表名
* @param fieldName 字段名
* @param fieldValue 判断条件
* @return 表结构管理
*/
int insertTableDateList(@Param("tableName") String tableName,
@Param("fieldName") String fieldName,
@Param("fieldValue") String fieldValue);
/**
* 删除表数据
* @param tableName 表名
* @param fieldValue 判断条件
* @return 表结构管理
*/
int deleteTableDateList(@Param("tableName") String tableName,
@Param("fieldValue") String fieldValue);
}
3.service.java
/**
* 表数据管理Service接口
*
* @author abab
* @date 2023-04-20
*/
public interface IMtyxsTableDataService {
List<Map<String, String>> selectTableDateList(Map<String,String> map);
AjaxResult updateTableDateList(Map<String,String> map);
String insertTableDateList(Map<String,String> map);
AjaxResult deleteTableDateList(Map<String,String> map);
/**
* 导出表数据List
* 导出
*/
void exportData(HttpServletResponse response, Map<String, String> map);
/**
* 导入表数据List
* 导入
*/
AjaxResult importData(MultipartFile file, String businessKeys);
/**
* 下载模板
* 导出
*/
AjaxResult importTemplate(HttpServletResponse response, Map<String, String> map);
}
4.serviceImpl.java
/**
* 表数据管理Service业务层处理
*
* @author abab
* @date 2023-04-20
*/
@Service
public class MtyxsTableDataServiceImpl implements IMtyxsTableDataService {
@Autowired
private MtyxsTableDateMapper mtyxsTableDateMapper;
@Autowired
private MtyxsTableStructureMapper mtyxsTableStructureMapper;
@Autowired
private MtyxsBusinessTableMapper mtyxsBusinessTableMapper;
@Override
public List<Map<String, String>> selectTableDateList(Map<String,String> map) {
//查询所有字段
MtyxsTableStructure mtyxsTableStructure = new MtyxsTableStructure();
mtyxsTableStructure.setBusinessKeys(map.get("businessKeys"));
mtyxsTableStructure.setStatus("0");
List<MtyxsTableStructure> mtyxsTableStructures = mtyxsTableStructureMapper.selectMtyxsTableStructureList(mtyxsTableStructure);
//字段
String fieldName = "";
for (int i = 0; i < mtyxsTableStructures.size(); i++) {
if (i==mtyxsTableStructures.size()-1){
fieldName+=mtyxsTableStructures.get(i).getColumnName();
}else {
fieldName+=mtyxsTableStructures.get(i).getColumnName()+",";
}
}
//表名
String tableName="mtyxs_gen_"+map.get("businessKeys")+"_data";
//查询语句
String selectString = selectString(mtyxsTableStructures,map);
startPage();
List<Map<String, String>> maps = mtyxsTableDateMapper.selectTableDateList(fieldName, tableName, selectString);
return maps;
}
@Override
public AjaxResult updateTableDateList(Map<String,String> map) {
String businessKeys=map.get("businessKeys");
if (map.get("businessKeys").equals("") || map.get("businessKeys")==null){
return AjaxResult.error("未指定业务表");
}
//查询所有字段
MtyxsTableStructure mtyxsTableStructure = new MtyxsTableStructure();
mtyxsTableStructure.setBusinessKeys(businessKeys);
mtyxsTableStructure.setStatus("0");
List<MtyxsTableStructure> mtyxsTableStructures = mtyxsTableStructureMapper.selectMtyxsTableStructureList(mtyxsTableStructure);
//判断id
if (map.get("id").equals("") || map.get("id")==null){
return AjaxResult.error("主键的值不可以为空");
}
//判断条件
String fieldValue=map.get("id");
//修改字段
String fieldName="";
for (int i = 0; i < mtyxsTableStructures.size(); i++) {
if (mtyxsTableStructures.get(i).getIsRequired().equals("1")){
if (map.get(mtyxsTableStructures.get(i).getColumnName()).equals("") || map.get(mtyxsTableStructures.get(i).getColumnName())==null){
//必填但是空值
return AjaxResult.error(mtyxsTableStructures.get(i).getColumnDescribe()+"不可以为空");
}
}
if (!mtyxsTableStructures.get(i).getColumnName().equals("id") && mtyxsTableStructures.get(i).getIsUpdate().equals("1") && map.get(mtyxsTableStructures.get(i).getColumnName())!=null){
fieldName+=mtyxsTableStructures.get(i).getColumnName()+"= '"+map.get(mtyxsTableStructures.get(i).getColumnName())+"' ,";
}
}
if (fieldName.length()==0){
return AjaxResult.error("未输入需要修改的数据信息");
}
//已处理修改字段
fieldName= fieldName.substring(0,fieldName.length()-1);
//表名
String tableName="mtyxs_gen_"+businessKeys+"_data";
try {
mtyxsTableDateMapper.updateTableDateList(tableName, fieldName, fieldValue);
}catch (Exception e){
throw new SqlException("字段数值赋予异常,请核对字段类型操作");
}
return AjaxResult.success();
}
@Override
public String insertTableDateList(Map<String,String> map) {
String businessKeys=map.get("businessKeys");
//查询所有字段
MtyxsTableStructure mtyxsTableStructure = new MtyxsTableStructure();
mtyxsTableStructure.setBusinessKeys(businessKeys);
mtyxsTableStructure.setStatus("0");
List<MtyxsTableStructure> mtyxsTableStructures = mtyxsTableStructureMapper.selectMtyxsTableStructureList(mtyxsTableStructure);
Map<String,String> mapList=new HashMap<>();
//添加字段
String fieldName="";
String fieldValues="";
for (int i = 0; i < mtyxsTableStructures.size(); i++) {
if (mtyxsTableStructures.get(i).getIsRequired().equals("1")){
if (map.get(mtyxsTableStructures.get(i).getColumnName()).equals("") || map.get(mtyxsTableStructures.get(i).getColumnName())==null){
//必填但是空值
return mtyxsTableStructures.get(i).getColumnDescribe();
}
}
if (mtyxsTableStructures.get(i).getIsInsert().equals("0") && map.get(mtyxsTableStructures.get(i).getColumnName())!=null){
//不允许 添加但是有值
return mtyxsTableStructures.get(i).getColumnDescribe();
}
if (!mtyxsTableStructures.get(i).getColumnName().equals("id") && mtyxsTableStructures.get(i).getIsInsert().equals("1") && map.get(mtyxsTableStructures.get(i).getColumnName())!=null &&!map.get(mtyxsTableStructures.get(i).getColumnName()).equals("")){
//是允许增加的
fieldName+=mtyxsTableStructures.get(i).getColumnName()+",";
fieldValues+="'"+map.get(mtyxsTableStructures.get(i).getColumnName())+"',";
}
}
//已处理修改字段
fieldName= fieldName.substring(0,fieldName.length()-1);
String fieldValue= fieldValues.substring(0,fieldValues.length()-1);
fieldValue = fieldValue.replace("\n", "");
fieldValue = fieldValue.replace("\r", "");
//表名
String tableName="mtyxs_gen_"+businessKeys+"_data";
try {
mtyxsTableDateMapper.insertTableDateList(tableName, fieldName, fieldValue);
}catch (Exception e){
throw new SqlException("字段数值赋予异常,请核对字段类型操作");
}
return "ok";
}
@Override
public AjaxResult deleteTableDateList(Map<String,String> map) {
String businessKeys=map.get("businessKeys");
if (map.get("businessKeys").equals("") || map.get("businessKeys")==null){
return AjaxResult.error("未指定业务表");
}
//表名
String tableName="mtyxs_gen_"+businessKeys+"_data";
if (map.get("id")==null || map.get("id").equals("")){
return AjaxResult.error("未指定数据");
}
//指定id的值
String fieldValue=map.get("id");
try {
return AjaxResult.success(mtyxsTableDateMapper.deleteTableDateList(tableName,fieldValue););
}catch (Exception e){
throw new SqlException("字段数值赋予异常,请核对字段类型操作");
}
}
/**
* 导出表数据List
* 导出
*/
@Override
public void exportData(HttpServletResponse response, Map<String,String> map)
{
//查询所有字段
MtyxsTableStructure mtyxsTableStructure = new MtyxsTableStructure();
mtyxsTableStructure.setBusinessKeys(map.get("businessKeys"));
mtyxsTableStructure.setStatus("0");
List<MtyxsTableStructure> mtyxsTableStructures = mtyxsTableStructureMapper.selectMtyxsTableStructureList(mtyxsTableStructure);
//字段处理
String fieldName = "";
Object[] fields = new Object[mtyxsTableStructures.size()];
for (int i = 0; i < mtyxsTableStructures.size(); i++) {
fields[i]=mtyxsTableStructures.get(i).getColumnDescribe();
if (i==mtyxsTableStructures.size()-1){
fieldName+=mtyxsTableStructures.get(i).getColumnName();
}else {
fieldName+=mtyxsTableStructures.get(i).getColumnName()+",";
}
}
//表名
String tableName="mtyxs_gen_"+map.get("businessKeys")+"_data";
//查询语句
String selectString = selectString(mtyxsTableStructures,map);
//数据集合
List<Map<String, String>> list = mtyxsTableDateMapper.selectTableDateList(fieldName, tableName, selectString);
MtyxsBusinessTable mtyxsBusinessTable = new MtyxsBusinessTable();
mtyxsBusinessTable.setBusinessKeys(map.get("businessKeys"));
List<MtyxsBusinessTable> mtyxsBusinessTables = mtyxsBusinessTableMapper.selectMtyxsBusinessTableList(mtyxsBusinessTable);
XSSFWorkbook xssfSheets = new XSSFWorkbook();
XSSFSheet userList = xssfSheets.createSheet(mtyxsBusinessTables.get(0).getBusinessName());
//创建第一行,起始为0
Row titleRow = userList.createRow(0);
for (int i = 0; i < mtyxsTableStructures.size(); i++) {
titleRow.createCell(i).setCellValue(mtyxsTableStructures.get(i).getColumnDescribe());
}
//内容
for (int i = 0; i < list.size(); i++) {
Row row = userList.createRow(i + 1);
for (int j = 0; j < mtyxsTableStructures.size(); j++) {
String columName = "";
if (String.valueOf(list.get(i).get(mtyxsTableStructures.get(j).getColumnName())).equals("null")){
columName="";
}else if (mtyxsTableStructures.get(j).getColumnType().equals("datetime") || mtyxsTableStructures.get(j).getColumnType().equals("date")){
columName=String.valueOf(list.get(i).get(mtyxsTableStructures.get(j).getColumnName())).replace("T"," ");
}else {
columName=String.valueOf(list.get(i).get(mtyxsTableStructures.get(j).getColumnName()));
}
row.createCell(j).setCellValue(columName);
}
}
String fileName = mtyxsBusinessTables.get(0).getBusinessName()+"数据表.xlsx";
OutputStream outputStream=null;
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
//设置ContentType请求信息格式
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
outputStream = response.getOutputStream();
xssfSheets.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 导入表数据List
* 导入
*/
@Override
public AjaxResult importData(MultipartFile file,String businessKeys){
MtyxsBusinessTable mtyxsBusinessTable = new MtyxsBusinessTable();
mtyxsBusinessTable.setBusinessKeys(businessKeys);
List<MtyxsBusinessTable> mtyxsBusinessTables = mtyxsBusinessTableMapper.selectMtyxsBusinessTableList(mtyxsBusinessTable);
//查询导入的业务表结构
MtyxsTableStructure mtyxsTableStructure = new MtyxsTableStructure();
mtyxsTableStructure.setBusinessKeys(businessKeys);
List<MtyxsTableStructure> mtyxsTableStructures = mtyxsTableStructureMapper.selectMtyxsTableStructureList(mtyxsTableStructure);
if (mtyxsTableStructures.size() == 0) {
return AjaxResult.error("该业务表结构无效");
}
//中文字段名称
List listChName = new ArrayList();
//字段的键值对
Map<String, String> mapColumn = new HashMap<>();
for (int i = 0; i < mtyxsTableStructures.size(); i++) {
listChName.add(mtyxsTableStructures.get(i).getColumnDescribe());
mapColumn.put(mtyxsTableStructures.get(i).getColumnDescribe(), mtyxsTableStructures.get(i).getColumnName());
}
//添加
List list = null;
try {
list = ExcelUtil.readExcel(file, listChName, mapColumn, businessKeys);
} catch (IOException e) {
e.printStackTrace();
}
String msg = "";
if (list.size() == 0) {
return AjaxResult.error("表格内无数据");
}
if (list.get(0).equals("模板不匹配")) {
if (mtyxsBusinessTables.size() > 0) {
return AjaxResult.error(mtyxsBusinessTables.get(0).getBusinessName() + "模板不匹配");
} else {
return AjaxResult.error("模板不匹配");
}
}
int errorCount = 0;
int successCount = 0;
int sameCount = 0;
for (int i = 0; i < list.size(); i++) {
String s = insertTableDateList((Map<String, String>) list.get(i));
if (s.equals("ok")) {
successCount++;
} else if (s.equals("重复")) {
sameCount++;
} else {
//防止返回字段重复
if (!msg.contains(s)) {
msg += s + ",";
}
errorCount++;
}
}
if (msg.length() == 0 && sameCount == 0) {
return AjaxResult.success(successCount + "条数据全部导入成功!");
} else if (sameCount > 0 && msg.length() > 0) {
return AjaxResult.success(successCount + "条数据导入成功!" + errorCount + "条数据导入失败,原因" + msg.substring(0, msg.length() - 1) + "字段异常," + sameCount + "条数据已去重处理");
} else if (sameCount > 0 && msg.length() == 0) {
return AjaxResult.success(successCount + "条数据导入成功!" + sameCount + "条数据已去重处理");
} else {
return AjaxResult.success(successCount + "条数据导入成功!" + errorCount + "条数据导入失败,原因" + msg.substring(0, msg.length() - 1) + "字段异常");
}
}
/**
* 下载模板
* 导出
*/
@Override
public AjaxResult importTemplate(HttpServletResponse response,@RequestParam Map<String,String> map)
{
if (map.get("businessKeys")==null || map.get("businessKeys").equals("")){
return AjaxResult.error("未选择业务表");
}
//查询所有字段
MtyxsTableStructure mtyxsTableStructure = new MtyxsTableStructure();
mtyxsTableStructure.setBusinessKeys(map.get("businessKeys"));
mtyxsTableStructure.setStatus("0");
List<MtyxsTableStructure> mtyxsTableStructures = mtyxsTableStructureMapper.selectMtyxsTableStructureList(mtyxsTableStructure);
MtyxsBusinessTable mtyxsBusinessTable = new MtyxsBusinessTable();
mtyxsBusinessTable.setBusinessKeys(map.get("businessKeys"));
List<MtyxsBusinessTable> mtyxsBusinessTables = mtyxsBusinessTableMapper.selectMtyxsBusinessTableList(mtyxsBusinessTable);
XSSFWorkbook xssfSheets = new XSSFWorkbook();
XSSFSheet userList = xssfSheets.createSheet(mtyxsBusinessTables.get(0).getBusinessName());
//创建第一行,起始为0
Row titleRow = userList.createRow(0);
int j=0;
for (int i = 0; i < mtyxsTableStructures.size(); i++) {
if (mtyxsTableStructures.get(i).getColumnName().equals("id") || mtyxsTableStructures.get(i).getColumnName().equals("status")){
}else {
titleRow.createCell(j).setCellValue(mtyxsTableStructures.get(i).getColumnDescribe());
j++;
}
}
String fileName = mtyxsBusinessTables.get(0).getBusinessName()+"导入模板.xlsx";
OutputStream outputStream=null;
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
outputStream = response.getOutputStream();
xssfSheets.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return AjaxResult.success();
}
/**
* 查询条件语句
*/
public String selectString(List<MtyxsTableStructure> mtyxsTableStructures,Map<String,String> map){
String selectString = "";
for (int i = 0; i < mtyxsTableStructures.size(); i++) {
if (map.get(mtyxsTableStructures.get(i).getColumnName())==null || map.get(mtyxsTableStructures.get(i).getColumnName()).equals("")){
}else {
if (mtyxsTableStructures.get(i).getSelectType().equals("between")){
selectString+=" and "+mtyxsTableStructures.get(i).getColumnName()+" between '"+map.get(mtyxsTableStructures.get(i).getColumnName()) +"' and '"+map.get(mtyxsTableStructures.get(i).getColumnName()+"_before")+"'";
}else if (mtyxsTableStructures.get(i).getSelectType().equals("like")){
selectString+=" and "+mtyxsTableStructures.get(i).getColumnName()+" like '%"+map.get(mtyxsTableStructures.get(i).getColumnName()) +"%'";
}else {
selectString+=" and "+mtyxsTableStructures.get(i).getColumnName()+" "+mtyxsTableStructures.get(i).getSelectType()+"'"+map.get(mtyxsTableStructures.get(i).getColumnName())+"'";
}
}
}
return selectString;
}
}
5.controller.java
/**
* 表数据管理Controller
*
* @author abab
* @date 2023-04-20
*/
@RestController
@RequestMapping("/mtyxs/tableDate")
public class MtyxsTableDateController extends BaseController {
@Autowired
private IMtyxsTableStructureService mtyxsTableStructureService;
@Autowired
private IMtyxsTableDataService mtyxsTableDataService;
/**
* 回显字段名称List
*/
@PreAuthorize("@ss.hasPermi('mtyxs:data:list')")
@GetMapping("/fieldList")
public AjaxResult fieldList(@RequestParam Map<String,String> map)
{
MtyxsTableStructure mtyxsTableStructure = new MtyxsTableStructure();
mtyxsTableStructure.setBusinessKeys(map.get("businessKeys"));
mtyxsTableStructure.setStatus("0");
List<MtyxsTableStructure> mtyxsTableStructures = mtyxsTableStructureService.selectMtyxsTableStructureList(mtyxsTableStructure);
return AjaxResult.success(mtyxsTableStructures);
}
/**
* 查询表数据List or one
* 查询
*/
@PreAuthorize("@ss.hasPermi('mtyxs:data:list')")
@GetMapping("/dataList")
public AjaxResult TableDateList(@RequestParam Map<String,String> map)
{
List<Map<String, String>> maps = mtyxsTableDataService.selectTableDateList(map);
return AjaxResult.success(maps);
}
/**
* 修改表数据List
* 修改
*/
@PreAuthorize("@ss.hasPermi('mtyxs:data:edit')")
@PostMapping("/updateDataList")
public AjaxResult updateDataList(@RequestBody Map<String,String> map)
{
return mtyxsTableDataService.updateTableDateList(map);
}
/**
* 添加表数据List
* 添加
*/
@PreAuthorize("@ss.hasPermi('mtyxs:data:add')")
@PostMapping("/insertDataList")
public AjaxResult insertDataList(@RequestBody Map<String,String> map)
{
String s = insertDataListNew(map);
if (s.equals("ok")){
return AjaxResult.success();
}else {
return AjaxResult.error(s+"不符合业务规范");
}
}
/**
* 添加表数据List
* 添加
*/
public String insertDataListNew(Map<String,String> map)
{
return mtyxsTableDataService.insertTableDateList(map);
}
/**
* 删除表数据List
* 删除
*/
@PreAuthorize("@ss.hasPermi('mtyxs:data:remove')")
@PostMapping("/deleteDataList")
public AjaxResult deleteDataList(@RequestBody Map<String,String> map)
{
return mtyxsTableDataService.deleteTableDateList(map);
}
/**
* 导出表数据List
* 导出
*/
@PreAuthorize("@ss.hasPermi('mtyxs:data:export')")
@PostMapping("/exportDataList")
public void exportDataList(HttpServletResponse response, @RequestParam Map<String,String> map)
{
mtyxsTableDataService.exportData(response,map);
}
/**
* 导入表数据List
* 导入
*/
@Log(title = "用户管理", businessType = BusinessType.IMPORT)
@PreAuthorize("@ss.hasPermi('mtyxs:data:import')")
@PostMapping("/importDataList")
public AjaxResult importDataList(MultipartFile file,@RequestParam String businessKeys) {
return mtyxsTableDataService.importData(file,businessKeys);
}
/**
* 下载模板
* 导出
*/
@PostMapping("/importTemplate")
public AjaxResult importTemplate(HttpServletResponse response,@RequestParam Map<String,String> map) {
return mtyxsTableDataService.importTemplate(response,map);
}
}
6.导入的方法 可以实现读取合并单元格的信息
public static List readExcel(MultipartFile file,List listChName,Map<String,String> mapColumn,String businessKeys) throws IOException {
//检查文件
checkFile(file);
//获得Workbook工作薄对象
Workbook workbook = getWorkBook(file);
//字段名
List listFirst=new ArrayList<>();
//数据
Map<String,Object> map=new HashMap<>();
List listMap=new ArrayList();
if(workbook != null){
for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
//获得当前sheet工作表
org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(sheetNum);
if(sheet == null){
continue;
}
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
//获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
//获得第一行
Row rowOne = sheet.getRow(0);
//获得当前行的开始列
int firstCellNum = rowOne.getFirstCellNum();
//获得当前行的列数
int lastCellNum = rowOne.getPhysicalNumberOfCells();
//循环所有行
for(int rowNum = firstRowNum;rowNum <= lastRowNum;rowNum++){
//获得当前行
Row row = sheet.getRow(rowNum);
if(row == null){
continue;
}
if (rowNum==firstRowNum && listChName.size()-1!=lastCellNum){
listMap.add("模板不匹配");
return listMap;
}
//循环当前行
for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
Cell cell = row.getCell(cellNum);
if (rowNum==firstRowNum && listChName.size()-1==lastCellNum){
listFirst.add(getCellValue(cell));
if (!listChName.contains(getCellValue(cell))){
listMap.add("模板不匹配");
return listMap;
}
}else {
boolean isMerge = isMergedRegion(sheet,rowNum, cellNum);
Object str="";
if(isMerge) {
Object rs = getMergedRegionValue(sheet, rowNum, cellNum);
str = rs;
}else {
str = getCellValue(cell);
}
map.put(mapColumn.get(listFirst.get(cellNum)),str);
}
}
if (rowNum!=firstRowNum){
map.put("businessKeys",businessKeys);
listMap.add(map);
}
map=new HashMap<>();
}
}
}
return listMap;
}
public static void checkFile(MultipartFile file) throws IOException{
//判断文件是否存在
if(null == file){
System.out.println("文件不存在!");
throw new FileNotFoundException("文件不存在!");
}
//获得文件名
String fileName = file.getOriginalFilename();
//判断文件是否是excel文件
if(!fileName.endsWith("xls") && !fileName.endsWith("xlsx")){
System.out.println(fileName + "不是excel文件");
throw new IOException(fileName + "不是excel文件");
}
}
public static Workbook getWorkBook(MultipartFile file) {
//获得文件名
String fileName = file.getOriginalFilename();
//创建Workbook工作薄对象,表示整个excel
Workbook workbook = null;
try {
//获取excel文件的io流
InputStream is = file.getInputStream();
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if(fileName.endsWith("xls")){
//2003
workbook = new HSSFWorkbook(is);
}else if(fileName.endsWith("xlsx")){
//2007
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
System.out.println(e.getMessage());
}
return workbook;
}
public static Object getCellValue(Cell cell){
Object val = "";
if (StringUtils.isNotNull(cell))
{
if (cell.getCellType() == NUMERIC || cell.getCellType() == CellType.FORMULA)
{
val = cell.getNumericCellValue();
if (DateUtil.isCellDateFormatted(cell))
{
Date date = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
val=new SimpleDateFormat("yyyy-MM-dd").format(date);
}
else
{
if ((Double) val % 1 != 0)
{
val = val.toString();
}
else
{
val = new DecimalFormat("0").format(val);
}
}
}
else if (cell.getCellType() == STRING)
{
val = cell.getStringCellValue();
}
else if (cell.getCellType() == CellType.BOOLEAN)
{
val = cell.getBooleanCellValue();
}
else if (cell.getCellType() == CellType.ERROR)
{
val = cell.getErrorCellValue();
}
}else {
val="";
}
return val;
}
/**
* 判断指定的单元格是否是合并单元格
* @param sheet
* @param row 行下标
* @param column 列下标
* @return
*/
private static boolean isMergedRegion(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
return true;
}
}
}
return false;
}
/**
* 获取合并单元格的值
* @param sheet
* @param row
* @param column
* @return
*/
public static Object getMergedRegionValue(Sheet sheet, int row, int column){
int sheetMergeCount = sheet.getNumMergedRegions();
for(int i = 0 ; i < sheetMergeCount ; i++){
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getCellValue(fCell) ;
}
}
}
return null ;
}
总结:
1.业务表就是用来控制数据库的“创建一张数据库表”和“假删除数据库表“,假删除会对这个表做重命名的操作表示已废弃(恶势力要求)
2.表结构就是用来记录每一张数据库表的字段信息的,可以实现对字段进行属性修改,假删除时不会删除数据库表的字段(恶势力要求),会在表结构里假删除
3.表数据通过表结构内的字段进行SQL动态查询,可以实现增删改查导入导出