13、ShardingSphere实战：ShardingJDBC4.0.×版Namespace配置分库分表（十三）

1.Spring MVC,Spring,Mybatis+XML Namespache配置

关于这个部分如何配置，大家可以参考下Jeesite 2.*框架，或者网上找寻相关脚手架项目，这里不做过多介绍。

２.配置applicationContext.xml和mybatis和sharding-jdbc的xml文件

applicationContext.xml加入如下配置文件：

mybatis-spring-shading.xml文件如下：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"*
** *xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
** *xmlns:context="http://www.springframework.org/schema/context"
** *xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"*
** *xmlns:tx="http://www.springframework.org/schema/tx"
** *xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"
** *xmlns:bean="http://www.springframework.org/schema/util"
** *xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd * *
* * * * http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd * *
* * * * http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd * *
* * * * http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
* * * * http://shardingsphere.apache.org/schema/shardingsphere/sharding http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
* * * * http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">
* * * **
* * <context:component-scan
* * * * * * base-package="cn.com.*.*.usemybatis.*" />
* * * * * **
** *<bean id="sharding_0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
** *** *<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
** *** *<property name="jdbcUrl" value="${jdbc.url}" />
** *** *<property name="username" value="${jdbc.username}" />
** *** *<property name="password" value="${jdbc.password}" />
** *</bean>

** *<bean id="sharding_1" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
** *** *<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
** *** *<property name="jdbcUrl" value="${jdbc.shardingurl}" />
** *** *<property name="username" value="${jdbc.shardingusername}" />
** *** *<property name="password" value="${jdbc.shardingpassword}" />
** *</bean>
** *
** *<bean id="preciseModuloDatabaseShardingAlgorithm" class="cn.com.*.*.component.usemybatis.algorithm.PreciseModuloShardingDatabaseAlgorithm" />
** *
** *<sharding:standard-strategy id="databaseStrategy" sharding-column="work_id" precise-algorithm-ref="preciseModuloDatabaseShardingAlgorithm" />
** *<!-- <sharding:inline-strategy id="databaseStrategy" sharding-column="work_id" algorithm-expression="sharding_${id % 2}" /> -->
* * <sharding:inline-strategy id="messageTableStrategy" sharding-column="id" algorithm-expression="tb_message${id % 2}" />
* * <!-- <sharding:inline-strategy id="orderItemTableStrategy" sharding-column="order_id" algorithm-expression="t_order_item_${order_id % 2}" /> -->
* **
* * <bean:properties id="properties">
* * * * <prop key="worker.id">123</prop>
* * </bean:properties>
* **
* * <sharding:key-generator id="messageKeyGenerator" type="SNOWFLAKE" column="id" props-ref="properties"/>
* * <!-- <sharding:key-generator id="itemKeyGenerator" type="SNOWFLAKE" column="order_item_id" props-ref="properties" /> -->
* **
* * <sharding:data-source id="shardingDataSource">
* * * * <sharding:sharding-rule data-source-names="sharding_0, sharding_1">
* * * * * * <sharding:table-rules>
* * * * * * * * <sharding:table-rule logic-table="tb_message" actual-data-nodes="sharding_${0..1}.tb_message${0..1}" database-strategy-ref="databaseStrategy" table-strategy-ref="messageTableStrategy" key-generator-ref="messageKeyGenerator" />
* * * * * * * * <!-- <sharding:table-rule logic-table="t_order_item" actual-data-nodes="demo_ds_${0..1}.t_order_item_${0..1}" database-strategy-ref="databaseStrategy" table-strategy-ref="orderItemTableStrategy" key-generator-ref="itemKeyGenerator" /> -->
* * * * * * </sharding:table-rules>
* * * * * * <sharding:binding-table-rules>
* * * * * * * * <!-- <sharding:binding-table-rule logic-tables="t_order,t_order_item"/> -->
* * * * * * * * <sharding:binding-table-rule logic-tables="tb_message"/>
* * * * * * </sharding:binding-table-rules>
* * * * * * <!-- <sharding:broadcast-table-rules>
* * * * * * * * <sharding:broadcast-table-rule table="t_address"/>
* * * * * * </sharding:broadcast-table-rules> -->
* * * * </sharding:sharding-rule>
* * * * <sharding:props>
* * * * * * <prop key="sql.show">true</prop>
* * * * </sharding:props>
* * </sharding:data-source>
* **
* * <bean id="transactionMybatisManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
* * * * <property name="dataSource" ref="shardingDataSource" />
* * </bean>
* **
* * <!--使用注解事务管理 ，为添加mybatis添加 -->
** *<tx:annotation-driven transaction-manager="transactionMybatisManager" />

** *<!-- 配置SqlSessionFactory对象，为添加mybatis添加 -->
** *<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
** *** *<!-- 注入数据库连接池 -->
** *** *<property name="dataSource" ref="shardingDataSource" />
** *** *<!-- 配置MyBaties全局配置文件:mybatis-config.xml -->
** *** *<property name="configLocation" value="classpath:mybatis-config.xml" />
** *** *<!-- 扫描entity包 使用别名 -->
** *** *<!-- <property name="typeAliasesPackage" value="cn.com.*.*.component.portal.message.model" /> -->
** *** *<!-- 扫描sql配置文件:mapper需要的xml文件 -->
** *** *<!-- <property name="mapperLocations" value="cn.com.*.*.component.usemybatis.dao.*.xml" /> -->
** *</bean>

** *<!-- 配置扫描Dao接口包，动态实现Dao接口，注入到spring容器中，为添加mybatis添加 -->
** *<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
** *** *<!-- 注入sqlSessionFactory -->
** *** *<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
** *** *<!-- 给出需要扫描Dao接口包 -->
** *** *<property name="basePackage" value="cn.com.*.*.component.usemybatis.dao" />
** *</bean>
</beans> 

mybatis配置如下如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<!-- 配置全局属性 -->
	<settings>
		<!-- 使全局的映射器启用或禁用缓存。 -->
		<setting name="cacheEnabled" value="true" />

		<!-- 全局启用或禁用延迟加载。当禁用时，所有关联对象都会即时加载。 -->
		<setting name="lazyLoadingEnabled" value="true" />

		<!-- 当启用时，有延迟加载属性的对象在被调用时将会完全加载任意属性。否则，每种属性将会按需要加载。 -->
		<setting name="aggressiveLazyLoading" value="true" />

		<!-- 是否允许单条sql 返回多个数据集 (取决于驱动的兼容性) default:true -->
		<setting name="multipleResultSetsEnabled" value="true" />

		<!-- 是否可以使用列的别名 (取决于驱动的兼容性) default:true -->
		<setting name="useColumnLabel" value="true" />

		<!-- 允许JDBC 生成主键。需要驱动器支持。如果设为了true，这个设置将强制使用被生成的主键，有一些驱动器不兼容不过仍然可以执行。 default:false -->
		<setting name="useGeneratedKeys" value="false" />

		<!-- 指定 MyBatis 如何自动映射 数据基表的列 NONE：不隐射 PARTIAL:部分 FULL:全部 -->
		<setting name="autoMappingBehavior" value="PARTIAL" />

		<!-- 这是默认的执行类型 （SIMPLE: 简单； REUSE: 执行器可能重复使用prepared statements语句；BATCH: 执行器可以重复执行语句和批量更新） -->
		<setting name="defaultExecutorType" value="SIMPLE" />

		<!-- 使用驼峰命名法转换字段。 -->
		<setting name="mapUnderscoreToCamelCase" value="true" />

		<!-- 设置但JDBC类型为空时,某些驱动程序 要指定值,default:OTHER，插入空值时不需要指定类型 -->
		<setting name="jdbcTypeForNull" value="NULL" />
	</settings>
	<typeAliases>
		<package name="cn.com.*.*.component.usermybatis.model" />
	</typeAliases>
	<mappers>
		<mapper resource="cn/com/**/component/usemybatis/dao/MessageMyBatisDao.xml" />
	</mappers>
</configuration>

3对应Oracle的数据库如下说明，有两个schema，每个schema有下面两个表：

-- Create table
create table TB_MESSAGE0
(
  id                   NUMBER(20) not null,
  sender_id            VARCHAR2(64),
  sender_name          VARCHAR2(50),
  create_time          DATE,
  work_id              NUMBER(1)
)

-- Create table
create table TB_MESSAGE1
(
  id                   NUMBER(20) not null,
  sender_id            VARCHAR2(64),
  sender_name          VARCHAR2(50),
  create_time          DATE,
  work_id              NUMBER(1)
)

4、 最后使用dao文件的save方法测试下，dao文件和xml具体代码如下：；

package cn.com.*.*.component.usemybatis.dao;

import java.math.BigDecimal;

import org.springframework.stereotype.Repository;

import cn.com.*.*.component.usemybatis.model.TbMessageMybatisEntity;

@Repository
public interface UnifiedMessageMybatisDao {
	public void save(TbMessageMybatisEntity tbGbUnifiedMessagePo);
	public TbMessageMybatisEntity get(Double id);
}

<?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.com.*.*.component.usemybatis.dao.MessageMybatisDao">
	<insert id="save" parameterType="cn.com.*.*.component.usemybatis.model.TbMessageMybatisEntity">
		INSERT INTO tb_message (id, work_id,sender_id, sender_name,
		create_time)
		VALUES
		(
		#{id},#{workId},#{senderId},#{senderName},#{createTime}
		)
	</insert>

	<select id="get" parameterType="long"
		resultType="cn.com.*.*.component.usemybatis.model.TbMessageMybatisEntity">
		select * from tb_message where id = #{id}
	</select>
</mapper>

5.自己实现雪花算法，在调用save方法的使用使用这个雪花算法，配置的雪花算法没有生效，暂时还不清楚是什么原因导致，后续跟踪。

/**
     * 起始的时间戳
     */
    private final static long START_STMP = 1480166465631L;

    /**
     * 每一部分占用的位数
     */
    private final static long SEQUENCE_BIT = 12; //序列号占用的位数
    private final static long MACHINE_BIT = 5;   //机器标识占用的位数
    private final static long DATACENTER_BIT = 5;//数据中心占用的位数

    /**
     * 每一部分的最大值
     */
    private final static long MAX_DATACENTER_NUM = -1L ^ (-1L << DATACENTER_BIT);
    private final static long MAX_MACHINE_NUM = -1L ^ (-1L << MACHINE_BIT);
    private final static long MAX_SEQUENCE = -1L ^ (-1L << SEQUENCE_BIT);

    /**
     * 每一部分向左的位移
     */
    private final static long MACHINE_LEFT = SEQUENCE_BIT;
    private final static long DATACENTER_LEFT = SEQUENCE_BIT + MACHINE_BIT;
    private final static long TIMESTMP_LEFT = DATACENTER_LEFT + DATACENTER_BIT;

    private static long datacenterId;  //数据中心
    private static long machineId;     //机器标识
    private static long sequence = 0L; //序列号
    private static long lastStmp = -1L;//上一次时间戳

    public SnowFlakeIdKeyGenerator(long datacenterId, long machineId) {
        if (datacenterId > MAX_DATACENTER_NUM || datacenterId < 0) {
            throw new IllegalArgumentException("datacenterId can't be greater than MAX_DATACENTER_NUM or less than 0");
        }
        if (machineId > MAX_MACHINE_NUM || machineId < 0) {
            throw new IllegalArgumentException("machineId can't be greater than MAX_MACHINE_NUM or less than 0");
        }
    }

    /**
     * 产生下一个ID
     *
     * @return
     */
    public static synchronized long nextId() {
        long currStmp = getNewstmp();
        if (currStmp < lastStmp) {
            throw new RuntimeException("Clock moved backwards.  Refusing to generate id");
        }

        if (currStmp == lastStmp) {
            //相同毫秒内，序列号自增
            sequence = (sequence + 1) & MAX_SEQUENCE;
            //同一毫秒的序列数已经达到最大
            if (sequence == 0L) {
                currStmp = getNextMill();
            }
        } else {
            //不同毫秒内，序列号置为0
        	Random random=new Random();
            sequence = random.nextInt(2);
        }

        lastStmp = currStmp;

        return (currStmp - START_STMP) << TIMESTMP_LEFT //时间戳部分
                | datacenterId << DATACENTER_LEFT       //数据中心部分
                | machineId << MACHINE_LEFT             //机器标识部分
                | sequence;                             //序列号部分
    }

    private static long getNextMill() {
        long mill = getNewstmp();
        while (mill <= lastStmp) {
            mill = getNewstmp();
        }
        return mill;
    }

    private static long getNewstmp() {
        return System.currentTimeMillis();
    }

    public static void main(String[] args) {
    	//SnowFlakeIdKeyGenerator snowFlake = new SnowFlakeIdKeyGenerator(2, 3);

        long start = System.currentTimeMillis();
        for (int i = 0; i < 1000000; i++) {
            System.out.println(SnowFlakeIdKeyGenerator.nextId());
        }

        System.out.println(System.currentTimeMillis() - start);
    }

具体调用方法：

SnowFlakeIdKeyGenerator snowFlakeIdKeyGenerator = new SnowFlakeIdKeyGenerator(1, 1);
unifiedMessageShardingPo.setId(snowFlakeIdKeyGenerator.nextId()

6.PreciseModuloShardingTableAlgorithm.java来源于https://github.com/apache/incubator-shardingsphere-example项目里面。

7.最后使用mvc写一个controller即可，这里省略了，目前只包含保存，如何读后面给大家介绍，或者参考spring boot这两篇文章

https://blog.csdn.net/qq_40102894/article/details/96302535

https://my.oschina.net/u/4034639/blog/3079441/print

版权声明：本文不是「本站」原创文章，版权归原作者所有 | 原文地址: