springboot整合sharding-jdbc连接oracle实现单库分表

最新推荐文章于 2024-05-21 08:17:41 发布

我吃包菜

最新推荐文章于 2024-05-21 08:17:41 发布

阅读量8.1k

点赞数 2

分类专栏： sharding-jdbc springboot

本文链接：https://blog.csdn.net/qq_40102894/article/details/96302535

版权

springboot 同时被 2 个专栏收录

13 篇文章 0 订阅

订阅专栏

sharding-jdbc

0 篇文章 0 订阅

订阅专栏

springboot整合sharding-jdbc连接oracle实现单库分表

在业务处理中很经常会有大数据的保存，涉及到分表操作，水平分表。
涉及到的版本
oracle 11
springboot 2.1.6
sharding-jdbc 3.0.0.M1

在本文章中只介绍简单的一种

文章目录

springboot整合sharding-jdbc连接oracle实现单库分表

1 介绍sharding-jdbc

Sharding-jdbc是ShardingSphere项目下的一个子项目

定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
基于任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer和PostgreSQL。

更多详细的介绍可以看ShardingSphere官方中文文档

2 案例介绍

2.1 pom

核心pom

        <!--sharding-jdbc-->
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding.jdbc.version}</version>
        </dependency>

整体的pom


<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.sharding</groupId>
    <artifactId>jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>jdbc</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <sharding.jdbc.version>3.0.0.M1</sharding.jdbc.version>
        <mybatis.version>1.3.0</mybatis.version>
        <druid.version>1.1.10</druid.version>
        <mysql.version>5.1.38</mysql.version>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.1</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.oracle.jdbc</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.1.0.6.0</version>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid.version}</version>
        </dependency>

        <!--sharding-jdbc-->
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding.jdbc.version}</version>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

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

</project>

2.2 yaml配置


server:
  port: 8080
spring:
  application:
    name: sharding-jdbc

mybatis:
  mapper-locations: classpath:mybatis/*Mapper.xml
  type-aliases-package: com.sharding.jdbc.bean

sharding:
  jdbc:
    datasource:
      names: ds0
      # 数据源ds0
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: oracle.jdbc.driver.OracleDriver
        url: jdbc:oracle:thin:@localhost:1521:xe
        username: ******
        password: ******

    config:
      sharding:
        props:
          sql.show: true
        tables:
          t_user:  #t_user表
            key-generator-column-name: id  #主键
            actual-data-nodes: ds0.t_user${0..1}    #数据节点,均匀分布
            table-strategy:  #分表策略
              inline: #行表达式
                sharding-column: task_id
                algorithm-expression: t_user${task_id % 2}  #按模运算分配

2.3 准备表DDL

T_ADDRESS

create table T_ADDRESS
(
	ID NUMBER(20) not null
		constraint T_ADDRESS_PK
			primary key,
	NAME VARCHAR2(64),
	ADDR VARCHAR2(32)
)
/

comment on column T_ADDRESS.ID is '主键'
/

comment on column T_ADDRESS.NAME is '名称'
/

comment on column T_ADDRESS.ADDR is '地址'
/

T_USER0

create table T_USER0
(
	ID NUMBER(20) not null
		constraint TABLE1_PK
			primary key,
	NAME VARCHAR2(64),
	TASK_ID NUMBER(12),
	CALL_MONTH VARCHAR2(20)
)
/

comment on column T_USER0.ID is '主键'
/

comment on column T_USER0.NAME is '名称'
/

comment on column T_USER0.TASK_ID is '任务id'
/

comment on column T_USER0.CALL_MONTH is '呼叫月份'
/

T_USER1

create table T_USER1
(
	ID NUMBER(20) not null
		constraint T_USER1_PK
			primary key,
	NAME VARCHAR2(64),
	TASK_ID NUMBER(12),
	CALL_MONTH VARCHAR2(20)
)
/

comment on column T_USER1.ID is '主键'
/

comment on column T_USER1.NAME is '名称'
/

comment on column T_USER1.TASK_ID is '任务id'
/

comment on column T_USER1.CALL_MONTH is '呼叫月份'
/

2.4准备测试对象

Address

public class Address {


    private Long id;
    private String name;
    private String addr;
}

User

public class User {

    private Long id;
    private String name;
    private Long taskId;
    private String callMonth;
}

AddressMapper

@Mapper
public interface AddressMapper {

    void save(Address address);

    Address get(Long id);

}

UserMapper

@Mapper
public interface UserMapper {


    void save(User address);


    User get(@Param("id") Long id,@Param("taskId") Long taskId);

    /**
     * 分页
     * @param params
     * @return
     */
    List<User> pagingUser(Map<String,Object> params);

}

AddressMapper.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="com.sharding.jdbc.mapper.AddressMapper">
    <insert id="save" parameterType="Address">
        INSERT INTO t_address(id,name,addr)
        VALUES
        (
        #{id},#{name},#{addr}
        )
    </insert>

    <select id="get" parameterType="long" resultType="Address">
    	select * from t_address where id = #{id}
    </select>
</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="com.sharding.jdbc.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="com.sharding.jdbc.bean.User">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="task_id" property="taskId"/>
        <result column="call_month" property="callMonth"/>
    </resultMap>

    <insert id="save" parameterType="User">
        INSERT INTO t_user(id,name,task_id,call_month)
        VALUES
        (
        #{id},#{name},#{taskId},#{callMonth}
        )
    </insert>

    <select id="get" resultMap="BaseResultMap">
    	select * from t_user where id = #{id} and task_id=#{taskId}
    </select>

    <select id="pagingUser" parameterType="java.util.Map" resultMap="BaseResultMap">
        SELECT * FROM (
        SELECT T.*, ROWNUM RN
        FROM (
        SELECT
        t.id,
        t.name,
        t.task_id,
        t.call_month

        FROM
        t_user t
        WHERE t.task_id=#{task_id}
        ) T
        )
        WHERE RN <![CDATA[>]]> #{start}
        AND RN <![CDATA[<=]]> #{end}
    </select>

</mapper>

3 测试

3.1 不分表对象测试

插入测试

  @Test
    public void save() {

        for (int i = 0; i < 10; i++) {
            Address address = new Address();

            Long id = i + 1L;

            address.setId(id);
            address.setName("name_" + i);
            address.setAddr("addr_" + i);

            addressMapper.save(address);
        }
    }

在这里插入图片描述

查询测试

   @Test
    public void testGet() {
        Address address = this.addressMapper.get(2L);
        System.out.println(address.toString());
    }

在这里插入图片描述

3.2 单库分表数据测试

插入例子是根据task_id分表

    @Test
    public void testSave() {

        for (int i = 0; i < 10; i++) {
            User user = new User();
            Long id =i+1L;
            user.setId(id);
            user.setName("test" + i);
            user.setCallMonth("201907");
            user.setTaskId(2L);
            userMapper.save(user);
        }

    }

在这里插入图片描述
10条数据都到t_user0

查询


  @Test
    public void testGet() {

        User user = userMapper.get(2L, 2L);

        System.out.println(user.toString());

    }

在这里插入图片描述

分页查询

   @Test
    public void testPaging() {
        Map<String, Object> map = new HashMap<>();
        map.put("task_id", 2L);
        map.put("start", 0);
        map.put("end", 5);

        List<User> users = this.userMapper.pagingUser(map);
        
        System.out.println(users.toString());
    }

在这里插入图片描述

4 源码

案例源码

参考文档：sharding-jdbc官方文档

我吃包菜

关注

2
点赞
踩
10

收藏

觉得还不错? 一键收藏
6
评论
springboot整合sharding-jdbc连接oracle实现单库分表

springboot整合sharding-jdbc连接oracle实现单库分表在业务处理中很经常会有大数据的保存，涉及到分表操作，水平分表。涉及到的版本oracle 11springboot 2.1.6sharding-jdbc 3.0.0.M1在本文章中只介绍简单的一种文章目录springboot整合sharding-jdbc连接oracle实现单库分表1 介绍sharding-j...
复制链接

扫一扫