On my current J2EE Web App project , there is the idea to externalize SELECT statements for web page pick lists . So there are 1 table that keeps alias and sql statement.
when you need the data you call
and it performs the Select statement and returns the results. Also userInfo is on the input ,so that you can retrieve only data for given user properties (if they are inserted in SQL statement ,similat to "?" in preparedStatement
Is it useful to externalize the sql statements into db. Have you any experience. I ve always used the DBAccess class to separate the db from all java code ? and I loved that.
If you're using Struts, there's a LabelValueBean that keeps name value pairs. This way you can populate the bean from the database, and then just display it on the JSP with the <html:options> tag. <html:options collection="STATES" property="value" lableProperty="label" />
So, if I understand correctly. DBUtils.getPickList() reads an SQL statement from a database table, then executes it to retrieve data from other tables. This seems like it pollutes the schema model with the access for it.
What we've done is externalize all of our SQL into XML files, and then created a framework that sits on top of JDBC. We use named parameters instead of positional (which I always hated) for statements and preparedstatements. Our layer also handles many type conversions automatically.
But what is the real value of bringing out the SQL statements (XML or db) , it is some kinda of metadata , it is very difficult to change or develop , because it is the part of persistence layer on app server. So when I want to change something , I must switch off to TOAD and edit that in stupid db column.
I am first java developer and second interested in DB or XML
Is there any advantages of externalizing ?