[spring boot 27] springboot is configured with two databases, from Java to proficient PDF Baidu online disk

/**

• Close the connection

• @param stmt sql Perform object

• @param conn Database connection object

*/

public static void close(Statement stmt, Connection conn){

if(stmt != null){

try {

stmt.close();

} catch (SQLException e) {

log.error(“close error,errorMessage:{}”, e);

}

}

if(conn != null){

try {

conn.close();

} catch (SQLException e) {

log.error(“close error,errorMessage:{}”, e);

}

}

}

/**

• Close the overloaded method of the resource

• @param rs The object that handles the result set

• @param stmt perform sql Object of statement

• @param conn Objects connected to the database

*/

public static void close(ResultSet rs, Statement stmt, Connection conn){

if(rs != null){

try {

rs.close();

} catch (SQLException e) {

log.error(“close error,errorMessage:{}”, e);

}

}

if(stmt != null){

try {

stmt.close();

} catch (SQLException e) {

log.error(“close error,errorMessage:{}”, e);

}

}

if(conn != null){

try {

conn.close();

} catch (SQLException e) {

log.error(“close error,errorMessage:{}”, e);

}

}

}

}

Two 、jdbcTemplate.queryForList Source code exploration

---

public class JdbcTemplate extends JdbcAccessor implements JdbcOperations {

public JdbcTemplate(DataSource dataSource) {

setDataSource(dataSource);

afterPropertiesSet();

}

@Override

public List<Map<String, Object>> queryForList(String sql) throws DataAccessException {

return query(sql, getColumnMapRowMapper());

}

@Override

public List query(String sql, RowMapper rowMapper) throws DataAccessException {

return result(query(sql, new RowMapperResultSetExtractor<>(rowMapper)));

}

@Override

@Nullable

public T query(final String sql, final ResultSetExtractor rse) throws DataAccessException {

Assert.notNull(sql, “SQL must not be null”);

Assert.notNull(rse, “ResultSetExtractor must not be null”);

if (logger.isDebugEnabled()) {

logger.debug(“Executing SQL query [” + sql + “]”);

}

/**

• Callback to execute the query.

*/

class QueryStatementCallback implements StatementCallback, SqlProvider {

@Override

@Nullable

public T doInStatement(Statement stmt) throws SQLException {

ResultSet rs = null;

try {

rs = stmt.executeQuery(sql);

return rse.extractData(rs);

}

finally {

JdbcUtils.closeResultSet(rs);

}

}

@Override

public String getSql() {

return sql;

}

}

return execute(new QueryStatementCallback());

}

@Override

@Nullable

public T execute(StatementCallback action) throws DataAccessException {

Assert.notNull(action, “Callback object must not be null”);

Connection con = DataSourceUtils.getConnection(obtainDataSource());

Statement stmt = null;

try {

stmt = con.createStatement();

applyStatementSettings(stmt);

T result = action.doInStatement(stmt);

handleWarnings(stmt);

return result;

}

catch (SQLException ex) {

// Release Connection early, to avoid potential connection pool deadlock

// in the case when the exception translator hasn’t been initialized yet.

String sql = getSql(action);

JdbcUtils.closeStatement(stmt);

stmt = null;

DataSourceUtils.releaseConnection(con, getDataSource());

con = null;

throw translateException(“StatementCallback”, sql, ex);

}

finally {

JdbcUtils.closeStatement(stmt);

DataSourceUtils.releaseConnection(con, getDataSource());

}

}

…

}

public interface Statement extends Wrapper, AutoCloseable {

ResultSet executeQuery(String sql) throws SQLException;

…

}

public abstract class JdbcAccessor implements InitializingBean {

@Nullable

private DataSource dataSource;

public void setDataSource(@Nullable DataSource dataSource) {

this.dataSource = dataSource;

}

}

3、 ... and 、 More elegant way -> By configuring classes

---

1、application.yml

server:

port: 8080

spring:

application:

name: test

datasource:

sqlserve

《 A big factory Java Analysis of interview questions + Back end development learning notes + The latest architecture explanation video + Practical project source code handout 》

【docs.qq.com/doc/DSmxTbFJ1cmN1R2dB】 Full content open source sharing

r:

jdbc-url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test

driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver

username: sa

password: sa

postgres:

jdbc-url: jdbc:postgresql://127.0.0.1:5432/test

driverClassName: org.postgresql.Driver

username: postgres

password: 123456

2、 Configuration class

package com.guor.config;

import org.apache.ibatis.session.SqlSessionFactory;

import org.mybatis.spring.SqlSessionFactoryBean;

import org.mybatis.spring.SqlSessionTemplate;

import org.mybatis.spring.annotation.MapperScan;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.boot.jdbc.DataSourceBuilder;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration

@MapperScan(basePackages = “com.guor.dao.postgres”, sqlSessionTemplateRef = “postgresSqlSessionTemplate”)

public class PostgresConfig {

@Bean(name = “postgresDataSource”)

@ConfigurationProperties(prefix = “spring.datasource.postgres”)

public DataSource postgresDataSource() {

return DataSourceBuilder.create().build();

}

@Bean(name = “postgresSqlSessionFactory”)

public SqlSessionFactory postgresSqlSessionFactory(@Qualifier(“postgresDataSource”) DataSource dataSource) throws Exception {

SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

bean.setDataSource(dataSource);

bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(“com/guor/dao/postgres/mapping/*.xml”));

return bean.getObject();

}

@Bean(name = “postgresTransactionManager”)

public DataSourceTransactionManager postgresTransactionManager(@Qualifier(“postgresDataSource”) DataSource dataSource) {

return new DataSourceTransactionManager(dataSource);

}

@Bean(name = “postgresSqlSessionTemplate”)

public SqlSessionTemplate postgresSqlSessionTemplate(@Qualifier(“postgresSqlSessionFactory”) SqlSessionFactory sqlSessionFactory) throws Exception {

return new SqlSessionTemplate(sqlSessionFactory);

}

}

package com.guor.config;

import org.apache.ibatis.session.SqlSessionFactory;

import org.mybatis.spring.SqlSessionFactoryBean;

import org.mybatis.spring.SqlSessionTemplate;

import org.mybatis.spring.annotation.MapperScan;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.boot.jdbc.DataSourceBuilder;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.Primary;

import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration

@MapperScan(basePackages = “com.guor.dao.sqlserver”, sqlSessionTemplateRef = “sqlserverSqlSessionTemplate”)

public class SqlserverConfig {

@Bean(name = “sqlserverDataSource”)

@ConfigurationProperties(prefix = “spring.datasource.sqlserver”)

@Primary

public DataSource sqlserverDataSource() {

return DataSourceBuilder.create().build();

}

@Bean(name = “sqlserverSqlSessionFactory”)

@Primary

public SqlSessionFactory sqlserverSqlSessionFactory(@Qualifier(“sqlserverDataSource”) DataSource dataSource) throws Exception {

SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

bean.setDataSource(dataSource);

bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(“com/guor/dao/sqlserver/mapping/*.xml”));

return bean.getObject();

}

@Bean(name = “sqlserverTransactionManager”)

@Primary

public DataSourceTransactionManager sqlserverTransactionManager(@Qualifier(“sqlserverDataSource”) DataSource dataSource) {

return new DataSourceTransactionManager(dataSource);

}

@Bean(name = “sqlserverSqlSessionTemplate”)

@Primary

public SqlSessionTemplate sqlserverSqlSessionTemplate(@Qualifier(“sqlserverSqlSessionFactory”) SqlSessionFactory sqlSessionFactory) throws Exception {

return new SqlSessionTemplate(sqlSessionFactory);

}

}

3、UserPostgresMapper

package com.guor.dao.postgres;

import java.util.List;

import java.util.Map;

public interface UserPostgresMapper {

List<Map<String, Object>> getUsersByPostgres();

}

4、UserPostgresMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>

SELECT * FROM t_user

5、UserSqlserverMapper

package com.guor.dao.sqlserver;

import java.util.List;

import java.util.Map;

public interface UserSqlserverMapper {

List<Map<String, Object>> getUsersFromSqlserver();

}

6、UserSqlserverMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>

SELECT * FROM t_user

7、controller

package com.guor.controller;

import java.util.List;

import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import com.guor.dao.postgres.UserPostgresMapper;

import com.guor.dao.sqlserver.UserSqlserverMapper;

@RestController

@RequestMapping(“user”)

public class UserController {

@Autowired

private UserSqlserverMapper userMapper;

@GetMapping("/getUsersBySqlserver")

public List<Map<String, Object>> getUsersBySqlserver(){

return userMapper.getUsersFromSqlserver();

}

@Autowired

private UserPostgresMapper userPostgresMapper;

@GetMapping("/getUsersByPostgres")

public List<Map<String, Object>> getUsersByPostgres(){

return userPostgresMapper.getUsersByPostgres();

}

}

8、 Browser access

http://localhost:8080/user/getUsersByPostgres

[{“id”:1,“name”:“zs”,“age”:18,“version”:“1”,“deleted”:0},{“id”:1,“name”:“ls”,“age”:28,“creator_id”:87368736,“created_time”:“2021-06-11T02:43:48.000+0000”},{“id”:2,“name”:“ww”,“age”:35,“creator_id”:87368736,“created_time”:“2021-06-11T05:29:20.000+0000”}]

http://localhost:8080/user/getUsersBySqlserver

[{“id”:1,“name”:“zs”,“age”:18},{“id”:2,“name”:“ls”,“age”:20}]

9、pom

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns=“http://maven.apache.org/POM/4.0.0”

xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”

xsi:schemaLocation=“http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd”>

4.0.0

com.guor

test-pom

1.0.0

…/pom.xml

test

test

test

1.0.0