本次項目雾叭，從來沒有用過mybatis，之前用過hibernate jpa 和jdbcTemplate 落蝙。于是這次心血來潮用一下mybatis結(jié)合阿里巴巴的數(shù)據(jù)庫監(jiān)控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 監(jiān)控框架 -->
        <!-- 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
    # 配置間隔多久才進行一次檢測，檢測需要關(guān)閉的空閑連接病瞳，單位是毫秒
    timeBetweenEvictionRunsMillis: 60000
    # 配置一個連接在池中最小生存的時間揽咕，單位是毫秒
    minEvictableIdleTimeMillis: 40000
    validationQuery: select 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    # 打開PSCache，并且指定每個連接上PSCache的大小
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
    # 配置監(jiān)控統(tǒng)計攔截的filters套菜，去掉后監(jiān)控界面sql無法統(tǒng)計亲善，'wall'用于防火墻
    filters: stat,slf4j
    # 通過connectProperties屬性來打開mergeSql功能；慢SQL記錄
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # 合并多個DruidDataSource的監(jiān)控數(shù)據(jù)
    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
  # 配置文件路徑（已經(jīng)在yaml配置蛹头，故不用）
  # config-location:
  configuration:
    # 所有映射器中配置的緩存的全局開關(guān)
    cache-enabled: false
    # 延遲加載的全局開關(guān) 配置二級緩存時將此屬性關(guān)閉
    lazy-loading-enabled: false
    # 關(guān)聯(lián)對象加載 配置二級緩存時將此屬性關(guān)閉
    aggressive-lazy-loading: false
    # 是否允許單一語句返回多結(jié)果集
    multiple-result-sets-enabled: true
    # 使用列標簽代替列名
    use-column-label: false
    # 允許在嵌套語句中使用行分界
    safe-row-bounds-enabled: false
    # 允許 JDBC 支持自動生成主鍵，需要驅(qū)動兼容
    use-generated-keys: false
    # 指定 MyBatis 是否以及如何自動映射指定的列到字段或?qū)傩?    auto-mapping-behavior: partial
    # 配置默認的執(zhí)行器
    default-executor-type: simple
    # 設(shè)置超時時間戏溺，它決定驅(qū)動等待數(shù)據(jù)庫響應的秒數(shù)
    default-statement-timeout: 30
    # 是否開啟自動駝峰命名規(guī)則映射
    map-underscore-to-camel-case: false
    # 利用本地緩存機制防止循環(huán)引用和加速重復嵌套查詢 默認值為 SESSION渣蜗，這種情況下會緩存一個會話中執(zhí)行的所有查詢
    local-cache-scope: session
    # 當沒有為參數(shù)提供特定的 JDBC 類型時，為空值指定 JDBC 類型
    jdbc-type-for-null: other
    # 指定哪些對象的方法觸發(fā)一次延遲加載
    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的監(jiān)控統(tǒng)計頁面的功能
 * Created by Tomas on 2017/7/11.
 */
@Configuration
public class DruidConfiguration {

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

    /**
     * 使用DruidDataSourceBuilder創(chuàng)建數(shù)據(jù)源
     * @return
     */
    //@Primary在此表示替換springboot默認的數(shù)據(jù)源
    //@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 數(shù)據(jù)源
     *  想使用jdbcTemplate的同學在這里寫好就行
     * @param dataSource
     * @return
     */
    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    /**
     * druid的監(jiān)控地址、登錄員托享、黑白名單設(shè)置
     * @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優(yōu)先于allow)
        servletRegistrationBean.setInitParameters(initParameters);
        return servletRegistrationBean;
    }
    /**
     * druid的過濾器設(shè)置
     * @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
// 添加上會產(chǎn)生警告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配置項中已經(jīng)配置赃绊，所以此處注釋
//            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);
    }

    /**
     * 這個類猜測應該是設(shè)置環(huán)境既峡，具體得看過源碼之后才了解
     * 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本來的設(shè)計理念就是減少配置文件运敢，降低開發(fā)人員的工作量。如果再向項目中添加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注解會報錯
    // 方法參數(shù)我在下一篇文章會提到
    @Select({"select  * from RecDescribe where TimeFlag >= #{bF} and TimeFlag <= #{eF}"})
    public List<RecDescribe> selectByTimeFlag(@Param("bF") String bF, @Param("eF") String eF);
}

8.service層調(diào)用

/**
 * 查詢gp數(shù)據(jù)類
 * @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);
    }
}
//最后控制臺打印的數(shù)據(jù)
//[RecDescribe{id='cc6e6a70-b268-11e6-8521-61e5052161e5', guid='275475dd-cbd4-4274-a42b-2d140fbcd715', did='0259', minX='120.07340166.........

至此腐螟，mybatis springboot 和 druid的集合使用。其中有許多細節(jié)我已經(jīng)在各處的注釋中講明困后，參Mapper數(shù)的問題將在下一章討論乐纸。