JDBI using @bind for variables in queries inside quotes

Question

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

Walling

You should change the accepted answer to the concatenation solution. There is a way to do this without making code changes that may introduce a bug. – Roof

Answer 1

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.

Gullah

Thank you for your reply! This is what I had done, I was hoping there was a better way out there but it seems not. - Madeline – Walling

The answer below using a concatenation operation is a better answer as it doesn't require logical changes. If you're using postgres, you can do `'%' || :term || '%' – Roof

Answer 2

I use concat to surround input with % signs while still using a bound variable to avoid SQL injection:

@SqlQuery("select * from atable where acolumn like concat('%',:thingName,'%')")
public Set getNames(@Bind("thingName") String thingName);

Thuggee

Answer 3

This must be the case with LIKE while binding variables. As per JDBI doc(Here) using SQL query concatenation can solve the issue. It worked for me.

Treatment