druid监控页面sql有数据 session没数据

转载

编程梦想家 2024-10-30 10:21:09

文章标签 mybatis mysql java spring boot spring 文章分类 运维

spring boot整合mybatis+druid和多数据源

单个数据源

pom文件：
application.properties文件配置内容
单数据代码奉献上

多个数据源

目录结构
application.properties
数据源1的配置
数据源2的配置

整合MyBatis之前，先搭建一个基本的Spring Boot项目开启Spring Boot

单个数据源

pom文件：

<!--引入druid数据源 -->
     <dependency>
         <groupId>com.alibaba</groupId>
         <artifactId>druid</artifactId>
         <version>1.1.8</version>
     </dependency>
     <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-jdbc</artifactId>
     </dependency>
     <!-- 数据库 -->
     <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
     </dependency>
     <!-- Mybatis -->
     <dependency>
         <groupId>org.mybatis.spring.boot</groupId>
         <artifactId>mybatis-spring-boot-starter</artifactId>
         <version>1.3.2</version>
     </dependency>

application.properties文件配置内容

单数据源
#datasource1
spring.datasource.druid.type=com.alibaba.druid.pool.DataSource
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql:///myredis?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=123456
#连接池配置
spring.datasource.initial-size=5
spring.datasource.min-idle=5
spring.datasource.max-active=20
#连接等待超时时间
spring.datasource.max-wait=30000
#配置检测可以关闭的空闲连接间隔时间
spring.datasource.time-between-eviction-runs-millis=60000
#配置连接在池中的最小生存时间
spring.datasource.min-evictable-idle-time-millis=300000
spring.datasource.validation-query=select '1' from dual
spring.datasource.test-while-idle=true
spring.datasource.test-on-borrow=false
spring.datasource.test-on-return=false
#打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.pool-prepared-statements=true
spring.datasource.max-open-prepared-statements=20
spring.datasource.max-pool-prepared-statement-per-connection-size=20

单数据代码奉献上

DemoController 控制层的代码

@RestController
public class DemoController {
    @Autowired
    public DemoService demoService;

    @RequestMapping("/demo")
    String getStringAll() {
        return demoService.getQueryAll().toString();
    }
}

service层

public interface DemoService {
    /**
     * 查询数据源1所有信息
     *
     * @return
     */
    List<Teacher> getQueryAll();
}
@Service
public class DemoServiceImpl implements DemoService {
    @Autowired
    DemoQuery demoQuery;
    @Override
    public List<Teacher> getQueryAll() {
        return demoQuery.getQueryAll();
    }

dao 层我这里为了方便没有写.xml映射文件

@Component
@Mapper
public interface DemoQuery {
    /**
     *  查询所有老师信息
     * @return List<Teacher>
     */
    @Select("select * from teacher")
    @Results(id = "teacher",value= {
            @Result(property = "sno", column = "sno", javaType = String.class),
            @Result(property = "name", column = "sname", javaType = String.class),
            @Result(property = "sex", column = "ssex", javaType = String.class)
    })
    List<Teacher> getQueryAll();

}

需要在启动类加上MapperScan 后面跟上要扫描的dao

@SpringBootApplication
@MapperScan("study.startspringboot.mapper")
public class StartSpringBootApplication {
    public static void main(String[] args) {
        SpringApplication.run(StartSpringBootApplication.class, args);
    }
}

sql语句

CREATE TABLE teacher (
    SNO VARCHAR(5),
    SNAME VARCHAR(9),
    SSEX CHAR(3) 
);

INSERT INTO teacher VALUES ('001', 'KangKang', 'M ');
INSERT INTO teacher VALUES ('002', 'Mike', 'M ');
INSERT INTO teacher VALUES ('003', 'Jane', 'F ');

到这里单个数据源就可以使用了，下面进入多个数据源模式

多个数据源

一般用于读写分离，或者分库来使用，有的教程会加上 @Primary表示主数据源，既然分开来说明有这个需求没有主次之分，这里我也使用这样的方式吧
pom文件和上面的单个数据源的！你们复制即可！然后从application.properties默认配置改成了注解方式来进行
@bean druidServlet这个是开启druid的sql时间监控的运行项目打开这个链接即可：http://localhost:8080/druid/login.html

目录结构

druid监控页面sql有数据 session没数据_spring boot

application.properties

#数据库访问配置,使用数据源
#datasource1
ds1.datasource.url=jdbc:mysql:///myredis?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
ds1.datasource.username=root
ds1.datasource.password=123456
ds1.datasource.driverClassName=com.mysql.cj.jdbc.Driver
#datasource2
ds2.datasource.url=jdbc:mysql:///myredis?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
ds2.datasource.username=root
ds2.datasource.password=123456
ds2.datasource.driverClassName=com.mysql.cj.jdbc.Driver
##
datasource.initialSize=20
datasource.minIdle=20
datasource.maxActive=200
datasource.maxWait=-1
datasource.timeBetweenEvictionRunsMillis=60000
datasource.minEvictableIdleTimeMillis=300000
datasource.testWhileIdle=true
datasource.testOnBorrow=false
datasource.testOnReturn=false
datasource.poolPreparedStatements=true
datasource.maxPoolPreparedStatementPerConnectionSize=20
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
datasource.filters=stat,wall,slf4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
datasource.useGlobalDataSourceStat=true

数据源1的配置

@Configuration
@MapperScan(basePackages = Datasource1Config.PACKAGE, sqlSessionFactoryRef = "ds1SqlSessionFactory")
public class Datasource1Config {
   /**
    * 数据的Dao层，你要扫描的类包的全称
    */
   static final String PACKAGE = "study.startspringboot.mapper.datasource2";
   /**
    * 映射的资源文件
    */
   static final String MAPPER_LOCATION = "classpath*:mapper/datasource1dao/*.xml";

   @Value("${ds1.datasource.url}")
   private String url;
   @Value("${ds1.datasource.username}")
   private String user;
   @Value("${ds1.datasource.password}")
   private String password;
   @Value("${ds1.datasource.driverClassName}")
   private String driverClass;
   
   @Value("${datasource.maxActive}")
   private Integer maxActive;
   @Value("${datasource.minIdle}")
   private Integer minIdle;
   @Value("${datasource.initialSize}")
   private Integer initialSize;
   @Value("${datasource.maxWait}")
   private Long maxWait;
   @Value("${datasource.timeBetweenEvictionRunsMillis}")
   private Long timeBetweenEvictionRunsMillis;
   @Value("${datasource.minEvictableIdleTimeMillis}")
   private Long minEvictableIdleTimeMillis;
   @Value("${datasource.testWhileIdle}")
   private Boolean testWhileIdle;
   @Value("${datasource.testWhileIdle}")
   private Boolean testOnBorrow;
   @Value("${datasource.testOnBorrow}")
   private Boolean testOnReturn;
   @Value("${datasource.poolPreparedStatements}")
   private boolean poolPreparedStatements;
   @Value("${datasource.maxPoolPreparedStatementPerConnectionSize}")
   private int maxPoolPreparedStatementPerConnectionSize;
   @Value("${datasource.filters}")
   private String filters;
   @Value("${datasource.connectionProperties}")
   private String connectionProperties;
   @Value("${datasource.useGlobalDataSourceStat}")
   private boolean useGlobalDataSourceStat;

   @Bean(name = "ds1DataSource")
   @Primary
   public DataSource ds1DataSource() {
       DruidDataSource datasource = new DruidDataSource();
       datasource.setUrl(url);
       datasource.setUsername(user);
       datasource.setPassword(password);
       datasource.setDriverClassName(driverClass);
       datasource.setInitialSize(initialSize);
       datasource.setMinIdle(minIdle);
       datasource.setMaxActive(maxActive);
       datasource.setMaxWait(maxWait);
       datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
       datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
       datasource.setValidationQuery("SELECT 'x'");
       datasource.setTestWhileIdle(testWhileIdle);
       datasource.setTestOnBorrow(testOnBorrow);
       datasource.setTestOnReturn(testOnReturn);
       datasource.setPoolPreparedStatements(poolPreparedStatements);
       datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
       try {
           datasource.setFilters(filters);
       } catch (SQLException e) {
           System.err.println("druid configuration initialization filter: " + e);
       }
       datasource.setConnectionProperties(connectionProperties);
       datasource.setUseGlobalDataSourceStat(useGlobalDataSourceStat);
       return datasource;

   }

   @Bean(name = "ds1TransactionManager")
   @Primary
   public DataSourceTransactionManager ds1TransactionManager() {
       return new DataSourceTransactionManager(ds1DataSource());
   }




   @Bean(name = "ds1SqlSessionFactory")
   @Primary
   public SqlSessionFactory ds1SqlSessionFactory(@Qualifier("ds1DataSource") DataSource ds1DataSource)
           throws Exception {
       final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
       sessionFactory.setDataSource(ds1DataSource);
       sessionFactory.setTypeAliasesPackage("com.uchat.entity");
       sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
               .getResources(Datasource1Config.MAPPER_LOCATION));
       return sessionFactory.getObject();
   }

   @Bean
   public ServletRegistrationBean druidServlet() {
         //访问路径为/druid时，跳转到StatViewServlet
       ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
      //启动druid 监控路径（不设置没有办法启动）
       servletRegistrationBean.addUrlMappings("/druid/*");
       // IP白名单
       servletRegistrationBean.addInitParameter("allow", "127.0.0.1,127.0.0.1");
       // IP黑名单(共同存在时，deny优先于allow)
       servletRegistrationBean.addInitParameter("deny", "192.16.1.100");
       //控制台管理用户
       servletRegistrationBean.addInitParameter("loginUsername", "admin");
       servletRegistrationBean.addInitParameter("loginPassword", "1111");
       //是否能够重置数据 禁用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;
   }
}

数据源2的配置

@Configuration
@MapperScan(basePackages = Datasource2Config.PACKAGE, sqlSessionFactoryRef = "ds2SqlSessionFactory")
public class Datasource2Config {
   /**
    * 数据的Dao层，打出他的包路径
    */
   static final String PACKAGE = "study.startspringboot.mapper.datasource2";
   /**
    * 映射的资源文件
    */
   static final String MAPPER_LOCATION = "classpath*:mapper/datasource2dao/*.xml";

   @Value("${ds1.datasource.url}")
   private String url;
   @Value("${ds1.datasource.username}")
   private String user;
   @Value("${ds1.datasource.password}")
   private String password;
   @Value("${ds1.datasource.driverClassName}")
   private String driverClass;
   
   @Value("${datasource.maxActive}")
   private Integer maxActive;
   @Value("${datasource.minIdle}")
   private Integer minIdle;
   @Value("${datasource.initialSize}")
   private Integer initialSize;
   @Value("${datasource.maxWait}")
   private Long maxWait;
   @Value("${datasource.timeBetweenEvictionRunsMillis}")
   private Long timeBetweenEvictionRunsMillis;
   @Value("${datasource.minEvictableIdleTimeMillis}")
   private Long minEvictableIdleTimeMillis;
   @Value("${datasource.testWhileIdle}")
   private Boolean testWhileIdle;
   @Value("${datasource.testWhileIdle}")
   private Boolean testOnBorrow;
   @Value("${datasource.testOnBorrow}")
   private Boolean testOnReturn;
   @Value("${datasource.poolPreparedStatements}")
   private boolean poolPreparedStatements;
   @Value("${datasource.maxPoolPreparedStatementPerConnectionSize}")
   private int maxPoolPreparedStatementPerConnectionSize;
   @Value("${datasource.filters}")
   private String filters;
   @Value("${datasource.connectionProperties}")
   private String connectionProperties;
   @Value("${datasource.useGlobalDataSourceStat}")
   private boolean useGlobalDataSourceStat;

   @Bean(name = "ds2DataSource")
   public DataSource ds2DataSource() {
       DruidDataSource datasource = new DruidDataSource();
       datasource.setUrl(url);
       datasource.setUsername(user);
       datasource.setPassword(password);
       datasource.setDriverClassName(driverClass);
       datasource.setInitialSize(initialSize);
       datasource.setMinIdle(minIdle);
       datasource.setMaxActive(maxActive);
       datasource.setMaxWait(maxWait);
       datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
       datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
       datasource.setValidationQuery("SELECT 'x'");
       datasource.setTestWhileIdle(testWhileIdle);
       datasource.setTestOnBorrow(testOnBorrow);
       datasource.setTestOnReturn(testOnReturn);
       datasource.setPoolPreparedStatements(poolPreparedStatements);
       datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
       try {
           datasource.setFilters(filters);
       } catch (SQLException e) {
           System.err.println("druid configuration initialization filter: " + e);
       }
       datasource.setConnectionProperties(connectionProperties);
       datasource.setUseGlobalDataSourceStat(useGlobalDataSourceStat);
       return datasource;

   }

   @Bean(name = "ds2TransactionManager")
   public DataSourceTransactionManager ds2TransactionManager() {
       return new DataSourceTransactionManager(ds2DataSource());
   }
   @Bean(name = "ds2SqlSessionFactory")
   public SqlSessionFactory ds2SqlSessionFactory(@Qualifier("ds2DataSource") DataSource ds2DataSource)
           throws Exception {
       final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
       sessionFactory.setDataSource(ds2DataSource);
       sessionFactory.setTypeAliasesPackage("com.uchat.entity");
       sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
               .getResources(Datasource2Config.MAPPER_LOCATION));
       return sessionFactory.getObject();
   }


}

有什么问题不懂得评论我会解答！
我把代码放在了git上：https://github.com/aloneoneself/springStudy 记得给星星呀，不会Git的我会把源文件上传到附件的！！！！

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：kafka怎么查看未同步的副本

下一篇：spring实现多数据源

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯