how do i form the query using java

Hi Experts,
Can some one guide the below coding issues.

Application ui side
5 check boxes are there and on test area
user can select any of the check box he can search for data
5 check boxes are title,descprition,bkid,keywords,source-->ALL means (all 5 will be checked)
user can select all or user can select multiple check boxes      

CONTROLLER:
@Requestmapping
getBKEDetails(String title,String descritption,String bkid,String keywords,String source,String customid,int pagenum,int norecords)
{
//HERE I HAVE CREATED MAP TO PASS ALL THE PARAMETERS IF I DIDN'T CREATE MAP MY METHOD PARAMETER IS TOO LONG
//IS THIS APPROACH IS CORRECT IS THERE ANY OTHER ALTERNATIVE FOR THIS?
Map<String,String> searcMap =new HashMap();
searcMap.put("title",title);
searcMap.put("description",description)
searcMap.put("bkid",bkid)
searcMap.put("keywords",keywords)
searcMap.put("source",source)
serviceImple.getBKEInfo(searchmap,customid,pagenum,norecords);
}

SERVICE:
getBKEInfo(Map<String,String> searcMap,String customid,Integer pagenum,Integer noOfRecords){
daoImpl.getBKEDetailsFromDAO(searchmap,customid,pagenum,norecords);
}

DAOIMPL:
getBKEDetailsFromDAO(Map<String,String> searcMap,String customid,Integer pagenum,Integer noOfRecords){

			StringBuilder stringBuilder = new StringBuilder();  
            stringBuilder.append("SELECT t.bkId, bke.uid, bke.description, bke.title, bke.createdTime, bke.ownerId "
                    + " FROM bkEntry as bke " + " LEFT OUTER JOIN bke.bkEntryAttributes as t "
                    + " LEFT OUTER JOIN bke.bkEntrySource as bkes with (bkes.ticketId=0) WHERE");
            if(searchMap.get("title") != null){
                stringBuilder.append("((bke.title like '%" + searchMap.get("title") + "%'");
            }
            if(searchMap.get("description") != null){
                stringBuilder.append(" OR bke.description like '%" + searchMap.get("description") + "%'");
            }
            if(searchMap.get("bkid") != null){
                stringBuilder.append(" OR bke.bkid like '%" + searchMap.get("bkid") + "%'");
            }
            if(searchMap.get("keywords") != null){
                stringBuilder.append(" OR bke.keywords like '%" + searchMap.get("keywords") + "%'");
            }
            if(searchMap.get("source") != null){
                stringBuilder.append(" OR bke.source like '%" + searchMap.get("source") + "%'");
            }            
            stringBuilder.append(" AND (bke.customerId IN (" + customerIds + ") and bke.status IN (2,5)))) GROUP BY t.bkId ");
            queryToGetbk = stringBuilder.toString();
			List<Object> bkeDetails = super.findByHql(queryToGetbk,pagenum,noOFRecords);
		}

Open in new window


INPUTS:
title,descprition,bkid,keywords,source all are not a mandatory fields,
any of the input will come from the use selection
user can search with title or description or ids or keywords,

From the above query i have to form a proper query


my above query works only when user selects all the fields--title,descprition,bkid,keywords,source
Let suppose if the title is not a mandatory field
it will starts from description except title rest will start with or condition
so after where condition it should not be or conditon how can i avoid orcondition here

Let suppose
use select bkid,source
after where condition
it should be like ----> stringBuilder.append(" bke.keywords like '%" + searchMap.get("keywords") + "%'");
                                    stringBuilder.append(" OR bke.source like '%" + searchMap.get("source") + "%'");      
                                    
if user select only description--->stringBuilder.append(" bke.description like '%" + searchMap.get("description") + "%'");

can some one suggest how to do

Thanks,
LVL 2
srikoteshAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gurpsbassiCommented:
How about this:
 final StringBuilder stringBuilder = new StringBuilder();
        stringBuilder
        .append("SELECT t.bkId, bke.uid, bke.description, bke.title, bke.createdTime, bke.ownerId "
                + " FROM bkEntry as bke "
                + " LEFT OUTER JOIN bke.bkEntryAttributes as t "
                + " LEFT OUTER JOIN bke.bkEntrySource as bkes with (bkes.ticketId=0)");

        if (searchMap.size() > 0) {
            stringBuilder.append(" WHERE ");
        }

        final Iterator<Entry<String, String>> iterator = searchMap.entrySet().iterator();
        while (iterator.hasNext()) {
            final Entry<String, String> entry = iterator.next();
            stringBuilder.append(" bke." + entry.getKey() + " like '%" + entry.getValue() + "%'");
            if (iterator.hasNext()) {
                stringBuilder.append(" OR ");
            }
        }

Open in new window


Note, I've put the "WHERE" clause on a seperate line since it is possible none of the checkboxes are selected.
srikoteshAuthor Commented:
Hi gurpsbassi,

thanks a lot.
In controller class I am putting all the values to map even it is null also for some fields.
Is that fine or need to modify?
gurpsbassiCommented:
It will be fine.
Test it and see
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

srikoteshAuthor Commented:
Hi gurpsbasi,

I am getting the expected output.

I have one doubt when i gave title =Test --->then rest of the four fields or check box  will be null values
in my query it is showing like:
title like '%Test% OR description like%null% OR keywords%null%
will it cause any issues in future.
if possible how to avoid this null in query?

Thanks,
gurpsbassiCommented:
ok slight change:

final Iterator<Entry<String, String>> iterator = searchMap.entrySet().iterator();
        String logicalOperator = "";
        while (iterator.hasNext()) {
            final Entry<String, String> entry = iterator.next();
            if(entry.getValue() != null){
                stringBuilder.append(logicalOperator).append(" bke." + entry.getKey() + " like '%" + entry.getValue() + "%'");
                logicalOperator = " OR "; // second and subsequence iterations
                // will begin with 'OR' operator
            }

        }

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
srikoteshAuthor Commented:
Excellent
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.