Spring JDBC: provide a simple utility API for loading parameterizable SQL strings from externalized Resources [SPR-9228]

[spring-projects-issues]

Keith Donald opened SPR-9228 and commented

It's much easier to maintain non-trivial SQL like the example below(1) when it is externalized into a separate file. It wouldn't take much to provide basic support in Spring JDBC for loading SQL from externalized resources. I don't want to bring in another tool like MyBatis just for this. I rolled my own implementation based on some code from ResourceDatabasePopulator(2).

(1)

SELECT o.id as organization_id, o.name as organization_name, o.logo as organization_logo, u.username as organization_username,
       l.id as league_id, l.slug as league_slug, l.sport as league_sport, l.format as league_format, l.nature as league_nature,
       s.number as season_number, s.name as season_name, s.picture as season_picture, s.start_date as season_start_date,
       v.venue_id, v.venue_name, v.venue_latitude, v.venue_longitude
  FROM seasons s
    INNER JOIN leagues l ON s.league = l.id
    INNER JOIN organizations o ON l.organization = o.id
    INNER JOIN (SELECT l.league, l.venue as venue_id, v.name as venue_name, v.latitude as venue_latitude, v.longitude as venue_longitude
                  FROM league_venues l
                    INNER JOIN venues v ON l.venue = v.id
                  WHERE l.primary_venue = true) v ON l.id = v.league
    LEFT OUTER JOIN usernames u ON o.id = u.organization
  WHERE s.status = 'o'

(2) https://gist.github.com/2018152

---

Affects: 3.1.1

Reference URL: http://stackoverflow.com/a/835323

Issue Links:

• Improve comment handling in ResourceDatabasePopulator [SPR-9261] #13899 Improve comment handling in ResourceDatabasePopulator

1 votes, 3 watchers

[spring-projects-issues]

Chris Beams commented

Putting in the General Backlog for now. Keith, from the gist it appears that this utility just reads the text in from the given resource, with the only SQL-specific functionality being stripping out comments. Am I missing something? Is the comment-removal functionality actually that important? Otherwise, this could be accomplished through a simple and general purpose resource-to-string utility.

[spring-projects-issues]

Keith Donald commented

Well we already have code for importing externalized SQL in the Spring JDBC module, but it's internal. This ticket would make that code available through some public, documented API. Yes the code allows SQL comments and I don't see why we would drop that when we already have it.

[spring-projects-issues]

Keith Donald commented

If there is any work at all done on Spring JDBC for Spring 3.2, I'd recommend this be included as part of it. It's not a lot of work and it's clear people want documented support for this feature (just look at all the stack overflow discussion asking about how to do this).

[spring-projects-issues]

Keith Donald commented

Even if we don't end up offer any kind of convenient Spring JDBC utility API for this, at a minimum I'd expect to see documentation on how to load in externalized SQL in the Spring JDBC reference. Expecting people to put one module (Spring JDBC) together with another (Resource To String or whatever) themselves doesn't leave a great impression that Spring is a well integrated and documented framework.