This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Soft Skills and have John Sonmez on-line!
See this thread for details.
The moose likes OO, Patterns, UML and Refactoring and the fly likes pattern ideas? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Engineering » OO, Patterns, UML and Refactoring
Bookmark "pattern ideas?" Watch "pattern ideas?" New topic
Author

pattern ideas?

Michael J. Makunas
Ranch Hand

Joined: Mar 11, 2002
Posts: 37
I have a situation where due to db performance issues, we are trying to limit the number of queries in our code. For the sake of argument, let say I am constantly creating "User" ojects, that represent a user on the site. There are cases where I intatiate 12 of them, one after another, each with it's own db hit. What I would like to do is create an object structure that enables me to create an array of objects (using one db query) when I want and still be able to instantiate a single object, too.

Are there any patterns that fit this?

Thanks....
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
Some place you have something that executes SQL and populates a User object from the result set, right? Can you modify the SQL to select for userid "in" a set of ids? When the set has only one id the query fetches only one row. When the set has mulitple userids the single query will return multiple rows. Then you'd just build a User object per row and return a collection of some kind.

Is that the kind of thing you had in mind? I really don't know what kind of speed advantage a single query will have over multiples, but it might be worth doing.

Are you caching so things are not retrieved again? That's a good strategy sometimes depending on the fetch vs update ratio on things. Be sure to include a way to clear the cache and get the latest database data again.


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
Don't know about a pattern, but the usual way to implement this would be to have two methods:
public User findUser(String uid);
public Collection findUsers(String[] uid);

Are you using a persistence framework like Hibernate/JDO/Castor/TopLink?


Author of Test Driven (2007) and Effective Unit Testing (2013) [Blog] [HowToAskQuestionsOnJavaRanch]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31057
    
232

Michael,
Are you trying to create a bunch of users in the database or retrieve them?

If creating, JDBC has an executeBatch() statement that does just that.

If retrieving, search the JDBC forum for my posts on batching. It's not a formal pattern, but it helps limit database roundtrips. Basically, you have an in clause with predefined numbers of ?'s to take advantage of the preparedness part. It is a middle ground for many roundtrips (network issues) and doing everything at once (forcing the database to prepare the statement each time.) A few other people here have tried it and noticed a performance boost.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Michael J. Makunas
Ranch Hand

Joined: Mar 11, 2002
Posts: 37
Originally posted by Stan James:
Some place you have something that executes SQL and populates a User object from the result set, right? Can you modify the SQL to select for userid "in" a set of ids? When the set has only one id the query fetches only one row. When the set has mulitple userids the single query will return multiple rows. Then you'd just build a User object per row and return a collection of some kind.


Yes, my plan was to modify the SQL to use an "IN" clause. I'm more interested in figuring out how to model this so that the data layer stays suficiently decoupled.
Michael J. Makunas
Ranch Hand

Joined: Mar 11, 2002
Posts: 37
Originally posted by Lasse Koskela:
Don't know about a pattern, but the usual way to implement this would be to have two methods:
public User findUser(String uid);
public Collection findUsers(String[] uid);

Are you using a persistence framework like Hibernate/JDO/Castor/TopLink?


I'm not using any kind of persistence framework. But when you mention having two methods, findUser and findUsers, you made me think of something. I think I need to completely seperate the SQL and db code from the user class, and have another layer that hits the db and then creates a collection of users.
Michael J. Makunas
Ranch Hand

Joined: Mar 11, 2002
Posts: 37
Originally posted by Jeanne Boyarsky:

If retrieving, search the JDBC forum for my posts on batching. It's not a formal pattern, but it helps limit database roundtrips. Basically, you have an in clause with predefined numbers of ?'s to take advantage of the preparedness part. It is a middle ground for many roundtrips (network issues) and doing everything at once (forcing the database to prepare the statement each time.) A few other people here have tried it and noticed a performance boost.


Thanks. I'm not sure if a prepared statement will be work in my particular case but I think your post will help in some other areas where we are having performance issues.
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
Originally posted by Michael J. Makunas:
I'm not using any kind of persistence framework. But when you mention having two methods, findUser and findUsers, you made me think of something. I think I need to completely seperate the SQL and db code from the user class, and have another layer that hits the db and then creates a collection of users.


Yes, that's definitely a good idea!


The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
Yes, that specialized IO layer sounds good!

I'm just refactoring my Wiki (for entertainment value). I used to have a Repository layer that did all the IO. I had a simple flat file implementation and an archiving implementation that saved all revisions.

This time around I have the Repository layer that does many common file manipulations and a DataStore layer that does physical IO. In real life I use a FileSystemDataStore, but for unit testing I have an InMemoryDataStore.

Keeps me off the street at night.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: pattern ideas?