Announcement Announcement Module
No announcement yet.
jdbctemplate parameters date column and wildcard Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • jdbctemplate parameters date column and wildcard

    Hello there,

    i've searched but found no thread, so hoping for some pointers about something that has been biting me.

    I have a db table with a datetime column. I want to find rows that matches a given date, i.e. ignores the time part of the column ('2011-05-05 12:34:44', only match the date part).

    I usually do "SELECT from x where date like '2011-05-05%', since i havent found any better way.

    But with jdbctemplate i simply can't get it to work!

    my code is something like
    String sql = "select from x where date like ? ... "
    Date myDate = ...
    Object args = new Object[]{myDate}
    jdbcTemplate.query(sql, args, callback);
    (above is ofc. mockup code, hope you get the picture.

    I've tried with various versions of the above, setting '?' rather than ?, using arg-types with both date, string etc.

    Can't get anything to work, if anybody knows how to precisely use the %-wildcard, i'd be happy as a camper.


  • #2
    if you want a query with low performance use a like... It isn't very performant to first convert a data to a string and then do a like (that is more or less how a database handles it). With a lot of records this becomes a very slow query.

    Use a query which select the records where the date is between 0:00:00 and 23:59:59

    select * from x where date >= ? and date <= ?
    You might also want to take a look at a to_date function (although using a function in a query with a lot of data might also be not good idea).


    • #3
      Thanks for your time!

      Well yeah, that's what i'm doing now, i.e. sending in start- and enddates. I was more asking because i havent been able to figure out how to use wildcards with jdbc-templates at all...

      The date()-function (im using mysql) is hard for me to use since i'm using HSQLDB for my integration tests and date() doesn't exist there, failing my tests.


      • #4
        Now you lost me, you post a query with like (which has nothing to do with selecting a data between) and now you wonder about wildcards... Maybe ask the correct question next time.

        Simply put in a ? and pass in the arguments. You only need to make sure you pass in the correct types. A like with a date isn't going to work, ou first need a string (but as I stated you shouldn't be doing that!). Also a like '?%' the ? isn't a wildcard because it is between '' and it is followed by %...