springboot整合mybatis xml方式配置SQL

springboot免去了一大堆的xml配置，达到了开箱即用，springboot中的springdatajpa是完整的ORM框架，对于简单的字段可以直接通过方法中的字段名即可，但是对于有些情况下，复杂的查询，查询的调优还是用mybatis这种半自动的框架比较合适，mybatis也可以注解开发，但是为了更加流程的开发体验还是用xml噢诶值的方式更加让人喜欢

整合步骤

1. 引入依赖

可以使用IDEA方便的选择主要的start

<?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.marry.dezhou</groupId>    <artifactId>fg</artifactId>    <version>0.0.1-SNAPSHOT</version>    <packaging>jar</packaging>    <name>fg</name>    <description></description>    <parent>        <groupId>org.springframework.boot</groupId>        <artifactId>spring-boot-starter-parent</artifactId>        <version>1.4.7.RELEASE</version>        <relativePath/> <!-- lookup parent from repository -->    </parent>    <properties>        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>        <java.version>1.7</java.version>        <commons-lang3.version>3.4</commons-lang3.version>    </properties>    <dependencies>        <dependency>            <groupId>org.mybatis.spring.boot</groupId>            <artifactId>mybatis-spring-boot-starter</artifactId>            <version>1.3.1</version>        </dependency>        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-web</artifactId>        </dependency>        <dependency>            <groupId>org.projectlombok</groupId>            <artifactId>lombok</artifactId>            <optional>true</optional>        </dependency>        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-test</artifactId>            <scope>test</scope>        </dependency>        <!--引入druid的maven-->        <dependency>            <groupId>com.alibaba</groupId>            <artifactId>druid</artifactId>            <version>1.0.29</version>        </dependency>        <!-- 阿里巴巴fastjson，解析json视图 -->        <dependency>            <groupId>com.alibaba</groupId>            <artifactId>fastjson</artifactId>            <version>1.2.15</version>        </dependency>        <!--oracle数据库-->        <dependency>            <groupId>ojdbc</groupId>            <artifactId>ojdbc14</artifactId>            <version>10.2.0.4.0</version>        </dependency>        <dependency>            <groupId>org.apache.commons</groupId>            <artifactId>commons-lang3</artifactId>            <version>${commons-lang3.version}</version>        </dependency>    </dependencies>    <build>        <finalName>fgshare</finalName>        <plugins>            <plugin>                <groupId>org.springframework.boot</groupId>                <artifactId>spring-boot-maven-plugin</artifactId>            </plugin>        </plugins>    </build></project>

推荐使用阿里的Druid数据库连接池，监控功能可以方便得查看每个SQL的执行时间等信息，对于调优很方便

2.配置数据库连接

这里使用的.yml格式的配置文件

spring:  datasource:    type: com.alibaba.druid.pool.DruidDataSource    driver-class-name: oracle.jdbc.driver.OracleDriver    url: jdbc:oracle:thin:@127.0.0.1:1521:orcl    username: estate_dz    password: estate    #最大活跃数    maxActive: 50    #初始化数量    initialSize: 1    #最大连接等待超时时间    maxWait: 60000    #打开PSCache，并且指定每个连接PSCache的大小    poolPreparedStatements: true    maxPoolPreparedStatementPerConnectionSize: 20    #通过connectionProperties属性来打开mergeSql功能；慢SQL记录    #connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000    minIdle: 1    timeBetweenEvictionRunsMillis: 60000    minEvictableIdleTimeMillis: 300000    validationQuery: select 1 from dual    testWhileIdle: true    testOnBorrow: false    testOnReturn: false    #配置监控统计拦截的filters，去掉后监控界面sql将无法统计,'wall'用于防火墙    filters: stat, log4j

为了可以正确显示监控等信息，还需要具体配置一下，springboot是java配置的形式

@Configurationpublic class DruidConfiguration {    @Bean(initMethod = "init",destroyMethod = "close")    @ConfigurationProperties(prefix = "spring.datasource")    public DruidDataSource dataSource(){        return new DruidDataSource();    }    @Bean    public ServletRegistrationBean statViewServlet(){        ServletRegistrationBean servletRegistrationBean=new ServletRegistrationBean(new StatViewServlet(),"/druid/*");        servletRegistrationBean.addInitParameter("loginUsername","malin");        servletRegistrationBean.addInitParameter("loginPassword","123456");        servletRegistrationBean.addInitParameter("resetEnable","false");        return servletRegistrationBean;    }    @Bean    public FilterRegistrationBean statFilter(){        FilterRegistrationBean filterRegistrationBean=new FilterRegistrationBean(new WebStatFilter());        filterRegistrationBean.addUrlPatterns("/*");        filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");        return filterRegistrationBean;    }}

@Bean(initMethod = "init",destroyMethod = "close")initMethod参数不配置的话监控页面的数据源界面可能无法正确显示

3.配置mybatis的配置文件及.xml等

还是application.yml文件添加代码

mybatis:  mapper-locations: classpath:mapper/*.xml  type-aliases-package: com.marry.dezhou.fg.domain  config-location: classpath:mybatis-config.xml

mapper-locations 指定.xml文件所在的位置
type-aliases-package 指定别名的包 写type时直接写类名即可
type-aliases-package 指定mybatis其他配置文件的位置 比如可以在控制台打印sql

4.编写Mapper接口

import com.marry.dezhou.fg.domain.Houseroom;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Param;/** * Created by ml on 2017-10-26. */@Mapperpublic interface StudentMapper {    /**     * 通过名字获去学生     * @param name     * @return     */    Student selectStudentByName(@Param("name") String name);}}

直接在相应的接口上写上@Mapper注解即可
方法名即是接下来写在.xml文件中的查询id
@Param()注解将字段映射到.xml文件中的#{}参数中 一个参数的话可以不写

5.编写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.marry.dezhou.fg.mapper.StudentMapper">    <select id="selectStudentByName" resultType="Student">        SELECT *        FROM STUDENT        WHERE NAME= #{name}    </select></mapper>

namespace 写上需要连接到的Mapper接口的全类名

6.完工 在需要的地方将接口注入就行了

附上mybatis-config.xml的配置文件

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration>    <!-- 设置运行参数 -->    <settings>        <!-- 全局映射器启用缓存 -->        <setting name="cacheEnabled" value="true" />        <!-- 查询时,关闭关联对象及时加载以提高性能 -->        <setting name="lazyLoadingEnabled" value="false" />        <!-- 设置关联对象加载的形态,此处为按需加载字段(加载字段由SQL指定),不会加载关联表的所有字段,以提高性能 -->        <setting name="aggressiveLazyLoading" value="false" />        <!-- 对于位置的SQL查询,允许返回不同的结果集以达到通用的效果 -->        <setting name="multipleResultSetsEnabled" value="true" />        <!-- 允许使用列标签代替列明 -->        <setting name="useColumnLabel" value="true" />        <!-- 允许使用自定义的主键值(比如由程序生成的UUID 32位编码作为键值), 数据表的pk生成策略将被覆盖 -->        <setting name="useGeneratedKeys" value="true" />        <!-- 给予被嵌套的resultMap以字段-属性的映射支持 -->        <setting name="autoMappingBehavior" value="PARTIAL" />        <!-- 对于批量更新操作缓存SQL以提高性能 -->        <setting name="defaultExecutorType" value="REUSE" />        <!-- 数据库超过25000秒仍未响应则超时 -->        <setting name="defaultStatementTimeout" value="25000" />        <!-- 打印查询语句 -->        <!--<setting name="logImpl" value="STDOUT_LOGGING" />-->        <setting name="logImpl" value="SLF4J"/>    </settings></configuration>