?
Solved

Using java programming i have to create Dynamic hql queries

Posted on 2014-03-24
6
Medium Priority
?
680 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
  • 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 46

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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Introduction A frequently used term in Object-Oriented design is "SOLID" which is a mnemonic acronym that covers five principles of OO design.  These principles do not stand alone; there is interplay among them.  And they are not laws, merely princ…
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

589 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