servlets and resource file

Question

Is it possible to write database methods in properties file, like some database queries and can be accessed using servlets which may reduce the code?

Answer 1

1. webapp can access properties file.
2. a propeties file may have key-value pairs like query1=select something from somewhere

You can combine 1 and 2 in order to execute sql queries in webapp, but I'm not sure it will reduce code (you still have to open db connection, reading the rs, close the connection, etc.)

Answer 2

Of course, you can put you queries in a properties file but if you are using JDBC, the code depends a lot on these queries. The number of parameters and their order must not be changed, same goes for the results, so I don't see many advantages to do so.

Answer 3

You can store the queries in a file that the webapp can access, and then use prepared statements to execute them. By doing this, your queries strings are in an external file, all you do in the code is fill in the parameters, but you will have to read the query strings from what you call "properties file" first.

Example:

String query; // read the query from the "properties file"

//Suppose you put "INSERT INTO BOOKS (AUTHOR, TITLE) VALUES (?, ?);"

PreparedStatement ps = conn.prepareStatement(query);
ps.setString(1, "param1");
ps.setString(2, "param2");

ps.executeUpdate();

Answer 4

I've seen DB work done all sorts of ways from hard coding SQL directly into each method that access a database, to sql stored in static strings in central files, to it being stored in properties files and finally of course ORMs such as hibernate. I've even seen SQL stored in DBs!

I would never recommend storing the SQL (or ORM queries) far from the code that needs them. The simple reason is that my experience has been that by breaking the connection, developers start to reuse and mis-use the queries. Over time the developers loose track of what query is used where, and start adding new ones to avoid the risk of breaking things. Eventually they end up with files full of queries with no idea which ones are even being used. The worst I have ever seen was where the developers were centralising the SQL and braking it up into individual parts as well. It was almost impossible to tell if a change would break things.

The initial argument for separating out the queries is usually "maintenance" or "in case we change the DB", etc. But at the end of the day it becomes a bigger problem than is was intended to fix.

To summarise, I'd recommend storing the queries as close to the code that uses them as possible. Hibernate and similar go a long way to making this the pragmatic solution.