数据库分片未开发完成

吴学文

2019-05-16 9592a96bc773354e93b048df7183cc6accfe5229

数据库分片未开发完成

 CommunityService/src/main/java/com/java110/community/listener/SaveCommunityInfoListener.java

@@ -89,24 +89,47 @@

        //小区信息
        Map businessCommunityInfo = communityServiceDaoImpl.getBusinessCommunityInfo(info);

        if (businessCommunityInfo != null && !businessCommunityInfo.isEmpty()) {
            communityServiceDaoImpl.saveCommunityInfoInstance(info);
            reFresh(info, businessCommunityInfo);
            communityServiceDaoImpl.saveCommunityInfoInstance(businessCommunityInfo);
            dataFlowContext.addParamOut("communityId", businessCommunityInfo.get("community_id"));
        }
        //小区属性
        List<Map> businessCommunityAttrs = communityServiceDaoImpl.getBusinessCommunityAttrs(info);
        if (businessCommunityAttrs != null && businessCommunityAttrs.size() > 0) {
            reFresh(info, businessCommunityAttrs.get(0));
            communityServiceDaoImpl.saveCommunityAttrsInstance(info);
        }
        //小区照片
        List<Map> businessCommunityPhotos = communityServiceDaoImpl.getBusinessCommunityPhoto(info);
        if (businessCommunityPhotos != null && businessCommunityPhotos.size() > 0) {
            reFresh(info, businessCommunityPhotos.get(0));
            communityServiceDaoImpl.saveCommunityPhotoInstance(info);
        }

    }

    /**
     * 刷 communityId
     *
     * @param info         查询对象
     * @param businessInfo 小区ID
     */
    private void reFresh(Map info, Map businessInfo) {

        if (info.containsKey("communityId")) {
            return;
        }

        if (!businessInfo.containsKey("community_id")) {
            return;
        }

        info.put("communityId", businessInfo.get("community_id"));
    }

    /**
     * 撤单
     *
     * @param dataFlowContext 数据对象

 CommunityService/src/main/resources/application-dev.yml

@@ -23,6 +23,8 @@
    uri-encoding: UTF-8

spring:
  profiles:
    active: share
  http:
    encoding:
      charset: UTF-8

 CommunityService/src/main/resources/application-share.yml

New file
@@ -0,0 +1,61 @@
spring:
  shardingsphere:
    datasource:
#      names: ds_master_0,ds_master_1,ds_master_0_slave_0,ds_master_0_slave_1,ds_master_1_slave_0,ds_master_1_slave_1
      names: ds_master_0,ds_master_1
#      ds_master_0_slave_0:
#        jdbc-url: jdbc:mysql://localhost:3306/demo_ds_master_0_slave_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
#        password:
#        type: com.zaxxer.hikari.HikariDataSource
#        driver-class-name: com.mysql.jdbc.Driver
#        username: root
      ds_master_0:
        driver-class-name: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        jdbc-url: jdbc:mysql://dev.db.java110.com:3306/hc_community?useUnicode=true&characterEncoding=utf-8
        username: hc_community
        password: hc_community@12345678
        minIdle: 5
        validationQuery: SELECT 1 FROM DUAL
        initialSize: 5
        maxWait: 60000
        filters: stat,wall,log4j
        poolPreparedStatements: true
      ds_master_1:
        driver-class-name: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        jdbc-url: jdbc:mysql://dev.db.java110.com:3306/TT?useUnicode=true&characterEncoding=utf-8
        username: TT
        password: TT@12345678
        minIdle: 5
        validationQuery: SELECT 1 FROM DUAL
        initialSize: 5
        maxWait: 60000
        filters: stat,wall,log4j
        poolPreparedStatements: true
    sharding:
      tables:
        business_community:
          actual-data-nodes: ds_master_$->{0..1}.business_community
          database-strategy:
            inline:
              sharding-column: community_id
              algorithm-expression: ds_master_$->{Long.parseLong(community_id) % 2}
        community:
          actual-data-nodes: ds_master_$->{0..1}.community
          database-strategy:
            inline:
              sharding-column: community_id
              algorithm-expression: ds_master_$->{Long.parseLong(community_id) % 2}
      default-database-strategy:
        none:
      default-table-strategy:
        none:
      default-datasource-name: ds_master_0
#      master-slave-rules:
#        ds_1:
#          slave-data-source-names: ds_master_1_slave_0, ds_master_1_slave_1
#          master-data-source-name: ds_master_1
#        ds_0:
#          slave-data-source-names: ds_master_0_slave_0, ds_master_0_slave_1
#          master-data-source-name: ds_master_0

 CommunityService/src/main/resources/application.yml

@@ -1,3 +1,63 @@
spring:
  shardingsphere:
    datasource:
      #      names: ds_master_0,ds_master_1,ds_master_0_slave_0,ds_master_0_slave_1,ds_master_1_slave_0,ds_master_1_slave_1
      names: ds_master_0,ds_master_1
      #      ds_master_0_slave_0:
      #        jdbc-url: jdbc:mysql://localhost:3306/demo_ds_master_0_slave_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
      #        password:
      #        type: com.zaxxer.hikari.HikariDataSource
      #        driver-class-name: com.mysql.jdbc.Driver
      #        username: root
      ds_master_0:
        driver-class-name: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        jdbc-url: jdbc:mysql://dev.db.java110.com:3306/hc_community?useUnicode=true&characterEncoding=utf-8
        username: hc_community
        password: hc_community@12345678
        minIdle: 5
        validationQuery: SELECT 1 FROM DUAL
        initialSize: 5
        maxWait: 60000
        filters: stat,wall,log4j
        poolPreparedStatements: true
      ds_master_1:
        driver-class-name: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        jdbc-url: jdbc:mysql://dev.db.java110.com:3306/TT?useUnicode=true&characterEncoding=utf-8
        username: TT
        password: TT@12345678
        minIdle: 5
        validationQuery: SELECT 1 FROM DUAL
        initialSize: 5
        maxWait: 60000
        filters: stat,wall,log4j
        poolPreparedStatements: true
    sharding:
      tables:
        business_community:
          actual-data-nodes: ds_master_$->{0..1}.business_community
          database-strategy:
            inline:
              sharding-column: community_id
              algorithm-expression: ds_master_$->{Long.parseLong(community_id) % 2}
        community:
          actual-data-nodes: ds_master_$->{0..1}.community
          database-strategy:
            inline:
              sharding-column: community_id
              algorithm-expression: ds_master_$->{Long.parseLong(community_id) % 2}
      default-database-strategy:
        none:
      default-table-strategy:
        none:
      default-datasource-name: ds_master_0
#      master-slave-rules:
#        ds_1:
#          slave-data-source-names: ds_master_1_slave_0, ds_master_1_slave_1
#          master-data-source-name: ds_master_1
#        ds_0:
#          slave-data-source-names: ds_master_0_slave_0, ds_master_0_slave_1
#          master-data-source-name: ds_master_0
  profiles:
    active: dev

 CommunityService/src/main/resources/dataSource.yml

@@ -49,13 +49,13 @@
      databaseStrategy:
        inline:
          shardingColumn: community_id
          algorithmExpression: ds${Integer.parseInt(community_id) % 2}
          algorithmExpression: ds${Long.parseLong(community_id) % 2}
    community:
      actualDataNodes: ds${0..1}.community
      databaseStrategy:
        inline:
          shardingColumn: community_id
          algorithmExpression: ds${Integer.parseInt(community_id) % 2}
          algorithmExpression: ds${Long.parseLong(community_id) % 2}
  bindingTables:
    - business_community,community


 java110-config/src/main/resources/logback.xml

@@ -79,6 +79,10 @@
        <appender-ref ref="STDOUT_FILE" />
    </logger>

    <logger name="io.shardingsphere" level="DEBUG">
        <appender-ref ref="STDOUT_FILE" />
    </logger>

    <!--<logger name="org.thymeleaf" level="DEBUG">
        <appender-ref ref="STDOUT_FILE" />
    </logger>-->

 java110-db/pom.xml

@@ -47,9 +47,15 @@
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc</artifactId>
        </dependency>-->
        <dependency>
        <!--<dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
        </dependency>-->

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
        </dependency>

    </dependencies>
</project>

 java110-db/src/main/java/com/java110/db/DataSourceConfig.java

@@ -1,24 +1,8 @@
package com.java110.db;

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.pool.DruidDataSource;
import com.google.common.collect.Lists;
import io.shardingsphere.core.yaml.sharding.YamlShardingConfiguration;
import io.shardingsphere.core.yaml.sharding.YamlShardingRuleConfiguration;
import io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.yaml.snakeyaml.Yaml;
import org.yaml.snakeyaml.constructor.Constructor;

import javax.sql.DataSource;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.sql.SQLException;

/**
@@ -27,10 +11,10 @@
@Configuration
public class DataSourceConfig {

    @Autowired
    private Filter statFilter;

    private static final String SHARDING_YML_PATH = "dataSource.yml";
//    @Autowired
//    private Filter statFilter;
//
//    private static final String SHARDING_YML_PATH = "dataSource.yml";

    /**
     * 构建dataSource
@@ -42,32 +26,32 @@
     * @throws IOException  IO 异常
     * @since 1.8
     */
    @Bean
    public DataSource dataSource() throws SQLException, IOException {
        YamlShardingConfiguration config = parse();
        YamlShardingRuleConfiguration rule = config.getShardingRule();
        for (String key : config.getDataSources().keySet()) {
            DruidDataSource d = (DruidDataSource) config.getDataSources().get(key);
            d.setProxyFilters(Lists.newArrayList(statFilter));
        }
        return ShardingDataSourceFactory.createDataSource(config.getDataSources(),
                rule.getShardingRuleConfiguration(), config.getConfigMap(), config.getProps());
    }

    /**
     * 解析yml
     *
     * @return yaml 配置文件
     * @throws IOException                  IO 异常
     * @throws FileNotFoundException        文件未发现异常
     * @throws UnsupportedEncodingException 不支持编码异常
     */
    private YamlShardingConfiguration parse() throws IOException, FileNotFoundException, UnsupportedEncodingException {
        Resource certResource = new ClassPathResource(SHARDING_YML_PATH);
        try (
                InputStreamReader inputStreamReader = new InputStreamReader(certResource.getInputStream(), "UTF-8")
        ) {
            return new Yaml(new Constructor(YamlShardingConfiguration.class)).loadAs(inputStreamReader, YamlShardingConfiguration.class);
        }
    }
//    @Bean
//    public DataSource dataSource() throws SQLException, IOException {
//        YamlShardingConfiguration config = parse();
//        YamlShardingRuleConfiguration rule = config.getShardingRule();
//        for (String key : config.getDataSources().keySet()) {
//            DruidDataSource d = (DruidDataSource) config.getDataSources().get(key);
//            d.setProxyFilters(Lists.newArrayList(statFilter));
//        }
//        return ShardingDataSourceFactory.createDataSource(config.getDataSources(),
//                rule.getShardingRuleConfiguration(), config.getConfigMap(), config.getProps());
//    }
//
//    /**
//     * 解析yml
//     *
//     * @return yaml 配置文件
//     * @throws IOException                  IO 异常
//     * @throws FileNotFoundException        文件未发现异常
//     * @throws UnsupportedEncodingException 不支持编码异常
//     */
//    private MasterSlaveDataSourceFactory parse() throws IOException, FileNotFoundException, UnsupportedEncodingException {
//        Resource certResource = new ClassPathResource(SHARDING_YML_PATH);
//        try (
//                InputStreamReader inputStreamReader = new InputStreamReader(certResource.getInputStream(), "UTF-8")
//        ) {
//            return YamlMasterSlaveDataSourceFactory;
//        }
//    }
}

 java110-db/src/main/java/com/java110/db/druid/DuridConfig.java

@@ -11,7 +11,7 @@
/**
 * durid 配置类
 */
@Configuration
//@Configuration
public class DuridConfig {


@@ -23,7 +23,7 @@
     * 新建Filter
     * @return Filter
     */
    @Bean
    //@Bean
    public Filter statFilter() {
        StatFilter filter = new StatFilter();
        filter.setSlowSqlMillis(DEFAULT_SHOW_SQL_MILLIS);
@@ -37,7 +37,7 @@
     *  servlet注册
     * @return ServletRegistrationBean
     */
    @Bean
    //@Bean
    public ServletRegistrationBean statViewServlet() {
        //创建servlet注册实体
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

 java110-db/src/main/resources/mapper/community/CommunityServiceDaoImplMapper.xml

@@ -74,9 +74,7 @@
    <!-- 保存小区信息至 instance表中 add by wuxw 2018-07-03 -->
    <insert id="saveCommunityInfoInstance" parameterType="Map">
        insert into s_community(community_id,b_id,name,address,city_code,nearby_landmarks,map_x,map_y,status_cd)
        select s.community_id,s.b_id,s.name,s.address,s.city_code,s.nearby_landmarks,s.map_x,s.map_y,'0'
        from business_community s where 
        s.operate = 'ADD' and s.b_id=#{bId}
        values(#{community_id},#{b_id},#{name},#{address},#{city_code},#{nearby_landmarks},#{map_x},#{map_y},'0')
    </insert>

    <!-- 保存小区属性信息到 instance add by wuxw 2018-07-03 -->
@@ -85,6 +83,9 @@
        select sa.b_id,sa.attr_id,sa.community_id,sa.spec_cd,sa.value,'0'
        from business_community_attr sa
        where sa.operate = 'ADD' and sa.b_id=#{bId}
        <if test="communityId != null and communityId != ''">
            and sa.community_id = #{communityId}
        </if>
    </insert>
    
    <!-- 保存 小区照片信息 instance add by wuxw 2018-07-03 -->
@@ -93,6 +94,9 @@
        select sp.community_photo_id,sp.b_id,sp.community_id,sp.community_photo_type_cd,sp.photo,'0'
        from business_community_photo sp
        where  sp.operate = 'ADD' and sp.b_id=#{bId}
        <if test="communityId != null and communityId != ''">
            and sp.community_id = #{communityId}
        </if>
    </insert>


@@ -247,6 +251,9 @@
        select ms.community_member_id,ms.b_id,ms.community_id,ms.member_id,ms.member_type_cd,'0',ms.audit_status_cd
        from business_community_member ms where
        ms.operate = 'ADD' and ms.b_id=#{bId}
        <if test="communityId != null and communityId != ''">
            and ms.community_id = #{communityId}
        </if>
    </insert>

    <!-- 查询小区成员 add by wuxw 2018-10-27 getBusinessCommunityMember-->

 java110-db/src/test/java/com/java110/AppTest.java

@@ -17,4 +17,12 @@
    {
        assertTrue( true );
    }


    @Test
    public void parseInteger(){
        String communityId = "702019051443120001";

        System.out.println(Long.parseLong(communityId));
    }
}

 pom.xml

@@ -311,10 +311,16 @@
                <version>3.0.0.M3</version>
            </dependency>-->

            <dependency>
            <!--<dependency>
                <groupId>io.shardingsphere</groupId>
                <artifactId>sharding-jdbc-core</artifactId>
                <version>3.1.0</version>
            </dependency>-->

            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-jdbc-core</artifactId>
                <version>4.0.0-RC1</version>
            </dependency>