It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

How to use LIKE operator in Active Objects query

Max Madjarov Aug 12, 2013

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

3 answers

1 accepted

4 votes
Answer accepted
Max Madjarov Aug 19, 2013

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!

2 votes
codelab expert Aug 12, 2013
You should write ...LIKE '%?%'
Max Madjarov Aug 12, 2013
Ok, it makes sense :-) i will try it out
Max Madjarov Aug 13, 2013

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)

0 votes
Jason Hinch Atlassian Team Aug 13, 2013

You need to concatenate the % with the search text:

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

Max Madjarov Aug 13, 2013

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 :-(

Fhatu Nel Apr 24, 2014

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?

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

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published in Confluence

6 Awesome Ways to Apply Trello, JIRA and Confluence to your Project

I attended  Atlassian Summit 2019  and learned a lot from the presenters, attendees and knowledgeable Atlassian product managers. The presentations I attended focused on applying Agile, pla...

907 views 6 17
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you