Solved

Using java programming i have to create Dynamic hql queries

Posted on 2014-03-24
6
521 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 1

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:Kdo
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 1

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 1

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 1

Author Closing Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CA single sign on 2 74
Server error in 'XXXXXXXX' application. 12 73
java 8 lambda expresssions exception handling 3 75
Java - Why doesn't this JFrame work 3 43
New Relic: Our company recently started researching several products to figure out what were the best ways for us to increase our web page speed and to quickly identify performance problems that we may be having. One of the products we evaluated wa…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now