Use restrictions based on sharding jdbc

Directory usage restriction JDBC does not support list DataSource interface Connection interface Statement and PreparedStatement interface for ResultSet interface JDBC 4.1 SQL statement restricts shardingjdbc usage and pit content 1. Use shardingjdbc as sub-library table 2. pit content

Use restriction

JDBC does not support lists

Sharding-JDBC does not support the infrequent JDBC method for the time being.

DataSource interface timeout related operations are not supported

Connection interface Operations of stored procedures, functions and cursors are not supported SQL that executes native is not supported savepoint related operations are not supported Operation of Schema/Catalog is not supported Custom type mappings are not supported

Statement and PreparedStatement interfaces Statements that return multiple result sets (i.e. stored procedures, non-SELECT multiple pieces of data) are not supported Internationalized character operations are not supported

For the ResultSet interface Pointer position determination for result set is not supported Changing the result pointer position by non-next method is not supported Modifying result set contents is not supported Getting internationalized characters is not supported Getting Array is not supported

JDBC 4.1 New features of JDBC 4.1 interface are not supported For all unsupported methods, read the package com. dangdang. ddframe. rdb. sharding. jdbc. unsupported.

SQL statement restriction

DDL statement is not supported Substatement not supported UNION and UNION ALL are not supported Special INSERT is not supported Only one piece of data can be inserted into each INSERT statement, and statements with multiple rows of data after VALUES are not supported DISTINCT aggregation is not supported

shardingjdbc use and pit treading content

1. Use shardingjdbc to make sub-database and sub-table

Recently, due to the business needs, the company is increasingly intolerant of the increasing amount of data, so it makes a decision to divide the database into tables. After investigating several technical schemes, it is decided to use shardingsphere as a middleware for dividing tables.

To pull an jar package using maven:

    <dependency>
       <groupId>io.shardingsphere</groupId>
       <artifactId>sharding-jdbc</artifactId>
       <version>3.0.0.M3</version>
      </dependency>
      <dependency>
       <groupId>io.shardingsphere</groupId>
       <artifactId>sharding-jdbc-spring-namespace</artifactId>
       <version>3.0.0.M3</version>
      </dependency>

Sub-table configuration:

<?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:tx="http://www.springframework.org/schema/tx"
    xmlns:sharding="http://shardingsphere.io/schema/shardingsphere/sharding"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
     http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
  http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
  http://shardingsphere.io/schema/shardingsphere/sharding
       http://shardingsphere.io/schema/shardingsphere/sharding/sharding.xsd"
    default-autowire="byName">
    
 <!--  Tabular algorithm  -->
 <bean id="tableShardingAlgorithm" class="pxf.commom.support.sharding.tableShardingAlgorithm" />
 <sharding:complex-strategy id="tableStrategy"
          sharding-columns="uid"
          algorithm-ref="tableShardingAlgorithm" />
 <!-- ds_0 It is a data source. If you do sub-library, you can configure multiple data sources ; Tables that are not divided into tables do not need to be configured here -->
 <sharding:data-source id="dataSource">
  <sharding:sharding-rule data-source-names="ds_0" default-data-source-name="ds_0" >
   <sharding:table-rules>
    <sharding:table-rule
      logic-table="test_table"
      actual-data-nodes="ds_0.test_table_$->{0..128}"
      table-strategy-ref="tableStrategy" />
   </sharding:table-rules>
  </sharding:sharding-rule>
 </sharding:data-source>
</beans>

2. Step on the pit content

1). The column used to divide the table cannot be empty in sql, so statements like insert need to be judged as non-empty;

2). The LONGVARCHER field in sqlmap can not be used, and serialization exception will be reported, which can be changed to VARCHAR type;

3). The union syntax is not supported and can be changed to an OR query (shardingjdbc is not supported even OR, so shardingsphere is recommended).