QueryBySqlDto.java 6.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231
  1. package com.boman.domain.dto;
  2. import com.alibaba.fastjson.JSONObject;
  3. import com.boman.domain.GenTableColumn;
  4. import lombok.Data;
  5. import org.apache.commons.lang3.StringUtils;
  6. import org.springframework.util.CollectionUtils;
  7. import java.util.*;
  8. import static com.boman.domain.dto.FormDataDto.DEFAULT_PAGE_NO;
  9. import static com.boman.domain.dto.FormDataDto.MAX_PAGE_SIZE;
  10. import static com.boman.domain.dto.FormDataDto.DEFAULT_PAGE_SIZE;
  11. import static com.boman.domain.constant.SqlConstant.*;
  12. /**
  13. * @author shiqian
  14. * @date 2021年07月13日 11:25
  15. **/
  16. @Data
  17. public class QueryBySqlDto {
  18. private static final String LEFT_JOIN = " left join ";
  19. private static final String COUNT_1 = " count(1) ";
  20. /** 主表表名 **/
  21. private String primaryTableName;
  22. /** 子表表名 **/
  23. private String deputyTableName;
  24. /** 主表和子表 关联字段名称 **/
  25. private String primaryTableNameRelKey;
  26. /** 子表和主表 关联字段名称 **/
  27. private String deputyTableNameRelKey;
  28. /** 需要展示的列 **/
  29. private List<String> showData;
  30. /** 条件 <表名.字段名称, value> **/
  31. private JSONObject condition;
  32. private Integer pageNo;
  33. private Integer pageSize;
  34. private String orderBy;
  35. public int getLimit() {
  36. try {
  37. return pageNo == 0 ? 0 : (pageNo - 1) * pageSize;
  38. } catch (Exception e) {
  39. e.printStackTrace();
  40. return DEFAULT_PAGE_NO;
  41. }
  42. }
  43. public int getOffset(){
  44. try {
  45. int pageSize = this.pageSize == 0 ? DEFAULT_PAGE_SIZE : this.pageSize;
  46. return Math.min(pageSize, MAX_PAGE_SIZE);
  47. } catch (Exception e) {
  48. e.printStackTrace();
  49. return DEFAULT_PAGE_SIZE;
  50. }
  51. }
  52. /**
  53. * 功能描述: 构建sql语句,暂且只查主表中的数据 todo
  54. *
  55. * @param pShowData 主表的showData
  56. * @param queryBySql 前台传过来的dto
  57. * @return java.lang.String
  58. */
  59. public static String buildDataSql(List<String> pShowData, QueryBySqlDto queryBySql) {
  60. String primaryTableName = queryBySql.getPrimaryTableName();
  61. String deputyTableName = queryBySql.getDeputyTableName();
  62. return SELECT + buildShowData(pShowData, primaryTableName)
  63. // + buildShowData(dShowData, queryBySql.getDeputyTableName())
  64. + FROM + primaryTableName + LEFT_JOIN + deputyTableName + buildOn(queryBySql);
  65. }
  66. /**
  67. * 功能描述: 构建sql语句,暂且只查主表中的条数 todo
  68. *
  69. * @param queryBySql 前台传过来的dto
  70. * @return java.lang.String
  71. */
  72. public static String buildCountSql(QueryBySqlDto queryBySql) {
  73. return SELECT + COUNT_1
  74. // + buildShowData(dShowData, queryBySql.getDeputyTableName())
  75. + FROM + queryBySql.getPrimaryTableName() + LEFT_JOIN + queryBySql.getDeputyTableName() + buildOn(queryBySql);
  76. }
  77. /**
  78. * 功能描述: 拼接sql语句 showData
  79. *
  80. * @param showData showData
  81. * @param tableName tableName
  82. * @return java.lang.String
  83. */
  84. public static String buildShowData(List<String> showData, String tableName) {
  85. StringBuilder sql = new StringBuilder(ALL);
  86. if (!CollectionUtils.isEmpty(showData)) {
  87. sql = new StringBuilder(EMPTY);
  88. for (String showDatum : showData) {
  89. String str = tableName + POINT + showDatum + ", ";
  90. sql.append(str);
  91. }
  92. return StringUtils.substringBeforeLast(sql.toString(), ", ");
  93. }
  94. return sql.toString();
  95. }
  96. /**
  97. * 功能描述: 连表查的 on 条件
  98. *
  99. * @param queryBySql queryBySql
  100. * @return java.lang.String
  101. */
  102. public static String buildOn(QueryBySqlDto queryBySql) {
  103. return ON + queryBySql.getPrimaryTableName() + POINT + queryBySql.getPrimaryTableNameRelKey()
  104. + EQ + queryBySql.getDeputyTableName() + POINT + queryBySql.getDeputyTableNameRelKey();
  105. }
  106. /**
  107. * 功能描述: 表名.属性名 取出属性名
  108. *
  109. * @param key 表名.属性名
  110. * @return java.lang.String
  111. */
  112. public static String getColumnNameFromCondition(String key) {
  113. if (StringUtils.isEmpty(key)) {
  114. throw new IllegalArgumentException("key为空");
  115. }
  116. String[] split = key.split("\\$");
  117. assert split.length == 2;
  118. // split[0] 表名, split[1] 属性名
  119. return split[1].trim();
  120. }
  121. public static boolean isEmpty(Object input) {
  122. if (input instanceof String) {
  123. return StringUtils.isEmpty((String) input);
  124. } else if (input instanceof Collection) {
  125. return CollectionUtils.isEmpty(((Collection<?>) input));
  126. } else {
  127. return Objects.isNull(input);
  128. }
  129. }
  130. public static String packColumn(String tableName, String columnName) {
  131. return tableName + POINT + columnName;
  132. }
  133. /******************************************************** get set ********************************************************/
  134. public String getPrimaryTableName() {
  135. return primaryTableName;
  136. }
  137. public void setPrimaryTableName(String primaryTableName) {
  138. this.primaryTableName = primaryTableName;
  139. }
  140. public String getDeputyTableName() {
  141. return deputyTableName;
  142. }
  143. public void setDeputyTableName(String deputyTableName) {
  144. this.deputyTableName = deputyTableName;
  145. }
  146. public String getPrimaryTableNameRelKey() {
  147. return primaryTableNameRelKey;
  148. }
  149. public void setPrimaryTableNameRelKey(String primaryTableNameRelKey) {
  150. this.primaryTableNameRelKey = primaryTableNameRelKey;
  151. }
  152. public String getDeputyTableNameRelKey() {
  153. return deputyTableNameRelKey;
  154. }
  155. public void setDeputyTableNameRelKey(String deputyTableNameRelKey) {
  156. this.deputyTableNameRelKey = deputyTableNameRelKey;
  157. }
  158. public List<String> getShowData() {
  159. return showData;
  160. }
  161. public void setShowData(List<String> showData) {
  162. this.showData = showData;
  163. }
  164. public JSONObject getCondition() {
  165. return condition;
  166. }
  167. public void setCondition(JSONObject condition) {
  168. this.condition = condition;
  169. }
  170. public Integer getPageNo() {
  171. return pageNo;
  172. }
  173. public void setPageNo(Integer pageNo) {
  174. this.pageNo = pageNo;
  175. }
  176. public Integer getPageSize() {
  177. return pageSize;
  178. }
  179. public void setPageSize(Integer pageSize) {
  180. this.pageSize = pageSize;
  181. }
  182. public String getOrderBy() {
  183. return orderBy;
  184. }
  185. public void setOrderBy(String orderBy) {
  186. this.orderBy = orderBy;
  187. }
  188. }