Skip to main content

New to Java - Design question about storing a series of text (SQL) resources for a web application

Please note these java.net forums are being decommissioned and use the new and improved forums at https://community.oracle.com/community/java.
4 replies [Last post]
p.f.moore
Offline
Joined: 2012-05-15

Hi,
I'm new to Java, but experienced with other languages. I am trying to create a web application that executes a series of SQL statements against a database and collects, summarises and displays the results.

The basics of the code are not too difficult, but I'm not sure what is the best way (the most natural for Java, I guess) of storing the queries and a small amount of metadata. I'd have a series of maybe 10-15 queries, and for each query I'd want to keep a name.

If I were coding in another language, I'd probably store all of the queries in a series of files alongside the app, with the filename being the query name, and the content of the file being the SQL to run. I'd then scan the directory at runtime for *.sql files, and read each one in turn.

As my Java code will ultimately be turned into a WAR file for deployment, I'm not quite sure what would be the equivalent approach for Java. Or if this is a completely inappropriate way of working in Java, what I should be doing instead.

If anyone can give me some suggestions, or point me at some examples or tutorials on the web that would help, I'd be most grateful.

Thanks,
Paul.

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
surfman
Offline
Joined: 2006-11-23

It's most useful to store your queries in a properties file. You can then give them all a name like this:

users.create=insert into users values(?,?)
users.get.all=select * from users
usres.get.single=select * from users where id=?

Now you can use the standard Properties mechanism to get the queries and use them as PreparedStatements.

p.f.moore
Offline
Joined: 2012-05-15

OK, that's certainly something I thought of briefly, but wasn't sure it was the best way. How does it scale to 10-15 line queries, though? Presumably I'd have to use backslashes to continue the lines.

Separate files would be ideal, except for the one aspect, which is that I don't know of a way to get a list of what files there are. I suppose I could put the filenames in a property file:

queries.1.description = List all employees with more than 10 years service due for a raise
queries.1.filename = raise.sql

Thanks,
Paul.

egc2224
Offline
Joined: 2006-02-17

how are you accessing the database from within java? are you using JDBC? if so there are classes of type ResultSet and ResultSetMetaData (i think i have that name right) which are for the very purpose you speak up. i would suggest starting by looking up the documentation on the ResultSet class that is part of JDBC and become familiar with it and look at some examples online somewhere where it gets used to store result sets returned from executed queries and then that should put you on the correct path. same thing with the MetaData question. if you need to store all of the result sets it seems you could create a vector that would use generics to specify that it is meant for store result sets and that would set you up to be able to add as mean result sets as you generate to that vector and then return just that vector back to your main part of your code.

p.f.moore
Offline
Joined: 2012-05-15

Yes, I'll be using JDBC and result sets to process the queries. I'm happy with that and I know what to do there. What I'm asking is where I should store the queries themselves. I want them external to the code, so they can be edited by SQL experts who may not know Java.

Thanks,
Paul