File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes query  a  resultset like CFQUERY Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "query  a  resultset like CFQUERY" Watch "query  a  resultset like CFQUERY" New topic

query a resultset like CFQUERY

Karen Gomes
Ranch Hand

Joined: Aug 25, 2003
Posts: 60
Is there a way to query a resultset in java. Yes what I mean is after obtaining one resultset, again query on that resultset like we do to a table in the DB.
Here is my problem in detail and any help doing the same in Java will be most appreciated :-)
I am working on a peculiar problem where we are moving our application from cold fusion to Java. Cold fusion being very powerful for database oriented applications my project was initially developed using CF and uses a lot of advanced features of CF. However the need to move to java was soon realised due to various reasons. Now one of the main problems we are facing is the CFQUERY tag in cold fusion.
This tag lets you create a resultset by
<cfquery name="aaa" datasource="blah-blah">
The best part is.... you can also query the above resultset obtained as follows:
<cfquery name="bbb" dbtype="query">
SELECT * from aaa where ***************
We have used it so often in CF since it significantly reduces the query throughput times. Now we would love to find a way to do the same in Java.
Any ideas
Michael Crutcher
Ranch Hand

Joined: Feb 18, 2002
Posts: 48
I don't think that there is any way to do what you're talking about in java. It would be possible to create a class that parsed the result set and returned an Arraylist of all of the columns and values. I think that would be pretty difficult and a waste of time. You've really got two options:
1. Create views; you won't get the performance benefit, but your pages will be able to be recreated with a minimal amount of editing (point at the view instead of the coldfusion temp table)
2. (Depending on the database you're using) Create derived tables. These tables will be actual tables that represent the subset of data that you wish to query. You'll get the performance boost but you'll also get a maintenance headache. The data won't be a real time representation of what's in the tables from which you derive the data. This might not be that big of a deal depending on how fresh your data needs to be. You could write an ETL process that refreshes the derived tables every night, or whatever your requirments need to be.
So views will be really simple, but will add some overhead over your current process (generally, some db's have support for materialized views). Derived tables will require an ETL process to keep fresh but will keep the same performance (or better) that you're used to.
I agree. Here's the link:
subject: query a resultset like CFQUERY
It's not a secret anymore!