Java如何使用Query動態(tài)拼接SQL詳解
更新時間：2019年01月14日 08:35:34 作者：零落星塵
這篇文章主要給大家介紹了關(guān)于Java如何使用Query動態(tài)拼接SQL的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
前言
之前有做個一個自定義報表的查詢，這里使用的是一個動態(tài)的sql拼接，是前端選擇了什么指標(biāo)就查詢什么信息?。ㄟ@里的指標(biāo)是多個表的字段，前端隨便選擇了這些指標(biāo)，然后后端根據(jù)這些指標(biāo)拼接sql，返回這些指標(biāo)的數(shù)據(jù)）。
參數(shù)接受DTO
public class DefinedReportFormDTO {
/**
* 指標(biāo)id
*/
private List ids;
/**
* 開始時間
*/
@DateTimeFormat(pattern = "yyyy-MM")
private Date startTime;
/**
* 結(jié)束時間
*/
@DateTimeFormat(pattern = "yyyy-MM")
private Date endTime;
/**
* 頻率
*/
private String timeStyle;
private boolean avg =false;
private String idsParam;
private String companyIdsParam;
public void setCompanyIdsParam(String companyIdsParam) {
this.companyIdsParam = companyIdsParam;
}
public void setIdsParam(String idsParam) {
this.idsParam = idsParam;
}
public String getCompanyIdsParam() {
return companyIdsParam;
}
public String getIdsParam() {
return idsParam;
}
public boolean isAvg() {
return avg;
}
public void setAvg(boolean avg) {
this.avg = avg;
}
public Date getStartTime() {
return startTime;
}
public void setStartTime(Date startTime) {
this.startTime = startTime;
}
public Date getEndTime() {
return endTime;
}
public void setEndTime(Date endTime) {
this.endTime = endTime;
}
public String getTimeStyle() {
return timeStyle;
}
public void setTimeStyle(String timeStyle) {
this.timeStyle = timeStyle;
}
public List getIds() {
return ids;
}
public void setIds(List ids) {
this.ids = ids;
}
}
數(shù)據(jù)返回VO
public class DefinedReportFormVO implements Serializable {
private String time;
private List<Map<String, Object>> arr = new ArrayList<>();
public String getTime() {
return time;
}
public void setTime(String time) {
this.time = time;
}
public List<Map<String, Object>> getArr() {
return arr;
}
public void setArr(List<Map<String, Object>> arr) {
this.arr = arr;
}
}
控制器Controller
@GetMapping("/report/defindReport")
public JsonResponseExt defindReport(DefinedReportFormDTO definedReportFormDTO){
//測試數(shù)據(jù)
List list1 = new ArrayList<>();
list1.add("111");
definedReportFormDTO.setIds(list1);
definedReportFormDTO.setTimeStyle("month");
definedReportFormDTO.setAvg(true);
Calendar instance = Calendar.getInstance();
instance.set(2018,1,11);
definedReportFormDTO.setStartTime(instance.getTime());
instance.setTime(new Date());
definedReportFormDTO.setEndTime(instance.getTime());
return JsonResponseExt.success(dataAcquisitionFileInfoService.defindQuery(definedReportFormDTO));
}
服務(wù)類Service
public interface DataAcquisitionFileInfoService {
List<DefinedReportFormVO> defindQuery(DefinedReportFormDTO parameter);
}
實現(xiàn)類ServiceImpl
@SuppressWarnings("unchecked")
@Override
public List<DefinedReportFormVO> defindQuery(DefinedReportFormDTO parameter) {
/**
* 定義五張表的查詢字符串，年月，和機(jī)構(gòu)id默認(rèn)查詢
*/
StringBuilder orgInformationCbrc = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id ,");
StringBuilder orgBasicInformation = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id,");
StringBuilder orgBusinessStructure = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id,");
StringBuilder orgProfit = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id,");
StringBuilder orgBalanceSheets = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id,");
//定義機(jī)構(gòu)的字符串
StringBuilder companyIds = new StringBuilder("");
//查詢所有機(jī)構(gòu)
List<Company> orgList = orgService.getOrgList();
//拼接所有機(jī)構(gòu)的字符串（如果需要求平均數(shù)的話）
for (Company company : orgList) {
companyIds.append(company.getId()+",");
}
companyIds.deleteCharAt(companyIds.length()-1);
//定義每個表的字符串判斷
Map<String ,String> bool = new HashMap<>();
//指標(biāo)名
List<String> fieldNames = new ArrayList();
//返回結(jié)果
List<Map<String,Object>> result = new ArrayList<>();
//指標(biāo)名默認(rèn)添加年月機(jī)構(gòu)id
fieldNames.add("reportingYear");
fieldNames.add("reportingMonth");
fieldNames.add("companyId");
//定義指標(biāo)id集合
List ids = parameter.getIds();
//循環(huán)所有的指標(biāo)
for (Object id : ids) {
//如果指標(biāo)為空
if (!"".equals(id) && id != null) {
//根據(jù)指標(biāo)id查詢指標(biāo)
OrgStatisticalIndicators orgStatisticalIndicators = orgStatisticalIndicatorsRespository.findByIdAndAndDelFlag(Long.parseLong(id.toString()));
if(("year".equals(parameter.getTimeStyle()) && "0".equals(orgStatisticalIndicators.getYearQuery())) || ("month".equals(parameter.getTimeStyle()) && "0".equals(orgStatisticalIndicators.getMonthQuery()))){
/**
* 判斷指標(biāo)所在的表，然后為各自的表拼接上表的字段
*/
if ("org_information_cbrc".equals(orgStatisticalIndicators.getTableName())) {
orgInformationCbrc.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,");
//
if (bool.get("org_information_cbrc") == null) {
bool.put("org_information_cbrc", orgStatisticalIndicators.getTableField());
}
//如果其他表不存在這個屬性則為其他表拼接null
orgBasicInformation.append("null as " + orgStatisticalIndicators.getField() + ",");
orgBalanceSheets.append("null as " + orgStatisticalIndicators.getField() + ",");
orgBusinessStructure.append("null as " + orgStatisticalIndicators.getField() + ",");
orgProfit.append("null as " + orgStatisticalIndicators.getField() + ",");
//行業(yè)平均
if (parameter.isAvg()) {
if("year".equals(parameter.getTimeStyle())){
orgInformationCbrc.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,");
}else{
orgInformationCbrc.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
}
orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
orgBasicInformation.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
orgBusinessStructure.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
orgProfit.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
}
} else if ("org_basic_information".equals(orgStatisticalIndicators.getTableName())) {
if (bool.get("org_basic_information") == null) {
bool.put("org_basic_information", orgStatisticalIndicators.getTableField());
}
orgBasicInformation.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,");
orgInformationCbrc.append("null as " + orgStatisticalIndicators.getField() + ",");
orgBalanceSheets.append("null as " + orgStatisticalIndicators.getField() + ",");
orgBusinessStructure.append("null as " + orgStatisticalIndicators.getField() + ",");
orgProfit.append("null as " + orgStatisticalIndicators.getField() + ",");
//行業(yè)平均
if (parameter.isAvg()) {
if("year".equals(parameter.getTimeStyle())){
orgBasicInformation.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,");
}else{
orgBasicInformation.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
}
orgProfit.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
orgInformationCbrc.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
orgBusinessStructure.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
}
} else if ("org_business_structure".equals(orgStatisticalIndicators.getTableName())) {
orgBusinessStructure.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,");
if (bool.get("org_business_structure") == null) {
bool.put("org_business_structure", orgStatisticalIndicators.getTableField());
}
orgBasicInformation.append("null as " + orgStatisticalIndicators.getField() + ",");
orgInformationCbrc.append("null as " + orgStatisticalIndicators.getField() + ",");
orgBalanceSheets.append("null as " + orgStatisticalIndicators.getField() + ",");
orgProfit.append("null as " + orgStatisticalIndicators.getField() + ",");
//行業(yè)平均
if (parameter.isAvg()) {
if("year".equals(parameter.getTimeStyle())){
orgBusinessStructure.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,");
}else{
orgBusinessStructure.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
}
orgProfit.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
orgInformationCbrc.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
orgBasicInformation.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
}
} else if ("org_profit".equals(orgStatisticalIndicators.getTableName())) {
orgProfit.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,");
if (bool.get("org_profit") == null) {
bool.put("org_profit", orgStatisticalIndicators.getTableField());
}
orgBasicInformation.append("null as " + orgStatisticalIndicators.getField() + ",");
orgInformationCbrc.append("null as " + orgStatisticalIndicators.getField() + ",");
orgBalanceSheets.append("null as " + orgStatisticalIndicators.getField() + ",");
orgBusinessStructure.append("null as " + orgStatisticalIndicators.getField() + ",");
//行業(yè)平均
if (parameter.isAvg()) {
if("year".equals(parameter.getTimeStyle())){
orgProfit.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,");
}else{
orgProfit.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
}
orgBasicInformation.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
orgInformationCbrc.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
orgBusinessStructure.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
}
} else if ("org_balance_sheets".equals(orgStatisticalIndicators.getTableName())) {
orgBalanceSheets.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,");
if (bool.get("org_balance_sheets") == null) {
bool.put("org_balance_sheets", orgStatisticalIndicators.getTableField());
}
orgBasicInformation.append("null as " + orgStatisticalIndicators.getField() + ",");
orgInformationCbrc.append("null as " + orgStatisticalIndicators.getField() + ",");
orgBusinessStructure.append("null as " + orgStatisticalIndicators.getField() + ",");
orgProfit.append("null as " + orgStatisticalIndicators.getField() + ",");
//行業(yè)平均
if (parameter.isAvg()) {
if("year".equals(parameter.getTimeStyle())){
orgBalanceSheets.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,");
}else{
orgBalanceSheets.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
}
orgProfit.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
orgInformationCbrc.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
orgBusinessStructure.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
}
}
if (parameter.isAvg()==true) {
fieldNames.add(orgStatisticalIndicators.getField());
fieldNames.add(orgStatisticalIndicators.getField()+"Avg");
} else {
fieldNames.add(orgStatisticalIndicators.getField());
}
}
}
}
//拼接where條件
StringBuilder whereSql = new StringBuilder(" WHERE 1 = 1");
if("year".equals(parameter.getTimeStyle())){
whereSql.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' ");
}else{
whereSql.append(" and CONCAT(reporting_year , '-' ,Right(100+CAST(reporting_month as SIGNED),2) )>= :startYear and CONCAT(reporting_year , '-' ,Right(100+CAST(reporting_month as SIGNED),2) ) <= :endYear");
}
//獲取所有機(jī)構(gòu)id
List parameterCompanyIds = parameter.getCompanyIds();
//如果機(jī)構(gòu)id不為空
if (parameterCompanyIds.size()>0) {
whereSql.append(" AND company_id in ( ");
for (int i = 0; i < parameterCompanyIds.size(); i++) {
whereSql.append(":s"+i+" ,");
}
whereSql.deleteCharAt(whereSql.length()-1);
whereSql.append(" )");
}
//定義Query
Query orgBalanceSheetsQuery = null;
//拼接五張表和條件
orgBalanceSheets.deleteCharAt(orgBalanceSheets.length()-1);
orgBalanceSheets.append(" from org_balance_sheets ");
orgBalanceSheets.append(whereSql);
orgBasicInformation.deleteCharAt(orgBasicInformation.length()-1);
orgBasicInformation.append(" from org_basic_information ");
orgBasicInformation.append(whereSql);
orgBusinessStructure.deleteCharAt(orgBusinessStructure.length()-1);
orgBusinessStructure.append(" from org_business_structure ");
orgBusinessStructure.append(whereSql);
orgInformationCbrc.deleteCharAt(orgInformationCbrc.length()-1);
orgInformationCbrc.append(" from org_information_cbrc ");
orgInformationCbrc.append(whereSql);
orgProfit.deleteCharAt(orgProfit.length()-1);
orgProfit.append(" from org_profit ");
orgProfit.append(whereSql);
//關(guān)聯(lián)五張表
orgBalanceSheets.append(" UNION ");
orgBalanceSheets.append(orgBasicInformation.toString());
orgBalanceSheets.append(" UNION ");
orgBalanceSheets.append(orgBusinessStructure.toString());
orgBalanceSheets.append(" UNION ");
orgBalanceSheets.append(orgInformationCbrc.toString());
orgBalanceSheets.append(" UNION ");
orgBalanceSheets.append(orgProfit.toString());
System.out.println(">>"+orgBalanceSheets.toString());
//創(chuàng)建本地sql查詢實例
orgBalanceSheetsQuery = entityManager.createNativeQuery(orgBalanceSheets.toString());
//如果時間為空那就獲取現(xiàn)在的時間
if(parameter.getEndTime() == null){
parameter.setEndTime(new Date());
}
if(parameter.getStartTime() == null){
parameter.setStartTime(new Date());
}
if("year".equals(parameter.getTimeStyle())){
orgBalanceSheetsQuery.setParameter("startYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getStartTime(),"yyyy"));
orgBalanceSheetsQuery.setParameter("endYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getEndTime(),"yyyy"));
}else if("month".equals(parameter.getTimeStyle())){
orgBalanceSheetsQuery.setParameter("startYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getStartTime(),"yyyy-MM"));
orgBalanceSheetsQuery.setParameter("endYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getEndTime(),"yyyy-MM"));
}
if (parameterCompanyIds.size()>0) {
for (int i = 0; i < parameterCompanyIds.size(); i++) {
orgBalanceSheetsQuery.setParameter("s"+i, parameterCompanyIds.get(i));
}
}
//獲取數(shù)據(jù)
List resultList = orgBalanceSheetsQuery.getResultList();
System.out.println("resultList==="+resultList);
//給數(shù)據(jù)設(shè)置屬性
for (int i = 0; i < resultList.size(); i++) {
Object o = resultList.get(i);
Object[] cells = (Object[]) o;
Map<String,Object> map = new HashMap<>();
if(cells.length == 3){
continue;
}
for (int j = 0; j<cells.length; j++) {
if (cells[j] != null && !"".equals(cells[j].toString())) {
map.put((String) fieldNames.get(j),cells[j]);
}else{
setField(resultList,fieldNames,map,i,j);
}
}
result.add(map);
}
System.out.println("result == "+result);
List<DefinedReportFormVO> definedReportFormVOList = new ArrayList<>();
Map<String,List> stringListMap = new HashMap<>();
//定義返回的格式
for (Map<String, Object> map : result) {
String reportingYear = (String) map.get("reportingYear");
String reportingMonth = (String) map.get("reportingMonth");
String reportingDate = reportingYear+"-"+reportingMonth;
//如果時間類型是年
if ("year".equals(parameter.getTimeStyle())) {
List list = stringListMap.get(reportingYear);
if (list != null) {
list.add(map);
stringListMap.put(reportingYear,list);
}else{
List inner =new ArrayList();
inner.add(map);
stringListMap.put(reportingYear,inner);
}
}else{//如果為月
List list = stringListMap.get(reportingDate);
if (list != null) {
list.add(map);
stringListMap.put(reportingDate,list);
}else{
List inner =new ArrayList();
inner.add(map);
stringListMap.put(reportingDate,inner);
}
}
}
System.out.println("stringListMap == "+stringListMap);
for (Map.Entry<String,List> entry : stringListMap.entrySet()) {
DefinedReportFormVO formVO = new DefinedReportFormVO();
formVO.setTime(entry.getKey());
if(parameter.isAvg()==true){
formVO.setArr(setAvg(entry.getValue(),fieldNames));
}else{
formVO.setArr(entry.getValue());
}
definedReportFormVOList.add(formVO);
}
return definedReportFormVOList;
}
指標(biāo)實體
/**
* 統(tǒng)計指標(biāo)
*/
@Entity
@Table(name = "org_statistical_indicators", catalog = "zhsupervision")
public class OrgStatisticalIndicators {
@Id
@GeneratedValue
private Long id;
/**
* 前端顯示名
*/
private String name;
/**
* 表屬性
*/
private String tableField;
/**
* 表名稱
*/
private String tableName;
/**
* 創(chuàng)建時間
*/
private Date createTime;
/**
* 更新時間
*/
private Date updateTime;
/**
* 刪除標(biāo)識
*/
private String delFlag;
//父節(jié)點(diǎn)
private Long pId;
//屬性
private String field;
//該指標(biāo)查詢月的時候是否查詢
private String monthQuery;
//該指標(biāo)查詢年的時候是否查詢
private String yearQuery;
public String getMonthQuery() {
return monthQuery;
}
public void setMonthQuery(String monthQuery) {
this.monthQuery = monthQuery;
}
public String getYearQuery() {
return yearQuery;
}
public void setYearQuery(String yearQuery) {
this.yearQuery = yearQuery;
}
public String getField() {
return field;
}
public void setField(String field) {
this.field = field;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Long getpId() {
return pId;
}
public void setpId(Long pId) {
this.pId = pId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getTableField() {
return tableField;
}
public void setTableField(String tableField) {
this.tableField = tableField;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Date getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
public String getDelFlag() {
return delFlag;
}
public void setDelFlag(String delFlag) {
this.delFlag = delFlag;
}
}
指標(biāo)Service
/**
* 統(tǒng)計指標(biāo)服務(wù)類
*/
public interface OrgStatisticalIndicatorsService {
/**
* 根據(jù)id獲取
* @param id
* @return
*/
OrgStatisticalIndicators findOrgStatisticalIndicatorsById(Long id);
/**
* 根據(jù)表名查詢
*/
List<OrgStatisticalIndicators> findOrgStatisticalIndicatorsByTableName(String name);
}
指標(biāo)serviceImpl
@Service
public class OrgStatisticalIndicatorsServiceImpl extends BaseServiceImpl<OrgStatisticalIndicators, String> implements OrgStatisticalIndicatorsService {
@Autowired
private OrgStatisticalIndicatorsRespository respository;
@Override
public OrgStatisticalIndicators findOrgStatisticalIndicatorsById(Long id) {
return respository.findByIdAndAndDelFlag(id);
}
@Override
public List<OrgStatisticalIndicators> findOrgStatisticalIndicatorsByTableName(String name) {
return respository.findOrgStatisticalIndicatorsByTableName(name);
}
}
指標(biāo)repository
public interface OrgStatisticalIndicatorsRespository extends JpaSpecificationExecutor {
@Query(value = "select * from org_statistical_indicators WHERE ID=?1 and del_flag = '0'",nativeQuery = true)
OrgStatisticalIndicators findByIdAndAndDelFlag(Long id);
@Query(value = "select * from org_statistical_indicators WHERE del_flag = '0' and NAME =?1",nativeQuery = true)
OrgStatisticalIndicators findOrgStatisticalIndicatorsByName(String name);
}
這個repository要繼承 extends JpaRepository<T, ID> 才可以，寫漏了。
上面使用了union 進(jìn)行表之間的關(guān)聯(lián)查詢，關(guān)聯(lián)的表有點(diǎn)多，所以代碼有些長，同時因為表多，指標(biāo)（表的屬性）有500多個，無法確定查詢的返回實體，所以只能自己根據(jù)數(shù)據(jù)的返回給數(shù)據(jù)綁定屬性。
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。
相關(guān)文章
Jmeter后置處理器實現(xiàn)過程及方法應(yīng)用
這篇文章主要介紹了Jmeter后置處理器實現(xiàn)過程及方法應(yīng)用,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-09-09
這篇文章主要介紹了springboot整合消息隊列RabbitMQ,文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價值，需要的小伙伴可以參考一下2022-08-08
Java 實現(xiàn)完整功能的學(xué)生管理系統(tǒng)實例
讀萬卷書不如行萬里路，只學(xué)書上的理論是遠(yuǎn)遠(yuǎn)不夠的，只有在實戰(zhàn)中才能獲得能力的提升，本篇文章手把手帶你用Java實現(xiàn)一個完整版學(xué)生管理系統(tǒng),大家可以在過程中查缺補(bǔ)漏，提升水平2021-11-11
SpringBoot中的@RequestMapping注解的用法示例
@RequestMapping注解是SpringBoot中最常用的注解之一，它可以幫助開發(fā)者定義和處理HTTP請求，本篇文章我們將詳細(xì)為大家介紹如何使用SpringBoot中的@RequestMapping注解，感興趣的同學(xué)跟著小編一起來學(xué)習(xí)吧2023-06-06
這篇文章主要介紹了Java設(shè)計模式之靜態(tài)代理模式,結(jié)合實例形式分析了靜態(tài)代理模式的概念、原理、定義與用法,需要的朋友可以參考下2018-04-04
這篇文章主要介紹了java檢查數(shù)組是否有重復(fù)元素的方法,涉及java針對數(shù)組元素的操作技巧,具有一定參考借鑒價值,需要的朋友可以參考下2015-07-07
java抓取網(wǎng)頁數(shù)據(jù)獲取網(wǎng)頁中所有的鏈接實例分享
java抓取網(wǎng)頁數(shù)據(jù)獲取網(wǎng)頁中所有的鏈接實例分享，使用方法，只要實例化HtmlParser時傳入網(wǎng)頁地址就可以了2013-12-12
在使用maven進(jìn)行Java項目的開發(fā)過程中，難免會有些公共的私有庫，這些庫是不太方便放到中央倉庫的，可以通過Nexus搭建一個私有倉庫，這篇文章主要介紹了maven私服搭建與使用,需要的朋友可以參考下2023-03-03
servlet監(jiān)聽器的學(xué)習(xí)使用（三）
這篇文章主要為大家詳細(xì)介紹了servlet監(jiān)聽器學(xué)習(xí)使用的相關(guān)資料，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-09-09
springboot使用定時器@Scheduled不管用的解決
這篇文章主要介紹了springboot使用定時器@Scheduled不管用的解決方案，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2021-12-12