How to use LIKE operator in Active Objects query

[Max Madjarov]

Hallo :-)

i would like to fetch all records in DB that have similar titles with LIKE operator. My query looks like:

import com.atlassian.activeobjects.external.ActiveObjects;
...
private final ActiveObjects ao;
...
int queryResult0 = ao.find(CategoryEntity.class,
                Query.select().where("CATEGORY_TITLE LIKE ?", categoryTitle)).length;

but i allways get an empty set!!! only if the category title exactly matches, like MyTitle -> MyTitle (TRUE) i get some results if not, like MyTitle - mytitle (FALSE) i get no results!

Please help :-)

Thanks

Max

[Max Madjarov · Accepted solution]

it was very easy... CATEGORY_TITLE and "query" string should be casted to lower or upper case:

int queryResult0 = ao.find(CategoryEntity.class,
                Query.select().where("LOWER(CATEGORY_TITLE) LIKE LOWER(?)", "%" + categoryTitle + "%")).length;

because like- query is case sensetive and you should have two strings in same, lower or upper case to compare....

Thanks!

[codelab expert]

You should write ...LIKE '%?%'

[Max Madjarov]

Ok, it makes sense :-) i will try it out

[Max Madjarov]

i tried it out and my query looks like this one:

int queryResult0 = ao.find(CategoryEntity.class,
                Query.select().where("CATEGORY_TITLE LIKE '%?%'", categoryTitle)).length;

but i got exception:

categoryTitle : management
caused by: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
  - name:HSQL Database Engine
  - version:2.2.4
  - minor version:2
  - major version:2
Driver:
  - name:HSQL Database Engine Driver
  - version:2.2.4

java.sql.SQLException: Invalid argument in JDBC call: parameter index out of range: 1
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:165)
caused by: java.sql.SQLException: Invalid argument in JDBC call: parameter index out of range: 1
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
caused by: org.hsqldb.HsqlException: Invalid argument in JDBC call: parameter index out of range: 1
at org.hsqldb.error.Error.error(Unknown Source)

[jhinch (Atlassian)]

You need to concatenate the % with the search text:

int num = ao.count(CategoryEntity.class,
  Query.select()
    .where("CATEGORY_TITLE LIKE ?",
      "%" + categoryTitle + "%"));

[Max Madjarov]

Unfortunately this does not work too. I have adapted the query to:

int queryResult1 = ao.count(CategoryEntity.class,
   Query.select().where("CATEGORY_TITLE LIKE ?",        
   "%"+categoryTitle+"%"));

i have one record in db: Management and i try to test the query and find management or mAnagement but i get allways an empty set back :-(

[FhatuN]

Hi guys,

I have a similar problem too. I implemented Max solution it works inMemory database but when using PostgreSql does not work. Is there a work around for PostGreSql when we come to compare without checking the case?

[Deleted user]

Hi,

I am also facing the above issue with PostgreSQL, LOWER(NAME) is not working as part of the WHERE clause. It is throwing the below exception. Does anyone have the solution for this issue (case-insensitive search in PostgreSQL database)?

com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
 - name:PostgreSQL
 - version:9.6.9
 - minor version:6
 - major version:9
Driver:
 - name:PostgreSQL Native Driver
 - version:PostgreSQL 9.4.1212
org.postgresql.util.PSQLException: ERROR: column "name" does not exist

[Sascha Novakovic]

Hi,

case-insensitive search with PostgresQL should work with column name quoted, like LOWER("NAME").

Best regards
Sascha