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
Solved

Using java programming i have to create Dynamic hql queries

Posted on 2014-03-24
6
528 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 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
egit plugin on eclipse 8 82
Is there a tool that can take a URL, scan the page and identify dead links? 9 75
Java basic valueOf question 1 29
Java Eclipse Loop 3 20
Foolproof security solutions has become one of the key necessities of every e-commerce or Internet banking website. If you too own an online shopping site then its vital for you to equip your web portal with customer security features that can allow…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

809 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