123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231 |
- package com.boman.domain.dto;
- import com.alibaba.fastjson.JSONObject;
- import com.boman.domain.GenTableColumn;
- import lombok.Data;
- import org.apache.commons.lang3.StringUtils;
- import org.springframework.util.CollectionUtils;
- import java.util.*;
- import static com.boman.domain.dto.FormDataDto.DEFAULT_PAGE_NO;
- import static com.boman.domain.dto.FormDataDto.MAX_PAGE_SIZE;
- import static com.boman.domain.dto.FormDataDto.DEFAULT_PAGE_SIZE;
- import static com.boman.domain.constant.SqlConstant.*;
- /**
- * @author shiqian
- * @date 2021年07月13日 11:25
- **/
- @Data
- public class QueryBySqlDto {
- private static final String LEFT_JOIN = " left join ";
- private static final String COUNT_1 = " count(1) ";
- /** 主表表名 **/
- private String primaryTableName;
- /** 子表表名 **/
- private String deputyTableName;
- /** 主表和子表 关联字段名称 **/
- private String primaryTableNameRelKey;
- /** 子表和主表 关联字段名称 **/
- private String deputyTableNameRelKey;
- /** 需要展示的列 **/
- private List<String> showData;
- /** 条件 <表名.字段名称, value> **/
- private JSONObject condition;
- private Integer pageNo;
- private Integer pageSize;
- private String orderBy;
- public int getLimit() {
- try {
- return pageNo == 0 ? 0 : (pageNo - 1) * pageSize;
- } catch (Exception e) {
- e.printStackTrace();
- return DEFAULT_PAGE_NO;
- }
- }
- public int getOffset(){
- try {
- int pageSize = this.pageSize == 0 ? DEFAULT_PAGE_SIZE : this.pageSize;
- return Math.min(pageSize, MAX_PAGE_SIZE);
- } catch (Exception e) {
- e.printStackTrace();
- return DEFAULT_PAGE_SIZE;
- }
- }
- /**
- * 功能描述: 构建sql语句,暂且只查主表中的数据 todo
- *
- * @param pShowData 主表的showData
- * @param queryBySql 前台传过来的dto
- * @return java.lang.String
- */
- public static String buildDataSql(List<String> pShowData, QueryBySqlDto queryBySql) {
- String primaryTableName = queryBySql.getPrimaryTableName();
- String deputyTableName = queryBySql.getDeputyTableName();
- return SELECT + buildShowData(pShowData, primaryTableName)
- // + buildShowData(dShowData, queryBySql.getDeputyTableName())
- + FROM + primaryTableName + LEFT_JOIN + deputyTableName + buildOn(queryBySql);
- }
- /**
- * 功能描述: 构建sql语句,暂且只查主表中的条数 todo
- *
- * @param queryBySql 前台传过来的dto
- * @return java.lang.String
- */
- public static String buildCountSql(QueryBySqlDto queryBySql) {
- return SELECT + COUNT_1
- // + buildShowData(dShowData, queryBySql.getDeputyTableName())
- + FROM + queryBySql.getPrimaryTableName() + LEFT_JOIN + queryBySql.getDeputyTableName() + buildOn(queryBySql);
- }
- /**
- * 功能描述: 拼接sql语句 showData
- *
- * @param showData showData
- * @param tableName tableName
- * @return java.lang.String
- */
- public static String buildShowData(List<String> showData, String tableName) {
- StringBuilder sql = new StringBuilder(ALL);
- if (!CollectionUtils.isEmpty(showData)) {
- sql = new StringBuilder(EMPTY);
- for (String showDatum : showData) {
- String str = tableName + POINT + showDatum + ", ";
- sql.append(str);
- }
- return StringUtils.substringBeforeLast(sql.toString(), ", ");
- }
- return sql.toString();
- }
- /**
- * 功能描述: 连表查的 on 条件
- *
- * @param queryBySql queryBySql
- * @return java.lang.String
- */
- public static String buildOn(QueryBySqlDto queryBySql) {
- return ON + queryBySql.getPrimaryTableName() + POINT + queryBySql.getPrimaryTableNameRelKey()
- + EQ + queryBySql.getDeputyTableName() + POINT + queryBySql.getDeputyTableNameRelKey();
- }
- /**
- * 功能描述: 表名.属性名 取出属性名
- *
- * @param key 表名.属性名
- * @return java.lang.String
- */
- public static String getColumnNameFromCondition(String key) {
- if (StringUtils.isEmpty(key)) {
- throw new IllegalArgumentException("key为空");
- }
- String[] split = key.split("\\$");
- assert split.length == 2;
- // split[0] 表名, split[1] 属性名
- return split[1].trim();
- }
- public static boolean isEmpty(Object input) {
- if (input instanceof String) {
- return StringUtils.isEmpty((String) input);
- } else if (input instanceof Collection) {
- return CollectionUtils.isEmpty(((Collection<?>) input));
- } else {
- return Objects.isNull(input);
- }
- }
- public static String packColumn(String tableName, String columnName) {
- return tableName + POINT + columnName;
- }
- /******************************************************** get set ********************************************************/
- public String getPrimaryTableName() {
- return primaryTableName;
- }
- public void setPrimaryTableName(String primaryTableName) {
- this.primaryTableName = primaryTableName;
- }
- public String getDeputyTableName() {
- return deputyTableName;
- }
- public void setDeputyTableName(String deputyTableName) {
- this.deputyTableName = deputyTableName;
- }
- public String getPrimaryTableNameRelKey() {
- return primaryTableNameRelKey;
- }
- public void setPrimaryTableNameRelKey(String primaryTableNameRelKey) {
- this.primaryTableNameRelKey = primaryTableNameRelKey;
- }
- public String getDeputyTableNameRelKey() {
- return deputyTableNameRelKey;
- }
- public void setDeputyTableNameRelKey(String deputyTableNameRelKey) {
- this.deputyTableNameRelKey = deputyTableNameRelKey;
- }
- public List<String> getShowData() {
- return showData;
- }
- public void setShowData(List<String> showData) {
- this.showData = showData;
- }
- public JSONObject getCondition() {
- return condition;
- }
- public void setCondition(JSONObject condition) {
- this.condition = condition;
- }
- public Integer getPageNo() {
- return pageNo;
- }
- public void setPageNo(Integer pageNo) {
- this.pageNo = pageNo;
- }
- public Integer getPageSize() {
- return pageSize;
- }
- public void setPageSize(Integer pageSize) {
- this.pageSize = pageSize;
- }
- public String getOrderBy() {
- return orderBy;
- }
- public void setOrderBy(String orderBy) {
- this.orderBy = orderBy;
- }
- }
|