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.

127 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 sharing this valuable information to our vision. You have posted a trust worthy
    SAS Training In Hyderabad

    ReplyDelete
  12. 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
  13. 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
  14. 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
  15. 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
  16. 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
  17. 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
  18. 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
  19. 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
  20. 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
  21. 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
  22. 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
  23. 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
  24. 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
  25. Superb. I really enjoyed very much with this article here. Really it is an amazing article I had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article. thank you for sharing such a great blog with us.
    rpa training in bangalore
    best rpa training in bangalore
    rpa training in pune
    rpa online training

    ReplyDelete
  26. Some us know all relating to the compelling medium you present powerful steps on this blog and therefore strongly encourage
    contribution from other ones on this subject while our own child is truly discovering a great deal.
    Have fun with the remaining portion of the year.
    Selenium training in Chennai
    Selenium training in Bangalore
    Selenium training in Pune
    Selenium Online training
    Selenium training in bangalore

    ReplyDelete
  27. I sat at home and heard the screams of joy from my son's room. I decided to peep that there, and he played in an online casino. Of course, I got angry, but he quickly reassured me and showed everything on this website. novel gamble online for money He won money twice as much as he put a lot of slot machines, slots and all that, now we sit together

    ReplyDelete
  28. Attend The Python Training in Bangalore From ExcelR. Practical Python Training in Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Python Training in Bangalore.

    ReplyDelete
  29. This comment has been removed by the author.

    ReplyDelete
  30. ThanksDELTA CUSTOMER SUPPORT AND SERVICES
    +18882623768 Delta customer number for
    Technical Support

    Reservations

    Cargo Department

    Customer Service

    Lost Baggage

    Medallion Status
    24 hours, 7 days
    Delta customer service.
    Get in touch with Delta’s customer service department through the following phone numbers,
    email and contact form.
    For more information about reservations, technical support, cargo department,
    lost baggage and medallion status, please call the numbers listed Above .
    Delta Airlines Customer Service
    Wpfixd
    Indian e Visa Online
    I-visa online

    ReplyDelete
  31. Quickbooks Enterprise Support Phone Number
    Get Help for all your issues with QuickBooks Enterprise.
    Call (888) 802-9333
    Call Intuit QuickBooks Online Customer Services Helpline Phone Number USA 1-888-802-9333 to fix your Accounting Issues.
    QuickBooks Technical Support Contact Team Will fixes your issue Enterprises,
    Payroll, Pro, Premier, Vat, Bank Errors etc , QuickBooks Online Support Number 1-888-802-9333 USA

    ReplyDelete
  32. Agarwal Packers and Movers is the best service provider in India. Agarwal Packers give the service more than your imagination. You can not believe by watching the working style of packing, loading, and unloading, How perfect they work. In my opinion, Everyone should try the service of Agarwal Packers and Movers for once at least.

    Agarwal Packers Reviews
    Agarwal Packers Feedback
    Agarwal Packers Complaint

    ReplyDelete
  33. Very nice blog website… I always enjoy to read your blog post… Very good writing skill.. I appreciated what you have done here… Good job! Keep posting. 192.168.l.l

    ReplyDelete
  34. well explanation for this related topic , i like very much this site , nice expretion.
    web app development

    ReplyDelete
  35. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    Machine Learning Course Bangalore

    ReplyDelete
  36. Excellent information with unique content and it is very useful to know about the information based on blogs...
    salesforce Training in Bangalore
    uipath Training in Bangalore
    blueprism Training in Bangalore

    ReplyDelete
  37. Thanks for this great post, i find it very interesting and very well thought out and put together. I look forward to reading your work in the future. tank removal companies

    ReplyDelete
  38. I would definitely thank the admin of this blog for sharing this information with us. Waiting for more updates from this blog admin.
    thanks for your information really good and very nice web design company in velachery

    ReplyDelete
  39. I am very happy when read this blog post because blog post written in good manner and write on good topic. Thanks for sharing valuable information.Keep blogging!! Machine Learning Course

    ReplyDelete
  40. Great blog thanks for sharing Take care of all your search engine optimization SEO, graphic design, logo creation, social media marketing and digital branding need at one stop - Adhuntt Media. Customer satisfaction and service is our priority - We tread that fine line between projecting your visions into tangible reality! Why wait when you can begin your digital marketing journey with us right now at Adhuntt Media
    digital marketing company in chennai
    seo service in chennai
    web designing company in chennai
    social media marketing company in chennai

    ReplyDelete
  41. Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.
    ExcelR Data Analytics courses

    ReplyDelete
  42. Website Design Companies in New Zealand with their strategic approach drives the complete look of your website. They have efficient experts who lead to provide a user-friendly and responsive website and can also revamp your website in case, it has lost its charm and become outdated.

    Software Development Company in New Zealand
    E-Commerce Development Company in New Zealand
    Content Writing Company in New Zealand
    Digital Marketing Company in New Zealand
    Pay Per Click Company in New Zealand
    Social Media Marketing Company in New Zealand
    SEO Company in New Zealand
    Website Revamp Services in New Zealand

    ReplyDelete
  43. Very interesting blog Thank you for sharing such a nice and interesting blog and really very helpful article.sap mm Training in Bangalore

    ReplyDelete
  44. Its really helpful for the users of this site. I am also searching about these type of sites now a days. So your site really helps me for searching the new and great stuff.sap basis Training in Bangalore

    ReplyDelete
  45. Very useful and information content has been shared out here, Thanks for sharing it.sap hr Training in Bangalore

    ReplyDelete
  46. I gathered a lot of information through this article.Every example is easy to undestandable and explaining the logic easily.sap sd Training in Bangalore

    ReplyDelete
  47. Your articles really impressed for me,because of all information so nice.sap ehs Training in Bangalore

    ReplyDelete
  48. Linking is very useful thing.you have really helped lots of people who visit blog and provide them use full information.sap ehs Training in Bangalore

    ReplyDelete
  49. Being new to the blogging world I feel like there is still so much to learn. Your tips helped to clarify a few things for me as well as giving.sap bods Training in Bangalore

    ReplyDelete
  50. I know that it takes a lot of effort and hard work to write such an informative content like this.sap fico Training in Bangalore

    ReplyDelete
  51. it is wonderful as always and do more and share more
    BEST ANGULAR JS TRAINING IN CHENNAI WITH PLACEMENT

    https://www.acte.in/angular-js-training-in-chennai
    https://www.acte.in/angular-js-training-in-annanagar
    https://www.acte.in/angular-js-training-in-omr
    https://www.acte.in/angular-js-training-in-porur
    https://www.acte.in/angular-js-training-in-tambaram
    https://www.acte.in/angular-js-training-in-velachery

    ReplyDelete
  52. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    share some more details.it may help us lot
    AngularJS training in chennai | AngularJS training in anna nagar | AngularJS training in omr | AngularJS training in porur | AngularJS training in tambaram | AngularJS training in velachery

    ReplyDelete
  53. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.
    business analytics certification

    ReplyDelete
  54. For example, let's say one would like to predict the kind of weather for tomorrow or maybe teach a computer how to play chess machine learning institute in hyderabad

    ReplyDelete
  55. this post is very helpful ...thank you for sharing information
    Mom Blog Names
    Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.

    ReplyDelete
  56. Very nice job... Thanks for sharing this amazing ExcelR Machine Learning Courses and educative blog post!

    ReplyDelete
  57. Attend online training from one of the best training institute Data Science

    Course in Hyderabad

    ReplyDelete
  58. Good Post! it was so good to read and useful to improve my knowledge as an updated one, keep blogging. After seeing your article I want to say that also a well-written article with some very good information which is very useful for the readers....thanks for sharing it and do share more posts like this.
    Python Online Training

    ReplyDelete
  59. Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.
    data science training in Hyderabad

    ReplyDelete
  60. This is extremely helpful info!! Very good work. Everything is very interesting to learn and easy to understand. I hope visit this site Rajasthan Budget Tours

    ReplyDelete
  61. One of the best blogs that i have read still now. Thanks for your contribution in sharing such a useful information. Waiting for your further updates.
    Angular js Training in Chennai

    Angular js Training in Velachery

    Angular js Training in Tambaram

    Angular js Training in Porur

    Angular js Training in Omr
    Angular js Training in Annanagar

    ReplyDelete
  62. 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.
    IELTS Coaching in chennai

    German Classes in Chennai

    GRE Coaching Classes in Chennai

    TOEFL Coaching in Chennai

    Spoken english classes in chennai | Communication training

    ReplyDelete
  63. I've read this post and if I could I desire to suggest you some interesting things or suggestions. Perhaps you could write next articles referring to this article. I want to read more things about it!
    data science course in hyderabad with placements

    ReplyDelete
  64. Fantastic website. Lots of useful info here. I’m sending it to some friends and additionally sharing in delicious. And obviously, thank you on your sweat!

    Java Training in Chennai

    Java Course in Chennai

    ReplyDelete
  65. Here at this site really the fastidious material collection so that everybody can enjoy a lot. ExcelR Data Analyst Course

    ReplyDelete
  66. Informative blog! it was very useful for me.Thanks for sharing. Do share more ideas regularly.
    Village Talkies a top-quality professional corporate video production company in Bangalore and also best explainer video company in Bangalore & animation video makers in Bangalore, Chennai, India & Maryland, Baltimore, USA provides Corporate & Brand films, Promotional, Marketing videos & Training videos, Product demo videos, Employee videos, Product video explainers, eLearning videos, 2d Animation, 3d Animation, Motion Graphics, Whiteboard Explainer videos Client Testimonial Videos, Video Presentation and more for all start-ups, industries, and corporate companies. From scripting to corporate video production services, explainer & 3d, 2d animation video production , our solutions are customized to your budget, timeline, and to meet the company goals and objectives.
    As a best video production company in Bangalore, we produce quality and creative videos to our clients.

    ReplyDelete
  67. I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post.
    data scientist training and placement

    ReplyDelete
  68. I have to search sites with relevant information ,This is a
    wonderful blog,These type of blog keeps the users interest in
    the website, i am impressed. thank you.
    https://www.dettifossit.com/servicenow-training-in-chennai

    ReplyDelete
  69. Incredible blog here! It's mind boggling posting with the checked and genuinely accommodating data. Newt Scamander Coat

    ReplyDelete
  70. This article content is really unique and amazing. This article really helpful and explained very well. So I am really thankful to you for sharing keep it up..

    ReplyDelete
  71. This post is so interactive and informative.keep update more information...
    DevOps course in Tambaram
    DevOps Training in Chennai

    ReplyDelete
  72. I am visiting first here. Really awesome content and informative blog. Keep up this good work.
    Data Science Institute

    ReplyDelete
  73. I must say that you are my favourite author. You always bring surprised things for me everytime I see your articles. Great efforts!! Billionaire Boys Club Varsity Jacket

    ReplyDelete
  74. Great to become visiting your weblog once more, it has been a very long time for me. Pleasantly this article i've been sat tight for such a long time. I will require this post to add up to my task in the school, and it has identical subject along with your review. Much appreciated, great offer.
    Software Testing Tools Training in Hyderabad

    ReplyDelete
  75. NordVPN Premium Accounts List 2022 gives you peace of mind each time you use public Wi-Fi, access personal and work accounts on the road, or want to keep your browsing history to yourself. NordVPN Cracked For PC software for each of these, plus apps for iOS, Android, and Android TV. Oh, and also encrypted proxy extensions for Chrome and Firefox. Nord VPN Crack

    ReplyDelete
  76. Samsung Pro Tool activation allows you to SW update/change, full factory reset, switch phone to download mode, read/write calibration data files, ..Z3X Samsung Tool Crack

    ReplyDelete
  77. 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.


    Read more: Oracle ADF - Tips and Techniques: ADF Model: Generating and using 'in' clause in VO sql statement http://www.adftips.com/2012/07/using-in-clause.html#ixzz7qwdqvm00

    ReplyDelete
  78. Amazing information providing by your article, thank you so much for taking the time to share a wonderful article.

    ReplyDelete
  79. 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.

    ReplyDelete
  80. 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.

    ReplyDelete
  81. 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.
    netgear login/

    pikashow.ltd

    ReplyDelete

Related Posts with Thumbnails