springboot +mybatise +druid静态访问多个数据库

最新推荐文章于 2024-05-06 09:45:49 发布

我滴神呀

最新推荐文章于 2024-05-06 09:45:49 发布

阅读量403

点赞数

分类专栏：数据库访问

本文链接：https://blog.csdn.net/zhanglin1220/article/details/78039448

版权

数据库访问专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1、application.properties配置文件：

#数据库设置
spring.datasource.mytest.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.mytest.driverClassName=com.mysql.jdbc.Driver
spring.datasource.mytest.url=jdbc:mysql://127.0.0.1:3306/mytest
spring.datasource.mytest.username=root
spring.datasource.mytest.password=176658
#--------------------------
spring.datasource.mytest1.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.mytest1.driverClassName=com.mysql.jdbc.Driver
spring.datasource.mytest1.url=jdbc:mysql://127.0.0.1:3306/mytest1
spring.datasource.mytest1.username=root
spring.datasource.mytest1.password=176658
# 下面为连接池的补充设置，应用到上面所有数据源中
# 初始化大小，最小，最大
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 
spring.datasource.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒 
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
# 打开PSCache，并且指定每个连接上PSCache的大小 
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙 
spring.datasource.filters=stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
#spring.datasource.useGlobalDataSourceStat=true

2、工程包结构。

这里写图片描述

3、controller包
//HelloController

package cn.controller;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import cn.entity.City;
import cn.mapper1.CityMapper;
@RestController
@RequestMapping("/demo")

public class HelloController {
    static Logger log = LogManager.getLogger(HelloController.class);
    @Resource
    private CityMapper cityMapper;
    @RequestMapping("/test")
    String test1(){
        return "hello,test1()";
    }
    @RequestMapping("/findCity2")
    City findCity2(){
        //log.info("太牛了");
         Map map=new HashMap();
         map.put("id", 2);
        return cityMapper.findCityById(map);
    }
    @RequestMapping("/addCity")
    void addCity(String id ,String name,String province){
        System.out.println("id:"+id+","+"name:"+name+","+"province:"+province);

        City city =new City();
        city.setId(id);
        city.setName(name);
        city.setProvince(province);
        cityMapper.add(city);
    }
    @RequestMapping("/findState")
    List<Map<String,Object>> find(){
        return cityMapper.findState();
    }

}

//UserController

package cn.controller;
import javax.annotation.Resource;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import cn.entity.User;
import cn.mapper2.UserMapper;
@RestController
@RequestMapping("/demoUser")
public class UserController {
    static Logger log = LogManager.getLogger(UserController.class);
    @Resource
    private UserMapper userMapper;
    @RequestMapping("/test")
    String test1(){
        return "hello,test1()";
    }
    @RequestMapping("/findUser")
    User findUser(@RequestParam String name){
        //log.info("");
        return userMapper.findCityByName(name);
    }
}

4、entity包,因为案例的数据比较简单，大家可以通过这两个实体类，创建对应的数据库变这里就不添加了。
//City

package cn.entity;
import java.io.Serializable;
public class City implements Serializable{
    private static final long serialVersionUID = 1L;
    private String id;
    private String name;
    private String province;
    public City(){
    }
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getProvince() {
        return province;
    }
    public void setProvince(String province) {
        this.province = province;
    }
    @Override
    public String toString() {
        return "City [id=" + id + ", name=" + name + ", province=" + province + "]";
    }
}

//User

package cn.entity;
public class User {

    private String name;
    private Integer age;
    public User() {

    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    @Override
    public String toString() {
        return "User [name=" + name + ", age=" + age + "]";
    }
}

5、mapper1.

package cn.mapper1;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
import cn.entity.City;
public interface CityMapper {

    City findCityById(Map map);
    void add(City city);
    List<Map<String,Object>> findState();
}

//mapper2

package cn.mapper2;
import org.apache.ibatis.annotations.Param;
import cn.entity.User;
public interface UserMapper {

    User findCityByName(@Param("name") String name);
    void add(User user);

}

6、application

package cn;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.servlet.ServletComponentScan;

@SpringBootApplication
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

//DruidDataSource1Configuration对于mytest指定CityMapper.xml映射文件

package cn;

import javax.sql.DataSource;

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.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 com.alibaba.druid.pool.DruidDataSource;

@Configuration
@MapperScan(basePackages = { "cn.Mapper1" }, sqlSessionTemplateRef = "mytestSqlSessionTemplate")
public class DruidDataSource1Configuration {
    @Bean(name = "mytestDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.mytest")
    public DataSource druidDataSource() {
        DataSource druidDataSource = new DruidDataSource();
        return druidDataSource;
    }

    @Bean(name = "mytestSqlSessionFactory")
    @Primary
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("mytestDataSource") DataSource dataSource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("file:mybatis/mapper/CityMapper.xml"));
        return bean.getObject();
    }

    @Bean(name = "mytestTransactionManager")
    @Primary
    public DataSourceTransactionManager testTransactionManager(@Qualifier("mytestDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

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

}

//DruidDataSource2Configuration对于mytest1库指定UserMapper.xml映射文件

package cn;

import javax.sql.DataSource;

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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import com.alibaba.druid.pool.DruidDataSource;

@Configuration
@MapperScan(basePackages={"cn.Mapper2"},sqlSessionTemplateRef = "mytest1SqlSessionTemplate")
public class DruidDataSource2Configuration {
    @Bean("mytest1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.mytest1")
    public DataSource druidDataSource(){
        DataSource druidDataSource = new DruidDataSource();
        return druidDataSource;
    }
     @Bean(name = "mytest1SqlSessionFactory")

        public SqlSessionFactory testSqlSessionFactory(@Qualifier("mytest1DataSource") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("file:mybatis/mapper/UserMapper.xml"));
            return bean.getObject();
        }
      @Bean(name = "mytest1TransactionManager")

        public DataSourceTransactionManager testTransactionManager(@Qualifier("mytest1DataSource") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }

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

}

//DruidStatFilter和DruidStatViewServlet只是用于SQL语句的监测在这里链接多源数据库可略。

package cn;

import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;

import com.alibaba.druid.support.http.WebStatFilter;

@WebFilter(filterName="druidWebStatFilter",urlPatterns="/*",
initParams={
         @WebInitParam(name="exclusions",value="\*.js,\*.gif,\*.jpg,\*.bmp,\*.png,\*.css,\*.ico,/druid/*")// 忽略资源
 }
)
public class DruidStatFilter extends WebStatFilter{

}

//DruidStatViewServlet

package cn;
import com.alibaba.druid.support.http.StatViewServlet;
import javax.servlet.annotation.WebServlet;
import javax.servlet.annotation.WebInitParam;
@WebServlet(urlPatterns = "/druid/*", 
    initParams={
            @WebInitParam(name="allow",value="192.168.16.110,127.0.0.1"),// IP白名单 (没有配置或者为空，则允许所有访问)
            @WebInitParam(name="deny",value="192.168.16.111"),// IP黑名单 (存在共同时，deny优先于allow)
            @WebInitParam(name="loginUsername",value="admin"),// 用户名
            @WebInitParam(name="loginPassword",value="123"),// 密码
            @WebInitParam(name="resetEnable",value="false")// 禁用HTML页面上的“Reset All”功能
    })
public class DruidStatViewServlet extends StatViewServlet {
    private static final long serialVersionUID = 1L;

}

//mybatise/mapper映射文件

//CityMapper.xml

<?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="cn.mapper1.CityMapper" >

    <select id="findCityById" parameterType="map" resultType="cn.entity.City">
        select * from city where id=#{id}
    </select>
    <select id="findState" resultType="map">
        select * from atalarm_send_view
    </select>
    <insert id="add" parameterType="cn.entity.City" >
    insert into city (id,name,province) 
    values(#{id},#{name},#{province})

    </insert>

</mapper>

//UserMapper.xml

<?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="cn.mapper2.UserMapper" >

    <select id="findCityByName" parameterType="String"  resultType="cn.entity.User">
        select * from user where name = #{name}
    </select>
    <insert id="add" parameterType="cn.entity.User" >
    insert into User (name,age) 
    values(#{name},#{age})
    </insert>

</mapper>

7、案例访问样例：
访问mytest中的数据。
这里写图片描述

访问mytest1中的数据。
这里写图片描述

我滴神呀

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
springboot +mybatise +druid静态访问多个数据库

1、application.properties配置文件：#数据库设置spring.datasource.mytest.type=com.alibaba.druid.pool.DruidDataSourcespring.datasource.mytest.driverClassName=com.mysql.jdbc.Driverspring.datasource.mytest.url=jdbc
复制链接

扫一扫