Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support dynamic SQL provider [DATAJDBC-319] #542

Open
spring-projects-issues opened this issue Jan 21, 2019 · 8 comments
Open

Support dynamic SQL provider [DATAJDBC-319] #542

spring-projects-issues opened this issue Jan 21, 2019 · 8 comments
Assignees
Labels
in: repository type: enhancement

Comments

@spring-projects-issues
Copy link

@spring-projects-issues spring-projects-issues commented Jan 21, 2019

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

@spring-projects-issues
Copy link
Author

@spring-projects-issues spring-projects-issues commented Jan 21, 2019

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
Copy link
Author

@spring-projects-issues spring-projects-issues commented Jan 21, 2019

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
Copy link
Author

@spring-projects-issues spring-projects-issues commented Jan 21, 2019

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
Copy link
Author

@spring-projects-issues spring-projects-issues commented Mar 2, 2019

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

@chenjianjx
Copy link

@chenjianjx chenjianjx commented Nov 3, 2021

+1

1 similar comment
@rishiraj88
Copy link

@rishiraj88 rishiraj88 commented Nov 3, 2021

+1

mp911de pushed a commit that referenced this issue Feb 21, 2022
mp911de pushed a commit that referenced this issue Feb 21, 2022
mp911de pushed a commit that referenced this issue Feb 21, 2022
mp911de pushed a commit that referenced this issue Feb 21, 2022
mp911de pushed a commit that referenced this issue Feb 21, 2022
@artem-emelin
Copy link

@artem-emelin artem-emelin commented Mar 14, 2022

+1

1 similar comment
@holmofy
Copy link

@holmofy holmofy commented Apr 14, 2022

+1

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
in: repository type: enhancement
Projects
None yet
Development

No branches or pull requests

6 participants