File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Java in General and the fly likes getting or building strings Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "getting or building strings" Watch "getting or building strings" New topic
Author

getting or building strings

Chris Montgomery
Ranch Hand

Joined: Jan 14, 2004
Posts: 141
This deals with an sql string, but it's not about the sql mechanics, its really about building the string. It just so happens it's an sql string. But if you think it should go to the db area anyway, feel free to send it over. Thanks!!!

==

I've been working on an application that has the potential to be replicated many many times.

With this in mind, I have built an application, which contains all the necessary core logic in a core jar.

Next I built an "application instance" to manage the client-specific aspects of the application, etc. Works great.

With this architecture in mind, I keep the core sql strings in the core application and the client-specific sql strings in the application instance.

What I noticed is that 95% of each sql call is going to be the same for each application. The only reason I have to separate the sql is because someone might want to track something unique to their instance (like a birthday, which isn't stored in my core).

I don�t like to have 30 instances of mostly the same sql calls out there. It�s an upgrade/maintenance nightmare with that many installs.

The overall goal is to make updating and modifying the 95% common sql easy. If I make a change, I don't want to have to update 30 mostly equal sql strings. Obviously, if I make a change to the app, I'm going to have to deploy a new jar each time, the real goal is to update one area vs. thirty.

Does this make sense?

I'm storing my sql statements in a .properties file under my jdbc package (not in my classes directory). Anyway...

I thought about storing my sql strings in a way that I separate my sql statement. The select portion of the string in one variable, the from in another, a where in another, and so on...

Example:
stringMemberSelect=SELECT firstName,lastName
stringMemberFrom=From member
stringMemberWhere=WHERE active=1 AND memberID={0}

then in the application instance, I could have
stringMemberSelect=,birthday
stringMemberFrom=
stringMemberWhere=

Seems cumbersome, but I may not have many options�

I've seen an app that actually builds part of the sql string, then passes it to a custom class which then looks to see if that particular app instance has any additional things to add to the statement. If it does, it sends it the partially built string, tacks on to it the unique columns and then passes it back to the "core" area to finish off the string. The it executes it. Doesn't feel right though...

Thoughts?

Thanks!!!
Eric McIntyre
Greenhorn

Joined: Mar 03, 2005
Posts: 26
Sounds like you could use an ORM tool (Object/Relational Mapping) like Hibernate or iBatis. Then there's no need to worry about SQL.

But, since architecture changes in an existing app aren't always practical....

First of all, kudos to you for making reusability and extensability a goal.

As to your specific problem, it's hard to say without more familiarity with your overall architecture and code. On first glance, I thought there was lots of room for improvement. But since you don't need ultimate flexibility, I'd say that you're on the right track. I would think the tricky part is dealing with unknown resultsets.

First recommendation, take a look at one of the ORM's I mentioned above to see if it's a good fit. If you've got the time to spend now, it will make up for itself in maintenance. If that isn't an option right now, give your proposal a try and see how it works out. If you have good unit tests for everything, you'll be able to safely refactor as you go.
Chris Montgomery
Ranch Hand

Joined: Jan 14, 2004
Posts: 141
It�s funny, I keep avoiding Hibernate, but it keeps haunting me
I'm quite comfortable with my Connection Pool, DAO classes, my value objects (which represent a query result row), using collections to persist my data once I close my connection and finally an iterator to get at my data stored in the collection.

Is that wrong, am I being old and stubborn (actually, I still consider myself a rookie�)?

My goal is definitely reusability and extensibility as you have mentioned, but also efficiency.


Hmmmm.... I need to think about this for a while. The app I'm building is in it's infancy and the sky's the limit.

Thanks for the feedback!
Eric McIntyre
Greenhorn

Joined: Mar 03, 2005
Posts: 26
I wouldn't say you're being stubborn, since there's always a right tool for the job. It took me awhile to catch the "ORM bug", too. If the app is very small, and likely to stay so, the overhead of Hibernate setup and configuration is probably not justified. Most of the time, though, an app tends to quickly grow beyond the original vision. When that happens, you'll find yourself maintaining a lot of tedious code.

I definitely recommend checking it out and seeing what it can do for you. The learning curve is actually not that bad and it will surprise you how much code you don't have to write. Also don't count iBatis out, since its design is a little different, and, from what I've heard, is even easier to learn than Hibernate. It might be a better fit for you. At the very least, you'll have these in your toolbox to pull out when you do need them. They can definitely be your friend

Just my .02
Eric
 
 
subject: getting or building strings