• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Tim Cooke
  • Paul Clapham
  • Jeanne Boyarsky
Sheriffs:
  • Ron McLeod
  • Frank Carver
  • Junilu Lacar
Saloon Keepers:
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Al Hobbs
  • Carey Brown
Bartenders:
  • Piet Souris
  • Frits Walraven
  • fred rosenberger

Common SelectQuery method

 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi..i have created a common method for all my SelectQueries from the Data-Base.
--------------------------------------------------------------------
public void selectQuery(String saQuery, ResultSet objaResultSet) throws SQLException {

/* Connection Object */
Connection objlConnection = null;

/* Prepared Statement */
PreparedStatement objlPreparedStatement = null;


try {

objlConnection = getConnection();
/* Preparing Statement*/
objlPreparedStatement = objlConnection.prepareStatement(saQuery);
/* Executing Query*/
objaResultSet = objlPreparedStatement.executeQuery();
PFNLog.log("\nQuery executed: " + saQuery, DRRUtils.DRR_DEBUGON);
} catch (Exception objaException) {
PFNLog.log("Exception in selectQuery.", DRRUtils.DRR_DEBUGON);
PFNLog.logException(objaException);
/* Throwing SQLException to the calling method */
throw new SQLException();

} finally {

/* Closing the PreparedStatement */
try {
if (objlPreparedStatement != null) {
objlPreparedStatement.close();
}
} catch (Exception objaException) {

PFNLog.logError("Exception in closing PreparedStatement in " + this.getClass());
PFNLog.logException(objaException);
}

/* Closing the Connection Object */
if (objlConnection != null) {
closeConnection(objlConnection);
}
}

}
--------------------------------------------------------------------

Earlier i was creating a New ResultSet Object in this method itself.However i had to Return this ResultSet Object to the calling method.
To avoid this i recreated the method to pass a Reference of the ResultSetObject(created now in the calling method).However the problem is now when i close the PreparedStatement and Connection Object the ResultSet Object becomes null also

Now i must implement this comman method and Must have a ResultSet Object to iterate through in the calling method...

Guys please suggest a better way to solve this.And please let me know the reason for choosing also..

-Thanks-
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Write your results into an ArrayList and pass that back. Much better than passing ResultSets about.
 
Vivek Nanda
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Right.ArrayList should be actually passed.However every calling method would have an ArrayList made of up different kinds of VO objects.Now if i pass an ArrayList i would have to populate it in my common SelectQuery method and that would mean its no longer common.

What i do here is get the ResultSet object to the calling method and then iterate through it ..populate my ArrayList with the data in the ResultSet object.

So would need a methodology to avail this with the constraints as i had mentioned earlier.

Please Advice

-Thanks-
 
Vivek Nanda
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Right.ArrayList should be actually passed.However every calling method would have an ArrayList made of up different kinds of VO objects.Now if i pass an ArrayList i would have to populate it in my common SelectQuery method and that would mean its no longer common.

What i do here is get the ResultSet object to the calling method and then iterate through it ..populate my ArrayList with the data in the ResultSet object.

So would need a methodology to avail this with the constraints as i had mentioned earlier.

Please Advice

-Thanks-
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


Now if i pass an ArrayList i would have to populate it in my common SelectQuery method and that would mean its no longer common


A good "methodology" would be polymorphism. Have your method return an ArrayList of Objects (or a base wrapper object if you like) and cast them when you access them in your calling methods.
 
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Instead of filling your arraylist with objects that represent particular objects or tables, return a list of generic RowData objects that hold name-value pairs for the columns. It can get tricky making the values the right class for the database column type. Look into the metadata methods of the resultset.

Ooh! Second thought! Pass your generic method a RowBuilder object that can build the table-specific objects that go into your array list.

executeQuery( "somesql", aRowBuilder );

Then the generic executeQuery method could say

resultList.add( aRowBuilder.buildObjectFor( resultset ) );

Zat make any sense?
[ June 18, 2004: Message edited by: Stan James ]
 
Beware the other head of science - it bites! Nibble on this message:
Garden Master Course kickstarter
https://coderanch.com/t/754577/Garden-Master-kickstarter
reply
    Bookmark Topic Watch Topic
  • New Topic