CODEWITHSUNDEEP
javasqlhsqldbvert.x
KilledByCHeese
KilledByCHeese

Reputation: 872

VertX SQL queryWithParams Howto use LIKE ? with wildards

I'm working on a simple VertX Application. I have a hsqlDB and I'm trying to execute a query where I want to get all IDs from the Table where the Name contains a search parameter

String sql = "SELECT ID FROM MYTABLE WHERE NAME LIKE ?";

So this works when the Name is the same as the ?
When I try to use wildcards: 

String sql = "SELECT ID FROM MYTABLE WHERE NAME LIKE %?%";

or

String sql = "SELECT ID FROM MYTABLE WHERE NAME LIKE '%?%'";

it doesn't work.
My Code:

private void getIDsBySearchString(String search, SQLConnection conn, Handler<AsyncResult<Vector<Integer>>> resultHandler) {
    String sql = "SELECT ID FROM MYTABLE WHERE NAME LIKE ?";

    conn.queryWithParams(sql, new JsonArray().add(search), asyncResult -> {
        if(asyncResult.failed()) {
            resultHandler.handle(Future.failedFuture("No Names Found"));
        } else {
            int numRows = asyncResult.result().getNumRows();
            if(numRows >= 1) {
                Vector<Integer> IDVector = new Vector<>();
                for(int i = 0; i < numRows; i++) {
                    int id = asyncResult.result().getRows().get(i).getInteger("ID");
                    IDVector.add(id);
                }
                resultHandler.handle(Future.succeededFuture(IDVector));
            } else {
                resultHandler.handle(Future.failedFuture("No Names found"));
            }
        }
    });
}

How do I need to edit my query String so the ? will be replaced by the search String and I will be able to use wildcards?

Upvotes: 0

Views: 432

Answers (2)

Alexey Soshin
Alexey Soshin

Reputation: 17731

The part that should be changed is your search parameter, not the sql part:

String sql = "SELECT ID FROM MYTABLE WHERE NAME LIKE ?";

conn.queryWithParams(sql, new JsonArray().add("%"+search+"%"), asyncResult -> { ... }

Upvotes: 1

fredt
fredt

Reputation: 24372

A parameter cannot be inside a quoted string. It can be part of a concat expression involving other strings.

String sql = "SELECT ID FROM MYTABLE WHERE NAME LIKE '%' || ? || '%'";

Upvotes: 1

Related Questions