Support dynamic SQL provider [DATAJDBC-319]

[spring-projects-issues]

Sanghyuk Jung opened DATAJDBC-319 and commented

In MyBatis, Dynamic sql is supported by @SelectProvider

http:https://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

[spring-projects-issues]

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

[spring-projects-issues]

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

[spring-projects-issues]

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.

[spring-projects-issues]

Sanghyuk Jung commented

As I mentioned, MyBatis supports a similar feature, it can be a reference.

Following examples are from http:https://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

[chenjianjx]

+1

[rishiraj88]

+1

[artem-emelin]

+1

[holmofy]

+1

[hebaceous]

+1

[holmofy]

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);
}

[xiaofuliang]

+1