where to put SQL queries ?

[Deepak Chopra]

Hi,
I am currently working on a Project in which all the PL/SQL queries has been hard coded inside the java code, My PM asked me to provide some solution to avoid this..He also suggested me to put these queries into one database table whereas I suggested him to put all these queries into one properties file and fetch them from there.! But still confuse which is the better way to do that ? Is there any other alternative way to do that..?

[Steve McLeod]

Both suggestions sounds good. My preference is for a properties file. This is because

* it is easier to maintain and manipulate in a good text editor or IDE.
* you can put it in your source control, so that you can always retrieve older versions.

[Deepak Chopra]

Thanks,
But I want to know which will provide a better performance, I think it will be properties file, once uploaded it easier to fetch Query from there.
where do we generally keep the queries apart from properties file?

[Steve McLeod]

Properties file is stored on the local drive. Retrieving it is many times faster than establishing a database connection, performing a SQL select statement, and populating a data structure with the results.

The time to read a properties file with, say, 200 SQL queries is so fast it is barely measurable.

[Deepak Chopra]

Thanks, Although some of my friends has given me some other options like,
Putting the queries into an XML or EXCEL file. But i don't know the much difference performance vise. So i think i better go with the properties file.

[Adeel Ansari]

I am not sure what do you mean by PL/SQL queries? I assume that as PL/SQL blocks/procedures.

So, why not create database stored procedures with those PL/SQL?

[Deepak Chopra]

Hi Ansari,

Here PL/SQL queries means simple select,delete, update and insert statement, I can not create a procedure with those queries and also I can not hard code them into JAVA code, I am looking for a best place to keep those queries..

[Rob Spoor]

Originally posted by Sunny Jain:
Thanks, Although some of my friends has given me some other options like,
Putting the queries into an XML or EXCEL file. But i don't know the much difference performance vise. So i think i better go with the properties file.

XML and Excel have more complex structures so parsing that will bring performance down a bit more than properties files.

I'd go for properties files - in fact, I'm doing that myself already.

[Adeel Ansari]

Originally posted by Sunny Jain:
Here PL/SQL queries means simple select,delete, update and insert statement, I can not create a procedure with those queries and also I can not hard code them into JAVA code, I am looking for a best place to keep those queries..

O'rite. Actually, I got confused a bit, because for those we just normally use the word "SQL queries", instead. The word PL/SQL sounds procedural to me and I start thinking of procedures, cursors, triggers etc.. However, the properties file sounds better in your case, as already suggested by a few folks.