How to use IN Clause in active objects

VikasBasra April 18, 2014

import com.atlassian.activeobjects.external.ActiveObjects;

How do i use paramterized IN CLAUSE in WERE QUERY.

String releseIds="";

Query query = Query.select();

query = query.where("JIRA_PROJECT_KEY=?", projectKey);

query = query.where("JIRA_RELEASE_ID IN ?", releseIds);

List<InnotasMap> prjList = newArrayList(ao.find(ObjMap.class,query));

5 answers

1 accepted

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

1 vote
Answer accepted
VikasBasra April 18, 2014

I got the soluton. However for this we need to add the following in POM to use the google collectons guava library.

<dependency>

<groupId>com.google.guava</groupId>

<artifactId>guava</artifactId>

<version>16.0.1</version>

</dependency>

*****************CODE*************************************

String strMappedProjectID = "123,123123,12312321,12312312"

Iterable<String> matchValues=Splitter.on(',').split(strMappedProjectID);

String placeholderCommaList = Joiner.on(", ").join(

Iterables.transform(matchValues, Functions.constant("?")));

Object[] matchValuesArray = Iterables.toArray(matchValues, Object.class);

Query query = Query.select();

if (ProjectIDs != null) query = query.where("PROJECT_ID IN (" + placeholderCommaList + ")", matchValuesArray);

Steve Kling June 9, 2016

In case anyone comes across this and can't get it working.  it DOES work. (thank you Vikas) however you'll need to trim out any spaces in any comma-delimited string list before you perform splitter on your strMappedProjectID

0 votes
VikasBasra April 18, 2014

Hi Rafael,

if i use the array it throws an exception. And if i pass as plain string it gives no result.

public Map&lt;String,Object&gt;  getInnotasReleaseMapping(String projectKey, String[] releseIds){
		Query query = Query.select();
		query = query.where("JIRA_PROJECT_KEY=?", projectKey);
		query = query.where("JIRA_RELEASE_ID IN (?)", releseIds);
		//System.out.println("list of release id's: "+ releseIds);
		List&lt;InnotasMap&gt; prjList = newArrayList(ao.find(InnotasMap.class,query));
		System.out.println("Size of the mapping data found : "+prjList.size());
		Map&lt;String, Object&gt; prjMap = new TreeMap&lt;String, Object&gt;();
		for(InnotasMap prj : prjList) {		
			prjMap.put(prj.getJiraReleaseId(), prj);
			System.out.println("Innotas Project Name: "+prj.getJiraProjectKey());			
		}
	return prjMap;

Caused by: java.lang.ArrayIndexOutOfBoundsException: 2

Thanks,

Vikas


0 votes
VikasBasra April 18, 2014

Hi Rafael,

if i use the array it throws an exception. And if i pass as plain string it gives no result.

public Map&lt;String,Object&gt;  getInnotasReleaseMapping(String projectKey, String[] releseIds){
		Query query = Query.select();
		query = query.where("JIRA_PROJECT_KEY=?", projectKey);
		query = query.where("JIRA_RELEASE_ID IN (?)", releseIds);
		//System.out.println("list of release id's: "+ releseIds);
		List&lt;InnotasMap&gt; prjList = newArrayList(ao.find(InnotasMap.class,query));
		System.out.println("Size of the mapping data found : "+prjList.size());
		Map&lt;String, Object&gt; prjMap = new TreeMap&lt;String, Object&gt;();
		for(InnotasMap prj : prjList) {		
			prjMap.put(prj.getJiraReleaseId(), prj);
			System.out.println("Innotas Project Name: "+prj.getJiraProjectKey());			
		}
	return prjMap;

Caused by: java.lang.ArrayIndexOutOfBoundsException: 2

Thanks,

Vikas


0 votes
VikasBasra April 18, 2014

Hi rafael,

Do you mean to try this as is? Variable releseIds contains values in comma seperated like 132,123,4123

You mean

query = query.where("JIRA_RELEASE_ID IN (?)", releseIds);

@paramArray releseIds;

??

Thanks

Vikas

rsperafico
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
April 18, 2014

Hi Vikas,

Yes, please.

This is what I would suggest you on doing:

  1. Connect to your database and get the 'jira_release_id' from the table you are querying against. As a result you would have id: 132, 123, 4123.
  2. Once you have does ID, replace 'releseIds' within those values (e.g.: 132, 123, 4123).
  3. In case the above does not work, pass on the values as an Array.

Please enable DEBUG to be able to look the running query.

Kind regards,
Rafael

0 votes
rsperafico
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
April 18, 2014

Hi Vikas,

You are able to change the log settings to print out the query you are running. Please, visit Getting Startted with Active Objects and search for "Step 14. Adding some logging (Optional)" for further information.

Would you mind try the following, please:

query = query.where("JIRA_RELEASE_ID IN (?)", releseIds); // @param Array releseIds

Kind regards,
Rafael

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

TAGS
AUG Leaders

Atlassian Community Events