论Sqlite、SpringBoot、oracle、redis集成所需要的干货

论Sqlite、SpringBoot、oracle、redis集成所需要的干货

话不多多说！直接上菜

创建SpringBoot项目 这一块我们不说了 直接略过 大家基本上都会！！！

(1).pom文件依赖

   <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.51</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.3.5</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.0</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.7.2</version>
        </dependency>
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.3</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>com.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.6</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/src/main/resources/lib/lombok-1.18.6.jar</systemPath>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/src/main/resources/lib/ojdbc6-11.2.0.3.jar</systemPath>
        </dependency>
        
       <!-- <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>-->
    </dependencies>

    <build>
        <plugins>

            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>

            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.7</version>
                <configuration>
                    <configurationFile>${basedir}/src/main/resources/generator/generatorConfig.xml</configurationFile>
                    <overwrite>true</overwrite>
                    <verbose>true</verbose>
                </configuration>
            </plugin>

        </plugins>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.yml</include>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>

(2).配置文件集成

给大家说一下，oracle和sqlite集成是需要切换数据源的，所以配置这一块，我把oracle配置成主数据源了，稍后在下面会看到切换数据源的代码！！！

server:
  port: 8080
  servlet:
    context-path: /demo

spring:
  datasource:
    # master config 主数据源 oracle 
    master:
      url: jdbc:oracle:thin:@172.0.0.1:1521:orcl
      username: 123456
      password: 123456
      driver-class-name: oracle.jdbc.OracleDriver
    # slave config  次数据源 sqlite 
    slave:
      url: jdbc:sqlite::resource:DB/data.db
      username:
      password:
      driver-class-name: org.sqlite.JDBC

  redis:
    password: 123456
    cluster:
      nodes:
        - 172.0.0.1:7001
        - 172.0.0.1:7002
        - 172.0.0.1:7003
        - 172.0.0.1:7004
        - 172.0.0.1:7005
        - 172.0.0.1:7006

mybatis:
  mapper-locations: classpath*:/mapper/*.xml
  type-aliases-package: com.yky.sqlitedemo.model

我讲一下上面的配置的大致意思
1.master （自定义）代表oracle 主数据源，下面就是账号密码等
slave （自定义）代表sqlite 次数据源，这块我没有设置账号密码
这一块大家可以按照自己的想法定义数据源名称来区分数据源。
jdbc:sqlite::resource:DB/data.db 代表引用resource下DB文件下的data.db文件
2.接下来就是配置redis，这块我配置的是redis集群，有什么不懂的，百度一下！
3.配置mybatis
mapper-locations这个是配置mapper.xml地址，classpath代表mapper文件在resources文件下； type-aliases-package是指实体类地址

(3).检测数据库是否连接正常配置文件application.yml_back

server:
  port: 8080
  servlet:
    context-path: /demo

spring:
  datasource:
    # oracle config
    url: jdbc:oracle:thin:@172.0.0.1:1521:orcl
    username: 123456
    password: 123456
    driver-class-name: oracle.jdbc.OracleDriver
    # use druid config
    # type: com.alibaba.druid.pool.DruidDataSource
    # 初始化连接数
    initialSize: 5
    # 最小空闲连接数
    minIdle: 5
    # 最大空闲连接数
    maxActive: 20
    # 获取连接等待超时的时间配置
    maxWait: 60000
    # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    timeBetweenEvictionRunsMillis: 60000
    # 配置一个连接在池中最小的空闲时间，单位是毫秒
    minEvictableIdleTimeMillis: 60000
    # 连接验证查询语句
    validationQuery: SELECT * from dual
    # 指明连接是否被空闲连接回收器（如果有）进行检验，如果检测失败，则连接将被从池中去除
    testWhileIdle: false
    # 借出连接时用validationQuery配置的语句进行测试是否可用
    testOnBorrow: false
    # 返回连接时用validationQuery配置的语句进行测试是否可用
    testOnReturn: false
    # 是否缓存preparedStatement，即PSCache
    poolPreparedStatements: true
    # 指定每个连接缓存preparedStatement的最大个数
    maxPoolPreparedStatementPerConnectionSize: 20
    # 配置监控统计的过滤器，防SQL注入过滤器，日志过滤器
    filters: stat,wall,slf4j
    # 合并多个DruidDataSource的监控数据
    useGlobalDataSourceStat: true

# mybatis config
mybatis:
  mapper-locations: classpath*:/mapper/*Mapper.xml

这里有一点需要记住如果，连接验证查询语句SELECT * from dual 对sqlite进行验证的时候会报错，所以需要对操作的sqlite加一个dual表。

(4).数据源切换

配置完成之后，我们开始数据源切换。

<1>.定义数据源枚举

package com.yky.sqlitedemo.data.source.enums;

/**
 * 数据源枚举
 */
public enum DataSourceEnum {

	/**
	 * 主库源 代表oracle
	 */
	DATA_SOURCE_MASTER,

	/**
	 * 从库源 代表sqlite
	 */
	DATA_SOURCE_SLAVE
	
}

<2>.定义数据源注解

package com.yky.sqlitedemo.data.source.annotation;

import com.yky.sqlitedemo.data.source.enums.DataSourceEnum;

import java.lang.annotation.*;

/**
 * 自定义数据源注解
 */
@Documented
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSourceAnnotation {
	
	DataSourceEnum value() default DataSourceEnum.DATA_SOURCE_MASTER;
	
}

我们设置的默认数据源是主数据源 default DataSourceEnum.DATA_SOURCE_MASTER

<3>. 主库从库配置

我这点创建两个model来承载配置

package com.yky.sqlitedemo.data.source.config;

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

/**
 * 主库配置
 */
@Configuration
//这个配置文件切记是配置文件的主库的名字，如果主库名字更换这点也需要更换
@ConfigurationProperties(prefix = "spring.datasource.master")
public class MasterConfig {
	
	private String url;
	
	private String username;
	
	private String password;
	
	private String driverClassName;

	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getDriverClassName() {
		return driverClassName;
	}

	public void setDriverClassName(String driverClassName) {
		this.driverClassName = driverClassName;
	}

}

package com.yky.sqlitedemo.data.source.config;

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

/**
 * 从库配置
 */
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.slave")
public class SlaveConfig {
	
	private String url;
	
	private String username;
	
	private String password;
	
	private String driverClassName;

	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getDriverClassName() {
		return driverClassName;
	}

	public void setDriverClassName(String driverClassName) {
		this.driverClassName = driverClassName;
	}

}

<4>.数据源切换处理

package com.yky.sqlitedemo.data.source;


import com.yky.sqlitedemo.data.source.enums.DataSourceEnum;

/**
 * 数据源切换处理
 */
public class DynamicDataSourceContextHolder {

	// 定义一个ThreadLocal变量，保存数据源类型（保证线程安全，多个线程之间互不影响）
	private static final ThreadLocal<DataSourceEnum> DATA_SOURCE_CONTEXT_HOLDER = new ThreadLocal<>();

	public static final DataSourceEnum DEFAULT_DATA_SOURCE = DataSourceEnum.DATA_SOURCE_MASTER;
	
	static {
		setDefaultDataSource(); // 默认指定主库
	}

	/**
	 * 设置默认数据源变量
	 */
	public static void setDefaultDataSource() {
		DATA_SOURCE_CONTEXT_HOLDER.set(DEFAULT_DATA_SOURCE);
	}

	/**
	 * 设置数据源变量
	 * @param dataSourceEnum
	 */
	public static void setDataSource(DataSourceEnum dataSourceEnum) {
		DATA_SOURCE_CONTEXT_HOLDER.set(dataSourceEnum);
	}

	/**
	 * 获取数据源变量
	 * @return
	 */
	public static DataSourceEnum getDataSource() {
		return DATA_SOURCE_CONTEXT_HOLDER.get();
	}

}

<5>.自定义动态数据源类

package com.yky.sqlitedemo.data.source;

import com.yky.sqlitedemo.data.source.enums.DataSourceEnum;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * 自定义动态数据源类
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

	private final Logger logger = LoggerFactory.getLogger(DynamicDataSource.class);

	@Override
	protected Object determineCurrentLookupKey() {
		DataSourceEnum dataSourceEnum = DynamicDataSourceContextHolder.getDataSource();
		logger.info("当前使用数据源为：{}", dataSourceEnum);
		return dataSourceEnum;
	}

}

<6>.定义切面

package com.yky.sqlitedemo.data.source;


import com.yky.sqlitedemo.data.source.annotation.DataSourceAnnotation;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;

/**
 * 数据源注解切面实现
 */
@Aspect
@Component
public class DynamicDataSourceAspect {

	@Before("@annotation(dataSourceAnnotation)")
	public void before(JoinPoint point, DataSourceAnnotation dataSourceAnnotation) {
		Class<?> clazz = point.getTarget().getClass();
		MethodSignature signature = (MethodSignature) point.getSignature();
		try {
			Method method = clazz.getMethod(signature.getName(), signature.getParameterTypes());
			if (method.isAnnotationPresent(DataSourceAnnotation.class)) {
				// 根据注解设置数据源
				DataSourceAnnotation annotation = method.getAnnotation(DataSourceAnnotation.class);
				DynamicDataSourceContextHolder.setDataSource(annotation.value());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@After("@annotation(dataSourceAnnotation)")
	public void after(JoinPoint point, DataSourceAnnotation dataSourceAnnotation) {
		DynamicDataSourceContextHolder.setDefaultDataSource();
	}

}

<7>.数据源相关配置

package com.yky.sqlitedemo.data.source.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.yky.sqlitedemo.data.source.DynamicDataSource;
import com.yky.sqlitedemo.data.source.enums.DataSourceEnum;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
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.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * 数据源相关配置
 */
@Configuration
//扫描dao文件
@MapperScan(basePackages = "com.yky.sqlitedemo.dao")
public class DataSourceConfig {

	@Bean(name = "dataSourceMaster")
	@Primary // 当有多个实现类型时，优先使用
	public DataSource dataSourceMaster(MasterConfig masterConfig) throws Exception {
		DruidDataSource druidDataSourceMaster = new DruidDataSource();
		druidDataSourceMaster.setUrl(masterConfig.getUrl());
		druidDataSourceMaster.setUsername(masterConfig.getUsername());
		druidDataSourceMaster.setPassword(masterConfig.getPassword());
		druidDataSourceMaster.setDriverClassName(masterConfig.getDriverClassName());
		return druidDataSourceMaster;
	}

	@Bean(name = "dataSourceSlave")
	public DataSource dataSourceSlave(SlaveConfig slaveConfig) throws Exception {
		DruidDataSource druidDataSourceSalve = new DruidDataSource();
		druidDataSourceSalve.setUrl(slaveConfig.getUrl());
		druidDataSourceSalve.setUsername(slaveConfig.getUsername());
		druidDataSourceSalve.setPassword(slaveConfig.getPassword());
		druidDataSourceSalve.setDriverClassName(slaveConfig.getDriverClassName());
		return druidDataSourceSalve;
	}

	/**
	 * 动态数据源，配置需要使用到的多个数据源
	 * @param master
	 * @param slave
	 * @return
	 */
	@Bean
	public DynamicDataSource dynamicDataSource(@Qualifier("dataSourceMaster") DataSource master, @Qualifier("dataSourceSlave") DataSource slave) {
		Map<Object, Object> targetDataSources = new HashMap<>();
		targetDataSources.put(DataSourceEnum.DATA_SOURCE_MASTER, master);
		targetDataSources.put(DataSourceEnum.DATA_SOURCE_SLAVE, slave);
		DynamicDataSource dynamicDataSource = new DynamicDataSource();
		dynamicDataSource.setDefaultTargetDataSource(master);
		dynamicDataSource.setTargetDataSources(targetDataSources);
		return dynamicDataSource;
	}

	@Bean
	public PlatformTransactionManager transactionManager(DynamicDataSource dynamicDataSource) {
		return new DataSourceTransactionManager(dynamicDataSource);
	}

	@Bean
	public SqlSessionFactory sqlSessionFactory(DynamicDataSource dynamicDataSource) throws Exception {
		SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
		sessionFactory.setDataSource(dynamicDataSource);
		//设置Mapper文件地址
		sessionFactory.setMapperLocations(((ResourcePatternResolver) new PathMatchingResourcePatternResolver())
				.getResources("classpath*:/mapper/*.xml"));
		return sessionFactory.getObject();
	}
}

(5).使用

@DataSourceAnnotation(DataSourceEnum.DATA_SOURCE_MASTER)// 指定主库
@DataSourceAnnotation(DataSourceEnum.DATA_SOURCE_SLAVE) // 指定从库

@Service
public class VdServiceImpl implements VdService {
    @Autowired
    private VdDao vdDao;
    @Autowired
    private VdSqliteDao vdSqliteDao;

    @Override
    @DataSourceAnnotation(DataSourceEnum.DATA_SOURCE_MASTER) // 指定主库
    public List<VDModel> listVDs(String vdId) {
        return vdDao.listVDs(vdId);
    }

    @Override
    @DataSourceAnnotation(DataSourceEnum.DATA_SOURCE_SLAVE) // 指定从库
    public void saveVD(VDModel vdModel) {
        /*if(vdModel.getVdId() == null){
           vdModel.createId();
        }*/
         this.vdSqliteDao.saveVD(vdModel);
    }
}

(6).测试

//排除自动注入数据源的配置（取消数据库配置）
@SpringBootApplication(exclude = { DataSourceAutoConfiguration.class })
public class SqlitedemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(SqlitedemoApplication.class, args);
    }
    @Autowired
    private VdService vdService;

    @PostConstruct
    public void initTest() {
    	List<VDModel> vdModels = vdService.listVDs("YKY.VD");
        vdModels.forEach(System.out::println);*/
        List<VDModel> vdModels1 = vdService.listVD(null,null);
       vdModels1.forEach(System.out::println);
    }
}    

这点大家可能会问sqlite 怎么和mybatis集成
这点大家不需要有太多的不理解，mybatis已经集成了sqlite 直接按照使用oracle操作数据库的方法操作sqlite就可以.直接启动就可以了

(7).redis

其实对rides进行操作很简单，我们直接来看代码

public class RedisTest{
   @Autowired
   private StringRedisTemplate redisClient;
   public void Tasks(){
   		//往redis里面放数据
   		//A 代表 缓存名称
   		//B 代表key
   		//C 代表value	
   		redisClient.opsForHash().put(A,B,C);
   		//这点我们是使用的StringRedisTemplate 所以key,value都是字符串,如果想放一个对象进去，需要对对象json转换
   		DictModel dictModel =  new DictModel();
	    dictModel.setUpdateTime(v.getUpdateDate());
        dictModel.setId(v.getId());
        dictModel.setType(v.getType());
        dictModel.setCode(v.getVdId());
        dictModel.setName(v.getName()); 
       redisClient.opsForHash().put("VDCache",dictModel.getId(),JSONArray.toJSON(dictModel).toString());
   		//获取redis中的String数据
   		String vdCacheString = (String)redisClient.opsForHash().get("VDCache", v.getId());
   		//获取redis中的对象数据
   		String vdCacheModel = (String)redisClient.opsForHash().get("VDCache", v.getId());
   		DictModel vdCache = JSONArray.parseObject(vdCacheModel, DictModel.class);
   }
}

本次分享就到此结束了！！！
有什么问题欢迎交流，上面是本人微信，欢迎随时交流！！！