Java mysql语句动态执行、分页和获取总数

Java mysql语句动态执行、分页和获取总数

目录

1，动态执行

2，分页

1,Mysql的分页:

从零开始：

方式一：

方式二：offset

通用的规律：

3，综合的例子：

需求：

实现：

参数：SheetQuery

返回值：SheetResult

正则替换类：RegUtils

服务：SheetService

Mapper和xml

测试：

总结：

---

 

1，动态执行

使用mybatis

 

SheetService:

@Resource
private SheetSourceMapper sheetSourceMapper;



String sqlParam = "select * from account"
List<Map<String,Object>> result = excuteSoureSql(sqlParam);

public List<Map<String,Object>> excuteSoureSql(String sqlParam){
    return sheetSourceMapper.executeSql(sqlParam);
}

ReportSourceMapper:

List<Map<String, Object>> executeSql(@Param("sqlParameter") String sqlParameter);

Xml:

<select id="executeSql" resultType="map">

  ${sqlParameter}

</select>

这边注意 Mapper @Param("sqlParameter") 参数名的说明

 

2，分页

1,Mysql的分页:

从零开始：

比如： branch 有四条数据：

SELECT * FROM branch LIMIT 0, 5 ;

 

SELECT * FROM branch LIMIT 1, 5 ;

 

这时候就只有3条。 确认是从零开始计算的

方式一：

SELECT * FROM account LIMIT 0, 6 ;

 

 

LIMIT 0, 6： 0 变成开始的数字， 6表示限定的条数

 

方式二：offset

SELECT * FROM account LIMIT 6 OFFSET 0 ;

LIMIT 6 OFFSET 0 ： 6表示限定的条数, offset 偏移量，0 表示开始的数字

这个更好理解，限制返回多少条，偏移多少，即从第xx开始

 

通用的规律：

 

假设pageSize表示每页要显示的条数，pageNumber表示页码。

设定pageNumber从数字1开始，比如限定的size为10：

第一页： page: 1, size: 10; 即： 0-9

第二页： page: 2, size: 10; 即： 10-19

第三页： page: 3, size: 10; 即： 20-39

 

方式一：

SELECT * FROM account LIMIT (pageNumber-1)*pageSize,pageSize;

 

方式二：

SELECT * FROM account LIMIT pageSize OFFSET (pageNumber-1)*pageSize;

 

如果pageNumber是从0开始，就不用减1了。看具体情况而定

 

3，综合的例子：

需求：

 

请求参数

typeId

page 当前页数，为空不分页

size 每页大小，为空不分页

param:{} 动态查询参数

 

要求：

根据typeId动态获取要查询的值进行动态匹配后，再执行sql

比如： SELECT * FROM employee where f_name=$param.name and dept_id=$global.DEPT_ID

 

 

实现：

 

先声明参数和返回值：

 

参数：SheetQuery

public class SheetQuery {

    private Long classId;

    private Integer page;

    private Integer size;

    private Map<String,Object> param = new HashMap<>();

    public Long getClassId() {
        return classId;
    }

    public void setClassId(Long classId) {
        this.classId = classId;
    }

    public Integer getPage() {
        return page;
    }

    public void setPage(Integer page) {
        this.page = page;
    }

    public Integer getSize() {
        return size;
    }

    public void setSize(Integer size) {
        this.size = size;
    }

    public Map<String, Object> getParam() {
        return param;
    }

    public void setParam(Map<String, Object> param) {
        this.param = param;
    }

}

 

返回值：SheetResult

public class SheetResult {

    private Long total;

    private Object data;

    public Long getTotal() {
        return total;
    }

    public void setTotal(Long total) {
        this.total = total;
    }

    public Object getData() {
        return data;
    }

    public void setData(Object data) {
        this.data = data;
    }
}

 

正则替换类：RegUtils

public class RegUtils {

    public static String getReplaceStr(String content, Map<String, Object> param)
    {
        String pattern = "\\$[a-zA-Z_]*\\.([a-zA-Z_]*)";
        Pattern p = Pattern.compile(pattern);
        Matcher m = p.matcher(content);
        StringBuffer sb = new StringBuffer();
        while (m.find())
        {
            String key = m.group(1);
            String value = MapUtils.getString(param, key);
            m.appendReplacement(sb, value == null ? "" : ("\"").concat(value).concat("\""));
        }

        m.appendTail(sb);
        return sb.toString();
    }

}

 

服务：SheetService

@Service
public class SheetService {

    @Resource
    private SheetSourceMapper sheetSourceMapper;

    public SheetResult getSheetResult(SheetQuery sheetQuery){
        //替换表达式的参数
        // 根据typeId动态获取，这边就直接赋值
        String sqlExpr = "SELECT * FROM employee where f_name=$param.name and dept_id=$global.DEPT_ID";
        String sqlStr = RegUtils.getReplaceStr(sqlExpr, sheetQuery.getParam());
        String sqlParam = sqlStr.concat(appendSqlParam(sheetQuery));
        SheetResult sheetResult = new SheetResult();
        List<Map<String,Object>> result = excuteSoureSql(sqlParam);
        sheetResult.setTotal(getSqlTotal(sqlStr));
        sheetResult.setData(result);
        return sheetResult;
    }

    // 获取总数
    private long getSqlTotal(String sqlStr){
        // 处理from可能是小写的情况
        String splitStr = "FROM";

        if(!sqlStr.contains(splitStr)){
            splitStr = "from";
        }
        String[] wheres = sqlStr.split(splitStr);
        String sqlParam = "select count(*) as total from ".concat(wheres[1]);
        List<Map<String,Object>> result = excuteSoureSql(sqlParam);
        Map<String, Object> totalMap = ListUtils.emptyIfNull(result).stream().findFirst().orElse(new HashMap<>());
        return MapUtils.getLongValue(totalMap,"total");
    }

    // 拼接分页的查询内容
    private String appendSqlParam(SheetQuery sheetQuery){
        Integer pageNumber = sheetQuery.getPage();
        Integer pageSize = sheetQuery.getSize();
        String limitStr = "";
        // 如果size 为空就直接分页
        if (!Objects.isNull(pageSize)) {
            // pageNumber从1开始
            if(Objects.isNull(pageNumber) || pageNumber.equals(NumberUtils.INTEGER_ZERO)){
                pageNumber = NumberUtils.INTEGER_ONE;
            }
            limitStr = " limit "+ ((pageNumber-1)*pageSize) + ", "+ pageSize;
        }

        return limitStr;
    }

    public List<Map<String,Object>> excuteSoureSql(String sqlParam){
        return sheetSourceMapper.executeSql(sqlParam);
    }

}

 

Mapper和xml

 

Mapper:

public interface SheetSourceMapper {
    List<Map<String, Object>> executeSql(@Param("sqlParameter") String sqlParameter);
}

 

xml:

<select id="executeSql" resultType="map">
  ${sqlParameter}
</select>

 

测试：

@RunWith(SpringRunner.class)
@SpringBootTest
public class SheetApplicationTests {
 
   @Autowired
   private SheetService sheetService;


   @Test
   public void testSheetResult() {
      SheetQuery sheetQuery = new SheetQuery();
      int page = 0;
      int size = 10;
      Map<String,Object> param = new HashMap<>();
      param.put("name","Robert");
      param.put("DEPT_ID",1);
      sheetQuery.setPage(page);
      sheetQuery.setSize(size);
      sheetQuery.setParam(param);
      sheetService.getSheetResult(sheetQuery);

   }
 
}

 

备注： 这个是在springboot项目测试的，启动的时候实例化service

 

可以从 创建spring项目 直接建一个springboot项目，再引入对应的包就行

 

总结：

    在获取总数的时候，也想过用一次查询的时候，网上查了下使用：SELECT found_rows() AS recordCounts; 但是这种方式，我试验的时候并没有获取到全部，就放弃了。

    Mysql分页的时候，注意从0开始。其它嘛，根据需求去写。

   动态参数的处理