Announcement Announcement Module
Collapse
No announcement yet.
SQL and Spring ... Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL and Spring ...

    Sorry if this topic has already been discussed but I have tried to search for it and found nothing. Looking at all the examples of how to use JdbcTemplate in Spring everything seems to center around something like

    Code:
    jdbcTemplate.queryForInt("select count(*) from people")
    For small queries (like this) embedding SQL in the code seems like a perfectly reasonable thing to do however if we start to move onto a more complicated query like:

    Code:
    select p.firstname, p.secondname, t.name from person p, team t where p.username = 'mnp78' and p.id_person = t.id_person
    This begins to get a bit harder to read and any more joins or conditionals (what happens if we chuck a subquery in here) Java just cannot handle this (oh what I wouldn't give for the equivalent to qq/qw in Java ). Is there a known/generally accepted way of externalising SQL from a DAO in Spring?

    I only ask this since I'm a very recent convert to Spring but I do have access to quite a large amount of code which I wrote to get around the problems of using JDBC. I made an equivalent to iBATIS' SQLMaps but was developed with no knowledge of iBATIS. The code lets you perform something like this (CDATA is used because of the ' but normally this would be a prepared statement):

    Code:
    <sqllib>
    
    	<sql name="getUserDetails">
    		<![CDATA[
    		select 
    			p.firstname, p.secondname, t.name 
    		from 
    			person p, team t 
    		where 
    			p.username = 'mnp78' 
    		and 
    			p.id_person = t.id_person
    		]]>
    	</sql>
    <sqllib>
    In your Java code this results in:

    Code:
    jdbcTemplate.queryForInt(getLib().getQuery("getUserDetails"));
    In this example Spring has injected the required SqlLib object in and is accessable from the method getLib(). Any SQL that is contained in the XML file can have its comments parsed out such as -- or /**/ and the engine understands Oracle's /*+xx */ format for hints (I normally work on Oracle so I don't know enough about other vendors to make this same assumption).

    If there is an official manner in providing SQL to a JDBC based DAO I would be really happy to learn of it. If not then I have two questions.
    1. Is there a need for this kind of externalisation? I personally find it very useful to externalise my SQL for readability and debugging
    2. Would anyone be interested in me developing this to go into one of the Spring modules?

    Any response is greatly appreciated. Thanks in advance

  • #2
    You could inject the SQL into your code as you would with anything else. If you use standard xml comments then you shouldn't need to explicitly strip them from the SQL. That should also avoid any horrible vendor specific stuff such as /*+RULE */

    Comment


    • #3
      Any chance of you using Hibernate?

      HQL is typically shorter and easier to read than equivalent SQL, and can typically be used for 90%+ of queries.

      We use SQL and HQL, and sometimes use our own IQuery implementations to modify SQL statements.

      Various IQuery implementations know how to modify a SQL statement, via a string manipulation utility class.

      StringBuilder baseSql = new StringBuilder("select x, y, z from blah");
      someQuery.modify(baseSql);
      someOtherQuery.modify(baseSql);

      This tends to clear things up by explaining why various snippets of SQL appear within a larger SQL statement.

      Comment


      • #4
        iBATIS is what you want

        I think that iBATIS offers much of what you are asking for and potentially a lot more. It allows you to keep all your SQL in XML files, no matter how complex and Oracle specific it may be. Then you make a simple one-liner Java call to execute the named piece of SQL, with iBATIS taking care of passing in and out the parameters via Maps or object properties.

        Personally I much prefer this approach to that of Hibernate, since I have total control of the SQL and can use as much Oracle goodness as I want.

        Comment


        • #5
          It's really worth taking a 2nd look at Hibernate.

          Eager loading of an object graph via "inner join fetch" or "left join fetch" seriously reduces the number of SQL statements required.

          Add a 2nd level cache to that and you're getting a good speed improvement in a lot of cases.

          Add reduced tedium through using HQL and relying on mappings to specify joins and your code is much clearer than any SQL could be.

          Comment


          • #6
            You could inject the SQL into your code as you would with anything else.
            I'd second this option if you have existing SQL and mapping logic and aren't keen on changing it.

            If you use standard xml comments then you shouldn't need to explicitly strip them from the SQL. That should also avoid any horrible vendor specific stuff such as /*+RULE */
            If you use CDATA you should be able to insert any SQL.

            Comment


            • #7
              Originally posted by katentim
              If you use CDATA you should be able to insert any SQL.
              You are correct. What I was thinking, but neglected to explain, is that the advantage of the original poster's approach is that you could have the library code remove vendor specific aspects of the SQL. That would be more difficult using Spring alone, but I suspect that vendor changes are quite rare events, so the benefit is probably marginal.

              Originally posted by gmatthews
              Any chance of you using Hibernate?
              I'd love to use ORM, and I'll probably use hibernate next time I have a fresh project. However, there's no way it's going to be used on my current project. We've got a really crufty legacy database, and a very large existing codebase in multiple languages. It was badly designed in the beginning and has grown organically from there. Despite that it generally meets our customer's needs. There isn't any support in middle management to replace it with something better.

              Comment


              • #8
                Originally posted by LuckyStrike
                You are correct. What I was thinking, but neglected to explain, is that the advantage of the original poster's approach is that you could have the library code remove vendor specific aspects of the SQL.
                LuckyStrike is correct here. The code which parses XML has rules for removing SQL elements such as /**/ and --. This means I can paste my SQL from a program like SquirrelSQL into the XML without the need to remove any comments. The method I originally described also allows the SQL to be shared between programming languages; a powerful feature if the same function is re-implemented in another language.

                Just to go back to the original questions I made:
                1. It seems like there is a need for externalisation of SQL and this is echoed by frameworks like iBATIS
                2. The demand for a specific way of externalising SQL for JDBC seems to be low especially when solutions can be coded in Spring's configuration files

                If anyone is struggling with using JDBC, large SQL statements and wants to externalise them then mail me and I will make the relevant pieces of src available.

                Comment

                Working...
                X