SpringBoot+MyBatis+PostgreSQL配置
1.前置依赖配置
首先我们需要引入maven的依赖包
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.7</version>
</dependency>连接PostgreSQL时需要手动指定schema位置，否则，连接上的database会默认使用public这个内置的schema，导致在查询别的schema下的表时，会报类似如下的错误：
nested exception is org.postgresql.util.PSQLException: ERROR: relation "xxxTable" does not exist
我使用的方式是利用pgAdmin4，在控制界面上输入如下的SQL切换schema：
ALTER ROLE postgres SET SEARCH_PATH ='ROS'; #ROS是schema名，postgres是database用户名
当然也可以通过PostgreSQL提供的命令行界面来做切换：
root@1dc27bbb5253:/ su - postgres #首先需要切换用户到[postgres]用户 postgres@1dc27bbb5253:~$ psql #进入命令行模式 psql (11.2 (Debian 11.2-1.pgdg90+1)) Type "help" for help. postgres= c minedb; #切换数据库 minedb=# ALTER ROLE postgres SET SEARCH_PATH ='ROS'; ALTER ROLE
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:Usersxxxuser.m2
epositoryorgpostgresqlpostgresql42.2.5postgresql-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
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。