Spring Boot教程五：整合mybaits

来源：互联网发布：淘宝店铺怎么提前收款编辑：程序博客网时间：2024/05/22 12:50

mybatis框架已经成为一种大众普遍使用的数据库开发工具，因此，在这里我们就不讲其他的数据库连接操作方式了，直奔主题，整合mybaits：
首先修改pom文件，引入相关包：

    <dependencies>        <!--排除默认日志框架-->        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-web</artifactId>            <exclusions>                <exclusion>                    <groupId>org.springframework.boot</groupId>                    <artifactId>spring-boot-starter-logging</artifactId>                </exclusion>            </exclusions>        </dependency>        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-test</artifactId>            <scope>test</scope>        </dependency>        <!--log4j-->        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-log4j</artifactId>            <version>1.3.8.RELEASE</version>        </dependency>        <!--druid-->        <dependency>            <groupId>com.alibaba</groupId>            <artifactId>druid</artifactId>            <version>1.0.18</version>        </dependency>        <!-- 数据库驱动 -->        <dependency>            <groupId>mysql</groupId>            <artifactId>mysql-connector-java</artifactId>            <version>5.1.25</version>        </dependency>        <!--mybaits -->        <dependency>            <groupId>org.mybatis.spring.boot</groupId>            <artifactId>mybatis-spring-boot-starter</artifactId>            <version>${mybatis.spring.boot.version}</version>        </dependency>        <dependency>            <groupId>commons-collections</groupId>            <artifactId>commons-collections</artifactId>        </dependency>    </dependencies>

在配置文件中加入mybaits配置：

# Mybatis配置mybatis.mapperLocations=classpath:mapper/*.xmlmybatis.configLocation=classpath:config/mybatis.xml

在资源文件夹下新建config文件，在其他新建mybatis.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>        <!-- map下划线自动转大写 -->        <setting name="mapUnderscoreToCamelCase" value="true" />        <!-- 打印查询语句 -->        <setting name="logImpl" value="STDOUT_LOGGING" />        <!-- 返回字段为空时null也显示该字段 -->        <setting name="callSettersOnNulls" value="true"/>      </settings>    <!-- 别名定义 -->    <typeAliases>        <!-- 批量别名定义,指定包名，mybatis自动扫描包中的po类，自动定义别名，别名是类名(首字母大写或小写都可以,一般用小写) -->        <!-- <package name="com.hotpot..pojo" /> -->        <!--<typeAlias type="com.hotpot.sys.pojo.SysUser" alias="sysUser"/> -->        <!-- 批量别名定义                        指定包名，mybatis自动扫描包中的po类，自动定义别名，别名就是类名（首字母大写或小写都可以）        -->        <!--<package name="com.demo.pojo"/>-->    </typeAliases>    <plugins>        <!-- 打印sql拦截器 -->        <plugin interceptor="com.springboot.interceptor.MybatisInterceptor"></plugin>    </plugins></configuration>

然后新建一个mapper包，下面新建UserMapper接口类：

/** * @Package:com.springboot.test.mapper * @ClassName:UserMapper * @Description:user mapper类 * @Author Shuyu.Wang * @Date 2017-12-07 17:27 **/@Mapperpublic interface UserMapper {    List<Map<String,Object>> listUsers();}

然后再资源文件夹下新建mapper文件夹，在下面新建UserMapper.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.springboot.test.mapper.UserMapper">    <select id="listUsers" resultType="java.util.Map">        select * from user    </select></mapper>

然后再web类中新增接口：

     @Autowired     private UserMapper userMapper;    @RequestMapping(value = "/db",method = RequestMethod.GET)    public List<Map<String,Object>> db(){        return  userMapper.listUsers();    }

另外，新建一个interceptor包，新增一个拦截器，用于打印sql的完整语句，包括参数，mybatis会打印相关查询语句，但是对于复杂语句操作，拦截器类可以帮助我们更清楚的追踪错误，代码如下：

package com.springboot.interceptor;import org.apache.commons.collections.CollectionUtils;import org.apache.ibatis.executor.Executor;import org.apache.ibatis.mapping.BoundSql;import org.apache.ibatis.mapping.MappedStatement;import org.apache.ibatis.mapping.ParameterMapping;import org.apache.ibatis.plugin.*;import org.apache.ibatis.reflection.MetaObject;import org.apache.ibatis.session.Configuration;import org.apache.ibatis.session.ResultHandler;import org.apache.ibatis.session.RowBounds;import org.apache.ibatis.type.TypeHandlerRegistry;import org.apache.log4j.Logger;import java.text.DateFormat;import java.util.Date;import java.util.List;import java.util.Locale;import java.util.Properties;import java.util.regex.Matcher;/** * @Title: MybatisInterceptor.java  * @Package com.ganinfo.utils  * @Description: 拦截打印完整sql语句 * @author Shuyu.Wang * @date Creation time: 2017年9月4日 * @version V1.0    */@Intercepts({ @Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class }),        @Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class,                RowBounds.class, ResultHandler.class }) })public class MybatisInterceptor implements Interceptor {    private Logger logger = Logger.getLogger(MybatisInterceptor.class);    @SuppressWarnings("unused")    private Properties properties;    @Override    public Object intercept(Invocation invocation) throws Throwable {        try {            MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];            Object parameter = null;            if (invocation.getArgs().length > 1) {                parameter = invocation.getArgs()[1];            }            String sqlId = mappedStatement.getId();            BoundSql boundSql = mappedStatement.getBoundSql(parameter);            Configuration configuration = mappedStatement.getConfiguration();            String sql = getSql(configuration, boundSql, sqlId, 0);            logger.info("*************");            logger.info("***"+sql);            logger.info("*************");        } catch (Exception e) {            e.printStackTrace();            logger.error(e);        }        return invocation.proceed();    }    public static String getSql(Configuration configuration, BoundSql boundSql, String sqlId, long time) {        String sql = showSql(configuration, boundSql);        StringBuilder str = new StringBuilder(100);        str.append(sqlId);        str.append(":");        str.append(sql);        return str.toString();    }    private static String getParameterValue(Object obj) {        String value = null;        if (obj instanceof String) {            value = "'" + obj.toString() + "'";        } else if (obj instanceof Date) {            DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);            value = "'" + formatter.format(new Date()) + "'";        } else {            if (obj != null) {                value = obj.toString();            } else {                value = "";            }        }        return value;    }    /**     * @param configuration     * @param boundSql     * @return     */    public static String showSql(Configuration configuration, BoundSql boundSql) {        Object parameterObject = boundSql.getParameterObject();        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");        if (CollectionUtils.isNotEmpty(parameterMappings) && parameterObject != null) {            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {                sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(parameterObject)));            } else {                MetaObject metaObject = configuration.newMetaObject(parameterObject);                for (ParameterMapping parameterMapping : parameterMappings) {                    String propertyName = parameterMapping.getProperty();                    if (metaObject.hasGetter(propertyName)) {                        Object obj = metaObject.getValue(propertyName);                        sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));                    } else if (boundSql.hasAdditionalParameter(propertyName)) {                        Object obj = boundSql.getAdditionalParameter(propertyName);                        sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));                    } else {                        sql = sql.replaceFirst("\\?", "缺失");                    } // 打印出缺失，提醒该参数缺失并防止错位                }            }        }        return sql;    }    @Override    public Object plugin(Object target) {        return Plugin.wrap(target, this);    }    @Override    public void setProperties(Properties properties0) {        this.properties = properties0;    }}

启动程序，访问：http://127.0.0.1:8082/boot/db
返回：

[{“password”:”123123”,”address”:null,”phone”:”12312312312”,”userName”:”阿道夫”,”userId”:1,”account”:”啊”}]

控制台打印如下：
这里写图片描述

然后访问：http://127.0.0.1:8082/boot/druid2/index.html
登录后点击SQL监控：
这里写图片描述
可以监控到我们操作的具体语句和相关的参数。

阅读全文

0 0