1.pom设置
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.itstudy</groupId> <artifactId>demo</artifactId> <version>1.0-SNAPSHOT</version> <name>demo</name> <description>demo</description> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <maven.compiler.source>1.8</maven.compiler.source> <maven.compiler.target>1.8</maven.compiler.target> </properties> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>1.5.9.RELEASE</version> <relativePath/> </parent> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <!-- <version>1.0.0</version>--> <version>1.3.2</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <!-- 链接oracle 11 --> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc6</artifactId> <version>11.1.0.7.0</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.30</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
2.src/main/resources/application.properties
server.port=9101
server.display-name=
server.name=conduct-rules-ranking
spring.datasource.driverClassName=oracle.jdbc.driver.OracleDriver
#设置数据库链接
#spring.datasource.url=jdbc:oracle:thin:@192.168.150.63:1521:ufnc
spring.datasource.url=jdbc:oracle:thin:@192.168.150.63:1521/ufnc
spring.datasource.username=syxwexam
spring.datasource.password=am67#bcm1
#配置映射路径
mybatis.mapperLocations=classpath:mapper/*.xml
#打印sql语句
logging.level.com.itstudy.demo.dao=debug
3.src/main/resources/mapper/RankingMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.itstudy.demo.dao.RankingMapper"> <resultMap id="BaseResultMap" type="com.itstudy.demo.domain.RankingInfo"> <result property="zuzhixz" column="zuzhixz"/> <result property="zuzhifl" column="zuzhifl"/> <result property="zongshu" column="zongshu"/> <result property="yikaoshi" column="yikaoshi"/> <result property="bilv" column="bilv"/> </resultMap> <select id="findRankingAll" resultMap="BaseResultMap"> with s1 as ( select zuzhixz,zuzhifl,count(*) as zongshu from view_active_business where unitname not like '%分公司%' and unitname not like '%事业部%' group by zuzhifl,zuzhixz ), s2 as (select zuzhixz,zuzhifl,count(*) as yikaoshi from view_active_business where (unitname not like '%分公司%' and unitname not like '%事业部%') and zt ='正常' group by zuzhifl,zuzhixz ) select a.zuzhixz,a.zuzhifl,a.zongshu,nvl(b.yikaoshi,0) as yikaoshi,to_char(nvl(b.yikaoshi,0)/nvl(a.zongshu,1),'FM0.0000') as bilv from s1 a left join s2 b on a.zuzhifl = b.zuzhifl where a.zongshu >= 30 </select> </mapper>
3. src/main/java/com/itstudy/demo/dao/RankingMapper.java
package com.itstudy.demo.dao; import com.itstudy.demo.domain.RankingInfo; import org.apache.ibatis.annotations.Mapper; import java.util.List; @Mapper public interface RankingMapper { List<RankingInfo> findRankingAll(); }
4. src/main/java/com/itstudy/demo/domain/RankingInfo.java
package com.itstudy.demo.domain; /** * 排序信息 */ public class RankingInfo { private String zuzhixz; private String zuzhifl; //组织分类 private String zongshu; //总人数 private String yikaoshi; //已考试人数 private String bilv; //比率 public String getZuzhifl() { return zuzhifl; } public void setZuzhifl(String zuzhifl) { this.zuzhifl = zuzhifl; } public String getZongshu() { return zongshu; } public void setZongshu(String zongshu) { this.zongshu = zongshu; } public String getYikaoshi() { return yikaoshi; } public void setYikaoshi(String yikaoshi) { this.yikaoshi = yikaoshi; } public String getBilv() { return bilv; } public void setBilv(String bilv) { this.bilv = bilv; } public String getZuzhixz() { return zuzhixz; } public void setZuzhixz(String zuzhixz) { this.zuzhixz = zuzhixz; } }
5. src/main/java/com/itstudy/demo/controller/RankingController.java
package com.itstudy.demo.controller; import com.itstudy.demo.dao.RankingMapper; import com.itstudy.demo.domain.RankingInfo; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.scheduling.annotation.Scheduled; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RestController; import java.util.ArrayList; import java.util.Comparator; import java.util.List; import java.util.concurrent.ConcurrentHashMap; @RestController public class RankingController { @Autowired private RankingMapper rankingMapper; private ConcurrentHashMap<String, Object> concurrentHashMap = new ConcurrentHashMap<String, Object>(); private String cache_key = "ranking_data"; @GetMapping("/ranking") public List<RankingInfo> getUser() { List<RankingInfo> query = null; if (concurrentHashMap != null && concurrentHashMap.containsKey(cache_key)) { try { query = (ArrayList<RankingInfo>) concurrentHashMap.get(cache_key); } catch (Exception ex) { } } if (query != null) { System.out.println("缓存数据返回..."); return query; } System.out.println("数据库查询..."); query = rankingMapper.findRankingAll(); query.sort(new Comparator<RankingInfo>() { @Override public int compare(RankingInfo o1, RankingInfo o2) { if (o1.getBilv() == null || o2.getBilv() == null) { return 0; } return o2.getBilv().compareToIgnoreCase(o1.getBilv()); } }); if (concurrentHashMap == null) { concurrentHashMap = new ConcurrentHashMap<String, Object>(); } concurrentHashMap.put(cache_key, query); return query; } //15分钟更新一次缓存数据 @Scheduled(fixedDelay = 900000) public void updateCacheData() { List<RankingInfo> query = rankingMapper.findRankingAll(); query.sort(new Comparator<RankingInfo>() { @Override public int compare(RankingInfo o1, RankingInfo o2) { if (o1.getBilv() == null || o2.getBilv() == null) { return 0; } return o2.getBilv().compareToIgnoreCase(o1.getBilv()); } }); if (concurrentHashMap == null) { concurrentHashMap = new ConcurrentHashMap<String, Object>(); } concurrentHashMap.put(cache_key, query); System.out.println("更新缓存..."); } }
6 src/main/java/com/itstudy/demo/Application.java
package com.itstudy.demo; import org.springframework.boot.Banner; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.scheduling.annotation.EnableScheduling; /** * 考试排名启动类 * */ @SpringBootApplication @EnableScheduling public class Application { public static void main( String[] args ) { SpringApplication app = new SpringApplication(Application.class); //关闭banner app.setBannerMode(Banner.Mode.OFF); app.run(args); } }
7 src/main/java/com/itstudy/demo/CorsConfig.java
package com.itstudy.demo; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.web.cors.CorsConfiguration; import org.springframework.web.cors.UrlBasedCorsConfigurationSource; import org.springframework.web.filter.CorsFilter; /** * 实现基本的跨域请求 * * @author admin */ @Configuration public class CorsConfig { private CorsConfiguration buildConfig() { CorsConfiguration corsConfiguration = new CorsConfiguration(); corsConfiguration.addAllowedOrigin("*"); // 允许任何域名使用 corsConfiguration.addAllowedHeader("*"); // 允许任何头 corsConfiguration.addAllowedMethod("*"); // 允许任何方法(post、get等) return corsConfiguration; } @Bean public CorsFilter corsFilter() { UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource(); source.registerCorsConfiguration("/**", buildConfig()); // 对接口配置跨域设置 return new CorsFilter(source); } }
8.重要说明
使用下面的依赖包,要先进行maven安装
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.1.0.7.0</version>
</dependency>
1.下载ojdbc6.jar
链接:https://pan.baidu.com/s/1OgKUmExGfxIfMV1yUqFHOw
提取码:f3uy
2. maven安装命令
mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.1.0.7.0 -Dpackaging=jar -Dfile=C:UsersAdministratorDesktop11.1.0.7.0ojdbc6-11.1.0.7.0.jar