Using JPA queries, the following statement limit fails

[ytrlmy521]

When I upgrade the compile "io.shardingsphere:sharding-jdbc-spring-boot-starter version from 3.0.0 to 3.1.0, all my previous pagination is invalid, and the data found by the limit is wrong, but if I don't upgrade the version, the same SQL will prompt syntax and the like that are not compatible with sharding, please help give a reply

使用版本：compile "io.shardingsphere:sharding-jdbc-spring-boot-starter:3.1.0"

1. The following statement: limit invalidates select * from ad_space s where (:openDate = '' OR (:openDate BETWEEN s.start_time AND s.end_time) OR (s.start_time is null and :openDate <= s.end_time) ) and s.ad_id = :adId and s.deleted = 0 order by start_time desc,end_time desc limit :page,:size

可想以下表的查询一共才有64条数据

2. In the following statement, the given value of limit takes effect ，返回数据还是64条 select * from ad_space s where (:openDate = '' OR (:openDate BETWEEN s.start_time AND s.end_time) OR (s.start_time is null and :openDate <= s.end_time) ) and s.ad_id = :adId and s.deleted = 0 order by start_time desc,end_time desc limit 100,10

3.use this version ------》compile "io.shardingsphere:sharding-jdbc-spring-boot-starter:3.0.0" limit takes effect

[strongduanmu]

Hi @ytrlmy521, can you try latest 5.3.2? 3.x is too old.

[ytrlmy521]

Hi @ytrlmy521, can you try latest 5.3.2? 3.x is too old.

ok，thank you for reply，But when I execute this simple SQL statement, an error is reported again. I did not use any keywords, and the execution on the mysql terminal is completely successful.

version：5.1.1 implementation 'org.apache.shardingsphere:shardingsphere-jdbc-core-spring-boot-starter:5.1.1'

SQL：select admaterial0_.id as id1_2_, admaterial0_.created_by as created_2_2_, admaterial0_.created_time as created_3_2_, admaterial0_.deleted as deleted4_2_, admaterial0_.deleted_time as deleted_5_2_, admaterial0_.updated_by as updated_6_2_, admaterial0_.updated_time as updated_7_2_, admaterial0_.ad_type_id as ad_type_8_2_, admaterial0_.advertiser_id as advertis9_2_, admaterial0_.android_media_url as android10_2_, admaterial0_.bit_rate as bit_rat11_2_, admaterial0_.duration as duratio12_2_, admaterial0_.full_url as full_ur13_2_, admaterial0_.horizontal_resolution as horizon14_2_, admaterial0_.ios_media_url as ios_med15_2_, admaterial0_.link as link16_2_, admaterial0_.link_param as link_pa17_2_, admaterial0_.link_type as link_ty18_2_, admaterial0_.media_fail as media_f19_2_, admaterial0_.media_file_md5 as media_f20_2_, admaterial0_.media_id as media_i21_2_, admaterial0_.media_url as media_u22_2_, admaterial0_.name as name23_2_, admaterial0_.remarks as remarks24_2_, admaterial0_.skip as skip25_2_, admaterial0_.upload_type as upload_26_2_, admaterial0_.vertical_resolution as vertica27_2_ from ad_material admaterial0_ where admaterial0_.media_fail=0 and admaterial0_.deleted=0 and (admaterial0_.media_url is null) and (admaterial0_.media_id is not null)

ERROR: Caused by: org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an error in your SQL syntax

After upgrading to 5.2.1, the execution returns the error as follows：

从官网上拷贝的配置项，无法识别，启动就报错，文档中哪里的是正确的配置。 Caused by: java.lang.IllegalStateException: No available readwrite-splitting rule configuration in database logic_db.

@strongduanmu

[sandynz]

Hi @ytrlmy521 , use version 5.3.2, it could be cleaner and simpler, refer to JDBC Driver - Spring Boot for more details.

[ytrlmy521]

Hi @ytrlmy521 , use version 5.3.2, it could be cleaner and simpler, refer to JDBC Driver - Spring Boot for more details.

thank you for replay，Can you give me an official 5.3.2 demo link, I want to configure tables and separate services for reading and writing，Please @strongduanmu @sandynz

[ytrlmy521]

Hi @ytrlmy521 , use version 5.3.2, it could be cleaner and simpler, refer to JDBC Driver - Spring Boot for more details.

thank you for replay，Can you give me an official 5.3.2 demo link, I want to configure tables and separate services for reading and writing，Please @strongduanmu @sandynz

And I want to integrate directly with springboot-start, 5.3.2 need to manually switch the data source？

[sandynz]

You could have a look at Quick Start, there's examples directory in source code.

5.3.2 need to manually switch the data source？

I think not, just define ShardingSphere data source and use it, similar with connection pool.