Entries for Wednesday 08 February 2006, Colin's blog

CancellableFormController: about time :)

Writing about web page http://www.springframework.org/docs/api/org/springframework/web/servlet/mvc/CancellableFormController.html

Whenever I use Spring on a project there are a number of utilties that I end up writing time and time again (why don't I just copy them, or submit them to Spring; because I am a contractor and don't "own" the code I write :().

One of them is an extension to SimpleFormController which will understands a cancel request.

At long last ;) Spring has finally come up with their own implementation: link

Yeah!

: 08 Feb 2006 20:34 | Comments (0) | Close comments | Report a problem

Useful Spring tips

Writing about web page http://www.onjava.com/pub/a/onjava/2006/01/25/spring-xml-configuration-best-practices.html?page=1

Nothing too outstanding here, and I am not sure I agree with all of the tips, but a useful page for newbies :)

In particular, I disagree with not using autowiring; autowiring by type is safer than byName, and is very useful. Maybe I am making this point too strongly; I think it is fine to either use it or not.

The point I most strongly disagree with is preferring setter injection over constructor injection. That is just plain wrong ;) Constructors for collaborators which the class requires to work, setters for optional parameters. Simple as that.

But all in all, a nice quick read.

: 08 Feb 2006 20:27 | Tags: Spring Tips | Comments (2) | Close comments | Report a problem

SQL; I love it

So despite using SQL for about 8 years now; I have never really got to grips with the intracies of it.

Anyways, I had to count the number of rows that contained a unique combination of two columns.

In this case, I needed to find all the html pages that were deleted, all the html pages that were undeleted, all the binary pages that were deleted and all the binary pages that were undeleted.

So after banging my head against a brick SQL wall for a few minutes, I resorted to code:


public final class JdbcStatisticsDAO extends JdbcDaoSupport implements StatisticsDAO {
    @SuppressWarnings("unchecked")
    public PageStatistics getPageStatistics(final Page page) {
        String sql = "select discriminator, is_deleted from page where ….";
        List rows = super.getJdbcTemplate().queryForList(sql);
        int deletedFiles=0;
        int files = 0;
        int deletedPages = 0;
        int pages = 0;

        for (Map row: rows) {
            // ARRGGHH!! Unfortunately Oracle comes back as bigDecimal, hsqldb uses boolean.
            boolean isDeleted = ConversionUtils.isTrue(row.get("IS_DELETED"));
            String discriminator = (String) row.get("DISCRIMINATOR");

            if ("html".equals(discriminator)) {
                if (isDeleted) {
                    deletedPages++;
                } else {
                    pages++;
                }
            } else {
                if (isDeleted) {
                    deletedFiles++;
                } else {
                    files++;
                }
            }
        }
        PageStatisticsImpl stats = new PageStatisticsImpl();
        stats.setRecursiveNoOfFilesExcludeDelete(files);
        stats.setRecursiveNoOfFilesIncludeDelete(deletedFiles);
        stats.setRecursiveNoOfPagesExcludeDelete(pages);
        stats.setRecursiveNoOfPagesIncludeDelete(deletedPages);
        return stats;
    }

Lovely; isn't it :)

Then I happened to chat to the Oracle guru that is Hongfeng :) who pointed out that I should use a combination of count and groupBy. A few minutes later and Hongfeng managed to find the exact page in one of the 5642 Oracle reference manuals.

This results in this:


    public PageStatistics getPageStatistics(final Page page) {
        String sql = "select discriminator, is_deleted, count(*) as theCount from page where page.url = '" + page.getUrl() + "' or page.url like '" + page.getUrlPath() + "%' group by discriminator, is_deleted";
        List rows = super.getJdbcTemplate(). queryForList(sql);
        int deletedFiles=0;
        int files = 0;
        int deletedPages = 0;
        int pages = 0;

        for (Map row: rows) {
            // ARRGGHH!! Unfortunately Oracle comes back as bigDecimal, hsqldb uses boolean.
            boolean isDeleted = ConversionUtils.isTrue(row.get("IS_DELETED"));
            String discriminator = (String) row.get("DISCRIMINATOR");
            int count = Integer.valueOf(row.get("theCount").toString());    // oracle bigInt

            if ("html".equals(discriminator)) {
                if (isDeleted) {
                    deletedPages = count;
                } else {
                    pages = count;
                }
            } else {
                if (isDeleted) {
                    deletedFiles = count;
                } else {
                    files = count;
                }
            }
        }
        PageStatisticsImpl stats = new PageStatisticsImpl();
        stats.setRecursiveNoOfFilesExcludeDelete(files);
        stats.setRecursiveNoOfFilesIncludeDelete(deletedFiles);
        stats.setRecursiveNoOfPagesExcludeDelete(pages);
        stats.setRecursiveNoOfPagesIncludeDelete(deletedPages);
        return stats;
    }

The code is still ugly as anything; but the SQL is cool. Much more efficient.

Cheers Hongfeng :)

: 08 Feb 2006 15:29 | Tags: Sql | Comments (3) | Close comments | Report a problem