MyBatis Plus多数据源配置， Invalid bound statement (not found)报错解决

avatar 2021年12月06日08:54:05 6 7214 views
博主分享免费Java教学视频，B站账号：Java刘哥 ，长期提供技术问题解决、项目定制：本站商品点此

上周下班前遇到一个很奇怪的问题

在配置多数据源的时候，调用其中一个dao的selectById一直找不到，即报错  Invalid bound statement (not found)。

解决办法如下：

SqlSessionFactoryBean 改成 MybatisSqlSessionFactoryBean

 

顺便把多数据源配置也贴一下

主要看8和9的2个数据源配置

 

1、pom.xml

<dependency>
	<groupId>com.baomidou</groupId>
	<artifactId>mybatis-plus-boot-starter</artifactId>
	<version>2.3.3</version>
</dependency>

 

2、application.yml

server:
  port: 8080

#spring配置
spring:
  datasource:
    ## 主数据库
    main:
      url: jdbc:mysql://127.0.0.1:3306/db_main?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2b8
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver

    ## 配置系统数据库
    projectcfg:
      url: jdbc:mysql://127.0.0.1:3306/db_projectcfg?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2b8
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver

    ## Druid配置
    druid:
      type: com.alibaba.druid.pool.DruidDataSource
      initial-size: 80
      min-idle: 80
      maxActive: 200
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      filters: stat
      web-stat-filter.url-pattern: /*
      stat-view-servlet.enabled: true
      stat-view-servlet.url-pattern: /admin/druid/*
      stat-view-servlet.reset-enable: false
      filter.stat.log-slow-sql: true
      filter.stat.slow-sql-millis: 500
      
      
mybatis-plus:
  mapper-locations: classpath*:mapper/*.xml
  global-config:
    #主键类型  0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
    id-type: 0
    #字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"
    field-strategy: 1
    #驼峰下划线转换
    db-column-underline: true
    #刷新mapper 调试神器
    refresh-mapper: false
    # 逻辑删除字段
    logic-delete-value: 1
    logic-not-delete-value: 0
    #自定义SQL注入器
    sql-injector: com.baomidou.mybatisplus.mapper.LogicSqlInjector
    db-config:
      id-type: auto
      field-strategy: NOT_EMPTY
      db-type: MYSQL
  configuration:
    map-underscore-to-camel-case: true
    call-setters-on-nulls: true
    cache-enabled: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    
mybatis:
  configuration:
    map-underscore-to-camel-case: true

 

3、启动类

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@SpringBootApplication
@EnableTransactionManagement // 开启事物管理
public class Application
{
    public static void main(String[] args)
    {
        SpringApplication.run(SvnWebApplication.class, args);
    }
}

 

4、DruidConfiguration 类

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import lombok.Data;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Component;

/**
 * 数据库链接池druid监控平台的配置
 * 访问路径 http://localhost:8080/druid/index.html
 *
 */
@Configuration
public class DruidConfiguration
{
    private static final Logger logger = LoggerFactory.getLogger(DruidConfiguration.class);

    @Bean
    public ServletRegistrationBean druidServlet()
    {
        logger.info("init Druid Servlet Configuration ");
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        // IP白名单
        servletRegistrationBean.addInitParameter("allow", "*");
        // IP黑名单(共同存在时，deny优先于allow)
        //        servletRegistrationBean.addInitParameter("deny", "192.168.1.100");
        //控制台管理用户
        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        servletRegistrationBean.addInitParameter("loginPassword", "123456");
        //是否能够重置数据 禁用HTML页面上的“Reset All”功能
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean()
    {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }

    @ConfigurationProperties(prefix = "spring.datasource.druid")
    @Component
    @Data
    public static class DruidProperties
    {
        private int initialSize;
        private int minIdle;
        private int maxActive;
        private int maxWait;
        private int timeBetweenEvictionRunsMillis;
        private int minEvictableIdleTimeMillis;
        private String validationQuery;
        private boolean testWhileIdle;
        private boolean testOnBorrow;
        private boolean testOnReturn;
        private boolean poolPreparedStatements;
        private int maxPoolPreparedStatementPerConnectionSize;
        private String filters;
        private String connectionProperties;
        private boolean resetStatEnable;
        private long timeBetweenLogStatsMillis;
    }
}

 

5、MybatisPlusConfigurer类

import com.baomidou.mybatisplus.mapper.ISqlInjector;
import com.baomidou.mybatisplus.mapper.LogicSqlInjector;
import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * MybatisPlus配置
 */
@Configuration
public class MybatisPlusConfigurer {
	/**
	 * 分页插件
	 *
	 * @return PaginationInterceptor
	 */
	@Bean
	public PaginationInterceptor paginationInterceptor() {
		return new PaginationInterceptor();
	}

	@Bean
	public ISqlInjector sqlInjector(){ return new LogicSqlInjector(); }

}

 

6、DbProperties类

public interface DbProperties
{
    String getUrl();

    String getUsername();

    String getPassword();

    String getDriverClassName();
}

 

7、DataSourceUtil类

import com.alibaba.druid.pool.DruidDataSource;
import lombok.extern.slf4j.Slf4j;

import javax.sql.DataSource;

/**
 * 数据源工具类
 */
@Slf4j
public class DataSourceUtil
{
    public static DataSource build(DruidConfiguration.DruidProperties druidProperties, DbProperties dbProperties)
    {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(dbProperties.getUrl());
        datasource.setUsername(dbProperties.getUsername());
        datasource.setPassword(dbProperties.getPassword());
        datasource.setDriverClassName(dbProperties.getDriverClassName());

        //configuration
        datasource.setInitialSize(druidProperties.getInitialSize());
        datasource.setMinIdle(druidProperties.getMinIdle());
        datasource.setMaxActive(druidProperties.getMaxActive());
        datasource.setMaxWait(druidProperties.getMaxWait());
        datasource.setTimeBetweenEvictionRunsMillis(druidProperties.getTimeBetweenEvictionRunsMillis());
        datasource.setMinEvictableIdleTimeMillis(druidProperties.getMinEvictableIdleTimeMillis());
        datasource.setValidationQuery(druidProperties.getValidationQuery());
        datasource.setTestWhileIdle(druidProperties.isTestWhileIdle());
        datasource.setTestOnBorrow(druidProperties.isTestOnBorrow());
        datasource.setTestOnReturn(druidProperties.isTestOnReturn());
        datasource.setPoolPreparedStatements(druidProperties.isPoolPreparedStatements());
        datasource.setMaxPoolPreparedStatementPerConnectionSize(druidProperties.getMaxPoolPreparedStatementPerConnectionSize());
        datasource.setResetStatEnable(true);
        try
        {
            datasource.setFilters(druidProperties.getFilters());
        } catch (Exception e)
        {
            log.info("druid configuration initialization filter: " + e);
        }
        datasource.setConnectionProperties(druidProperties.getConnectionProperties());
        return datasource;
    }
}

 

8、数据源配置1

import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean;
import lombok.Data;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;
import tk.mybatis.spring.annotation.MapperScan;

import javax.sql.DataSource;
import java.util.Properties;

@Configuration
@MapperScan(value = {"com.example.entity.mapper"}, sqlSessionFactoryRef = "mainSqlSessionFactory")
public class DbMainConfig
{
    @ConfigurationProperties(prefix = "spring.datasource.main")
    @Component
    @Data
    class MainDbProperties implements DbProperties
    {
        private String url;
        private String username;
        private String password;
        private String driverClassName;
    }

    @Bean(name = "mainDataSource")
    public DataSource getFirstDataSource(DruidConfiguration.DruidProperties druidProperties, MainDbProperties MainDbProperties)
    {
        return DataSourceUtil.build(druidProperties, MainDbProperties);
    }

    @Bean(name = "mainSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("mainDataSource") DataSource dataSource) throws Exception
    {
//        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        // 不能用SqlSessionFactoryBean，要用MybatisSqlSessionFactoryBean
        MybatisSqlSessionFactoryBean factoryBean = new MybatisSqlSessionFactoryBean();
        // 设置数据源
        factoryBean.setDataSource(dataSource);

        // 设置mapper文件扫描位置
        PathMatchingResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
        // 能加载多个，所以可以配置通配符(如：classpath*:mapper/**/*.xml)
        factoryBean.setMapperLocations(resourcePatternResolver.getResources("classpath*:/mapper/main/*Mapper.xml"));

        // 设置属性
        Properties factoryProperties = new Properties();
        factoryProperties.setProperty("blobType", "BLOB");
        factoryProperties.setProperty("boolValue", "TRUE");
        factoryProperties.setProperty("prefix", "");
        factoryBean.setConfigurationProperties(factoryProperties);
        factoryBean.setPlugins(new Interceptor[]{new PaginationInterceptor()});

        // 设置包别名
        factoryBean.setTypeAliasesPackage("com.example.entity.main");
        return factoryBean.getObject();
    }

    @Bean(name = "mainTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("mainDataSource") DataSource dataSource)
    {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "mainSqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("mainSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception
    {
        return new SqlSessionTemplate(sqlSessionFactory);
    }


    @Bean(name = "mainJdbcTemplate")
    public JdbcTemplate mysqlJdbcTemplate(@Qualifier("mainDataSource") DataSource dataSource) throws Exception
    {
        return new JdbcTemplate(dataSource);
    }
}

 

9、数据源配置2

import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean;
import lombok.Data;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;
import tk.mybatis.spring.annotation.MapperScan;

import javax.sql.DataSource;
import java.util.Properties;

@Configuration
@MapperScan(value = {"com.example.mapper.projectcfg"}, sqlSessionFactoryRef = "projectcfgSqlSessionFactory")
public class DbProjectcfgConfig
{
    @ConfigurationProperties(prefix = "spring.datasource.projectcfg")
    @Component
    @Data
    class ProjectcfgDbProperties implements DbProperties
    {
        private String url;
        private String username;
        private String password;
        private String driverClassName;
    }

    @Bean(name = "projectcfgDataSource")
    public DataSource getFirstDataSource(DruidConfiguration.DruidProperties druidProperties, ProjectcfgDbProperties ProjectcfgDbProperties)
    {
        return DataSourceUtil.build(druidProperties, ProjectcfgDbProperties);
    }

    @Bean(name = "projectcfgSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("projectcfgDataSource") DataSource dataSource) throws Exception
    {
//        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        // 不能用SqlSessionFactoryBean，要用MybatisSqlSessionFactoryBean
        MybatisSqlSessionFactoryBean factoryBean = new MybatisSqlSessionFactoryBean();
        // 设置数据源
        factoryBean.setDataSource(dataSource);

        // 设置mapper文件扫描位置
        PathMatchingResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
        // 能加载多个，所以可以配置通配符(如：classpath*:mapper/**/*.xml)
        factoryBean.setMapperLocations(resourcePatternResolver.getResources("classpath*:/mapper/projectcfg/*Mapper.xml"));

        // 设置属性
        Properties factoryProperties = new Properties();
        factoryProperties.setProperty("blobType", "BLOB");
        factoryProperties.setProperty("boolValue", "TRUE");
        factoryProperties.setProperty("prefix", "");
        factoryBean.setConfigurationProperties(factoryProperties);
        factoryBean.setPlugins(new Interceptor[]{new PaginationInterceptor()});
        // 设置包别名
        factoryBean.setTypeAliasesPackage("com.example.entity.projectcfg");
        return factoryBean.getObject();
    }

    @Bean(name = "projectcfgTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("projectcfgDataSource") DataSource dataSource)
    {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "projectcfgSqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("projectcfgSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception
    {
        return new SqlSessionTemplate(sqlSessionFactory);
    }


    @Bean(name = "projectcfgJdbcTemplate")
    public JdbcTemplate mysqlJdbcTemplate(@Qualifier("projectcfgDataSource") DataSource dataSource) throws Exception
    {
        return new JdbcTemplate(dataSource);
    }
}

 

 

 

历史上的今天
十二月
06日
  • 微信
  • 交流学习，资料分享
  • weinxin
  • 个人淘宝
  • 店铺名：言曌博客咨询部

  • (部分商品未及时上架淘宝)
avatar

发表评论

avatar 登录者：匿名
匿名评论，评论回复后会有邮件通知

  

已通过评论：0   待审核评论数：0