• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

getting or building strings

 
Ranch Hand
Posts: 141
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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!!!
 
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 141
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
This is awkward. I've grown a second evil head. I'm going to need a machete and a tiny ad ...
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic