springboot 实用技巧

1. MySQL 数据库 # 根据severity 字段分类统计数量 @Query(value = "select severity,COUNT(*) as cCount FROM quality WHERE project=?1 GROUP BY severity", nativeQuery = true) List<Object[]> querySeverityForDashQualityPie(String project); # 对customer 进行stage 数量统计 @Query(value = "select a.customer,b.stage,count(*) as num FROM quality a LEFT JOIN project b ON a.id=b.id GROUP BY a.customer,b.stage", nativeQuery = true) List<Object[]> queryCustomerStageForDashProjectBar(); # 天统计 @Query(value = "select DATE_FORMAT(createtime,'%Y%m%d') as days,COUNT(id) as cCount FROM quality p WHERE project=?1 GROUP BY days ORDER BY days ASC", nativeQuery = true) List<Object[]> queryDailyCreateForDashQualityList(String project); # 周统计 @Query(value = "select DATE_FORMAT(createtime,'%y%u')+1 as weeks,COUNT(id) as cCount FROM quality p WHERE project=?1 GROUP BY weeks", nativeQuery = true) List<Object[]> queryWeeklyCreateForDashQualityList(String project); # 根据severity 统计当天创建的 @Query(value = "select severity,COUNT(*) as cCount FROM quality WHERE project=?1 AND to_days(createtime)=to_days(now()) GROUP BY severity", nativeQuery = true) List<Object[]> queryTodayCreateJiraForDi(String project); # updatedtime 在48小时之前 @Query(value = "select jira,DATE_FORMAT(updatedtime,'%Y%m%d %H:%i:%S') as updatedtime FROM quality p WHERE project=?1 AND severity in ('S', 'A') AND updatedtime < date_sub(now(), interval 48 hour)", nativeQuery = true) List<Object[]> querySaOpenTO(String project); # 通配符 @Query(value = "select * from project WHERE sysactive is true AND project=?1 AND (vpm=?2 OR svpm LIKE %?2%)", nativeQuery = true) ProjectEntity queryActivedProjectForVpm(String project, String vpmname); # 数据表两次别名后连表查询 SELECT moreinfo.customer, moreinfo.hoursneed, moreinfo.hoursreal, moreinfo.hoursgap, moreinfo.tasknum, lessinfo.taskcompletenum FROM (SELECT customer,count(id) as tasknum, sum(hours) as hoursneed,sum(hoursreal) as hoursreal,sum(hoursgap) as hoursgap FROM tinno_dev.weeklytask where week="29" and site="南昌" group by customer ) AS moreinfo INNER JOIN (SELECT customer,count(id) as taskcompletenum FROM tinno_dev.weeklytask where week="29" and site="南昌" and status="已完成" group by customer) AS lessinfo ON moreinfo.customer = lessinfo.customer; 1.1 mysql 使用注意事项 # 日期查询，遇到between ?1 and ?2 的情况，区分?1 和 ?2是否为同一天，是同一天则会无数据。 # 日期查询，MySQL 字段为Date类型，@Query查询的方法可能需要把Date类型参数转为String类型。 因为Date 类型在使用between 查询，遇到datetime 存储时会出现左右闭合问题。 2. MySQL workbench #解开My SQL workbench安全模式 SET SQL_SAFE_UPDATES=0; 3. Oracle # 筛选前10条 # 并非所有的数据库系统都支持 SELECT TOP 语句。 MySQL 支持 LIMIT 语句来选取指定的条数数据， Oracle 可以使用 ROWNUM 来选取。 select * from (select * from tinnobpm.tbl_proj_workhour WHERE center='测试中心' ORDER BY workdate DESC, id desc) where rownum <=10 ; 4. vscode 清理项目缓存 a. ctrl + shift + p b. 输入Java: Clean Java language server workspace c. 选择"Restart and delete" 1. MySQL 数据库 # 根据severity 字段分类统计数量 @Query(value =