Friday, July 27, 2012

ADF Model: Generating and using 'in' clause in VO sql statement

We know how to create view criteria declartively, execute it programatically and use the query results as needed. But, creating a view criteria that uses 'in' clause is not possible declaratively. So, here we'll see how to form a query criteria that uses 'in' clause and also meets the performance standards.

Here, we'll see how to to form a query statement to use list of values using SQL 'in' clause.

Requirement: For example, we have a list of employee nos and we need to form an SQL like 'select * from emp where empno in (empno1, empno2, empno3, and so on)'. Here, we should be able to form a query that can accept 'n' (where 'n' can be dynamic) no. of employee nos and use bind variables instead of hard coded the query.

Solution: We don't have declarative way of forming query using 'in' clause. So, we have to do it programatically.

For e.g., if the Empno list has 4 employee ids, we have to form the query like

select * from emp where empno in (:empno1,:empno2,:empno3,:empno4)
OR
select * from emp where empno in (:1,:2,:3,:4)

In the above SQL stmts, the first one uses the named bind parameters while the second one uses positional parameters.

To achieve the above requirement, generate the 'in' clause programatically using the following methods.

Forming 'in' clause with named bind parameters:
private String getInClauseWithParamNames(List ids) { //logic to form the in clause with multiple bind variables StringBuffer inClause = new StringBuffer(); for (int i = 1; i < ids.size() + 1; i++) { inClause.append(":empno" + (i)); if (i < ids.size()) { inClause.append(","); } } return inClause.toString(); }

Forming 'in' clause with positional bind parameters:
private String getInClause(List ids) { //logic to form the in clause with multiple bind variables StringBuffer inClause = new StringBuffer(); for (int i = 1; i < ids.size() + 1; i++) { inClause.append(":" + (i)); if (i < ids.size()) { inClause.append(","); } } return inClause.toString(); }

Use the generated 'in' clause with dynamic bind variables in the SQL stment and set the where clause programatically with vo.setWhereClause() method. Now, pass values for the bind variables progamatically and execute the query. Sample code is given below:

Using 'in' clause with named bind parameters:
public Row[] getEmployees1(List empIds) { ViewObjectImpl empVO = this.getEmpVO(); String inClause = getInClauseWithParamNames(empIds); //setting the where cluase to use the generated in clause empVO.setWhereClause("EmpEO.EMPNO in (" + inClause + ")"); //clearing all existing where clause params if any empVO.setWhereClauseParams(null); //setting values for all bind variables one by one in the in clause for (int i = 0; i < empIds.size(); i++) { //defining the named bind variables programatically empVO.defineNamedWhereClauseParam("empno" + (i + 1), null, null); //setting the value for each named bind variable empVO.setNamedWhereClauseParam("empno" + (i + 1), empIds.get(i)); } empVO.setRangeSize(-1); //executing the query empVO.executeQuery(); //returning the rows from query result return empVO.getAllRowsInRange(); }

Using 'in' clause with positional bind parameters:
public Row[] getEmployees(List empIds) { ViewObjectImpl empVO = this.getEmpVO(); String inClause = getInClause(empIds); //setting the where cluase to use the generated in clause empVO.setWhereClause("EmpEO.EMPNO in (" + inClause + ")"); //clearing all existing where clause params if any empVO.setWhereClauseParams(null); //setting values for all bind variables one by one in the in clause for (int i = 0; i < empIds.size(); i++) { //setting the value for each positional bind variable empVO.setWhereClauseParam(i, empIds.get(i)); } empVO.setRangeSize(-1); //executing the query empVO.executeQuery(); //returning the resultant rows return empVO.getAllRowsInRange(); }

Sample method that forms list of empnos, calls the above methods, gets the required results and prints the results:
public void sampleMethod() { //Forming a list of employee ids List<Long> empIds = new ArrayList<Long>(); empIds.add(new Long(7499)); empIds.add(new Long(7521)); empIds.add(new Long(7566)); empIds.add(new Long(7654)); empIds.add(new Long(7698)); empIds.add(new Long(7788)); //Get employee rows from list of empIds //1. Using positional parameters //Row[] empRows = getEmployees(empIds); //2. Using named bind parameters Row[] empRows = getEmployees1(empIds); //iterating through the employee rows and printing the emp name for (int i = 0; i < empRows.length; i++) { Row empRow = empRows[i]; System.out.println("Emp Name " + (i + 1) + ": " + empRow.getAttribute("Ename")); } }

The above code is self-explanatory. You can download the sample application from here. Once downloaded, run/debug the DemoAM and execute the sampleMethod. You'll get the following result:

If you look at the log window, you can see the SQL query statements generated as below at runtime.

Generated SQL stmt with named bind variables:


Generated SQL stmt with positional bind variables:

This query statement uses bind variables instead of hard coded the statement and results in a prepared statement at runtime. Hence, the stmt will be compiled only once and the same will be reused for multiple calls. So, this is the most performant way of generating and executing the SQL programatically.

58 comments:

  1. When I tried the similar logic I get the following exception

    Caused by: java.sql.SQLException: Attempt to set a parameter name that does not occur in the SQL: seqNum1

    ReplyDelete
  2. This is a bad solution because it could increase the memory of cache in the database server depending of number of elements in the array. ex. :

    select * from emp where empno in (:empno1,:empno2,:empno3,:empno4) ;
    select * from emp where empno in (:empno1,:empno2) ;
    select * from emp where empno in (:empno1) ;
    select * from emp where empno in (:empno1,:empno2,:empno3,:empno4, :empno5)

    ReplyDelete
  3. Great Information admin thanks For Your Information and Any body wants

    learn Oracle ADF through Online for Details Please go through the Link


    Oracle ADF Online Training with real time projects in INDIA | BRAZIL | UK | CANADA


    This Will Helps you aalot.

    ReplyDelete
  4. Lead online training is a brand and providing quality online to students in world wide. We are giving best online training on ORACLE ADF .Every faculty has Real Time experience .Trained Resources placed in countries like usa, uk, Canada, Malaysia, Australia, India, Singapore etc. lead online training classes are conducted every day. Weekend trainings for job goers, flexible timings in accordance with the resource comfort ability.

    Oracle adf Online Training

    ReplyDelete
  5. Thanks for the information It Hub Online Training provides Oracle ADF Online training
    http://www.ithubonlinetraining.com/oracle-adf-online-training/

    ReplyDelete
  6. Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly, but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing..
    SAP APO Online Training

    ReplyDelete
  7. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Thanks for such post and please keep it up. Oracle ADF online Training

    ReplyDelete
  8. Helpful info. Lucky me I found your site accidentally, and I'm surprised why this coincidence didn't happened in advance! I bookmarked it. Click on Servers Valley for the best hosting service in UK & all around the world.

    ReplyDelete
  9. I think mimicking popular posts on other blogs is one of the best ways to get a good idea which will be popular.Such a lovely blog you have shared here with us. Really nice.
    -------------------
    Whitecard NSW

    ReplyDelete
  10. Excellent post, some great resources. Styling your blog the right way is key. This information is impressive.
    SAP REFX Online Training in hyderabad

    ReplyDelete
  11. thanks for shared wonderful information of giving best information.its more useful and more helpful. great doing keep sharing
    hadoop training in Chennai

    ReplyDelete
  12. Thanks for sharing this valuable information to our vision. You have posted a trust worthy
    SAS Training In Hyderabad

    ReplyDelete
  13. Thanks for your valuable information,
    I am also searching for this kind of useful information; This information is very useful to me and who are searching for the oracle ADF online training.

    ReplyDelete
  14. Well said ,you have furnished the right information that will be useful to anyone at all time.Thanks for sharing your Ideas.
    hadoop online training

    ReplyDelete
  15. MSBI that provides the virtualization confirmation, and viewed as the pioneer advancements and driving supplier of the virtualization items and answers for t IT industry and organization.| https://www.gangboard.com/business-intelligence-training/msbi-training
    https://www.gangboard.com/app-programming-scripting-training/chef-training

    ReplyDelete
  16. Thank you for sharing this useful story.
    I think your shared information is helpful to me and who are want update their knowledge, who want to started their career with Oracle ADF Online Training.

    ReplyDelete
  17. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Oracle ADF TECHNOLOGY , kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor-led training on TECHNOLOGY. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ pieces of training in India, USA, UK, Australia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Pratik Shekhar
    MaxMunus
    E-mail: pratik@maxmunus.com
    Ph:(0) +91 9066268701
    http://www.maxmunus.com/

    ReplyDelete
  18. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Oracle ADF.kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on Oracle ADF. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.

    For Free Demo Contact us:
    Name : Arunkumar U
    Email : arun@maxmunus.com
    Skype id: training_maxmunus
    Contact No.-+91-9738507310
    Company Website –http://www.maxmunus.com


    ReplyDelete
  19. Superb! I found some useful information in your blog, it was awesome to read.Thank you for sharing.Software Testing Training Center in Velachery|Best Selenium Training Institute in Velachery

    ReplyDelete
  20. Well Said, you have furnished the right information that will be useful to anyone at all time. Thanks for sharing your Ideas.
    Software Testing Training in Chennai |No.1 Selenium Training Institute in Chennai | Web Designing Training Institute in Chennai

    ReplyDelete
  21. Thanks for sharing. I hope it will be helpful for too many people that are searching for this topic.
    ME/M.Tech Project Center in Chennai | ME/M.Tech Project Center in Velachery

    ReplyDelete
  22. Awesome article. It is so detailed and well formatted that i enjoyed reading it as well as get some new information too. weblogic admin training

    ReplyDelete
  23. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.

    Java training in USA

    Java training in Bangalore | Java training in Indira nagar

    Java training in Bangalore | Java training in Rajaji nagar

    Java training in Bangalore | Java training in Marathahalli

    ReplyDelete
  24. The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.
    python course in pune
    python course in chennai
    python course in Bangalore

    ReplyDelete
  25. This looks absolutely perfect. All these tiny details are made with lot of background knowledge. I like it a lot. 
    Online DevOps Certification Course - Gangboard
    Best Devops Training institute in Chennai

    ReplyDelete
  26. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
    rpa training in Chennai | rpa training in bangalore | best rpa training in bangalore | rpa course in bangalore | rpa training institute in bangalore | rpa online training

    ReplyDelete
  27. hi, nice information is given in this blog. Thanks for sharing this type of information, it is so useful for me. nice work keep it up. best digital marketing company in delhi

    ReplyDelete
  28. Laminated Doors manufacturer in hubli
    Thanks for giving great kind of information. So useful and practical for me. Thanks for your excellent blog, nice work keep it up thanks for sharing the knowledge.

    ReplyDelete
  29. led lawn lights in delhi
    Thanks for giving great kind of information. So useful and practical for me. Thanks for your excellent blog, nice work keep it up thanks for sharing the knowledge.

    ReplyDelete
  30. Hey, very nice site. I came across this on Google, and I am stoked that I did. I will definitely be coming back here more often. Wish I could add to the conversation and bring a bit more to the table, but am just taking in as much info as I can at the moment. Thanks for sharing.

    Custom Web Application Development

    ReplyDelete
  31. Play online casinos with fun and money fun slots Fun money, take while there.

    ReplyDelete
  32. Hello today decided to climb on the Internet in search of something interetninkogo and found this site fine baccarat online Today I will withdraw my first mo

    ReplyDelete
  33. I appreciate your efforts because it conveys the message of what you are trying to say. It's a great skill to make even the person who doesn't know about the subject could able to understand the subject . Your blogs are understandable and also elaborately described. I hope to read more and more interesting articles from your blog.
    rpa training in bangalore
    best rpa training in bangalore
    rpa course in bangalore
    rpa training in pune
    rpa training in chennai

    ReplyDelete
  34. I found your blog while searching for the updates, I am happy to be here. Very useful content and also easily understandable providing.
    Believe me I did wrote an post about tutorials for beginners with reference of your blog. 
    Java training in Bangalore


    ReplyDelete

Related Posts with Thumbnails