Solved

Using java programming i have to create Dynamic hql queries

Posted on 2014-03-24
6
514 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 31

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
If I have to fix slow responding website my first thoughts are server side optimizations: the database may not be optimized or caching is not enabled, or things like that. We often overlook another major part of our web application: the client. We o…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…

758 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

17 Experts available now in Live!

Get 1:1 Help Now