This module providesR2dbcRepository for Web Querying throw REST API and Criteria building viaDsl class. Working with PostgreSQL, MySQL, Oracle, MS SQL.

The extension of theR2dbcRepository from Spring Data R2DBC is due to the use of DSL features by all methods. Also modified integration tests to DSL features.

The Maven Central dependency instead of the libraryspring-data-r2dbc:

there included common jar withDsl andMementoPage models (it is convenient when using Reactive Feign Client):

That library produceR2dbcRepository interface with Dsl extention:

andDsl class:

• Support all predicates for PostgreSQL JSONB type, JsonNode in model automatically converted to JSONB and back

• SpEL Expression type in model automatically converted to TEXT type of PostgreSQL and back

• First level cache based on Caffeine with guided expire access and size for each cache from application.properties

• Convert first level cache to second level cache by property in application.properties:spring.r2dbc.dsl.secondCache = true then subscribe to change from database

• Support second level cache from any CacheManager implementation and may be both working with subscribe to change from database. Enabled by:spring.r2dbc.dsl.cacheManager = true

• Full manage of cache from repository methods: evict(Dsl dsl), put(Dsl dsl, T value) and get(Dsl dsl): T

• Dsl support all SQL predicates:=, >, <, >=, ⇐, like, in, not in, is true, is false, is null, is not null, also can operate with LocalDate, LocalDateTime, OffsetDateTime, ZonedDateTime types ascreatedAt>=2000-10-31T01:30.000-05:00 orcreatedAt==2000-10-31T01:30.001 orcreatedAt<<2000-10-31T01:30

• Ability to choose resulted columns infindAll(dsl: Dsl) andfindOne(dsl: Dsl) fromdsl.fields("id", "name") or in urlfields=id,name

• Joins to any tables 'tableName.type' and model must havetableNameId field and build criteria on joined columnstableName.type==space, also can select joined columns in resultfields=id,name,type

• Annotation@Id is not required in model, because column with nameid in 99% of all tables has nameid

• Annotation@Table is not required in model, because table name in 99% of all tables equals model class name, the camel name automatic convert to sql underlined

• Any reserved database words can be used by column name

• Repositorylistener method subscribe to listen of all changes of table from database in realtime overFlux<Notification> (Postgres only)

• Ranked full text search by fieldtsv as default with RUM index operator inDsl.fts method (Postgres only)

• Paging and Sorting full support with ordering in several columns at once

• Utility classFastMethodInvoker is a modern reflection access to object properties, in performance comparable to direct access.

• Utility classJsonUtils is a powerful utils to flexibility manage any json operations around JsonNode type.

• Utility classR2dbcUtils can create repository from url:R2dbcUtils.getRepository("r2dbc:postgresql://postgres:postgres@localhost/abac_rules", AbacRepository::class), also havefun saveBatch(models: Iterable<T>): Flux<Integer> method for call native Postgres driver batch operation for massive insert as one sql.

• Field with @Equality annotation on each update the value is comparing with previous value from database and throw exception is not equals, also can set in application.properties:spring.r2dbc.dsl.equality = tenantId,userId

• Field with @ReadOnly or @CreatedBy annotation on each update the value getting from previous record if not null and setting to current record without comparing, also can set in application.properties:spring.r2dbc.dsl.readOnly = tenantId,userId

• Field with namecreatedAt with typeZonedDateTime,OffsetDateTime orLocalDateTime on update using @CreatedDate annotation and @ReadOnly annotation logic, also can be set in application.properties:spring.r2dbc.dsl.createdAt = createdAt

• Field with nameupdatedAt and typeZonedDateTime,OffsetDateTime orLocalDateTime on update using @LastModifiedDate annotation logic, also can be set in application.properties:spring.r2dbc.dsl.updatedAt = updatedAt

• Field with nameversion do not required @Version annotation if it has typeLong,Integer,ZonedDateTime,OffsetDateTime orLocalDateTime for optimistic locking, also can be set in application.properties:spring.r2dbc.dsl.version = counter,innerIndex

• Fields with namecreatedBy,updatedBy or annotated by @CreatedBy, @UpdatedBy on create or update set current security userId, also fields can be set in application.properties:spring.r2dbc.dsl.createdBy = createdBy,spring.r2dbc.dsl.updatedBy = updatedBy

• ApplicationContext implementation asBeans utility class with caching resolved beans and registering new beans as ect or recreate by classname

• Utility class SqlField constants a most used sql-names in tables for no handwriting names

• Integration with Spring Security via AuthenticationIdentifierResolver Bean to get current userId

The primary idea is to reduce development time when all kinds of criteria can be formed at the frontend:

localhost:8080/items?query=!@shops.data.assign,shops.data.type==mega,name~~biggest,price>=100 & fields=id,name & page=0 & size=20 & sort=itemType:asc,createdAt:desc

then generated SQL:

select id, name from items join shops on items.shop_id = shops.id where shops.data→>'assign' is not null and shops.data→>'type'='mega' and name like '%biggest%' and price >= 100 order by item_type asc, created_at desc limit 20 offset 0

Each R2dbcRepository by default activate Caffeine cache as First Level Cache, and it is alive 500 ms. But First Level Cache can be converted to Second Level Cache with property in application.yml:

after turn on it is of the all repositories subscribed to listen database table for any changes and after receive event is evicted repository cache.

If you need registered any CacheManager to using in R2dbcRepository as Second Level Cache, then set this property:

Previous propertysecondCache can be worked both with CacheManager of in-memory database [Hazelcast, Redis].

We can manage for each R2dbcRepository internal Caffeine first level cache (also Caffeine as Second Level Cache) with custom timeouts and max size:

Examples of manage internal repository cache:

Before create universal notifier function:

and set to tables notifier by trigger:

and last in source code:

Default language may be setting in:spring.r2dbc.dsl.fts-lang

or get if nothing from:Locale.getCurrent()

or can dynamically set in Dsl class:Dsl.create().lang('English')

In table look field by default nametsv:Dsl.create().fts("web query text"), but field name can be setting in parameterDsl.create().fts("ts_vector", "web query text").

and in source code: