一、前言

前面我们在 《Spring Boot JdbcTemplate配置Druid多数据源》《Spring-Boot-MyBatis配置Druid多数据源》 中整合了oracle和mysql,今天我们使用在Spring Boot中JdbcTemplate与Mybatis使用PostgreSQL,具体过程如下:

二、JdbcTemplate中整合PostgreSQL

我们这里就在 《Spring Boot JdbcTemplate配置Druid多数据源》 基础上进行改造。

2.1 引入依赖

1
2
3
4
5
        <dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>

2.2 修改application.yml

这里我们的数据使用 《Spring-Boot整合JPA与PostgreSQL》 中的数据,需要在application.yml里面新增如下PostgreSQL的连接等信息。

1
2
3
4
5
6
      pgsql:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://localhost:5432/springboot?currentSchema=spring
username: spring
password: spring#123

在配置数据源配置中新增如下代码:

1
2
3
4
5
6
7
8
9
10
11
12
    @Bean(name = "pgsqldatasource")
@ConfigurationProperties("spring.datasource.druid.pgsql")
public DataSource dataSourceThree(){
return DruidDataSourceBuilder.create().build();
}


@Bean(name = "pgsqlJdbcTemplate")
public JdbcTemplate pgsqlJdbcTemplate(
@Qualifier("pgsqldatasource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}

2.3 编码

新增 PgsqlStudentDao.java

1
2
3
public interface PgsqlStudentDao {
List<Map<String, Object>> getAllStudents();
}

新增 PgsqlStudentDaoImpl.java

1
2
3
4
5
6
7
8
9
10
11
12
@Repository
public class PgsqlStudentDaoImpl implements PgsqlStudentDao {

@Autowired
@Qualifier("pgsqlJdbcTemplate")
private JdbcTemplate jdbcTemplate;

@Override
public List<Map<String, Object>> getAllStudents() {
return this.jdbcTemplate.queryForList("select * from student");
}
}

修改 StudentService.java

1
2
3
4
5
public interface StudentService {
List<Map<String, Object>> getAllStudentsWithMysql();
List<Map<String, Object>> getAllStudentsWithOracle();
List<Map<String, Object>> getAllStudentsWithPgsql();
}

修改 StudentServiceImpl.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
@Service("studentService")
public class StudentServiceImpl implements StudentService {

@Autowired
private OracleStudentDao oracleStudentDao;

@Autowired
private MysqlStudentDao mysqlStudentDao;

@Autowired
private PgsqlStudentDao pgsqlStudentDao;

@Override
public List<Map<String, Object>> getAllStudentsWithMysql() {
return this.mysqlStudentDao.getAllStudents();
}

@Override
public List<Map<String, Object>> getAllStudentsWithOracle() {
return this.oracleStudentDao.getAllStudents();
}

@Override
public List<Map<String, Object>> getAllStudentsWithPgsql() {
return this.pgsqlStudentDao.getAllStudents();
}
}

修改 TestController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@RestController
public class TestController {
@Autowired
private StudentService studentService;

@RequestMapping( value = "/getAllStudentsWithMysql", method = RequestMethod.GET)
public List<Map<String, Object>> getAllStudentsWithMysql() {
return this.studentService.getAllStudentsWithMysql();
}

@RequestMapping( value = "/getAllStudentsWithOracle", method = RequestMethod.GET)
public List<Map<String, Object>> getAllStudentsWithOracle() {
return this.studentService.getAllStudentsWithOracle();
}

@RequestMapping( value = "/getAllStudentsWithPgsql", method = RequestMethod.GET)
public List<Map<String, Object>> getAllStudentsWithPgsql() {
return this.studentService.getAllStudentsWithPgsql();
}
}

2.4 测试

数据库新增几条记录如下:

启动项目,浏览器访问: http://localhost:8080/getAllStudentsWithPgsql

三、Mybatis中整合PostgreSQL

我们这里就在 《Spring-Boot-MyBatis配置Druid多数据源》 基础上进行改造。

3.1 引入依赖

1
2
3
4
5
        <dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>

3.2 修改application.yml

1
2
3
4
5
6
      pgsql:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://localhost:5432/springboot?currentSchema=spring
username: spring
password: spring#123

新增PgsqlDatasourceConfig.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
@Configuration
@MapperScan(basePackages = PgsqlDatasourceConfig.PACKAGE, sqlSessionFactoryRef = "pgsqlSqlSessionFactory")
public class PgsqlDatasourceConfig {

// mysqldao扫描路径
static final String PACKAGE = "com.wno704.boot.pgsqldao";
// mybatis mapper扫描路径
static final String MAPPER_LOCATION = "classpath:mapper/pgsql/*.xml";

@Bean(name = "pgsqldatasource")
@ConfigurationProperties("spring.datasource.druid.pgsql")
public DataSource pgsqlDatasource() {
return DruidDataSourceBuilder.create().build();
}

@Bean(name = "pgsqlTransactionManager")
public DataSourceTransactionManager pgsqlTransactionManager() {
return new DataSourceTransactionManager(pgsqlDatasource());
}

@Bean(name = "pgsqlSqlSessionFactory")
public SqlSessionFactory pgsqlSqlSessionFactory(@Qualifier("pgsqldatasource") DataSource dataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
//如果不使用xml的方式配置mapper,则可以省去下面这行mapper location的配置。
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(PgsqlDatasourceConfig.MAPPER_LOCATION));
return sessionFactory.getObject();
}
}

3.3 编码

新增 PgsqlStudentMapper.java

1
2
3
4
5
@Mapper
@Repository("pgsqlStudentMapper")
public interface PgsqlStudentMapper {
List<Map<String, Object>> getAllStudents();
}

新增 PgsqlStudentMapper.xml

1
2
3
4
5
6
7
8
<?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.wno704.boot.pgsqldao.PgsqlStudentMapper">
<select id="getAllStudents" resultType="java.util.Map">
select * from student
</select>
</mapper>

修改StudentService.java

1
2
3
4
5
public interface StudentService {
List<Map<String, Object>> getAllStudentsWithMysql();
List<Map<String, Object>> getAllStudentsWithOracle();
List<Map<String, Object>> getAllStudentsWithPgsql();
}

修改StudentServiceImpl.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
@Service("studentService")
public class StudentServiceImpl implements StudentService {
@Autowired
private MysqlStudentMapper mysqlStudentMapper;

@Autowired
private OracleStudentMapper oracleStudentMapper;

@Autowired
private PgsqlStudentMapper pgsqlStudentMapper;

@Override
public List<Map<String, Object>> getAllStudentsWithMysql() {
return this.mysqlStudentMapper.getAllStudents();
}

@Override
public List<Map<String, Object>> getAllStudentsWithOracle() {
return this.oracleStudentMapper.getAllStudents();
}

@Override
public List<Map<String, Object>> getAllStudentsWithPgsql() {
return this.pgsqlStudentMapper.getAllStudents();
}
}

修改TestController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@RestController
public class TestController {
@Autowired
private StudentService studentService;

@RequestMapping( value = "/getAllStudentsWithMysql", method = RequestMethod.GET)
public List<Map<String, Object>> getAllStudentsWithMysql() {
return this.studentService.getAllStudentsWithMysql();
}

@RequestMapping( value = "/getAllStudentsWithOracle", method = RequestMethod.GET)
public List<Map<String, Object>> getAllStudentsWithOracle() {
return this.studentService.getAllStudentsWithOracle();
}

@RequestMapping( value = "/getAllStudentsWithPgsql", method = RequestMethod.GET)
public List<Map<String, Object>> getAllStudentsWithPgsql() {
return this.studentService.getAllStudentsWithPgsql();
}
}

3.4 测试

启动项目,浏览器访问: http://localhost:8080/getAllStudentsWithPgsql