分类:
项目1
本次的基本依赖 jdbc、web、swagger、Lombok、mybatis
pojo、实体
代码
@Data //Lombok
@AllArgsConstructor
@NoArgsConstructor
public class OutPageAndDescMenuDto {
@ApiModelProperty(value = "菜单id")
private Integer id;
@ApiModelProperty(value = "父级Id")
private Integer parentId;
@ApiModelProperty(value = "菜单名称")
private String name;
@ApiModelProperty(value = "显示名称")
private String displayName;
@ApiModelProperty(value = "菜单的链接地址数组")
private String url;
@ApiModelProperty(value = "按钮权限id数值字符串")
private String menuButtonPer_id;
@ApiModelProperty(value = "排序字段")
private Integer sortNo;
//=====================OutPageAndDesc分页排序专用
@ApiModelProperty(value = "每页的行数")
private Integer rows;
@ApiModelProperty(value = "第几页的页数")
private Integer pages;
// 排序 SQl
@ApiModelProperty(hidden = true)
private String sqlOrder;
@ApiModelProperty(value = "排序字段元素")
private List<String> listS;
@ApiModelProperty(value = "排序字段的升降序元素。0：升、1：降")
private List<Integer> listI;
}mapper、接口
@Mapper
@Repository
public interface IMenuMapper {
Integer addMenu(InputMenuDto inDto);
Integer delMenu(@Param("Ids") List<Integer> ids);
@Select("select* from Menu where Id=#{Id}")
Menu queryMenuById(@Param("Id") Integer id);
@Select("select* from Menu where parentId=#{parentId}")
List<Menu> queryMenuByParentId(@Param("parentId") Integer parentId);
Integer updateMenu(UpdateMenuDto upDto);
/**
* 模糊分页排序的接口
* @param outDto
* @return
*/
List<Menu> queryMenuByAll(OutPageAndDescMenuDto outDto);
}mybatis、与mybatis相关的查询条件映射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">
<!--这个是接口路径集dao层-->
<mapper namespace="com.example.demo.mapper.IMenuMapper">
<!--模糊分页排序查询-->
<select id="queryMenuByAll" resultType="Menu">
select <if test="rows!=null">top ${rows}</if> * from Menu
<where>
<if test="id!=null">and Id=#{id}</if>
<if test="parentId!=null">and parentId=#{parentId}</if>
<if test="name!=null">and name LIKE CONCAT('%',#{name},'%')</if>
<if test="displayName!=null">and displayName LIKE CONCAT('%',#{displayName},'%')</if>
<if test="url!=null">and url LIKE CONCAT('%',#{url},'%')</if>
<if test="menuButtonPer_id!=null">and menuButtonPer_id LIKE CONCAT('%',#{menuButtonPer_id},'%')</if>
<if test="sortNo!=null">and sortNo LIKE CONCAT('%',#{sortNo},'%')</if>
<if test="rows!=null and pages!=null">
and Id not in(select top(#{rows}*(#{pages}-1))Id from Menu
<where>
<!-- 这里的where筛选字段都是上面来的-->
<if test="id!=null">and Id=#{id}</if>
<if test="parentId!=null">and parentId=#{parentId}</if>
<if test="name!=null">and name LIKE CONCAT('%',#{name},'%')</if><!-- 字段 LIKE CONCAT('%',#{字段},'%') 模糊查询的关键-->
<if test="displayName!=null">and displayName LIKE CONCAT('%',#{displayName},'%')</if>
<if test="url!=null">and url LIKE CONCAT('%',#{url},'%')</if>
<if test="menuButtonPer_id!=null">and menuButtonPer_id LIKE CONCAT('%',#{menuButtonPer_id},'%')</if>
<if test="sortNo!=null">and sortNo LIKE CONCAT('%',#{sortNo},'%')</if>
</where>
<!-- 此处用了拼接语句-->
<if test="sqlOrder!=null">
order by ${sqlOrder})
</if>
<if test="sqlOrder==null">
order by Id)
</if>
</if>
</where>
<if test="sqlOrder!=null">
order by ${sqlOrder}
</if>
</select>
</mapper>此次我对排序字段进行了封装，如下
DescMessage：装返回信息用的模型
/**
* sql排序方法执行信息
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class DescMessage {
@ApiModelProperty(value = "错误信息")
private String message = "成功";
@ApiModelProperty(value = "返回的信息")
private String data;
public DescMessage(String data) {
this.data = data;
}
}DescFunction（）：分解组装排序字段的方法
代码：
@Component
public class DescFunction {
/**
* 生成排序的字段方法
* @param listI 升（0）降序（1）
* @param listS 排序字段
* @return
*/
public DescMessage descFunction(List<Integer> listI, List<String> listS) {
try {
if (listS != null) {
if (listI == null) {
return new DescMessage("失败", "报错！没有给排序的字段添加排序的方式，即升降序");
}
if (listI.size() != listS.size()) {
return new DescMessage("失败", "报错！排序字段与其升降序字段元素个数要相等");
}
String sortSql = "";
int IdNumber = 0;
for (int i = 0; i < listS.size(); i++) {
if (listS.get(i).equals("id") || listS.get(i).equals("Id")) {//这是检查是否存在id字段
IdNumber++;
}
sortSql += listS.get(i);
if (listI.get(i) == 0) { //0升序
}
if (listI.get(i) == 1) { //降序
sortSql += " desc";
}
if (i < listS.size() - 1) {
sortSql += ",";
}
}
if (IdNumber == 0) {
sortSql += ",Id";
}
return new DescMessage(sortSql);
}else {
return new DescMessage(null);
}
} catch (Exception e) {
return new DescMessage("失败","报错!"+e.getMessage());
}
}
}server：业务逻辑层
封装了排序字段的代码
@Service
public class MenuServer {
@Autowired
private IMenuMapper iMenuMapper;//菜单接口
@Autowired
private DescFunction descFunction;//生成排序的字段方法
/**
* 菜单单表模糊查
*
* @param outDescDto
* @return
*/
public RestMessage queryMenuByAll(OutPageAndDescMenuDto outDescDto) {
try {
//每次排序所需
DescMessage descMessage = descFunction.descFunction(outDescDto.getListI(), outDescDto.getListS());
if (descMessage.getMessage().equals("失败")) {
return new RestMessage(2000, descMessage.getData());
} else {
outDescDto.setSqlOrder(descMessage.getData());
}
List<Menu> menus = iMenuMapper.queryMenuByAll(outDescDto);
return new RestMessage(menus);
} catch (Exception e) {
return new RestMessage(2000, "报错！" + e.getMessage());
}
}
}没有封装排序字段的代码
public RestMessage queryMenuButtonPerByAll(OutPageAndDescMenuButtonPerDto outDescDto) {
try {
if (outDescDto.getListS() != null) {
List<String> listS = outDescDto.getListS();
List<Integer> listI = outDescDto.getListI();
if (listI == null) {
return new RestMessage(2000, "报错！没有给排序的字段添加排序的方式，即升降序");
}
if (listI.size() != listS.size()) {
return new RestMessage(2000, "报错！排序字段与其升降序字段元素个数要相等");
}
String sortSql = "";
int IdNumber = 0;
for (int i = 0; i < listS.size(); i++) {
if (listS.get(i).equals("id") || listS.get(i).equals("Id")) {
IdNumber++;
}
sortSql += listS.get(i);
if (listI.get(i) == 0) { //0升序
}
if (listI.get(i) == 1) { //降序
sortSql += " desc";
}
if (i < listS.size() - 1) {
sortSql += ",";
}
}
if (IdNumber == 0) {
sortSql += ",Id";
}
String s = sortSql;
outDescDto.setSqlOrder(sortSql);
}
List<MenuButtonPer> menuButtonPers = iMenuButtonPerMapper.queryMenuButtonPerByAll(outDescDto);
return new RestMessage(menuButtonPers);
} catch (Exception e) {
return new RestMessage(2000, "报错！" + e.getMessage());
}
}controller：控制层
@RestController
@Api(tags = "菜单管理")
@RequestMapping("/api/practice/v1" + "/Menu")
public class Menu {
@Autowired
private MenuButtonServer menuButtonServer;//按钮服务
@Autowired
private MenuButtonPerServer menuButtonPerServer;//按钮权限
@Autowired
private MenuServer menuServer;//菜单
@ApiOperation("模糊排序查询菜单")
@GetMapping("/queryMenuByAll")
public RestMessage queryMenuByAll(OutPageAndDescMenuDto outDescDto){
return menuServer.queryMenuByAll(outDescDto);
}
}swagger样式
评价
