Spring Boot教程五：整合mybaits

今天药忘吃喽~ 2021-09-14 11:28 300阅读 0赞

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>
    
            
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-log4j</artifactId>
                <version>1.3.8.RELEASE</version>
            </dependency>
            
            <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>
    
            
            <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/*.xml
    mybatis.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>
            
            <setting name="mapUnderscoreToCamelCase" value="true" />
            
            <setting name="logImpl" value="STDOUT_LOGGING" />
            
            <setting name="callSettersOnNulls" value="true"/>  
        </settings>
    
        
        <typeAliases>
            
            
            
    
            
            
        </typeAliases>
    
        <plugins>
            
            <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 **/
    @Mapper
    public 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][http_127.0.0.1_8082_boot_db]  
返回：

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

控制台打印如下：  
![这里写图片描述][SouthEast]

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

[http_127.0.0.1_8082_boot_db]: http://127.0.0.1:8082/boot/db
[SouthEast]: /images/20210901/19973d6f10214deb9d60e9e328e60af7.png
[http_127.0.0.1_8082_boot_druid2_index.html]: http://127.0.0.1:8082/boot/druid2/index.html
[SouthEast 1]: /images/20210901/eb4e39c49ca34c4c8f8824152bdff71f.png