一、前言
前面我们在 《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