【Java】mybatis:解决动态表，动态字段。foreach处理

• Z时代
• 2024-01-10
• 分类：技术分享

@ApiOperation("查询啊")
@RequestMapping(value = "/personManagementDetail", method = RequestMethod.POST)
public MapOutput personManagementDetail(String userTbname, String accountNumber, Integer pageNum, Integer pageSize) {
//查询系统表中是否存在新增的用户表
List<EditAndUserOutputDto> editAndUserEntity = pactera_SystemService.getUserTbNameAndEdit(userTbname);
Map maps = new HashMap();
List<String> stringList = new ArrayList<>();
//查询需要展示的用户表的表名，字段名，字段描述，用来动态展示title和body
List<TitleOutput> titleOutputList = pactera_SystemService.getFieldInformation(userTbname);
List list = new ArrayList();
for (int i = 0; i < titleOutputList.size(); i++) {
Map map = new HashMap<>();
map.put("key", titleOutputList.get(i).getFieldName());
map.put("name", titleOutputList.get(i).getFieldDescription());
list.add(map);
String fieldName = titleOutputList.get(i).getFieldName();
stringList.add(fieldName);
}
if (editAndUserEntity.size() == 0) {
maps.put("header", list);
maps.put("body", 0);
} else {
//当系统表中拥有此用户表时，进行查询，用来展示数据
PageInfo<Map<String, Object>> lists = userService.getBodyResult(userTbname, accountNumber, pageNum, pageSize, stringList);
List<Map<String, Object>> body = lists.getList();
maps.put("header", list);
maps.put("body", body);
}
//0是可编辑状态，1是不可编辑状态
if ("User".equals(userTbname)) {
maps.put("edit", 1);
} else {
maps.put("edit", 0);
}
return new MapOutput(200, "成功", null, maps);
}

SELECT
表名 =
CASE
WHEN a.colorder= 1 THEN
d.name ELSE ''
END,字段序号 = a.colorder,字段名 = a.name,
字段说明 = ISNULL( g.[value], '' )
FROM
syscolumns a
LEFT JOIN systypes b ON a.xusertype= b.xusertype
INNER JOIN sysobjects d ON a.id= d.id
AND d.xtype= 'U'
AND d.name<> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault= e.id
LEFT JOIN sys.extended_properties g ON a.id= g.major_id
AND a.colid= g.minor_id
LEFT JOIN sys.extended_properties f ON d.id= f.major_id
AND f.minor_id= 0
WHERE
d.name LIKE '%User' --如果查询所有表,加上此条件
--d.name = 某用户表  如果只查询指定表,加上此条件
ORDER BY
a.id,
a.colorder;

需求描述：不同系统拥有不同的用户表，如果新增一个系统的话，则需要新增用户表，此时如果在页面展示的话，则会出现问题，显得写的很死，不能灵活调用。
现想针对新增系统用户表的时候能做动态处理，不管新增任何表，都可以动态进行查询，新增，修改，删除操作，不需要后端新增接口，进行增删改查操作。
表结构:

代码：查询，展示详情
controller：

@ApiOperation("查询啊")
@RequestMapping(value = "/personManagementDetail", method = RequestMethod.POST)
public MapOutput personManagementDetail(String userTbname, String accountNumber, Integer pageNum, Integer pageSize) {
//查询系统表中是否存在新增的用户表
List<EditAndUserOutputDto> editAndUserEntity = pactera_SystemService.getUserTbNameAndEdit(userTbname);
Map maps = new HashMap();
List<String> stringList = new ArrayList<>();
//查询需要展示的用户表的表名，字段名，字段描述，用来动态展示title和body
List<TitleOutput> titleOutputList = pactera_SystemService.getFieldInformation(userTbname);
List list = new ArrayList();
for (int i = 0; i < titleOutputList.size(); i++) {
Map map = new HashMap<>();
map.put("key", titleOutputList.get(i).getFieldName());
map.put("name", titleOutputList.get(i).getFieldDescription());
list.add(map);
String fieldName = titleOutputList.get(i).getFieldName();
stringList.add(fieldName);
}
if (editAndUserEntity.size() == 0) {
maps.put("header", list);
maps.put("body", 0);
} else {
//当系统表中拥有此用户表时，进行查询，用来展示数据
PageInfo<Map<String, Object>> lists = userService.getBodyResult(userTbname, accountNumber, pageNum, pageSize, stringList);
List<Map<String, Object>> body = lists.getList();
maps.put("header", list);
maps.put("body", body);
}
//0是可编辑状态，1是不可编辑状态
if ("User".equals(userTbname)) {
maps.put("edit", 1);
} else {
maps.put("edit", 0);
}
return new MapOutput(200, "成功", null, maps);
}

对应mapper.xml

SELECT
表名 =
CASE
WHEN a.colorder= 1 THEN
d.name ELSE ''
END,字段序号 = a.colorder,字段名 = a.name,
字段说明 = ISNULL( g.[value], '' )
FROM
syscolumns a
LEFT JOIN systypes b ON a.xusertype= b.xusertype
INNER JOIN sysobjects d ON a.id= d.id
AND d.xtype= 'U'
AND d.name<> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault= e.id
LEFT JOIN sys.extended_properties g ON a.id= g.major_id
AND a.colid= g.minor_id
LEFT JOIN sys.extended_properties f ON d.id= f.major_id
AND f.minor_id= 0
WHERE
d.name LIKE '%User' --如果查询所有表,加上此条件
--d.name = 某用户表  如果只查询指定表,加上此条件
ORDER BY
a.id,
a.colorder;

查询结果：

以上是 【Java】mybatis:解决动态表，动态字段。foreach处理 的全部内容， 来源链接： utcz.com/a/106197.html