shardingJDBC精准分片

shardingJDBC不同表名分片方式

引入pom依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>4.1.1</version>
</dependency>

properties配置文件精确分片

spring.shardingsphere.datasource.names=ds0,ds1,ds2


spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/ds0?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123
spring.shardingsphere.datasource.ds0.hikari.minimum-idle=5
spring.shardingsphere.datasource.ds0.hikari.maximum-pool-size=15
spring.shardingsphere.datasource.ds0.hikari.auto-commit=true
spring.shardingsphere.datasource.ds0.hikari.login-timeout=500
spring.shardingsphere.datasource.ds0.hikari.idle-timeout=600
spring.shardingsphere.datasource.ds0.hikari.pool-name=Tata-HikariCP
spring.shardingsphere.datasource.ds0.hikari.validation-timeout=3000
spring.shardingsphere.datasource.ds0.hikari.max-lifetime=120000
spring.shardingsphere.datasource.ds0.hikari.connection-timeout=60000
spring.shardingsphere.datasource.ds0.hikari.connection-test-query=select 1 from dual


spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ds1?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123
spring.shardingsphere.datasource.ds1.hikari.minimum-idle=5
spring.shardingsphere.datasource.ds1.hikari.maximum-pool-size=15
spring.shardingsphere.datasource.ds1.hikari.auto-commit=true
spring.shardingsphere.datasource.ds1.hikari.login-timeout=500
spring.shardingsphere.datasource.ds1.hikari.idle-timeout=600
spring.shardingsphere.datasource.ds1.hikari.pool-name=Tata-HikariCP
spring.shardingsphere.datasource.ds1.hikari.validation-timeout=3000
spring.shardingsphere.datasource.ds1.hikari.max-lifetime=120000
spring.shardingsphere.datasource.ds1.hikari.connection-timeout=60000
spring.shardingsphere.datasource.ds1.hikari.connection-test-query=select 1 from dual


spring.shardingsphere.datasource.ds2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds2.jdbc-url=jdbc:mysql://localhost:3306/ds2?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8
spring.shardingsphere.datasource.ds2.username=root
spring.shardingsphere.datasource.ds2.password=123
spring.shardingsphere.datasource.ds2.hikari.minimum-idle=5
spring.shardingsphere.datasource.ds2.hikari.maximum-pool-size=15
spring.shardingsphere.datasource.ds2.hikari.auto-commit=true
spring.shardingsphere.datasource.ds2.hikari.login-timeout=500
spring.shardingsphere.datasource.ds2.hikari.idle-timeout=600
spring.shardingsphere.datasource.ds2.hikari.pool-name=Tata-HikariCP
spring.shardingsphere.datasource.ds2.hikari.validation-timeout=3000
spring.shardingsphere.datasource.ds2.hikari.max-lifetime=120000
spring.shardingsphere.datasource.ds2.hikari.connection-timeout=60000
spring.shardingsphere.datasource.ds2.hikari.connection-test-query=select 1 from dual


#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 3}
#广播表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds0.t_user0,ds0.t_user1,ds1.t_user2,ds1.t_user3,ds2.t_user4,ds2.t_user5,ds0.t_product0,ds0.t_product1,ds0.t_product2,ds0.t_product_spec0,ds0.t_product_spec1,ds0.t_product_spec2,ds1.t_product3,ds1.t_product4,ds1.t_product5,ds1.t_product_spec3,ds1.t_product_spec4,ds1.t_product_spec5,ds2.t_product6,ds2.t_product7,ds2.t_product8,ds2.t_product_spec6,ds2.t_product_spec7,ds2.t_product_spec8
#ds0.t_user${0..2},ds1.t_user${3..5} ds$->{0..2}.t_user$->{0..5}  ds0.t_user0,ds0.t_user2,ds0.t_user4,ds1.t_user0,ds1.t_user3,ds1.t_user5
##分片列名称
#spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=id
##分片算法行表达式
#spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user$->{id % 6}
spring.shardingsphere.sharding.tables.t_user.table-strategy.standard.sharding-column=id
##分片算法行表达式
spring.shardingsphere.sharding.tables.t_user.table-strategy.standard.precise-algorithm-class-name=com.orarcle.server.algorithm.UserTablePreciseShardingAlgorithm


#如果要分片，必须开启广播表配置，也就是分片的表名必须相同
#分片列名称
spring.shardingsphere.sharding.tables.t_user.database-strategy.standard.sharding-column=id
#精确分片算法类名称，用于=和IN
spring.shardingsphere.sharding.tables.t_user.database-strategy.standard.precise-algorithm-class-name=com.orarcle.server.algorithm.UserDBPreciseShardingAlgorithm


spring.shardingsphere.props.sql.show=true

 

精准分片分库规则

database-strategy.complex.algorithm-class-name= #复合分片算法类名称。该类需实现ComplexKeysShardingAlgorithm接口并提供无参数的构造器

精准分片分库规则

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;


import java.util.Collection;


/**
* @author wjc
* @description 分库规则
* @date 2020/12/1
*/
public class UserDBPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {


    public UserDBPreciseShardingAlgorithm() {
    }


    /**
     *
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {


        Long value = preciseShardingValue.getValue();
        value = value%3;
        System.out.println(value);
        for (String each : collection) {
            if (each.endsWith(String.valueOf(value))) {
                return each;
            }
        }
        throw new UnsupportedOperationException();


    }
}

精准分片分表规则

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;


import java.util.Collection;


/**
* @author wjc
* @description 分表规则
* @date 2020/12/1
*/
public class UserTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {


    public UserTablePreciseShardingAlgorithm() {
    }


    /**
     * ds0.t_user0, ds0.t_user1
     * ds1.t_user2, ds1.t_user3
     * ds2.t_user4, ds2.t_user5
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {


        Long value = preciseShardingValue.getValue();  //value 为代码中 id 的真实数据，比如 插入，id 为7,则 value 为7
        int length = collection.size();
        value = value%length;
        System.out.println(value);


        for (String each : collection) { //each 为t_manager_0  t_manager_1 配置的正式的表名
            int i = Integer.parseInt(each.substring(each.length()-1));
            if (value == i%length) {
                return each;
            }
        }
        throw new UnsupportedOperationException();


    }


}

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;


import java.util.Collection;


/**
* @author wjc
* @description 分表规则
* @date 2020/12/1
*/
public class UserTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {


    public UserTablePreciseShardingAlgorithm() {
    }


    /**
     * ds0.t_user0, ds0.t_user1
     * ds1.t_user2, ds1.t_user3
     * ds2.t_user4, ds2.t_user5
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {


        Long value = preciseShardingValue.getValue();  //value 为代码中 id 的真实数据，比如 插入，id 为7,则 value 为7
        int length = collection.size();
        value = value%length;
        System.out.println(value);


        for (String each : collection) { //each 为t_manager_0  t_manager_1 配置的正式的表名
            int i = Integer.parseInt(each.substring(each.length()-1));
            if (value == i%length) {
                return each;
            }
        }
        throw new UnsupportedOperationException();


    }


}

Product类

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.Data;


import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;


@TableName("t_product")
@Data
public class Product extends Model<Product> {


    private static final long serialVersionUID = 1L;


    @TableId(type = IdType.ASSIGN_ID)
    private Long id;


    private Date createTime;


    private Date updateTime;


    private String title;


    private BigDecimal price;


    @Override
    protected Serializable pkVal() {
        return this.id;
    }
}

User类

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.Data;


import java.io.Serializable;
import java.util.Date;


@TableName("t_user")
@Data
public class User extends Model<User> {


    private static final long serialVersionUID = 1L;


    @TableId(type = IdType.ASSIGN_ID)
    private Long id;
    private String name;
    private String phone;
    private String email;
    private String password;
    private Integer cityId;
    private Date createTime;
    private Integer sex;


    @Override
    protected Serializable pkVal() {
        return this.id;
    }
}

 

 

 

 

引入pom依赖

<dependency>

    <groupId>org.apache.shardingsphere</groupId>

    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>

    <version>4.1.1</version>

</dependency>

<dependency>

    <groupId>org.apache.shardingsphere</groupId>

    <artifactId>sharding-jdbc-spring-namespace</artifactId>

    <version>4.1.1</version>

</dependency>

 

在配置文件中添加相关配置（行表达式分片）

  shardingsphere:

    datasource:

      names: ds0

      ds0:

        type: com.zaxxer.hikari.HikariDataSource

        driver-class-name: com.mysql.cj.jdbc.Driver

        jdbc-url: jdbc:mysql://127.0.01:3306/ds0?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8

        username: root

        password: 123

        hikari:

          minimum-idle: 5 #连接池空闲连接的最小数量。默认10

          maximum-pool-size: 15 #连接池中允许的最大连接数。默认10；

          auto-commit: true #自动提交从池中返回的连接。默认true

          login-timeout: 500

          idle-timeout: 60000 #一个连接闲置的最大时长（毫秒）。默认10分钟

          pool-name: Tata-HikariCP #连接池的用户定义名称，主要出现在日志记录和JMX管理控制台中以识别池和池配置

          validation-timeout: 3000 #验证连接有效性的超时时间（毫秒）

          max-lifetime: 120000 #一个连接在池中最长生命周期（毫秒），超时而且没被使用则被释放 默认30分钟。建议设置比数据库超时时长少30秒，参考MySQL wait_timeout参数

          connection-timeout: 60000 #数据库连接超时时间（毫秒）。默认30秒

          connection-test-query: select 1 from dual

#      ds1:

#        type: com.zaxxer.hikari.HikariDataSource

#        driver-class-name: com.mysql.cj.jdbc.Driver

#        jdbc-url: jdbc:mysql://127.0.01:3306/ds1?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8

#        username: root

#        password: 123

#        hikari:

#          minimum-idle: 5 #连接池空闲连接的最小数量。默认10

#          maximum-pool-size: 15 #连接池中允许的最大连接数。默认10；

#          auto-commit: true #自动提交从池中返回的连接。默认true

#          login-timeout: 500

#          idle-timeout: 60000 #一个连接闲置的最大时长（毫秒）。默认10分钟

#          pool-name: Tata-HikariCP #连接池的用户定义名称，主要出现在日志记录和JMX管理控制台中以识别池和池配置

#          validation-timeout: 3000 #验证连接有效性的超时时间（毫秒）

#          max-lifetime: 120000 #一个连接在池中最长生命周期（毫秒），超时而且没被使用则被释放 默认30分钟。建议设置比数据库超时时长少30秒，参考MySQL wait_timeout参数

#          connection-timeout: 60000 #数据库连接超时时间（毫秒）。默认30秒

#          connection-test-query: select 1 from dual

    sharding:

      default-database-strategy:

        inline:

          sharding-column: id

          algorithm-expression: ds0

      tables:

        t_user:

          actual-data-nodes: ds0.t_user0,ds0.t_user1

          table-strategy:

            inline:

              sharding-column: id

              algorithm-expression: t_user$->{id % 2}

 

properties配置文件精确分片

spring.shardingsphere.datasource.names=ds0,ds1,ds2

 

 

spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource

spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver

spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/ds0?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8

spring.shardingsphere.datasource.ds0.username=root

spring.shardingsphere.datasource.ds0.password=123

spring.shardingsphere.datasource.ds0.hikari.minimum-idle=5

spring.shardingsphere.datasource.ds0.hikari.maximum-pool-size=15

spring.shardingsphere.datasource.ds0.hikari.auto-commit=true

spring.shardingsphere.datasource.ds0.hikari.login-timeout=500

spring.shardingsphere.datasource.ds0.hikari.idle-timeout=600

spring.shardingsphere.datasource.ds0.hikari.pool-name=Tata-HikariCP

spring.shardingsphere.datasource.ds0.hikari.validation-timeout=3000

spring.shardingsphere.datasource.ds0.hikari.max-lifetime=120000

spring.shardingsphere.datasource.ds0.hikari.connection-timeout=60000

spring.shardingsphere.datasource.ds0.hikari.connection-test-query=select 1 from dual

 

 

spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource

spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver

spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ds1?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8

spring.shardingsphere.datasource.ds1.username=root

spring.shardingsphere.datasource.ds1.password=123

spring.shardingsphere.datasource.ds1.hikari.minimum-idle=5

spring.shardingsphere.datasource.ds1.hikari.maximum-pool-size=15

spring.shardingsphere.datasource.ds1.hikari.auto-commit=true

spring.shardingsphere.datasource.ds1.hikari.login-timeout=500

spring.shardingsphere.datasource.ds1.hikari.idle-timeout=600

spring.shardingsphere.datasource.ds1.hikari.pool-name=Tata-HikariCP

spring.shardingsphere.datasource.ds1.hikari.validation-timeout=3000

spring.shardingsphere.datasource.ds1.hikari.max-lifetime=120000

spring.shardingsphere.datasource.ds1.hikari.connection-timeout=60000

spring.shardingsphere.datasource.ds1.hikari.connection-test-query=select 1 from dual

 

 

spring.shardingsphere.datasource.ds2.type=com.zaxxer.hikari.HikariDataSource

spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver

spring.shardingsphere.datasource.ds2.jdbc-url=jdbc:mysql://localhost:3306/ds2?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8

spring.shardingsphere.datasource.ds2.username=root

spring.shardingsphere.datasource.ds2.password=123

spring.shardingsphere.datasource.ds2.hikari.minimum-idle=5

spring.shardingsphere.datasource.ds2.hikari.maximum-pool-size=15

spring.shardingsphere.datasource.ds2.hikari.auto-commit=true

spring.shardingsphere.datasource.ds2.hikari.login-timeout=500

spring.shardingsphere.datasource.ds2.hikari.idle-timeout=600

spring.shardingsphere.datasource.ds2.hikari.pool-name=Tata-HikariCP

spring.shardingsphere.datasource.ds2.hikari.validation-timeout=3000

spring.shardingsphere.datasource.ds2.hikari.max-lifetime=120000

spring.shardingsphere.datasource.ds2.hikari.connection-timeout=60000

spring.shardingsphere.datasource.ds2.hikari.connection-test-query=select 1 from dual

 

 

#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id

#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 3}

#广播表

spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds0.t_user0,ds0.t_user1,ds1.t_user2,ds1.t_user3,ds2.t_user4,ds2.t_user5,ds0.t_product0,ds0.t_product1,ds0.t_product2,ds0.t_product_spec0,ds0.t_product_spec1,ds0.t_product_spec2,ds1.t_product3,ds1.t_product4,ds1.t_product5,ds1.t_product_spec3,ds1.t_product_spec4,ds1.t_product_spec5,ds2.t_product6,ds2.t_product7,ds2.t_product8,ds2.t_product_spec6,ds2.t_product_spec7,ds2.t_product_spec8

#ds0.t_user${0..2},ds1.t_user${3..5} ds$->{0..2}.t_user$->{0..5}  ds0.t_user0,ds0.t_user2,ds0.t_user4,ds1.t_user0,ds1.t_user3,ds1.t_user5

##分片列名称

#spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=id

##分片算法行表达式

#spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user$->{id % 6}

spring.shardingsphere.sharding.tables.t_user.table-strategy.standard.sharding-column=id

##分片算法行表达式

spring.shardingsphere.sharding.tables.t_user.table-strategy.standard.precise-algorithm-class-name=com.orarcle.server.algorithm.UserTablePreciseShardingAlgorithm

 

 

#如果要分片，必须开启广播表配置，也就是分片的表名必须相同

#分片列名称

spring.shardingsphere.sharding.tables.t_user.database-strategy.standard.sharding-column=id

#精确分片算法类名称，用于=和IN

spring.shardingsphere.sharding.tables.t_user.database-strategy.standard.precise-algorithm-class-name=com.orarcle.server.algorithm.UserDBPreciseShardingAlgorithm

 

 

spring.shardingsphere.props.sql.show=true

 

 

#用于多分片键的复合分片场景

spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.complex.sharding-columns= #分片列名称，多个列以逗号分隔

spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.complex.algorithm-class-name= #复合分片算法类名称。该类需实现ComplexKeysShardingAlgorithm接口并提供无参数的构造器

 

精准分片分库规则

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

 

 

import java.util.Collection;

 

 

/**

* @author wjc

* @description 分库规则

* @date 2020/12/1

*/

public class UserDBPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

 

 

    public UserDBPreciseShardingAlgorithm() {

    }

 

 

    /**

     *

     */

    @Override

    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {

 

 

        Long value = preciseShardingValue.getValue();

        value = value%3;

        System.out.println(value);

        for (String each : collection) {

            if (each.endsWith(String.valueOf(value))) {

                return each;

            }

        }

        throw new UnsupportedOperationException();

 

 

    }

}

 

精准分片分表规则

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

 

 

import java.util.Collection;

 

 

/**

* @author wjc

* @description 分表规则

* @date 2020/12/1

*/

public class UserTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

 

 

    public UserTablePreciseShardingAlgorithm() {

    }

 

 

    /**

     * ds0.t_user0, ds0.t_user1

     * ds1.t_user2, ds1.t_user3

     * ds2.t_user4, ds2.t_user5

     */

    @Override

    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {

 

 

        Long value = preciseShardingValue.getValue();  //value 为代码中 id 的真实数据，比如 插入，id 为7,则 value 为7

        int length = collection.size();

        value = value%length;

        System.out.println(value);

 

 

        for (String each : collection) { //each 为t_manager_0  t_manager_1 配置的正式的表名

            int i = Integer.parseInt(each.substring(each.length()-1));

            if (value == i%length) {

                return each;

            }

        }

        throw new UnsupportedOperationException();

 

 

    }

 

 

}

 

Product类

import com.baomidou.mybatisplus.annotation.IdType;

import com.baomidou.mybatisplus.annotation.TableId;

import com.baomidou.mybatisplus.annotation.TableName;

import com.baomidou.mybatisplus.extension.activerecord.Model;

import lombok.Data;

 

 

import java.io.Serializable;

import java.math.BigDecimal;

import java.util.Date;

 

 

@TableName("t_product")

@Data

public class Product extends Model<Product> {

 

 

    private static final long serialVersionUID = 1L;

 

 

    @TableId(type = IdType.ASSIGN_ID)

    private Long id;

 

 

    private Date createTime;

 

 

    private Date updateTime;

 

 

    private String title;

 

 

    private BigDecimal price;

 

 

    @Override

    protected Serializable pkVal() {

        return this.id;

    }

}

 

User类

import com.baomidou.mybatisplus.annotation.IdType;

import com.baomidou.mybatisplus.annotation.TableId;

import com.baomidou.mybatisplus.annotation.TableName;

import com.baomidou.mybatisplus.extension.activerecord.Model;

import lombok.Data;

 

 

import java.io.Serializable;

import java.util.Date;

 

 

@TableName("t_user")

@Data

public class User extends Model<User> {

 

 

    private static final long serialVersionUID = 1L;

 

 

    @TableId(type = IdType.ASSIGN_ID)

    private Long id;

    private String name;

    private String phone;

    private String email;

    private String password;

    private Integer cityId;

    private Date createTime;

    private Integer sex;

 

 

    @Override

    protected Serializable pkVal() {

        return this.id;

    }

}

 

 mapper文件

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.orarcle.server.entity.User;

public interface UserMapper extends BaseMapper<User> {
}

 

测试接口

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.orarcle.server.entity.User;
import com.orarcle.server.service.UserService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * @author wjc
 * @description
 * @date 2020/12/1
 */
@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/user/save")
    public String saveUser(User user){

        userService.save(user);
        System.out.println(user.getId()%6);
        return "success";
    }

    @GetMapping("/user/get")
    public User getUser(Long id){
        return userService.lambdaQuery().eq(User::getId, id).one();
    }

    @GetMapping("/user/list")
    public List<User> list(String name, String phone, Integer cityId, Integer sex){
        return userService.lambdaQuery()
                .like(StringUtils.isNotBlank(name), User::getName, name)
                .eq(StringUtils.isNotBlank(phone), User::getPhone, phone)
                .eq(cityId != null, User::getCityId, cityId)
                .eq(sex != null, User::getSex, sex)
                .orderByDesc(User::getCreateTime)
                .list();
    }

    @GetMapping("/user/page")
    public PageInfo<User> page(Integer pageNum, Long id, String name, String phone, Integer cityId, Integer sex){
        PageHelper.startPage(pageNum, 10);
        List<User> list = userService.lambdaQuery()
                .eq(id != null, User::getId, id)
                .like(StringUtils.isNotBlank(name), User::getName, name)
                .eq(StringUtils.isNotBlank(phone), User::getPhone, phone)
                .eq(cityId != null, User::getCityId, cityId)
                .eq(sex != null, User::getSex, sex)
                .orderByDesc(User::getCreateTime)
                .list();
        return new PageInfo<>(list);
    }

    @GetMapping("/user/update")
    public String update(User user){
        userService.updateById(user);
        return "success";
    }

    @GetMapping("/user/remove")
    public String remove(Long id){
        userService.removeById(id);
        return "success";
    }
}