The primary goal of Spring R2DBC iClient is to provide similar experience as Apache MyBatis in Reactive environment. There are some tweaks required to comply with Spring Data R2DBC.
Spring R2DBC iClient is totally annotation driven. Currently it does not support XML mapping unlike Apache MyBatis.
Add this as dependency into pom.xml for Maven project
<dependency> <groupId>io.github.bhautik008</groupId> <artifactId>spring-r2dbc-iclient</artifactId> <version>1.0.1</version> </dependency>
Or into build.gradle for Gradle project
implementation 'io.github.bhautik008:spring-r2dbc-iclient:1.0.1'
To start using Spring R2DBC iClient, provide following properties in application.properties or application.yml file or initialize custome ConnectionFactory bean.
spring.r2dbc.host=localhost spring.r2dbc.port=3306 spring.r2dbc.username=user spring.r2dbc.password=password spring.r2dbc.database=mydb spring.r2dbc.dbtype=mysql|postgresql|mariadb|oracle|mssql
Spring R2DBC iClient support MySql, PostgreSQL, MariaDB, Oracle and MSSql databases. Additional options can be set by spring.r2dbc.options property with "," separation and ":" for key name and value separation. As show below:
spring.r2dbc.options=useSSL:false,currentSchema:mydb
Once configs are provided or customer ConnectionFactory bean is initalized, need to provide @R2dbcMapperScanner annotation with package name to any configuration or spring boot application class, which will indicate Spring R2DBC iClient to search Mappers.
@R2dbcMapperScanner("example.mappers")Create an interface annotating with @R2dbcMapper.
@R2dbcMapper
public interface UserRepository {
// mapper method goes here
}
Use following annotations to create required SQL Statmement methods:
Annotate a method with @Select annotation providing SQL statment to execute select queries.
@Select("select * from user")
public Flux<User> getAll();
If result mapping is not provided then default return type would be Map<String, Object>.
To map SQL result to domain object, annotate a method with either @Results to provide a new mapping or with @ResultMap or resultMap property within @Select annotation to use exisiting mapping.
@Results(id = "userMap", type = User.class, value = {
@Result(property = "userId", column = "user_id", javaType = Integer.class),
@Result(property = "userName", column = "user_name", javaType = String.class),
@Result(property = "userPhone", column = "user_phone", javaType = String.class),
@Result(property = "userAddress", javaType = UserAddress.class, resultMap = "userAddressMap") })
@Select("select * from user")
public Flux<User> getAll();
@Results(id = "userAddressMap", type = UserAddress.class, value = {
@Result(property = "userAddress", column = "user_address", javaType = String.class),
@Result(property = "userCity", column = "user_city", javaType = String.class),
@Result(property = "userState", column = "user_state", javaType = String.class) })
@Select("select user_address, user_city, user_state from user")
public Flux<UserAddress> getUserAddress();
Method can be annotated with more than one @Results annotation.
@Results(id = "userMap", type = User.class, value = {
@Result(property = "userId", column = "user_id", javaType = Integer.class),
@Result(property = "userName", column = "user_name", javaType = String.class),
@Result(property = "userPhone", column = "user_phone", javaType = String.class),
@Result(property = "userAddress", javaType = UserAddress.class, resultMap = "userAddressMap") })
@Results(id = "userAddressMap", type = UserAddress.class, value = {
@Result(property = "userAddress", column = "user_address", javaType = String.class),
@Result(property = "userCity", column = "user_city", javaType = String.class),
@Result(property = "userState", column = "user_state", javaType = String.class) })
@Select(value = "select * from user", resultMap = "userMap")
public Flux<User> getAll();
As per above example, if more than one @Results annotations are defined then @Select annotation requires result map ID in resultMap property to map SQL records. resultMap property can have ID from defined @Results at same method or defined at different method.
As show above mapping, @Result requires property which will refer to domain object property, column refers to SQL result column and javaType refers to domain object property data type.
If domain property is another domain object then provide existing resultMap ID and no column requires.
If query requires a parameter then following way paramters can be passed:
@ResultMap("userMap")
@Select("select * from user where user_id = :userId")
public Mono<User> getUserById(@Param("userId") Integer userId);
@Param annotation is required when passing parameter to SQL statement. Refer Note #1 for propertyMapper
Annotate a method with @Insert annotation providing SQL statment to execute insert queries.
@Insert(value = "insert into user (user_name, user_phone, user_address, user_city, user_state) values (:user.userName, :user.userPhone, :user.userAddress.userAddress, :user.userAddress.userCity, :user.userAddress.userState)",
propertyMapper = {
@PropertyMapper(javaType = String.class, properties = "user.userPhone, user.userName, user.userAddress.userAddress, user.userAddress.userCity, user.userAddress.userState") },
retrieveId = "user_id",
idType = Integer.class)
public Mono<Integer> insertUser(@Param("user") User user);
** Refer Note #1 for propertyMapper**
retrieveId is optional property. Provide SQL result column name to get value as return of SQL method call. If no retrieveId provided then default return would be number of records affected by execution of statement.
idType is required when defining retrieveId. It will hold java data type in value must be returned. If idType provided then return type would by Mono<idType> else Mono.
Annotate a method with @Update annotation providing SQL statment to execute update queries.
@Update(value = "update user set user_name = :user.userName where user_id = :user.userId",
propertyMapper = {
@PropertyMapper(javaType = String.class, properties = "user.userName"),
@PropertyMapper(javaType = Integer.class, properties = "user.userId") })
public Mono updateUserName(@Param("user") User user);
** Refer Note #1 for propertyMapper**
Update statement will always return Mono providing number of records affected by executing statement.
Annotate a method with @Delete annotation providing SQL statment to execute delete queries.
@Delete(value = "delete from user where user_id = :user.userId",
propertyMapper = {
@PropertyMapper(javaType = Integer.class, properties = "user.userId") })
public Mono deleteUser(@Param("user") User user);
** Refer Note #1 for propertyMapper**
Delete statement will always return Mono providing number of records affected by executing statement.
TypeConverter interface is used to convert SQL result into different java data type or execute certain code before mapping column value to java property.
TypeConverter can be applied to mapping following way. javaType is require for returning resulting value into provided data type.
@Results(id = "userAddressMap", type = UserAddress.class, value = {
@Result(property = "userAddress", column = "user_address", javaType = String.class),
@Result(property = "userCity", column = "user_city", javaType = String.class),
@Result(property = "userState", column = "user_state", javaType = String.class),
@Result(property = "userFullAddress", javaType = String.class, typeConverter = AddressCombiner.class})
@Select("select user_address, user_city, user_state, user_zip from user")
public Flux<UserAddress> getUserAddress();
TypeConverter can be used following way to change data type of column value to java data type
import org.reactive.r2dbc.iclient.type.TypeConverter;
import io.r2dbc.spi.Row;
import io.r2dbc.spi.RowMetadata;
public class StringTypeConverter implements TypeConverter{
@Override
public String convert(Row row, RowMetadata rowMetadata) {
return String.valueOf(row.get("user_id", Integer.class));
}
}
io.r2dbc.spi.Row will hold each SQL result record with column name and value mapping. Single or all column values can be accessed here to apply code.
Use TypeConverter to execute code logic before returning value
import org.reactive.r2dbc.iclient.type.TypeConverter;
import io.r2dbc.spi.Row;
import io.r2dbc.spi.RowMetadata;
public class AddressCombiner implements TypeConverter{
@Override
public String convert(Row row, RowMetadata rowMetadata) {
return String.valueOf(row.get("user_address", Integer.class))
.concat(", ")
.concat(String.valueOf(row.get("user_city", Integer.class)))
.concat(", ")
.concat(String.valueOf(row.get("user_state", Integer.class)))
.concat(" - ")
.concat(String.valueOf(row.get("user_zip", Integer.class)));
}
}
1] Why to provide propertyMapper to SELECT|INSERT|UPDATE|DELETE annotation while passing parameters
Unlike MyBatis, propertyMapper is required when passing paramter as paramter value can be null. Please read Spring R2DBC DatabaseClient.BindSpec documentation. As Spring R2DBC iClient uses DatabaseClient to communicate with database, it needs propertyMapper with possible properties and their java data type listing to parse a query with parameters.
@PropertyMapper requires javaType which is java data type and properties having list of parameters or if parameter is domain object then list of properties that domain object having this java data type.