All 1 entries tagged Sql

View all 3 entries tagged Sql on Warwick Blogs | View entries tagged Sql at Technorati | There are no images tagged Sql on this blog

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 :)


November 2020

Mo Tu We Th Fr Sa Su
Oct |  Today  |
                  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 29
30                  

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…
RSS2.0 Atom
Not signed in
Sign in

Powered by BlogBuilder
© MMXX