I am working on web based project using Spring MVC framework and I am maintaining all SQL queries in a separate text file so that I can change them any time when needed. I just want to know am I doing right, it this a optmistict way of coding??
Narayanan Madaswamy wrote:I am working on web based project using Spring MVC framework and I am maintaining all SQL queries in a separate text file so that I can change them any time when needed. I just want to know am I doing right, it this a optmistict way of coding??
It's one possibility, but if they can be grouped into more generic queries, you might also want to think about PreparedStatement's.
Isn't it funny how there's always time and money enough to do it WRONG?
Articles by Winston can be found here
Seems to me that if you were storing your SQL queries externally, you would pretty much have to be using PreparedStatement. I can't see how parameters would work otherwise -- unless you produced your own design for identifying and inserting parameters.
Joined: Apr 17, 2013
Yes you are right Paul Clapham, I am using my own function to prepare query statements. please check the following and please let me know if I am doing something silly :-)
Query in text file :
select label,control,controlid,valuetypescript,validationscript,style,cssclass,field_id from dynamic_controls where page_id=val0 and portfolio_id=val1
This indeed isn't an ideal solution. Your code lacks all of the benefits of using PreparedStatements: it is prone to SQL injection attacks, it may suffer problems with formatting dates, numbers and strings correctly and in most databases it will perform worse (perhaps much worse) than necessary.
Edit: storing the SQL strings in a separate file is a workable solution, assuming you do use prepared statements. Some of the advantages are that you could easily create database specific versions of your statements, if necessary (some statements could be reworked to perform better in a different database), and you could also easily let an experienced database developer review your statements even if he isn't fluent with Java.
Joined: Apr 17, 2013
Thanks Martin Vajsar, can you put an example code here..??
Keeping your SQL in text files instead of "baking" it into Java classes is a good goal, but have have you considered using the MyBatis SQL Mapper framework instead of rolling you own solution? MyBatis lets you externalize your SQL in XML files. It has powerful mapping & dynamic SQL capabilities, and it uses PreparedStatements for optimum efficiency. It integrates nicely with Spring, but like all frameworks there is a learning curve to overcome in order to use it effectively.