Do you have plan for supporting 'where' clause for find functions?

[hytgbn]

Hi, I guess this doesn't support custom "where" clauses for search feature. I guess I can only find using 'id' or I have to find all. Do you have any plan to support where clause for find? (or search?)

[sheenobu]

Similar issue and discussion here: https://github.com/nurkiewicz/spring-data-jdbc-repository/issues/12 . I can not comment on the plans, however.

[nurkiewicz]

@hytgbn True, current API is way too limited to be useful. Some sort of filtering is a must. I definitely don't want to go with reflection like Spring Data JPA (findBy...()), on the other hand trivial:

findBy(String whereClause, Pageable page)

seems too low-level (?) Any thoughts guys?

[sheenobu]

I like the idea of a 'findLike(Entity e) where e is a semi-populated object. https://gist.github.com/sheenobu/cacb919ce1830e5bed5b .

However, it introduces complications in the rowunmapper, as each implementation would require an additional requirement: don't fail or throw null even when the object isn't fully populated.

[JoeJErnst]

I have extended JdbcRepository and added the following methods:

public List<T> find(String sql, Object... args) public T findOne(String sql, Object... args) public Page<T> queryPage(String sql, Pageable page, Object... args) public Long count(String sql, Object... args)

I prefer having complete control (and understanding!) of the SQL that is sent to the database. This makes troubleshooting and performance tuning much easier.

[nurkiewicz]

@JoeJErnst as far as I understand your sql query contains ? placeholders, later to be replaced with args? It's important to separate SQL from parameters, otherwise we promote programming style enabling SQL-injection attacks.

@sheenobu I like the idea of querying by example. It's not as powerful as arbitrary SQL, but suitable in many cases. I don't think additional requirement of object behaving properly in case of null is a big deal. I guess we can implement both.

Third approach: custom matchers, e.g.:

findBy(
  and(
    eq("name", "John"),
    gt("age", 20)
    not(like("lastName", "Smi%"))),
  page)

WDYT?

[JoeJErnst]

Yes, I am using bind variables.

I like your example.

-Joe Since 1970

On Nov 23, 2014, at 11:15 AM, Tomasz Nurkiewicz notifications@github.com wrote:

@JoeJErnst as far as I understand your sql query contains ? placeholders, later to be replaced with args? It's important to separate SQL from parameters, otherwise we promote programming style enabling SQL-injection attacks.

@sheenobu I like the idea of querying by example. It's not as powerful as arbitrary SQL, but suitable in many cases. I don't think additional requirement of object behaving properly in case of null is a big deal. I guess we can implement both.

Third approach: custom matchers, e.g.:

findBy( and( eq("name", "John"), gt("age", 20) not(like("lastName", "Smi%"))), page) WDYT?

— Reply to this email directly or view it on GitHub.

[woemler]

Any update on this? Supporting dynamic WHERE statements would make this an enormously useful library, personally.

[nurkiewicz]

I don't have free time right now to work on this feature. However I understand it's valuable so I'll look into it in the future. In the meantime feel free to propose API or PoC. Thank for your interest!

[woemler]

I took a stab at the third approach to dynamic SQL generation. It's hacky, but maybe a viable PoC?

https://gist.github.com/woemler/c6f901bc1ecc3c12eb2e

Example:

SqlBuilder sqlBuilder = new SqlBuilder(tableDescription);
String sql = sqlBuilder.select("*")
  .from("users")
  .where(
    and(
      eq("category", "active"),
      notEq("name", "Joe")
    )
  )
  .groupBy("name", "address")
  .orderBy(
    new Sort(
      new Sort.Order(Sort.Direction.ASC, "name"), 
      new Sort.Order(Sort.Direction.DESC, "address")
    )
  )
  .limit(10, 50)
  .toSql();

/* 
    Should produce the string:

    SELECT FROM users 
    WHERE category = ? AND name != ? 
    GROUP BY name, address
    ORDER BY name ASC, address DESC
    LIMIT 10, 50
*/

List<Object> parameters = sqlBuilder.getQueryParameterValues;

// Should return a list with { 'active', 'Joe' }

[stepan-romankov]

I think ideal way to implement it is Specification from Spring Data Commons http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#specifications.