root@1dc27bbb5253:/# psql -U postgres -d minedb
minedb=# ALTER ROLE postgres SET SEARCH_PATH =‘ROS‘;
ALTER ROLE

我们使用了mybatis-generator-maven-plugin这个插件快速生成通用CRUD配置xml
为了使用这个插件，我们需要新建一个mybatis-generator.xml文件来指导插件工作：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
    PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
    "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <!--数据库驱动-->
    <classPathEntry location="C:\Users\xxxuser\.m2\repository\org\postgresql\postgresql\42.2.5\postgresql-42.2.5.jar"/>
    <context id="DB2Tables" targetRuntime="MyBatis3">
        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>
        <!--数据库链接地址账号密码-->
        <jdbcConnection driverClass="org.postgresql.Driver" connectionURL="jdbc:postgresql://172.22.122.27:5432/minedb" userId="postgres" password="aq1sw2de"></jdbcConnection>
        <javaTypeResolver>
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>
        <!--生成Model类存放位置-->
        <javaModelGenerator targetPackage="zsh.demos.postgres.dao.pojo" targetProject="./src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>
        <!--生成映射文件存放位置-->
        <sqlMapGenerator targetPackage="mapping" targetProject="./src/main/resources">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>
        <!--生成Dao类存放位置-->
        <javaClientGenerator type="XMLMAPPER" targetPackage="zsh.demos.postgres.dao.mapper" targetProject="./src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>
        <!--生成对应表及类名-->
        <!--<table tableName="big_table" domainObjectName="BigTable" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>-->
        <table tableName="vehicle" domainObjectName="Vehicle" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
    </context>
</generatorConfiguration>

前置准备工作完毕，我们开始配置连接参数

2. 使用YMAL方式配置：

spring:
  datasource:
    url: jdbc:postgresql://172.22.122.27:5432/minedb
    username: postgres
    password: aq1sw2de
    driver-class-name: org.postgresql.Driver

mybatis:
  type-handlers-package: zsh.demos.postgres.mybatis.typehandler
  mapper-locations: classpath:mapping/*.xml

3. 使用注解配置：

@Configuration
@MapperScan(basePackages = "zsh.demos.postgres.dao.mapper", sqlSessionFactoryRef = "pgSqlSessionFactory")
public class PostgresConfig {

    @Value("${mybatis.mapper-locations}")
    private String MAPPER_LOCATION;
    @Value("${mybatis.type-handlers-package}")
    private String TYPE_HANDLERS_PACKAGE;

    @Bean(name = "pgSqlSessionFactory")
    public SqlSessionFactory postgresSqlSessionFactory(@Autowired DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);

        // case change.
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setMapUnderscoreToCamelCase(true);

        sqlSessionFactoryBean.setConfiguration(configuration);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        sqlSessionFactoryBean.setTypeHandlersPackage(TYPE_HANDLERS_PACKAGE);
        return sqlSessionFactoryBean.getObject();
    }
}

4. TypeHandler

TypeHandler是针对JDBCType=OTHER的扩展，例如Postgres支持的JSON格式数据，我们需要手动定义如下TypeHandler:

基类：

public abstract class JSONTypeHandler<T> implements TypeHandler<T> {

    /**
     * json数据和类名的分隔符号
     */
    protected Class<T> jsonClass = null;

    @Override
    public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
        // TODO Auto-generated method stub
        if (parameter == null) {
            ps.setString(i, "");
            return;
        }
        String json = JSONUtils.jsonToJSONStr(parameter);
        PGobject pGobject = new PGobject();
        pGobject.setType("json");
        pGobject.setValue(json);
//      ps.setString(i, json);
        ps.setObject(i, pGobject);
    }

    @Override
    public T getResult(ResultSet rs, String columnName) throws SQLException {
        // TODO Auto-generated method stub
        String json = rs.getString(columnName);
        return jsonToObject(json);
    }

    @Override
    public T getResult(CallableStatement cs, int columnIndex) throws SQLException {
        // TODO Auto-generated method stub
        String json = cs.getString(columnIndex);
        return jsonToObject(json);
    }

    @Override
    public T getResult(ResultSet rs, int columnIndex) throws SQLException {
        // TODO Auto-generated method stub
        String json = rs.getString(columnIndex);
        return jsonToObject(json);
    }
    /**
     * json 转换成对象
     */
    protected T jsonToObject(String json) {
        if (StringUtils.isEmpty(json)) {
            return null;
        }
        T ob = JSONUtils.jsonStrToJSON(json, jsonClass);
        return ob;
    }
}

public class JSONUtils {
    //
    public static <T> String jsonToJSONStr(T json) {
        String jsonString = "";
        ObjectMapper mapper = new ObjectMapper();
        try {
            jsonString = mapper.writeValueAsString(json);
        } catch (JsonProcessingException e) {
            e.printStackTrace();
        }
        return jsonString;
    }
    //
    public static <T> T jsonStrToJSON(String json, Class<T> clazz) {
        ObjectMapper mapper = new ObjectMapper();
        T object = null;
        try {
            object = mapper.readValue(json, clazz);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return object;
    }
    //
    public static <T> T jsonStrToJSON(String json, TypeReference<T> type) {
        ObjectMapper mapper = new ObjectMapper();
        T object = null;
        try {
            object = mapper.readValue(json, type);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return object;
    }
}

@MappedTypes(BusinessBean.class)
public class BusinessBeanHandler extends JSONTypeHandler<BusinessBean> {
    public BusinessBeanHandler () {this.jsonClass = BusinessBean.class;}
}

<?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="zsh.demos.postgres.dao.mapper.VehicleMapper">
  <resultMap id="BaseResultMap" type="zsh.demos.postgres.dao.pojo.BusinessBean">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="business_json" jdbcType="OTHER" property="businessJSON" typeHandler="zsh.demos.postgres.mybatis.typehandler.BusinessBeanHandler"/>
    <result column="business_string" jdbcType="CHAR" property="businessString" />
  </resultMap>
</mapper>

4. 事务管理器

如果应用只配置了一个数据源，那么在默认情况下，SpringBoot在
org.springframework.boot.autoconfig ure.jdbc.DataSourceTransactionManagerAutoConfiguration
自动配置类中已经为我们配好了一个默认的事务管理器。并且在
org.springframework.boot.autoconfigure.transaction.TransactionAutoConfiguration
中帮我们自动启动了事务管理支持@EnableTransactionManagement所以我们无需做任何配置。

作者：大猪小猪在菜盘
链接：https://www.jianshu.com/p/9347014b31a3
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

SpringBoot+MyBatis+PostgreSQL配置

标签：index   admin   sqlmap   输入   mst   用户   tor   自动配置   result