February 08, 2006

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:  |  Comments (3) |  Report a problem

- 3 comments by 1 or more people Not publicly viewable

  1. Hey, any chance you could avoid posting run-on lines of code to the blogs, they really, really mess up the page layout!

    :¬)

    08 Feb 2006, 16:55

  2. I will have a chat with one of the blog guys to see whether this can be done automatically in textile… Otherwise yes; I will try and behave :)

    08 Feb 2006, 17:47

  3. Mike

    Hi Colin,

    Just put the code in a DIV with the css marked with "overflow:scroll" and it'll appear with scrollbars…

    Cheers

    Mike

    PS congratulations on being published. Always good to see fellow Brits involved with OSS.

    24 Feb 2006, 15:29

Add a comment

You are not allowed to comment on this entry as it has restricted commenting permissions.

February 2006

Mo Tu We Th Fr Sa Su
Jan |  Today  | Mar
      1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28               

Search this blog

Tags

Galleries

Most recent comments

  • Interesting… While I'm not completely convinced in such microbenchmarks, I'm pretty sure that 1ms … by Alexander Snaps on this entry
  • Hello. I bought the book yesterday. I was trying to find the source code for chapter 11 and chapter … by Suleman on this entry
  • http://woosight.net/account/login?username=demo by live mashup demo on this entry
  • Thanks mate ….. This blog was really helpful. by Maaz Hurzuk on this entry
  • Ty. Not directly helpful for my problem, but pointed me in the right direction. You will also get th… by Mike E. on this entry

Blog archive

Loading…
Not signed in
Sign in

Powered by BlogBuilder
© MMXX