Mybatis结合springboot进行编码设置

最新推荐文章于 2021-07-24 11:20:42 发布

xueshuai0922

最新推荐文章于 2021-07-24 11:20:42 发布

阅读量844

点赞数

分类专栏： Java

本文链接：https://blog.csdn.net/xueshuai0922/article/details/106103074

版权

Java 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

Mybatis结合springboot进行编码设置

1.两种方式

业务背景：

数据库表结构导出，页面进行动态数据源提供，然后也提供编码，防止中文乱码

1）Filter

doFilter（）时候对response进行统一设置编码

但是因为我这个是动态编码，只是response得先解码然后再

2）mybatis中实现typeHandler接口

主要是对xml中的typeHandler

mapper的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.xs.database.mapper.DataBaseTableMapper">
    <!--加上二级缓存，虽然大材小用 哈哈，一级缓存就可以，因为我就写了这一个mapper的，
    二级缓存使用在namespace区域-->
    <!--<cache/>-->
    <resultMap id="dbMapOracle" type="com.xs.database.entity.DBTableEntity"
               >
        <id column="tableName" property="tableName" jdbcType="VARCHAR"></id>
        <result column="tableNameCn" property="tableNameCn" jdbcType="VARCHAR"
                typeHandler="com.xs.database.handler.BatisEncodeHandler"></result>
        <collection property="columnEntity" resultMap="colMapOracle">
        </collection>
    </resultMap>
    <resultMap id="colMapOracle" type="com.xs.database.entity.ColumnEntity">
        <id column="columnName" property="columnName" jdbcType="VARCHAR"></id>
        <result column="tableNameC" property="tableName" jdbcType="VARCHAR"></result>
        <result column="columnNameCn" property="columnNameCn" jdbcType="VARCHAR"
                typeHandler="com.xs.database.handler.BatisEncodeHandler"></result>
        <result column="dataType" property="dataType" jdbcType="VARCHAR"></result>
        <result column="nullAble" property="nullAble" jdbcType="VARCHAR"></result>
        <result column="dataLength" property="dataLength" jdbcType="INTEGER"></result>
        <!--oracle中long字段处理-->
        <result column="defaultData" property="defaultData"
                jdbcType="LONGVARBINARY"></result>
    </resultMap>


    <resultMap id="dbMap" type="com.xs.database.entity.DBTableEntity">
        <id column="tableName" property="tableName" jdbcType="VARCHAR"></id>
        <result column="tableNameCn" property="tableNameCn" jdbcType="VARCHAR"
                typeHandler="com.xs.database.handler.BatisEncodeHandler"></result>
        <collection property="columnEntity" resultMap="colMap">
        </collection>
    </resultMap>
    <resultMap id="colMap" type="com.xs.database.entity.ColumnEntity">
        <id column="columnName" property="columnName" jdbcType="VARCHAR"></id>
        <result column="tableNameC" property="tableName" jdbcType="VARCHAR"></result>
        <result column="columnNameCn" property="columnNameCn" jdbcType="VARCHAR"
                typeHandler="com.xs.database.handler.BatisEncodeHandler"></result>
        <result column="dataType" property="dataType" jdbcType="VARCHAR"></result>
        <result column="nullAble" property="nullAble" jdbcType="VARCHAR"></result>
        <result column="dataLength" property="dataLength" jdbcType="INTEGER"></result>
        <result column="defaultData" property="defaultData" jdbcType="VARCHAR"></result>
    </resultMap>


    <!--###################################################################-->
    <select id="getTableListByOracle" resultMap="dbMapOracle">
        SELECT Ss.TABLE_NAME as "tableName",
        T.COMMENTS as "tableNameCn",
        s.table_name as "tableNameC",
        s.COLUMN_NAME as "columnName",
        c.comments as "columnNameCn",
        s.DATA_TYPE as "dataType",
        s.NULLABLE as "nullAble",
        s.DATA_LENGTH as "dataLength", --number
        s.DATA_DEFAULT as "defaultData"--long
        FROM USER_TABLES Ss
        left join USER_TAB_COMMENTS T
        on Ss.TABLE_NAME = T.TABLE_NAME
        left join user_tab_columns S
        on s.TABLE_NAME = Ss.TABLE_NAME
        left join user_col_comments c
        on c.table_name = s.TABLE_NAME
        and c.column_name = s.COLUMN_NAME
        <where>
            t.table_type = 'TABLE'
            <if test="tableName != null and tableName!=''">
                and ss.table_name like #{tableName}
            </if>
        </where>
    </select>

    <select id="getTableListByMysql" resultMap="dbMap">
        SELECT
        t.table_name AS "tableName",
        t.table_comment AS "tableNameCn",
        c.column_name AS "columnName",
        c.column_default AS "defaultData",
        c.is_nullable AS "nullAble",
        c.data_type AS "dataType",
        c.character_maximum_length AS "dataLength",
        c.column_comment AS "columnNameCn"
        FROM
        information_schema.TABLES t
        LEFT JOIN information_schema.COLUMNS c ON t.table_name = c.table_name
        AND t.table_schema = c.table_schema


        <where>
            t.table_schema = (select database())
            <if test="tableName != null and tableName!=''">
                AND t.table_name like #{tableName}
            </if>
        </where>
        ORDER BY
        c.ordinal_position
    </select>

    <select id="testCon" resultType="int" useCache="false" flushCache="true">
        select 1 from dual
    </select>
</mapper>

实现接口，getResult（）对返回的值，进行重新编码

package com.xs.database.handler;

import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import java.io.UnsupportedEncodingException;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @Author xueshuai
 * @Date 2019/4/22 15:18
 * @Description  中文乱码处理handler，实现mybatis的typeHandler接口
 *  数据库编码：
 *      解数据库编码                      重新编码
 *      ISO-8559-1                        GBK
 *      GBK/GB2312                        GBK
 *      unicode UTF-8/UTF-16              不变
 *
 */
@Component
public class BatisEncodeHandler implements TypeHandler<String> {
    private String code;

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public BatisEncodeHandler(String code) {
        this.code = code;
    }

    public BatisEncodeHandler() {
        System.out.println("创建"+this+" bean============");
    }

    @Override
    public void setParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {

    }

    /**
     * 进行重新编码，解决中文乱码问题
     * @param rs
     * @param columnName
     * @return
     * @throws SQLException
     */
    @Override
    public String getResult(ResultSet rs, String columnName) throws SQLException {
        String string = rs.getString(columnName);
        try {
            if(string!=null && this.code != null){
                if("UTF-8".equals(this.code)){
                    return  string;
                }else{
                    byte[] bytes = string.getBytes(this.code);
                    return new String(bytes,"GBK");
                }

            }
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
       return string;
    }

    @Override
    public String getResult(ResultSet rs, int columnIndex) throws SQLException {
        String string = rs.getString(columnIndex);
        return string;
    }

    @Override
    public String getResult(CallableStatement cs, int columnIndex) throws SQLException {
        return null;
    }


}

因为我这边都是动态数据源，所以整个的sqlSessionFactory是自己来创建的，并且通过sqlSessionFactoryBean进行整个mybatis的设置。（之前很傻逼的做了很多无用功，疯狂直接在typeHandlerRegistry自己来设置，这样的错误，会创建两个对象，后面我自己动态设置编码就不起作用了,稍后附上源码流程）

package com.xs.database.config;

import com.xs.database.handler.BatisEncodeHandler;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.LocalCacheScope;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.*;
import org.springframework.core.io.DefaultResourceLoader;

import javax.sql.DataSource;


/**
 * @Author xueshuai
 * @Date 2019/4/27 14:53
 * @Description  DynamicDataSource配置类注册到spring容器中去
 */
@Configuration
@DependsOn("dataBaseTableController")
@MapperScan(basePackages="com.xs.database.mapper",sqlSessionFactoryRef = "sqlSessionFactory")
public class DynamicDataSourceConfig{

    @Value("${spring.datasource.username}")
    private String userName;
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.driver-class-name}")
    private String driver;
    @Value("${spring.datasource.password}")
    private  String password;
    @Autowired
    BatisEncodeHandler batisEncodeHandler;


    /**/
    @Bean
    public  DynamicDataSource dynamicDataSource(){
        DynamicDataSource dataSource = new DynamicDataSource();
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setPassword(password);
        hikariConfig.setJdbcUrl(url);
        hikariConfig.setUsername(userName);
        hikariConfig.setDriverClassName(driver);
        HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig);
        dataSource.map.put("defaultDatasource",
                hikariDataSource);
        //继承abstractRoutingDataSource类初始化bean会验证targetDataSource 不为空
        dataSource.setTargetDataSources(dataSource.map);
        return dataSource;
    }
    @Bean
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        factory.setDataSource(dataSource);
        org.apache.ibatis.session.Configuration configuration =
                new org.apache.ibatis.session.Configuration();

        LocalCacheScope localCacheScope =  LocalCacheScope.STATEMENT;
        configuration.setLocalCacheScope(localCacheScope);
        configuration.setCallSettersOnNulls(true);
        configuration.setCacheEnabled(false);
        configuration.setMapUnderscoreToCamelCase(true);
        //1.mybatis自动导入sqlsessionfactory时候，会注册一个handler，后面resultMap
        // 解析的时候，会验证是否存在typeHandler
        //如果存在，则不创建，不存在，会解析xml中的所有typeHandler（这样会导致创建多个typeHandler对象）
//        TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
//        typeHandlerRegistry.register(batisEncodeHandler.getClass());
        factory.setTypeHandlers(batisEncodeHandler);
        factory.setConfiguration(configuration);
        factory.setMapperLocations(new DefaultResourceLoader(this.getClass().getClassLoader()).getResource("static/xml/DataBaseTableMapper.xml"));
        return  factory.getObject();
    }





    @Bean
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

用法：

package com.xs.database.serivce.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.xs.database.entity.DBTableEntity;
import com.xs.database.config.CustomException;
import com.xs.database.config.DynamicDataSource;
import com.xs.database.config.ResultCode;
import com.xs.database.entity.ConnectionEntity;
import com.xs.database.handler.BatisEncodeHandler;
import com.xs.database.mapper.DataBaseTableMapper;
import com.xs.database.serivce.IDataBaseTableService;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import oracle.jdbc.driver.OracleDriver;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.sql.Driver;
import java.util.List;

/**
 * @Author xueshuai
 * @Date 2019/4/10 13:30
 * @Description
 */
@Service("MybatisTableService")
public class MybatisTableService implements IDataBaseTableService {
    @Autowired
    DataBaseTableMapper mapper;

    @Autowired
    BatisEncodeHandler batisEncodeHandler;

    private Logger logger = LoggerFactory.getLogger(MybatisTableService.class);

    @Override
    public List<DBTableEntity> getTableList(ConnectionEntity conEntity) throws Exception {
        try {
            reSetDB(conEntity);
        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new CustomException(ResultCode.FAILED.getCode(), "数据库配置错误，请检查数据库信息！");
        }
        //中文乱码处理 
        batisEncodeHandler.setCode(conEntity.getEncode());
        String dbType = conEntity.getDataType();

        String tableName = StringUtils.isEmpty(conEntity.getTableName())?"":
                "%" + conEntity.getTableName().toUpperCase() +
                        "%";
        List<DBTableEntity> tableList = null;
        if ("mysql".equals(dbType)) {
            tableList = mapper.getTableListByMysql(tableName);
        } else if ("oracle".equals(dbType)) {
            tableList = mapper.getTableListByOracle(tableName);
        }
        return tableList;
    }

    @Override
    public boolean testCon(ConnectionEntity conEntity) {
        try {
            reSetDB(conEntity);
        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new CustomException(ResultCode.FAILED.getCode(), "数据库配置错误，请检查数据库信息！");
        }
        return mapper.testCon()>0?true:false;
    }


    private void reSetDB(ConnectionEntity conEntity) throws Exception {
        String driver = null;
        String url = "";
        /*重新获取DataSource bean,然后设置数据库信息*/
        if ("mysql".equals(conEntity.getDataType())) {
            driver = "com.mysql.cj.jdbc.Driver";
            url = "jdbc:mysql://" + conEntity.getIp() + ":"
                    + conEntity.getPort() + "/" + conEntity.getdataName();
        } else if ("oracle".equals(conEntity.getDataType())) {
            driver = "oracle.jdbc.driver.OracleDriver";
            url = "jdbc:oracle:thin:@" + conEntity.getIp() + ":"
                    + conEntity.getPort() + ":" + conEntity.getdataName();

        }
        try{
            HikariConfig hikariConfig = new HikariConfig();
            hikariConfig.setPassword(conEntity.getPassword());
            hikariConfig.setJdbcUrl(url);
            hikariConfig.setUsername(conEntity.getUserName());
            hikariConfig.setDriverClassName(driver);
            HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig);
            DynamicDataSource.setDataSource(hikariDataSource);
        }catch (Exception e){
            throw e;
        }

    }
}

其实用法也很简单，主要我们来看看这个源码流程

第一我们得先知道spring创建bean的过程
在这里插入图片描述

springboot结合mybatis时候，如果是默认的，那么就会自动注入SqlSessionFactory

public class MybatisAutoConfiguration implements InitializingBean {
    private static final Logger logger = LoggerFactory.getLogger(MybatisAutoConfiguration.class);
    private final MybatisProperties properties;
    private final Interceptor[] interceptors;
    private final TypeHandler[] typeHandlers;
    private final LanguageDriver[] languageDrivers;
    private final ResourceLoader resourceLoader;
    private final DatabaseIdProvider databaseIdProvider;
    private final List<ConfigurationCustomizer> configurationCustomizers;

    public MybatisAutoConfiguration(MybatisProperties properties, ObjectProvider<Interceptor[]> interceptorsProvider, ObjectProvider<TypeHandler[]> typeHandlersProvider, ObjectProvider<LanguageDriver[]> languageDriversProvider, ResourceLoader resourceLoader, ObjectProvider<DatabaseIdProvider> databaseIdProvider, ObjectProvider<List<ConfigurationCustomizer>> configurationCustomizersProvider) {
        this.properties = properties;
        this.interceptors = (Interceptor[])interceptorsProvider.getIfAvailable();
        this.typeHandlers = (TypeHandler[])typeHandlersProvider.getIfAvailable();
        this.languageDrivers = (LanguageDriver[])languageDriversProvider.getIfAvailable();
        this.resourceLoader = resourceLoader;
        this.databaseIdProvider = (DatabaseIdProvider)databaseIdProvider.getIfAvailable();
        this.configurationCustomizers = (List)configurationCustomizersProvider.getIfAvailable();
    }

    public void afterPropertiesSet() {
        this.checkConfigFileExists();
    }

    private void checkConfigFileExists() {
        if (this.properties.isCheckConfigLocation() && StringUtils.hasText(this.properties.getConfigLocation())) {
            Resource resource = this.resourceLoader.getResource(this.properties.getConfigLocation());
            Assert.state(resource.exists(), "Cannot find config location: " + resource + " (please add config file or check your Mybatis configuration)");
        }

    }

    @Bean
    @ConditionalOnMissingBean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        factory.setDataSource(dataSource);
        factory.setVfs(SpringBootVFS.class);
        if (StringUtils.hasText(this.properties.getConfigLocation())) {
            factory.setConfigLocation(this.resourceLoader.getResource(this.properties.getConfigLocation()));
        }

        this.applyConfiguration(factory);
        if (this.properties.getConfigurationProperties() != null) {
            factory.setConfigurationProperties(this.properties.getConfigurationProperties());
        }

        if (!ObjectUtils.isEmpty(this.interceptors)) {
            factory.setPlugins(this.interceptors);
        }

        if (this.databaseIdProvider != null) {
            factory.setDatabaseIdProvider(this.databaseIdProvider);
        }

        if (StringUtils.hasLength(this.properties.getTypeAliasesPackage())) {
            factory.setTypeAliasesPackage(this.properties.getTypeAliasesPackage());
        }

        if (this.properties.getTypeAliasesSuperType() != null) {
            factory.setTypeAliasesSuperType(this.properties.getTypeAliasesSuperType());
        }

        if (StringUtils.hasLength(this.properties.getTypeHandlersPackage())) {
            factory.setTypeHandlersPackage(this.properties.getTypeHandlersPackage());
        }

        if (!ObjectUtils.isEmpty(this.typeHandlers)) {
            factory.setTypeHandlers(this.typeHandlers);
        }

        if (!ObjectUtils.isEmpty(this.properties.resolveMapperLocations())) {
            factory.setMapperLocations(this.properties.resolveMapperLocations());
        }

        Set<String> factoryPropertyNames = (Set)Stream.of((new BeanWrapperImpl(SqlSessionFactoryBean.class)).getPropertyDescriptors()).map(FeatureDescriptor::getName).collect(Collectors.toSet());
        Class<? extends LanguageDriver> defaultLanguageDriver = this.properties.getDefaultScriptingLanguageDriver();
        if (factoryPropertyNames.contains("scriptingLanguageDrivers") && !ObjectUtils.isEmpty(this.languageDrivers)) {
            factory.setScriptingLanguageDrivers(this.languageDrivers);
            if (defaultLanguageDriver == null && this.languageDrivers.length == 1) {
                defaultLanguageDriver = this.languageDrivers[0].getClass();
            }
        }

        if (factoryPropertyNames.contains("defaultScriptingLanguageDriver")) {
            factory.setDefaultScriptingLanguageDriver(defaultLanguageDriver);
        }

        return factory.getObject();
    }
    //后面注册的bean和注入就省略了，自行看下
}

我业务的话，自己生产的，然后注入在spring容器中，上文的代码已经贴出了

那么当你用到一个controller的时候，他会反射，根据类对象得出构造器，然后进行newInstance（）得到对象，然后会遍历其中的@autowire注入的bean，并同样的拿到对象，一直从service到mapper，找到sqlSessionFactory类型找sqlSessionFactory。默认的话，会找到MybatisAutoConfiguration，通过反射创建该对象。如果自行设置的话，他会找自行设置的配置类

    }
    }

    if (factoryPropertyNames.contains("defaultScriptingLanguageDriver")) {
        factory.setDefaultScriptingLanguageDriver(defaultLanguageDriver);
    }

    return factory.getObject();
}
//后面注册的bean和注入就省略了，自行看下

}

我业务的话，自己生产的，然后注入在spring容器中，上文的代码已经贴出了

xueshuai0922

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mybatis结合springboot进行编码设置

Mybatis结合springboot进行编码设置1.两种方式业务背景：数据库表结构导出，页面进行动态数据源提供，然后也提供编码，防止中文乱码1）Filter doFilter（）时候对response进行统一设置编码但是因为我这个是动态编码，只是response得先解码然后再2）mybatis中实现typeHandler接口主要是对xml中的typeHandlermapper的xml 如下：<?xml version="1.0" encoding="UTF-8"?>&l
复制链接

扫一扫

专栏目录