SpringBoot+Druid+Mybatis 结合使用

本次项目，从来没有用过mybatis，之前用过hibernate jpa 和jdbcTemplate 。于是这次心血来潮用一下mybatis结合阿里巴巴的数据库监控druid。

本篇中的服务配置涉及springcloud，但是并不影响服务单独启动（报错不能注册到eureka服务器，但能正常使用）。有具体需要的同学可以将涉及springcloud的配置从pom.xml和yaml配置文件中去掉。

1.pom.xml文件

<?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.wlw</groupId>
    <artifactId>syngis-map</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>syngis-map</name>
    <description>Demo project for Spring Boot</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.4.RELEASE</version>
        <relativePath/>
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>
    <!--springboot-->
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--spring boot 监控框架 -->
        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-actuator -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
            <version>1.5.7.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <!--Test-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>

        <!--druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.0</version>
        </dependency>
        <!--阿里巴巴fastjson依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.34</version>
        </dependency>


        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.3.9.RELEASE</version>
            <scope>compile</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl -->
        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>

        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.0.1</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.microsoft.sqlserver/sqljdbc4 -->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.0</version>
            <scope>compile</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework.cloud/spring-cloud-starter-eureka -->
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-eureka</artifactId>
            <version>1.3.5.RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework.cloud/spring-cloud-starter-feign -->
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-feign</artifactId>
            <version>1.3.5.RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework.cloud/spring-cloud-starter-ribbon -->
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-ribbon</artifactId>
            <version>1.3.5.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-war-plugin</artifactId>
                <configuration>
                    <warName>${project.artifactId}</warName>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <fork>true</fork>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

2.application.yaml配置文件

#Tomcat配置
server:
  context-path: /syngis-web-map
  port: 8088
spring:
 #jmx 配置 ：打包成war包时避免和其他的springcloud服务冲突
 jmx:
   default-domain: syngis-web-map
   enabled: true
   domain: syngis-web-map
 #应用名称
 application:
   name: syngis-web-map
 #dataSourceConfiguration
 datasource:
    # 连接池普通配置
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    url: jdbc:sqlserver://localhost:1433;databaseName=DFSTrackData7
    username: sa
    password: sa
    platform: sqlserver
    type: com.alibaba.druid.pool.DruidDataSource
    # 连接池补充配置
    # 初始化大小，最小，最大
    initialSize: 1
    minIdle: 3
    maxActive: 200
    # 配置获取连接等待超时的时间
    maxWait: 60000
    # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    timeBetweenEvictionRunsMillis: 60000
    # 配置一个连接在池中最小生存的时间，单位是毫秒
    minEvictableIdleTimeMillis: 40000
    validationQuery: select 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    # 打开PSCache，并且指定每个连接上PSCache的大小
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,slf4j
    # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # 合并多个DruidDataSource的监控数据
    useGlobalDataSourceStat: true
#springcloud配置
eureka:
  client:
    service-url:
      defaultZone: http://localhost:8089/eureka/
  instance:
    prefer-ip-address: true
mybatis:
  # mapper扫描路径
  # mapper-locations: com.wlw.mapper
  # 可以直接指定package的名字， mybatis会自动扫描你指定包下面的javabean,
  type-aliases-package: com.wlw.entity
  # 配置文件路径（已经在yaml配置，故不用）
  # config-location:
  configuration:
    # 所有映射器中配置的缓存的全局开关
    cache-enabled: false
    # 延迟加载的全局开关 配置二级缓存时将此属性关闭
    lazy-loading-enabled: false
    # 关联对象加载 配置二级缓存时将此属性关闭
    aggressive-lazy-loading: false
    # 是否允许单一语句返回多结果集
    multiple-result-sets-enabled: true
    # 使用列标签代替列名
    use-column-label: false
    # 允许在嵌套语句中使用行分界
    safe-row-bounds-enabled: false
    # 允许 JDBC 支持自动生成主键，需要驱动兼容
    use-generated-keys: false
    # 指定 MyBatis 是否以及如何自动映射指定的列到字段或属性
    auto-mapping-behavior: partial
    # 配置默认的执行器
    default-executor-type: simple
    # 设置超时时间，它决定驱动等待数据库响应的秒数
    default-statement-timeout: 30
    # 是否开启自动驼峰命名规则映射
    map-underscore-to-camel-case: false
    # 利用本地缓存机制防止循环引用和加速重复嵌套查询 默认值为 SESSION，这种情况下会缓存一个会话中执行的所有查询
    local-cache-scope: session
    # 当没有为参数提供特定的 JDBC 类型时，为空值指定 JDBC 类型
    jdbc-type-for-null: other
    # 指定哪些对象的方法触发一次延迟加载
    lazy-load-trigger-methods: equals,clone,hashCode,toString

3.服务主程序

// 启注解事务管理，等同于xml配置方式的 <tx:annotation-driven />
//@EnableTransactionManagement
// 通过注解配置factory-class
@EnableJpaRepositories(repositoryFactoryBeanClass = CustomRepositoryFactoryBean.class)
@EnableFeignClients
@EnableDiscoveryClient
@SpringBootApplication
@ServletComponentScan
//@MapperScan("com.wlw.mapper")这个为什么会注释，我会在7.Mapper类中提到
public class SyngisWebMapApplication {

    public static void main(String[] args) {
        SpringApplication.run(SyngisWebMapApplication.class, args);
    }
}

4.durid配置类

/**
 * Druid的监控统计页面的功能
 * Created by Tomas on 2017/7/11.
 */
@Configuration
public class DruidConfiguration {

    private static final Logger logger = LoggerFactory.getLogger(DruidConfiguration.class);

    /**
     * 使用DruidDataSourceBuilder创建数据源
     * @return
     */
    //@Primary在此表示替换springboot默认的数据源
    //@Bean将DataSource注册到spring中
    //ConfigurationProperties(prefix = "spring.datasource")
    //使用application.yaml配置文件中spring.datasource开头的配置项
    @Primary  
    @Bean   
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource druidDataSource() {
        DruidDataSource datasource = new DruidDataSource();
        return datasource;
    }


    /**
     *  JDBC Template 数据源
     *  想使用jdbcTemplate的同学在这里写好就行
     * @param dataSource
     * @return
     */
    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    /**
     * druid的监控地址、登录员、黑白名单设置
     * @return
     */
    @Bean
    public ServletRegistrationBean druidServlet() {
        logger.info("init Druid Servlet Configuration ");
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean();
        servletRegistrationBean.setServlet(new StatViewServlet());
        servletRegistrationBean.addUrlMappings("/druid/*");
        Map<String, String> initParameters = new HashMap<String, String>();
        initParameters.put("loginUsername", "9999");// 用户名
        initParameters.put("loginPassword", "1");// 密码
        initParameters.put("resetEnable", "false");// 禁用HTML页面上的“Reset All”功能
        initParameters.put("allow", ""); // IP白名单 (没有配置或者为空，则允许所有访问)
        //initParameters.put("deny", "192.168.20.38");// IP黑名单 (存在共同时，deny优先于allow)
        servletRegistrationBean.setInitParameters(initParameters);
        return servletRegistrationBean;
    }
    /**
     * druid的过滤器设置
     * @return
     */
    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*,*.properties");
        return filterRegistrationBean;
   }
}

5.mybatis配置类

/**
 * mybatis配置类
 * @author Tomas
 * @date 2017/12/13
 */
@Configuration
@EnableTransactionManagement
// 添加上会产生警告main No MyBatis mapper was found in ....
// 原因是没有目录下没有mapper.xml文件或者@Mapper注解的类
//@MapperScan(basePackages = {"com.wlw.mapper"})
public class MyBatisConfig implements EnvironmentAware {

    @Autowired
    DataSource dataSource;

    private RelaxedPropertyResolver propertyResolver;

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactoryBean() {
        try {
            //ResourcePatternResolver resourcePatternResolver;
            //resourcePatternResolver = new PathMatchingResourcePatternResolver();

            //  注入DataSource，此处是我们使用的druid
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);

//            在application.yaml  配置文件中的mybatis配置项中已经配置，所以此处注释
//            bean.setTypeAliasesPackage(propertyResolver.getProperty("typeAliasesPackage"));
//            bean.setMapperLocations(resourcePatternResolver.getResources(propertyResolver.getProperty("mapper-locations")));
//            bean.setConfigLocation(new DefaultResourceLoader().getResource(propertyResolver.getProperty("configLocation")));

            return bean.getObject();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 事物管理器
     */
    @Bean
    public PlatformTransactionManager platformTransactionManager() {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * 这个类猜测应该是设置环境，具体得看过源码之后才了解
     * Set the {@code Environment} that this object runs in.
     * @param environment
     */
    @Override
    public void setEnvironment(Environment environment) {
        this.propertyResolver = new RelaxedPropertyResolver(environment,"mybatis.");
    }

6.javaBean类

@Entity
public class RecDescribe {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private String id;
    private String guid;
    private String did;
    private String minX;
    private String minY;
    private String maxX;
    private String maxY;
    private String begTime;
    private String endTime;
    private String timeFlag;

    @Override
    public String toString() {
        return "RecDescribe{" +
                "id='" + id + '\'' +
                ", guid='" + guid + '\'' +
                ", did='" + did + '\'' +
                ", minX='" + minX + '\'' +
                ", minY='" + minY + '\'' +
                ", maxX='" + maxX + '\'' +
                ", maxY='" + maxY + '\'' +
                ", begTime='" + begTime + '\'' +
                ", endTime='" + endTime + '\'' +
                ", timeFlag='" + timeFlag + '\'' +
                '}';
    }

    public RecDescribe() {
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getGuid() {
        return guid;
    }

    public void setGuid(String guid) {
        this.guid = guid;
    }

    public String getDid() {
        return did;
    }

    public void setDid(String did) {
        this.did = did;
    }

    public String getMinX() {
        return minX;
    }

    public void setMinX(String minX) {
        this.minX = minX;
    }

    public String getMinY() {
        return minY;
    }

    public void setMinY(String minY) {
        this.minY = minY;
    }

    public String getMaxX() {
        return maxX;
    }

    public void setMaxX(String maxX) {
        this.maxX = maxX;
    }

    public String getMaxY() {
        return maxY;
    }

    public void setMaxY(String maxY) {
        this.maxY = maxY;
    }

    public String getBegTime() {
        return begTime;
    }

    public void setBegTime(String begTime) {
        this.begTime = begTime;
    }

    public String getEndTime() {
        return endTime;
    }

    public void setEndTime(String endTime) {
        this.endTime = endTime;
    }

    public String getTimeFlag() {
        return timeFlag;
    }

    public void setTimeFlag(String timeFlag) {
        this.timeFlag = timeFlag;
    }
}

7.Mapper类

此处我没有配置Mapp.xml文件，而是通过@Select注解将sql语句直接注入方法中。而且由于springboot本来的设计理念就是减少配置文件，降低开发人员的工作量。如果再向项目中添加Mapp.xml配置文件会让每个接口和每个映射文件必须对应，增加了耦合，更显得多余。

/* 1.此处@Maper作用是相当于起到一个mapper.xml配置文件的作用
 * @Maper注解最好和配置文件中的 mapper-locations: com.wlw.mapper配合使用
 * 或者配合启动类头上的@MapperScan("com.wlw.mapper")注解一起使用
 * 如果此处没有@Mapper注解的话，在控制台中可能会报warn：main No MyBatis mapper was found in ....
 *
 * 2.@Repository注解是将Mapper作为Dao层的接口注入Spring中
 * 如果此处没有它的话，在service中的   
 * @Autowired
 * RecDescribeMapper recDescribeMapper;
 * 会报错，但是不影响运行
 */
@Repository
@Mapper
public interface RecDescribeMapper {

    // 不加@param注解会报错
    // 方法参数我在下一篇文章会提到
    @Select({"select  * from RecDescribe where TimeFlag >= #{bF} and TimeFlag <= #{eF}"})
    public List<RecDescribe> selectByTimeFlag(@Param("bF") String bF, @Param("eF") String eF);
}

8.service层调用

/**
 * 查询gp数据类
 * @author Thomas
 */
@Service
public class QueryServiceImpl implements QueryService {

    @Autowired
    RecDescribeMapper recDescribeMapper;

    public List<GPS> queryPeriodData(String moduleName, String bTime, String eTime) {

        String bf = bTime.split(" ")[1].replace("-", "");
        String ef = eTime.split(" ")[1].replace("-", "");
        List<RecDescribe> recs = recDescribeMapper.selectByTimeFlag(bf, ef);
        System.out.println(recs);
    }
}
//最后控制台打印的数据
//[RecDescribe{id='cc6e6a70-b268-11e6-8521-61e5052161e5', guid='275475dd-cbd4-4274-a42b-2d140fbcd715', did='0259', minX='120.07340166.........

至此，mybatis springboot 和 druid的集合使用。其中有许多细节我已经在各处的注释中讲明，参Mapper数的问题将在下一章讨论。