springboot mybatis多数据源 hive gp hbase

尝＆试

已于 2023-01-04 15:03:20 修改

阅读量767

点赞数

分类专栏： JavaWeb 文章标签： java hive

于 2023-01-04 15:01:48 首次发布

人生的道路虽然漫长，但紧要处常常只有几步

本文链接：https://blog.csdn.net/qq_30512275/article/details/128548238

版权

JavaWeb 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

config

package com.cwl.datacomparison.config;


import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
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;


/**
 * @author ljq
 * @date 2019-10-19 14:17
 * @describe
 */
@Configuration
@MapperScan(basePackages = "com.cwl.datacomparison.dao.hive", sqlSessionTemplateRef  = "hiveSessionTemplate")
public class DataSourceHiveConfig {
    private final static Logger logger = LoggerFactory.getLogger(DataSourceHiveConfig.class);

    @Bean(name = "hiveDataSource")
    @ConfigurationProperties(prefix = "hived.spring.datasource.hive")
    public DataSource testDataSource() {

        return DataSourceBuilder.create().build();
    }

    @Bean(name = "hiveSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("hiveDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/hive/*.xml"));
        logger.info("加载hive数据库连接......");
        return bean.getObject();
    }

    @Bean(name = "hiveTransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("hiveDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "hiveSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("hiveSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

package com.cwl.datacomparison.config;


import com.github.pagehelper.PageInterceptor;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
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.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

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


/**
 * @author ljq
 * @date 2019-10-19 11:59
 * @describe
 */
@Configuration
@MapperScan(basePackages = "com.cwl.datacomparison.dao.gp", sqlSessionTemplateRef = "gpSqlSessionTemplate")
public class DataSourcePostgresConfig {
    private final static Logger logger = LoggerFactory.getLogger(DataSourcePostgresConfig.class);

    @Bean(name = "gpDataSource")
    @ConfigurationProperties(prefix = "postgresd.spring.datasource.gp")
    @Primary
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 此处必须在主数据库的数据源配置上加上@Primary
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean(name = "gpSessionTemplate")
    @Primary
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("gpDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/gp/*.xml"));
        //分页插件
        bean.setPlugins(new Interceptor[]{interceptor()});

        logger.info("加载postgresql数据库连接......");
        return bean.getObject();
    }

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

    @Bean(name = "gpSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("gpSessionTemplate") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean
    public Interceptor interceptor(){
        Interceptor interceptor = new PageInterceptor();
        Properties properties = new Properties();
        properties.setProperty("offsetAsPageNum", "true");
        properties.setProperty("rowBoundsWithCount", "true");
        properties.setProperty("reasonable", "true");
        properties.setProperty("supportMethodsArguments", "true");
        properties.setProperty("returnPageInfo", "check");
        properties.setProperty("params", "count=countSql,pageNum=pageNumKey;pageSize=pageSizeKey");
        interceptor.setProperties(properties);
        return interceptor;
    }

}

package com.cwl.datacomparison.config;


import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
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;


/**
 * @author ljq
 * @date 2019-10-19 14:17
 * @describe
 */
@Configuration
@MapperScan(basePackages = "com.cwl.datacomparison.dao.phoenix", sqlSessionTemplateRef  = "phoenixSessionTemplate")
public class DataSourcePhoenixConfig {
    private final static Logger logger = LoggerFactory.getLogger(DataSourcePhoenixConfig.class);

    @Bean(name = "phoenixDataSource")
    @ConfigurationProperties(prefix = "phoenixd.spring.datasource.phoenix")
    public DataSource testDataSource() {

        return DataSourceBuilder.create().build();
    }

    @Bean(name = "phoenixSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("phoenixDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/phoenix/*.xml"));
        logger.info("加载phoenix数据库连接......");
        return bean.getObject();
    }

    @Bean(name = "phoenixTransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("phoenixDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "phoenixSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("phoenixSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

dao

package com.cwl.datacomparison.dao.hive;

import com.cwl.datacomparison.entity.hive.Lottery;
import com.cwl.datacomparison.vo.sale.audit.DetailParam;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author ljq
 * @date 2020-09-17 15:04
 * @describe
 */
public interface HiveMapper {

    /**
     * 根据选项从hive中获取详情信息
     * @param detailParam 选择类
     * @param provinceId 省份编码
     * @return com.cwl.datacomparison.entity.hive.Lottery
     */
    List<Lottery> getLotteries(@Param(value = "detailParam") DetailParam detailParam,
                               @Param(value = "provinceId") String provinceId);
}



<?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="com.cwl.datacomparison.dao.hive.HiveMapper">
        <select id="getLotteries" resultType="com.cwl.datacomparison.entity.hive.Lottery">
            select
            <if test="detailParam.ticketNumber == true">
                error_lottery_audittime.ticketid as ticketId,
            </if>
            <if test="detailParam.saleDrawNumber == true">
                error_lottery_audittime.sale_saledrawnumber as saleSaleDrawNumber,
            </if>
            <if test="detailParam.serialNumber == true">
                error_lottery_audittime.sale_serialnumber as saleSerialNumber,
            </if>
            <if test="detailParam.saleTime == true">
                error_lottery_audittime.sale_saletime as saleSaleTime,
            </if>
            <if test="detailParam.saleAmount == true">
                error_lottery_audittime.sale_bettotal as saleBetTotal,
            </if>
            <if test="detailParam.codeAnnotation == true">
                error_lottery_audittime.sale_selectiondetail as saleSelectionDetail,
            </if>
            case
            when error_lottery_audittime.revoke_serialnumber is null then '否'
            else '是' end as revokeSerialNumber,
            case
            when error_lottery_audittime.restore_serialnumber is null then '否'
            else '是' end as restoreSerialNumber
            from error_lottery_audittime
            where error_lottery_audittime.province_id = #{provinceId}
            and error_lottery_audittime.game_id = #{detailParam.gameId}
            <if test="detailParam.gameId != '23004' and detailParam.gameId !='23005'">
                and error_lottery_audittime.draw_number = #{detailParam.drawNumber}
            </if>
            <if test="detailParam.gameId == '23004' or detailParam.gameId =='23005'">
                and error_lottery_audittime.sale_saledrawnumber = #{detailParam.drawNumber}
            </if>
        </select>



</mapper>

package com.cwl.datacomparison.dao.gp;

import com.cwl.datacomparison.entity.Account;

/**
 * @author ljq
 * @date 2020-01-11 11:49
 * @describe
 */
public interface AccountMapper {
    Account getAccountByPassword(Account account);

    boolean updatePassword(Account account);

}

<?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="com.cwl.datacomparison.dao.gp.AccountMapper">
    <select id="getAccountByPassword" resultType="com.cwl.datacomparison.entity.Account">
        select ua.id            as id,
               ua.username      as username,
               ua.password      as password,
               ua.province_id   as provinceId,
               ua.province_name as provinceName,
               ua.role          as role
        from ud_account ua
        where ua.username = #{username}
          and ua.password = #{password}
    </select>

    <update id="updatePassword">
        update ud_account
        set password = #{password}
        where id = #{id}
    </update>

</mapper>

package com.cwl.datacomparison.dao.phoenix;

import com.cwl.datacomparison.entity.SaleAudit;
import org.apache.ibatis.annotations.Param;

/**
 * @author ljq
 * @date 2020-09-17 16:39
 * @describe
 */
public interface HBaseMapper {

    /**
     * 从hbase中获取期号及其销量
     *
     * @param drawNumber 期号
     * @param length     期号长度
     * @return com.cwl.datacomparison.entity.SaleAudit
     */
    SaleAudit getSaleAudit(@Param(value = "drawNumber") String drawNumber,
                           @Param(value = "length") int length);
}

<?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="com.cwl.datacomparison.dao.phoenix.HBaseMapper">

    <select id="getSaleAudit" resultType="com.cwl.datacomparison.entity.SaleAudit">
        select sum(to_number("betTotal")) as "actulSaleTotal", #{drawNumber} as "key"
        from "lottery_sales_audit"
        where substr("ROW", 0, #{length}) = #{drawNumber}
          and "status" = '1'
          and ("rvSerialNumber" is null or
               ("rvSerialNumber" is not null and "rsSerialNumber" is not null and "revokeTime" &lt; "restoreTime"))
    </select>

</mapper>

调用

@Service
public class HBaseServiceImpl implements HBaseService {

    @Resource
    private HBaseMapper hBaseMapper;
    @Resource
    private DataComparisonMapper dataComparisonMapper;

    @Override
    public boolean saleAudit(BaseParam baseParam) {
        if (isJkp(baseParam.getGameId())) {
            List<String> drawNumbers = dataComparisonMapper.getDrawNumberJkp(baseParam);

            for (String drawNumber : drawNumbers) {
                SaleAudit saleAudit = hBaseMapper.getSaleAudit(drawNumber, drawNumber.length());
                dataComparisonMapper.updateAuditJkp(saleAudit);
            }
            return true;
        }

        //gp查期号
        //loop 循环查结果，并插入gp
        baseParam.ifDrawNumber();
        List<String> drawNumbers = dataComparisonMapper.getDrawNumber(baseParam);

        for (String drawNumber : drawNumbers) {
            SaleAudit saleAudit = hBaseMapper.getSaleAudit(drawNumber, drawNumber.length());
            dataComparisonMapper.updateAudit(saleAudit);
        }
        return true;
    }
}

@Service
public class HiveServiceImpl implements HiveService {

    @Resource
    private HiveMapper hiveMapper;

    @Override
    public List<Lottery> listDetail(DetailParam detailParam, String provinceId) {
        return hiveMapper.getLotteries(detailParam, provinceId);
    }
}