spring-data-relational
spring-data-relational copied to clipboard
spring-projects
Support dynamic SQL provider [DATAJDBC-319]
Sanghyuk Jung opened DATAJDBC-319 and commented
In MyBatis, Dynamic sql is supported by @SelectProvider
http://kamalmeet.com/java/mybatis-using-selectprovider-and-resultmap/ ( updated link)
It would be more convenient if a similar feature is in Spring Data JDBC
No further details from DATAJDBC-319
Jens Schauder commented
If I understand correctly you want an API for generating SQL dynamically. While this is perfectly reasonable Spring Data JDBC won't offer that.
The reason is that there are already many options for this available:
- QueryDSL
- jOOQ
- MyBatis
And you can integrate with those technologies using custom methods. For jOOQ there is even an example
So if this is what you want I'll close the issue. If it is anything else, please clarify
Sanghyuk Jung commented
Here's the API I imagined.
public interface ProductRepository extends CrudRepository<Product, String> {
@SelectProvider(type=ProductSql.class, method="select")
@Query(rowMapper=ProducMapper.class)
List<Product> findByName(@Param("name") String name);
}
public class ProductSql {
public String select(String name) {
String sql = "SELECT id name FROM product";
if (name !=null ) {
return sql + " WHERE name = :name";
}
return sql;
}
}
In this code, the attribute of new annotation(@SelectProvider) references a method to create dynamic SQL.
I understand that there is a way as shown in the example of JooqRepositoryImpl .
It would be more convenient if I do it without adding a custom interface and an implementation class
Jens Schauder commented
Interesting.
So basically one would provide a SqlProvider but parameter binding, execution and ResultSet-conversion would be left to Spring Data JDBC, right?
How would Spring Data JDBC determine the parameters that actually need binding?
I'm currently not at all convinced that this feature is worth its weight since it basically seems to save just a single interface and costs an additional annotation, compared to a custom method.
Sanghyuk Jung commented
As I mentioned, MyBatis supports a similar feature, it can be a reference.
Following examples are from http://www.mybatis.org/mybatis-3/java-api.html .
@SelectProvider(type = UserSqlBuilder.class, method = "buildGetUsersByName")
List<User> getUsersByName(
@Param("name") String name, @Param("orderByColumn") String orderByColumn);
class UserSqlBuilder {
// If not use @Param, you should be define same arguments with mapper method
public static String buildGetUsersByName(
final String name, final String orderByColumn) {
return new SQL(){{
SELECT("*");
FROM("users");
WHERE("name like #{name} || '%'");
ORDER_BY(orderByColumn);
}}.toString();
}
// If use @Param, you can define only arguments to be used
public static String buildGetUsersByName(@Param("orderByColumn") final String orderByColumn) {
return new SQL(){{
SELECT("*");
FROM("users");
WHERE("name like #{name} || '%'");
ORDER_BY(orderByColumn);
}}.toString();
}
}
The implementation of MyBatis is at https://github.com/mybatis/mybatis-3/blob/master/src/main/java/org/apache/ibatis/builder/annotation/ProviderSqlSource.java#L106
The ways of MyBatis has the disadvantage of weak inspection at the time of compilation.
An alternative is to define a stricter interface , as shown below.
// This interface is assumed to be provided by Spring Data JDBC.
public interface QueryProvider {
String getQuery(SqlParameterSource source);
default boolean isNotNull(SqlParameterSource source, String paramName){
return source.hasValue(paramName) && (source.getValue(paramName) != null);
}
// Another helper methods can be added as default methods, for example 'isNotEmpty()'
}
// An implementation class of QueryProvider by the framework user
public class ProductQueryProvider implements QueryProvider {
@Override
public String getQuery(SqlParameterSource source) {
String sql = "SELECT id name FROM product";
if (isNotNull(source, "name")) {
return sql + " WHERE name = :name";
}
return sql;
}
}
// The QueryProvider's implementation class is specified in the Repository interface, similar to the RowMapper's implementation class.
public interface ProductRepository extends CrudRepository<Product, String> {
@Query(rowMapper=ProducMapper.class, queryProvider = productSelectProvider.class)
List<Product> findByName(@Param("name") String name);
}
When infrastructure for semantic SQL generation ( https://jira.spring.io/browse/DATAJDBC-309) is merged in the future, this feature will create a larger synergy.
If the specifications are confirmed here, I am willing to send pull request
Recently, I provide a criteria extension, which can implement the feature of dynamic sql. https://github.com/holmofy/spring-data-jdbc-criteria
It is similar to the Spring Data JPA Specification.
default Page<User> searchByQuery(UserQuery query, Pageable pageable) {
return findAll(Criteria.from(eq(User_.province, query.province))
.and(eq(User_.city, query.city))
.and(like(User_.area, query.area))
.and(like(User_.name, query.nick))
.and(between(User_.created, query.createFrom, query.createTo))
, pageable);
}
