mybatis的xml映射文件思考

对于一些复杂的sql语句，在写xml映射时，会存在很多问题：
比如：

1. select 出来的字段名，如果非常多，而且这些字段不仅仅是一个实体类（entity包下）中的fields，对应多个实体类中的字段，就需要写resultMap了，然而，字段名非常多，都写出来显得十分笨猪。那么该怎么办呢？(如果都对应一个实体类中的field，不需要写resultMap,直接指定resultType为该实体类就可以了)
2. 在写resultMap时涉及到多表联结时，需要指定一个主类，即type=“某类”，其他类都添加到该主类的filed定义中，并添加getter,setter方法（可以利用lombok的@Data注解到该类，省略getter,setter的显示书写），然后在xml文件的resultMap中将select出来非主类的字段，通过主类中新添加的field过度到其他副类的具体字段。同样，如果字段非常多，也需要一个一个多写吗？
3. entity包中的实体类可以利用myabtis-plus的自动生成器生成，但是可以不可以把前面说的，需要区分主类副类的field也自动加进去？
4. 目前可以主动生成entity包，简单的增删改查的mapper,xml，其他复杂的sql语句如果将结果输出为一张表，那么新的维度上简单的增删改查也就是我们想要的增删改查了，是否可行，该怎么行呢？
5. mapper接口中，一个方法的参数一定需要加@Param(“xxx”)吗?
6. 如果sql语句中用到了某个函数，导致返回的数据字段不是基本数据类型，比如ST_AsGeoJson()这个函数就将返回json类型的数据，那么就需要额外定义typehandler处理，那么其他类型该怎么写typehandler呢？

<result column="ggeom" property="ggeom" typeHandler="com.atzhong.cityck.mapper.typehandler.JsonTypeHandler"/>
        

具体如下：

package com.atzhong.cityck.mapper.typehandler;

import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

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


public class JsonTypeHandler<T extends Object> extends BaseTypeHandler<T> {

    private static final ObjectMapper mapper = new ObjectMapper();
    private Class<T> clazz;

    public JsonTypeHandler(Class<T> clazz) {
        if (clazz == null) {
            throw new IllegalArgumentException("Type argument cannot be null");
        }
        this.clazz = clazz;
    }

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, this.toJson(parameter));
    }

    @Override
    public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return this.toObject(rs.getString(columnName), clazz);
    }

    @Override
    public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return this.toObject(rs.getString(columnIndex), clazz);
    }

    @Override
    public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return this.toObject(cs.getString(columnIndex), clazz);
    }

    private String toJson(T object) {
        try {
            return mapper.writeValueAsString(object);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    private T toObject(String content, Class<?> clazz) {
        if (content != null && !content.isEmpty()) {
            try {
                return (T) mapper.readValue(content, clazz);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        } else {
            return null;
        }
    }

}

8. 同一个项目需要连接多个数据库的设置
   application.yml

master:
  datasource:
    url: jdbc:postgresql://47.67.23.99:5432/citycheck_hk2020?stringtype=unspecified&useSSL=false
    username: pp
    password: Sss
    driver-class-name: org.postgresql.Driver
second:
  datasource:
    url: jdbc:sqlserver://47.95.98.20;DatabaseName=hkcheck
    username: haidd
    password: qwss345
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver

mybatis:
#  mapper-locations: classpath:mapper/*xml
  configuration:
    map-underscore-to-camel-case: true
    type-handlers-package: com.atzhong.cityck.mapper.typehandler
#  type-aliases-package: com.atzhong.cityck.mybatis.entity


server:
  port: 8087

配置类
主数据库

package com.atzhong.cityck.DBconfig;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {

    // 精确到 master 目录，以便跟其他数据源隔离
    static final String PACKAGE = "com.atzhong.cityck.mapper.hk2";
    static final String MAPPER_LOCATION = "classpath:mapper/hk2/*.xml";

    @Value("${master.datasource.url}")
    private String url;

    @Value("${master.datasource.username}")
    private String user;

    @Value("${master.datasource.password}")
    private String password;

    @Value("${master.datasource.driver-class-name}")
    private String driverClass;

    @Bean(name = "masterDataSource")
    @Primary
    public DataSource masterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(masterDataSource());
    }

    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MasterDataSourceConfig.MAPPER_LOCATION));
//        org.apache.ibatis.session.Configuration configuration=new org.apache.ibatis.session.Configuration();
//        configuration.setUseGeneratedKeys(true);//使用jdbc的getGeneratedKeys获取数据库自增主键值
//        configuration.setUseColumnLabel(true);//使用列别名替换列名，如：select user as User
//        configuration.setMapUnderscoreToCamelCase(true);//-自动使用驼峰命名属性映射字段，如userId    user_id
//        sessionFactory.setConfiguration(configuration);
        return sessionFactory.getObject();
    }
}

从数据库

package com.atzhong.cityck.DBconfig;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = SecondDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "secondSqlSessionFactory")
public class SecondDataSourceConfig {

    // 精确到 cluster 目录，以便跟其他数据源隔离
    static final String PACKAGE = "com.atzhong.cityck.mapper.haikou";
    static final String MAPPER_LOCATION = "classpath:mapper/haikou/*.xml";

    @Value("${second.datasource.url}")
    private String url;

    @Value("${second.datasource.username}")
    private String user;

    @Value("${second.datasource.password}")
    private String password;

    @Value("${second.datasource.driver-class-name}")
    private String driverClass;

    @Bean(name = "secondDataSource")
    public DataSource clusterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "secondTransactionManager")
    public DataSourceTransactionManager clusterTransactionManager() {
        return new DataSourceTransactionManager(clusterDataSource());
    }

    @Bean(name = "secondSqlSessionFactory")
    public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("secondDataSource") DataSource clusterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(clusterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(SecondDataSourceConfig.MAPPER_LOCATION));
//        org.apache.ibatis.session.Configuration configuration=new org.apache.ibatis.session.Configuration();
//        configuration.setUseGeneratedKeys(true);//使用jdbc的getGeneratedKeys获取数据库自增主键值
//        configuration.setUseColumnLabel(true);//使用列别名替换列名，如：select user as User
//        configuration.setMapUnderscoreToCamelCase(true);//-自动使用驼峰命名属性映射字段，如userId    user_id
//        sessionFactory.setConfiguration(configuration);
        return sessionFactory.getObject();
    }
}