SpringBoot+MyBatis+PostgreSQL配置
SpringBoot+MyBatis+PostgreSQL配置
1.前置依赖配置
⾸先我们需要引⼊maven的依赖包
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId&batis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId&ator</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
为了使⽤这个插件，我们需要新建⼀个l⽂件来指导插件⼯作：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-////DTD MyBatis Generator Configuration 1.0//EN"
"/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"></ta <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.pehandler
mapper-locations: classpath:mapping/*.xml
配置中指定数据源，指定xml格式的mapping⽂件存放位置，指定对于JDBCType=OTHER属性的字段(例如PostgreSQL中的JSON类型)的处理handler存放位置
3. 使⽤注解配置：
@Configuration
@MapperScan(basePackages = "zsh.demos.postgres.dao.mapper", sqlSessionFactoryRef = "pgSqlSessionFactory")
public class PostgresConfig {
@Value("${mybatis.mapper-locations}")
private String MAPPER_LOCATION;
@Value("${pe-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);
Object();
}
}
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 {
//
springframework和springbootpublic 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 = adValue(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 = adValue(json, type);
} catch (Exception e) {
e.printStackTrace();
}
return object;
}
}
具体业务对象类：
@MappedTypes(BusinessBean.class)
public class BusinessBeanHandler extends JSONTypeHandler<BusinessBean> {
public BusinessBeanHandler () {this.jsonClass = BusinessBean.class;}
}
使⽤时，需要再resultMap中指定需要typeHandler转换的字段：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/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.pehandler.BusinessBeanHandler"/> <result column="business_string" jdbcType="CHAR" property="businessString" />
</resultMap>
</mapper>
4. 事务管理器
如果应⽤只配置了⼀个数据源，那么在默认情况下，SpringBoot在
org.springframework.boot.autoconfig ure.jdbc.DataSourceTransactionManagerAutoConfiguration
⾃动配置类中已经为我们配好了⼀个默认的事务管理器。并且在
org.springframework.ansaction.TransactionAutoConfiguration
中帮我们⾃动启动了事务管理⽀持@EnableTransactionManagement所以我们⽆需做任何配置。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论