Java如何使用Query动态拼接SQL详解
前言
之前有做个一个自定义报表的查询，这里使用的是一个动态的sql拼接，是前端选择了什么指标就查询什么信息！（这里的指标是多个表的字段，前端随便选择了这些指标，然后后端根据这些指标拼接sql，返回这些指标的数据）。
参数接受DTO
public class DefinedReportFormDTO {
/**
* 指标id
*/
private List ids;
/**
* 开始时间
*/
@DateTimeFormat(pattern = "yyyy-MM")
private Date startTime;
/**
* 结束时间
*/
@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;
}
}
数据返回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){
//测试数据
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));
}
服务类Service
public interface DataAcquisitionFileInfoService {
List<DefinedReportFormVO> defindQuery(DefinedReportFormDTO parameter);
}
实现类ServiceImpl
@SuppressWarnings("unchecked")
@Override
public List<DefinedReportFormVO> defindQuery(DefinedReportFormDTO parameter) {
/**
* 定义五张表的查询字符串，年月，和机构id默认查询
*/
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,");
//定义机构的字符串
StringBuilder companyIds = new StringBuilder("");
//查询所有机构
List<Company> orgList = orgService.getOrgList();
//拼接所有机构的字符串（如果需要求平均数的话）
for (Company company : orgList) {
companyIds.append(company.getId()+",");
}
companyIds.deleteCharAt(companyIds.length()-1);
//定义每个表的字符串判断
Map<String ,String> bool = new HashMap<>();
//指标名
List<String> fieldNames = new ArrayList();
//返回结果
List<Map<String,Object>> result = new ArrayList<>();
//指标名默认添加年月机构id
fieldNames.add("reportingYear");
fieldNames.add("reportingMonth");
fieldNames.add("companyId");
//定义指标id集合
List ids = parameter.getIds();
//循环所有的指标
for (Object id : ids) {
//如果指标为空
if (!"".equals(id) && id != null) {
//根据指标id查询指标
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()))){
/**
* 判断指标所在的表，然后为各自的表拼接上表的字段
*/
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() + ",");
//行业平均
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() + ",");
//行业平均
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() + ",");
//行业平均
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() + ",");
//行业平均
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() + ",");
//行业平均
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");
}
//获取所有机构id
List parameterCompanyIds = parameter.getCompanyIds();
//如果机构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);
//关联五张表
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());
//创建本地sql查询实例
orgBalanceSheetsQuery = entityManager.createNativeQuery(orgBalanceSheets.toString());
//如果时间为空那就获取现在的时间
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));
}
}
//获取数据
List resultList = orgBalanceSheetsQuery.getResultList();
System.out.println("resultList==="+resultList);
//给数据设置属性
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;
}
指标实体
/**
* 统计指标
*/
@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;
/**
* 创建时间
*/
private Date createTime;
/**
* 更新时间
*/
private Date updateTime;
/**
* 删除标识
*/
private String delFlag;
//父节点
private Long pId;
//属性
private String field;
//该指标查询月的时候是否查询
private String monthQuery;
//该指标查询年的时候是否查询
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;
}
}
指标Service
/**
* 统计指标服务类
*/
public interface OrgStatisticalIndicatorsService {
/**
* 根据id获取
* @param id
* @return
*/
OrgStatisticalIndicators findOrgStatisticalIndicatorsById(Long id);
/**
* 根据表名查询
*/
List<OrgStatisticalIndicators> findOrgStatisticalIndicatorsByTableName(String name);
}
指标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);
}
}
指标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 进行表之间的关联查询，关联的表有点多，所以代码有些长，同时因为表多，指标（表的属性）有500多个，无法确定查询的返回实体，所以只能自己根据数据的返回给数据绑定属性。
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对我们的支持。
赞 (0)
相关推荐
-
利用java+mysql递归实现拼接树形JSON列表的方法示例
前言 本文给大家介绍的是关于利用java+mysql递归实现拼接树形JSON列表的相关内容,分享出来供大家参考学习,话不多说,来一起看看详细的介绍: 我们在做Java web项目时,前端控件例如国家-省-市-区-县等树形列表,常常需要多级树形json数据 例如: [ { "name": "商品目录", "pid": "-1", "id": "1", "children"
-
java连接MySQl数据库实例代码
复制代码 代码如下: package com.abc.dao; import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement; public class BaseDao { public Connection getConn() { Connection conn=null; tr
-
java连接mysql数据库乱码的解决方法
解决方法一: mysql安装时候的编码, 看下my.ini,有无 复制代码 代码如下: [mysql] default-character-set=utf8 [client] default-character-set=utf8 [mysqld] default-character-set=utf8创建表的时候设置: DROP TABLE IF EXISTS `Company`; CREATE TABLE IF NOT EXISTS `Company` ( `Cname` VARCHAR(10)
-
Java连接MySql的详细介绍
1. 现在工程(不是Src)上右键--Build Path--Add External Archives,选择驱动下的那个jar包,这是release版本,bin目录下的是debug版本. 示例在docs下的connector-j.html,里面有例子(其中的test是数据库名,换位自己的). 复制代码 代码如下: import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; Co
-
Java如何使用Query动态拼接SQL详解
前言 之前有做个一个自定义报表的查询,这里使用的是一个动态的sql拼接,是前端选择了什么指标就查询什么信息!(这里的指标是多个表的字段,前端随便选择了这些指标,然后后端根据这些指标拼接sql,返回这些指标的数据). 参数接受DTO public class DefinedReportFormDTO { /** * 指标id */ private List ids; /** * 开始时间 */ @DateTimeFormat(pattern = "yyyy-MM") private Da
-
Java静态代理与动态代理案例详解
代理模式 代理模式(Proxy):为其他对象提供一个代理以控制对这个对象的访问. 主要解决:在直接访问对象时带来的问题,比如说:要访问的对象在远程的机器上.在面向对象系统中,有些对象由于某些原因(比如对象创建开销很大,或者某些操作需要安全控制,或者需要进程外的访问),直接访问会给使用者或者系统结构带来很多麻烦,我们可以在访问此对象时加上一个对此对象的访问层. 代理模式的元素是:共同接口.代理对象.目标对象. 代理模式的行为:由代理对象执行目标对象的方法.由代理对象扩展目标对象的方法. 代理模式的
-
java代理模式与动态代理模式详解
1.代理模式 所谓代理,就是一个人或者一个机构代表另一个人或者另一个机构采取行动.在一些情况下,一个客户不想或者不能够直接引用一个对象,而代理对象可以在客户端和目标对象之前起到中介的作用.代理模式给某一个对象提供一个代理对象,并由代理对象控制对原对象的引用. 生活中的例子:过年加班比较忙,没空去买火车票,这时可以打个电话到附近的票务中心,叫他们帮你买张回家的火车票,当然这会附加额外的劳务费.但要清楚票务中心自己并不卖票,只有火车站才真正卖票,票务中心卖给你的票其实是通过火车站实现的.这点很重要!
-
Java反射(JDK)与动态代理(CGLIB)详解
目录 一.反射 二.动态代理 1.JDK代理 2.CGLIB代理 3.JDK代理与CGLIB代理对比 总结 一.反射 概念:在运行状态中,对于任意的一个类,都能够知道这个类的所有字段和方法,对任意一个对象都能够通过反射机制调用一个类的任意方法 实现方法:JVM在第一次加载某个类时会生成一个Class对象,里面记录了这个类的信息 链接:类加载机制(留坑) 二.动态代理 动态代理的作用:在不改变原代码的基础上增加新的功能,如日志.权限检验等 反射在动态代理中的应用:由于知道原类的字段.方法等信息,才
-
mybatis的动态sql详解（精）
MyBatis 的一个强大的特性之一通常是它的动态 SQL 能力.如果你有使用 JDBC 或其他 相似框架的经验,你就明白条件地串联 SQL 字符串在一起是多么的痛苦,确保不能忘了空 格或在列表的最后省略逗号.动态 SQL 可以彻底处理这种痛苦. 通常使用动态SQL不可能是独立的一部分,MyBatis当然使用一种强大的动态SQL语言来改进这种情形,这种语言可以被用在任意映射的SQL语句中. 动态SQL元素和使用 JSTL或其他相似的基于XML的文本处理器相似.在MyBatis之前的版本中,有很多
-
MyBatis 动态拼接Sql字符串的问题
MyBatis 的一个强大的特性之一通常是它的动态 SQL 能力.如果你有使用 JDBC 或其他 相似框架的经验,你就明白条件地串联 SQL 字符串在一起是多么的痛苦,确保不能忘了空格或在列表的最后省略逗号.动态 SQL 可以彻底处理这种痛苦. 动态SQL MyBatis的动态SQL,解决了SQL字符串拼接的痛苦. 1.if <select id="findActiveBlogWithTitleLike" parameterType="Blog" result
-
Java基础之动态代理Cglib详解
一.前言 经测试,jdk创建对象的速度远大于cglib,这是由于cglib创建对象时需要操作字节码.cglib执行速度略大于jdk,所以比较适合单例模式.另外由于CGLIB的大部分类是直接对Java字节码进行操作,这样生成的类会在Java的永久堆中.如果动态代理操作过多,容易造成永久堆满,触发OutOfMemory异常.spring默认使用jdk动态代理,如果类没有接口,则使用cglib. 二.服务 package proxy.cglib; /** * @Description: <br/>
-
java 中JDBC连接数据库代码和步骤详解及实例代码
java 中JDBC连接数据库代码和步骤详解 JDBC连接数据库 •创建一个以JDBC连接数据库的程序,包含7个步骤: 1.加载JDBC驱动程序: 在连接数据库之前,首先要加载想要连接的数据库的驱动到JVM(Java虚拟机),这通过java.lang.Class类的静态方法forName(String className)实现. 例如: try{ //加载MySql的驱动类 Class.forName("com.mysql.jdbc.Driver") ; }catch(Class
-
java 中MyBatis注解映射的实例详解
java 中MyBatis注解映射的实例详解 1.普通映射 @Select("select * from mybatis_Student where id=#{id}") public Student getStudent(int id); @Insert("insert into mybatis_Student (name, age, remark, pic,grade_id,address_id) values (#{name},#{age},#{remark}, #{
-
java 中MyBatis注解映射的实例详解
java 中MyBatis注解映射的实例详解 1.普通映射 @Select("select * from mybatis_Student where id=#{id}") public Student getStudent(int id); @Insert("insert into mybatis_Student (name, age, remark, pic,grade_id,address_id) values (#{name},#{age},#{remark}, #{