Mycat（实践篇 - 基于Mysql的水平切分、主从复制、读写分离）

        <user name="mysqlmycat">
                <property name="password">mysqlmycat</property>
                <property name="schemas">mysqlmycats</property>
        </user>

        <schema name="mysqlmycats" checkSQLschema="false" sqlMaxLimit="100">
                <table name="tb_user" dataNode="mydn1,mydn2" rule="user-mod-long" />
                <table name="tb_student" dataNode="mydn1,mydn2" rule="student-mod-long" />
        </schema>
        
        <dataNode name="mydn1" dataHost="myhost1" database="master1mycat" />
        <dataNode name="mydn2" dataHost="myhost2" database="master2mycat" />

        <dataHost name="myhost1" maxCon="100" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="localhost:3306" user="root" password="xxx">
                        <readHost host="hostS1" url="localhost:3307" user="root" password="xxx"/>
                </writeHost>
        </dataHost>

        <dataHost name="myhost2" maxCon="100" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM2" url="localhost:3308" user="root" password="xxx" >
                        <readHost host="hostS2" url="localhost:3309" user="root" password="xxx"/>
                </writeHost>
        </dataHost>

       <tableRule name="user-mod-long">
                <rule>
                        <columns>id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>
       <tableRule name="student-mod-long">
                <rule>
                        <columns>user_id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>

        <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
            <property name="count">2</property>
        </function>

spring:
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: update
      naming:
        strategy: org.hibernate.cfg.ImprovedNamingStrategy
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL5Dialect
  datasource:
    url: jdbc:mysql://localhost:8066/mysqlmycats?characterEncoding=UTF-8&useSSL=false&autoReconnect=true&rewriteBatchedStatements=true
    username: mysqlmycat
    password: mysqlmycat

@Entity
@Table(name = "tb_user")
@Data
public class User {

    @Id
    private Long id;

    private String name;

    private Integer gender;

}

@Entity
@Table(name = "tb_student")
@Data
public class Student {

    @Id
    private Long id;

    private String name;

    @Column(unique = true)
    private Long userId;

}

public interface UserDao extends JpaRepository<User, Long> {

    Page<User> findByNameLike(String name, Pageable pageable);

}

public interface StudentDao extends JpaRepository<Student, Long> {

    Page<User> findByNameLike(String name, Pageable pageable);

}

@Test
public void testAdd() {
        for (long i = 0; i < 30; i++) {
            User user = new User();
            user.setId(i);
            user.setName("张三" + i);
            user.setGender(i % 2 == 0 ? 0 : 1);
            userDao.save(user);

            Student student = new Student();
            student.setId(System.currentTimeMillis() + i);
            student.setName("张三学生" + i);
            student.setUserId(i);
            studentDao.save(student);
        }
}

@Test
public void testFind() {
        Pageable pageable = new PageRequest(0, 10, Sort.Direction.DESC, "id");
        List<User> userList = userDao.findByNameLike("%张三2%", pageable).getContent();
        userList.forEach(System.out::println);

        Pageable pageable2 = new PageRequest(0, 10, Sort.Direction.DESC, "userId");
        List<Student> studentList = studentDao.findByNameLike("%张三学生1%", pageable2).getContent();
        studentList.forEach(System.out::println);
}