NamedParameterJdbcTemplate多数据源指定数据源-Toy模板网

这篇具有很好参考价值的文章主要介绍了NamedParameterJdbcTemplate多数据源指定数据源。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

实战例子记录

pom

        <!-- https://mvnrepository.com/artifact/com.github.miemiedev/mybatis-paginator -->
        <dependency>
            <groupId>com.github.miemiedev</groupId>
            <artifactId>mybatis-paginator</artifactId>
            <version>1.2.17</version>
        </dependency>

config

package com.config;

import com.github.miemiedev.mybatis.paginator.OffsetLimitInterceptor;
import com.zaxxer.hikari.HikariDataSource;
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 javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.dao.gp", sqlSessionFactoryRef = "gpSqlSessionFactory")
public class GpDatasourceConfig {
    private String mapperLocal = "com/dao/gp/**/*Mapper.xml";

    @ConfigurationProperties(prefix = "spring.datasource.gp")
    @Bean("GpDatasource")
    public DataSource gpDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean("gpSqlSessionFactory")
    public SqlSessionFactory gpDataSqlSessionFactory(@Qualifier("GpDatasource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        OffsetLimitInterceptor offsetLimitInterceptor = new OffsetLimitInterceptor();
        offsetLimitInterceptor.setDialectClass("com.github.miemiedev.mybatis.paginator.dialect.PostgreSQLDialect");
        sqlSessionFactoryBean.setPlugins(offsetLimitInterceptor);
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setMapUnderscoreToCamelCase(true);
        sqlSessionFactoryBean.setConfiguration(configuration);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResource(mapperLocal));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean("gpSqlSessionTemplate")
    public SqlSessionTemplate gpSqlSessionTemplate(@Qualifier("gpSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

package com.config;

import com.github.miemiedev.mybatis.paginator.OffsetLimitInterceptor;
import com.zaxxer.hikari.HikariDataSource;
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.dao.oracle", sqlSessionFactoryRef = "oracleSqlSessionFactory")
public class OracleDatasourceConfig {
    private String mapperLocal = "com/dao/oracle/**/*Mapper.xml";

    @ConfigurationProperties(prefix = "spring.datasource.oracle")
    @Bean("oracleDatasource")
    public DataSource oracleDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean("oracleSqlSessionFactory")
    public SqlSessionFactory oracleDataSqlSessionFactory(@Qualifier("oracleDatasource") DataSource dataSource, @Qualifier("mybatisConfiguration") org.apache.ibatis.session.Configuration configuration) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        OffsetLimitInterceptor offsetLimitInterceptor = new OffsetLimitInterceptor();
        offsetLimitInterceptor.setDialectClass("com.github.miemiedev.mybatis.paginator.dialect.OracleDialect");
        sqlSessionFactoryBean.setPlugins(offsetLimitInterceptor);
        sqlSessionFactoryBean.setConfiguration(configuration);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResource(mapperLocal));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean("gpSqlSessionTemplate")
    public SqlSessionTemplate gpSqlSessionTemplate(@Qualifier("oracleSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean("mybatisConfiguration")
    @ConfigurationProperties(prefix = "mybatis.configuration")
    public org.apache.ibatis.session.Configuration configuration() {
        return new org.apache.ibatis.session.Configuration();
    }

    @Bean("oracleTransactionManger")
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("oracleDatasource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

}

NamedParameterJdbcTemplate（动态sql调用）文章来源地址https://www.toymoban.com/news/detail-480239.html

@Test
    public void dynamicSql(String tableName, List<HashMap<String, Object>> data) {
        String getColumnSql = "select * from %s where 1!=1";
        String getSaveSql = "insert into %s (%s) values (%s)";
        String getDelSql = "select * from %s where 1!=1";

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(gpDatasourceConfig.gpDataSource());

        //查询
        SqlRowSet sqlRowSet = namedParameterJdbcTemplate.queryForRowSet(String.format(getColumnSql, tableName), new HashMap<>());

        SqlRowSetMetaData metaData = sqlRowSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        StringBuilder valueBuilder = new StringBuilder();
        StringBuilder columnBuilder = new StringBuilder();
        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i).toLowerCase(Locale.ROOT);
            if (i != 1) {
                valueBuilder.append(",");
                columnBuilder.append(",");
            }
            valueBuilder.append(":").append(columnName);
            columnBuilder.append(columnName);
            String saveSql = String.format(getSaveSql, tableName, columnBuilder, valueBuilder);
            //list转数组（添加）
            namedParameterJdbcTemplate.batchUpdate(saveSql, data.toArray(new HashMap[data.size()]));
        }
        //删除
        String delSql = String.format(getDelSql, tableName, "column");
        HashMap<String, Object> map = new HashMap<>();
        map.put("column", "column");
        namedParameterJdbcTemplate.update(delSql, map);

    }

到了这里，关于NamedParameterJdbcTemplate多数据源指定数据源的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！