方式 1:application 上加配置
只需要在配置文件 上,增加 如下配置。
application 格式配置:
spring.jackson.default-property-inclusion=non_null
yml 格式配置:
spring:
jackson:
default-property-inclusion: non_null
注意:方式 1 其实有一定的局限性。如果项目里继承了 WebMvcConfigurationSupport,那么方式 1 配置就失效了。
方式 2:实体类上加配置
这种情况下,可以在实体类上加上 ==@JsonInclude(JsonInclude.Include.NON_NULL)==注解,如果实体类很多就不建议用此方法了
方式 3:config 全局配置
可以在继承了 WebMvcConfigurationSupport 的类上,进行全局配置。
// JsonInclude 全局配置
@JsonInclude(JsonInclude.Include.NON_NULL)
@Override
public void configureMessageConverters(List<HttpMessageConverter<?>> converters) {
Jackson2ObjectMapperBuilder builder = new Jackson2ObjectMapperBuilder()
.serializationInclusion(JsonInclude.Include.NON_NULL);
converters.add(new MappingJackson2HttpMessageConverter(builder.build()));
}
相关链接
WITH TEMP AS (
SELECT [MONTH],
#{kpiName} AS [KPI_NAME],
#{viewBy} AS [VIEW_BY],
MODEL_LINE AS MODEL_SHOW,
CASE WHEN [PRODUCT]='1' THEN 'Porsche Finance'
WHEN [PRODUCT]='2' THEN 'Porsche Finance S'
WHEN [PRODUCT]='3' THEN 'Porsche Finance 50:50'
WHEN [PRODUCT]='4' THEN 'Porsche Finance S'
WHEN [PRODUCT]='5' THEN 'Porsche Finance S'
WHEN [PRODUCT]='6' THEN 'Porsche Finance'
WHEN [PRODUCT]='7' THEN 'Porsche Finance +'
WHEN PRODUCT='Leasing (PFL RVM)' THEN 'Leasing (PFL RVM)'
WHEN PRODUCT='Leasing (w/o PFL RVM)' THEN 'Leasing (w/o PFL RVM)'
ELSE 'Others' END AS [PRODUCT],
CASE WHEN [CONTRACT_TERM]=12 THEN '12M/18M'
WHEN [CONTRACT_TERM]=18 THEN '12M/18M'
WHEN [CONTRACT_TERM]=24 THEN '24M'
WHEN [CONTRACT_TERM]=36 THEN '36M'
ELSE '48M/60M' END AS [TENOR],
[ABBR] AS [DEALER_CON],
CASE WHEN #{viewBy} IN ('PCN region','PFS region') THEN NULL ELSE [DEALER_GROUP] END AS [DEALER_GROUP_CON],
[IS_CAMPAIGN],
CASE WHEN [SERVICE_PROVIDER] IN ('VWNMS','PFLCN') THEN CONCAT([NEW_USED],'|L')
ELSE [NEW_USED] END AS [NEW_USED],
[POWER_TYPE],
CASE WHEN #{viewBy} IN ('PCN region','PFS region') THEN [REGION] ELSE NULL END AS [REGION_CON],
[SERVICE_PROVIDER],
CASE WHEN #{viewBy} = 'PCN region' THEN [SUBREGION]
WHEN #{viewBy} = 'PFS region' THEN [PFS_SUBREGION]
ELSE NULL END AS [SUBREGION_CON],
CASE WHEN #{newUsed} = 'L' THEN
(CASE WHEN SERVICE_PROVIDER IN ('VWNMS','PFLCN') THEN [CONTRACT] END)
ELSE [CONTRACT] END AS [CONTRACT],
CASE WHEN #{newUsed} = 'L' THEN
(CASE WHEN SERVICE_PROVIDER IN ('VWNMS','PFLCN') THEN [NET_AMOUNT_FINANCED] END)
ELSE [NET_AMOUNT_FINANCED] END AS [NET_AMOUNT_FINANCED],
CASE WHEN #{newUsed} = 'L' THEN
(CASE WHEN SERVICE_PROVIDER IN ('VWNMS','PFLCN') THEN [APPLICATION] END)
ELSE [APPLICATION] END AS [APPLICATION],
[APP_POTENTIAL],
[CON_POTENTIAL],
[RETAIL_SALES],
[RETAIL_POTENTIAL],
GETDATE() AS ETL_DATE
FROM DM.DM_FAC_PFS_FORSALES_KPI_M_NATIONAL_P2_DASHBOARD_H5 A
WHERE MONTH BETWEEN CONCAT(LEFT(#{month},4),'01') AND #{month}
AND NEW_USED LIKE CONCAT('%',(CASE WHEN #{newUsed} IN ('N','L') THEN 'N' ELSE 'U' END),'%')
)
SELECT [MONTH],
CASE WHEN #{td}='MTD' THEN (CASE WHEN [KPI_NAME] = 'NAF' THEN ROUND(TOTAL_VALUE/1000000,0)
ELSE TOTAL_VALUE END)
ELSE (CASE WHEN [KPI_NAME] = 'NAF' THEN ROUND(SUM([TOTAL_VALUE]) OVER(PARTITION BY LEFT(MONTH,4) ORDER BY MONTH ASC)/1000000,0)
ELSE SUM([TOTAL_VALUE]) OVER(PARTITION BY LEFT(MONTH,4) ORDER BY MONTH ASC) END)
END AS [TOTAL_VALUE],
CASE WHEN #{td}='MTD' THEN [POTENTIAL]
ELSE SUM([POTENTIAL]) OVER(PARTITION BY LEFT(MONTH,4) ORDER BY MONTH ASC)
END AS [POTENTIAL],
CASE WHEN KPI_NAME IN ('Contract','Application') THEN
(CASE WHEN #{td}='MTD' THEN ROUND(TOTAL_VALUE/(CASE WHEN ([POTENTIAL])<![CDATA[ <> ]]> 0 THEN ([POTENTIAL]) END),3)*100
ELSE ROUND(SUM([TOTAL_VALUE]) OVER(PARTITION BY LEFT(MONTH,4) ORDER BY MONTH ASC)/
(CASE WHEN (SUM([POTENTIAL]) OVER(PARTITION BY LEFT(MONTH,4) ORDER BY MONTH ASC)) <![CDATA[ <> ]]> 0 THEN (SUM([POTENTIAL]) OVER(PARTITION BY LEFT(MONTH,4) ORDER BY MONTH ASC)) END) ,3)*100
END)
WHEN KPI_NAME = 'NAF' THEN
CASE WHEN #{td}='MTD' THEN ROUND(TOTAL_VALUE/(CASE WHEN ([CONTRACT])<![CDATA[ <> ]]> 0 THEN ([CONTRACT]) END)/1000,0)
ELSE ROUND(SUM([TOTAL_VALUE]) OVER(PARTITION BY LEFT(MONTH,4) ORDER BY MONTH ASC)/
(CASE WHEN
(SUM (CONTRACT) OVER(PARTITION BY LEFT(MONTH,4) ORDER BY MONTH ASC))
<![CDATA[ <> ]]> 0
THEN (SUM (CONTRACT) OVER(PARTITION BY LEFT(MONTH,4) ORDER BY MONTH ASC)) END)
/1000,0)
END)
END AS POTENTIAL_VALUE
FROM (
SELECT [MONTH],
[KPI_NAME],
CASE WHEN [KPI_NAME] = 'Contract' THEN SUM([CONTRACT])
WHEN [KPI_NAME] = 'NAF' THEN SUM([NET_AMOUNT_FINANCED])
WHEN [KPI_NAME] = 'Application' THEN SUM([APPLICATION])
WHEN [KPI_NAME] = 'Retail' THEN SUM([RETAIL_POTENTIAL])
END AS TOTAL_VALUE,
CASE WHEN [KPI_NAME] = 'NAF' THEN SUM([CONTRACT]) END AS [CONTRACT],
CASE WHEN [KPI_NAME] = 'Contract' THEN SUM([CON_POTENTIAL])
WHEN [KPI_NAME] = 'Application' THEN SUM([APP_POTENTIAL])
END AS [POTENTIAL]
FROM TEMP
<include refid="TrendHistogramWhere"></include>
GROUP BY [MONTH],KPI_NAME
) T
ORDER BY 1