Search code examples
objectqueryjdbi

JDBI using @bind for variables in queries inside quotes

I'm wondering if/how this is possible, if it is, I'm sure its a simple fix that I can't seem to figure out

@SqlQuery("SELECT * FROM Table WHERE column LIKE '%:thingName%'")
public Set<Things> getThings(@Bind("thingName", String thingName)

Essentially for this toy example I am trying to select a row where a column contains [any text]thingName[anyText]. When using as above, I think the quotes obscure the bound variable so it literally looks for [any text]:thingName[anyText] and not my bound variable.

Thank you in advance, Madeline

Solution

  • It appears to be the case that you must add the '%' percentages to the bound variable:

    @SqlQuery("SELECT * FROM Table WHERE column LIKE :thingName")
public Set<Things> getThings(@Bind("thingName") String thingName); // where thingName = "%" + thingName + "%"

    See also: https://groups.google.com/forum/?fromgroups#!topic/jdbi/EwUi2jAEPdk

    Quote from Brian McCallister

    Using the :foo binding stuff creates a prepared statement and binds in the value for name in this case. You need the % to be part of the bound value, or you need to not use bindings to a prepared statement.

    • Approach 1 (the safer and generally better one): "select … from foo where name like :name" and bind the value ("%" + name)

    • Approach 2 (which opens you up to sql injection):

    "select … from foo where name like '%' " and define("name", name) (or in sql object, (@Define("name") name) -- which puts name in as a literal in your statement.

    The key thing is that the % character is part of the value you are testing against, not part of the statement.