Solved

Using java programming i have to create Dynamic hql queries

Posted on 2014-03-24
6
536 Views
Last Modified: 2014-04-03
HI i have the below query

select count(*) from FULL.REQUEST where VENDORTYPEID=4 and STATUSID = 2 and updatets>timestamp('2014-03-04 00:00:00') with ur

i have 4 different vendors so i am executing this query 4 time by changing vendor typeid
and Time Stamp has to show currentdate.

my requirement i have to take this query write using javaprogramming and dynamic hql
instead of timestamp everytime i have to show Currentdate dynamically.

This query will give count in integer value some times it vl give 0 values

whenever the query count is zero then i have to send one email.so email functionality i have.
 can any one help me how to write the query using javaprogram and hql
and instead of static date i have to show current date dynamically.
if the count is zero i hve to call sendmail mehtod
0
Comment
Question by:srikotesh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 39953708
What have you written thus far?
0
 
LVL 2

Author Comment

by:srikotesh
ID: 39955554
if i excute the query around 2014-3-26 06:00:00
it should fetch the records from 25th march 6am to 26th march 6am
how can we write the query to fetch the records.
String query = " select count(*) from  FULFILLMENT.VENDORREQUEST where VENDORTYPEID=:vendorTypeID and STATUSID =:statusID and updatets >:todayDatets";

how can i modify the above query to fectch the records
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39958924
Hi srikotesh,

Depending on your needs, you may get better overall performance by getting the row counts for all 4 vendors at once.

  select vendortypeid, count(*)
  from FULL.REQUEST
  where VENDORTYPEID between 1 and 4
    and STATUSID = 2
    and updatets>timestamp('2014-03-04 00:00:00')
  group by vendortypeid

You could use the same technique to fetch the rows for all 4 vendors and sort them out in your application.  If you've got a relative few number of rows this is probably the best solution.  Note that you should list the columns that you want returned instead of "SELECT *":

  select *
  from FULL.REQUEST
  where VENDORTYPEID between 1 and 4
    and STATUSID = 2
    and updatets>timestamp('2014-03-04 00:00:00')
  group by vendortypeid

And if you need to get the row data for just one vendor, a small change does that:

  select vendortypeid, count(*)
  from FULL.REQUEST
  where VENDORTYPEID = 1
    and STATUSID = 2
    and updatets>timestamp('2014-03-04 00:00:00')
  group by vendortypeid


Good Luck,
Kent
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:srikotesh
ID: 39963951
i need count separately for individual vendor,we can not combine and send the report to one vendor.
if i execute the query now means like 29th 10pm
from update timestamp column i have to fetch last 24hrs records count
date and time should be dynamic vals not hardcoded
0
 
LVL 2

Accepted Solution

by:
srikotesh earned 0 total points
ID: 39964564
finally i complete with this program

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date date = new Date();
            String today = dateFormat.format(date); //2013/10/15 16:16:39
            System.out.println("today date"+today);
            
            Calendar cal = Calendar.getInstance();
            //Calendar.add(Calendar.DATE-1);
            cal.add(Calendar.DATE, -1);
            String yesterday = dateFormat.format(cal.getTime());
            
          System.out.println("yesterday date"+yesterday);


select count(*) from FULL.REQUEST where VENDORTYPEID=:vendorTypeID and STATUSID =:statusID and updatets between :today  and :yesterday;
0
 
LVL 2

Author Closing Comment

by:srikotesh
ID: 39974500
i have done that query with java program
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Android development question 2 81
java mysql insert application 14 47
Html split(text) 2 32
Java array 21 87
What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question