Discussions

General J2EE: externalizing SQL statements into DB

Bookmark and Share Get thread feed

  1. externalizing SQL statements into DB (3 messages)

    • Posted by: Roman Smatana
    • Posted on: September 23 2003 13:31 EDT
    Hi all,


      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
      DBUtils.getPickList(alias, userInfo)
    )

    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.

    thanks
    Reply to this

    Threaded Messages (3)

  2. Struts has a LabelValueBean[ Go to top ]

    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" />
    Reply to this Reply to original

  3. externalizing SQL statements into DB[ Go to top ]

    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.
    Reply to this Reply to original

  4. externalizing SQL statements into DB[ Go to top ]

    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 ?
    Reply to this Reply to original