Monday, September 16, 2013

Externalizing Queries in Spring

Excessively large queries constructed in Java code (more than say, 10 lines) can be difficult to maintain - a lot of concatenation is used, even for static queries, which makes formatting for legibility difficult. In addition, the ability to extract the query for further tuning, debugging or other exploration is tedious. An excellent solution to this maintenance problem is to externalize the queries. Below are suggested approaches for both JPA and SQL (e.g., JDBCTemplate).


Create an XML properties file (e.g., in META-INF/queries.xml. In conjunction with CDATA this allows for multi-line queries with adequate legibility formatting.

<!DOCTYPE properties SYSTEM "">
  <entry key="query.1">
    select foo, bar, baz
      from flibberdigibit
     where foo in (
        select from blah-di-blah
     and created_at < {fn now()}

Declare the bean in context.xml:

<util:properties id="externalQueries" location="classpath:META-INF/queries.xml"/>

Assuming a data source is defined in context.xml, use the external query via the JdbcTemplate:

Properties externalQueries;

DataSource dataSource;

String sql = externalQueries.getProperty("query.1");
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
List<Map<String, Object>> results = jdbcTemplate.queryForList(sql);

The above code snippet uses a List<Map<String, Object>> return value, but if the set were large enough, a RowCallbackHandler or ResultSetExtractor could be called.


In persistence.xml, include a pointer to mapping file. Even if all classes are annotated, a mapping file allows for definition of named queries.


In the mapping file, include something like:

<!-- namespaces left out for clarity -->
    <!-- this can be native or hql -->
    <named-native-query name="myNamedQuery" result-set-mapping="no-op">
        <![CDATA[       …ginormous query here…    ]]>

In the appropriate Java code:

Query query = entityManager.createNamedQuery("myNamedQuery", Foo.class);
List<Foo> results = query.getResultList();

Friday, August 2, 2013

Upgrading PostgreSQL via Homebrew

Homebrew upgrade PostgreSQL from 9.1 to 9.2 needs some attention to convert the data. I did:

mv /usr/local/var/postgres /usr/local/var/postgres-9.1
mkdir /usr/local/var/postgres
pg_upgrade -b /usr/local/Cellar/postgres/9.1.4/bin -B /usr/local/Cellar/postgres/9.2.4/bin -d /usr/local/var/postgres-9.1 -D /usr/local/var/postgres

This assumes that the postmaster is not running (which, in my case it wasn't because of the upgrade). I imagine there is a smoother way to do this in the future...

Thursday, May 23, 2013

Installing Python Audio Tools

On Linux Mint, make sure the following are installed:



sudo make install
Also, for reading mp3 files: sudo apt-get install mpg123

Monday, February 11, 2013

Using Java Aspects to Support Monitoring

I am running a Java/Spring application in Heroku's Platform as a Service using New Relic as the monitoring and performance analysis tool. Normally, New Relic makes it easy to see what web transactions are doing because they are usually named by their URL.

However, for multiple reasons (framework, coding conventions on our part), the transactions all lumped under a generic name (e.g., /servletName), adding a layer of indirection in identifying problematic pages or REST API calls at a glance. No problem - New Relic supports multiple ways to set a name for a transaction, including via a request attribute. The challenge is ensuring that all methods that we want to track get a meaningful name. The ugly solution would be to mine the code and sprinkle the logic everywhere. Less ugly would be finding a way to do it via a base class or interceptors.

This turns out to be a perfect use case for aspects - setting a transaction name for a third party monitoring tool is a cross-cutting concern that can and should be decoupled from the mainline logic of the system.

The solution requires three pieces -

  • The aspect to set the transaction name
  • A marker annotation used to identify the join point
  • Annotating the relevant controllers (or base classes)

In addition, since we wanted as close to a 1:1 relationship between transaction and controller entry point, we specified two conditions for the join point - our marker annotation, and the spring RequestMapping annotation. Where appropriate, the annotation was added to a common base class and flagged @Inherited.

The aspect: The annotation: After that, getting the correct name to appear on for our transactions was a matter of putting @NamedTransaction on the common base class for our controllers. Now, the New Relic transactions show up as method calls, leading us straight to the problematic transactions. E.g., @ProfileController.getContacts(...)

Wednesday, March 16, 2011

Dork Alert

I heard Led Zeppelin's "Ramble On" the other day, and found myself obsessing over the Tolkien references in the lyrics. In a moment of grim realization, I realized I couldn't figure out which was worse:
  • Spending mental energy trying to derive a metaphorical interpretation ("Oh man! The girl is the RING!")
  • Fretting that the lyrics don't align to the LOTR canon to begin with

Tuesday, January 18, 2011

From Internet Debunking to Wobbly Knees

I received yet another one of those infinitely forwarded emails from a family member that gets my skeptic gears a-churning. This particular email purported to show some harrowing ascent up sheer granite cliffs to a restaurant or tea house at the top of a mountain in China. This is combined with a promise that if one is brave enough to make the trek, then the food is free. It's essentially an email version of this (go ahead and look... I'll wait), with some added flair and the bit about free food.

Hm. That just seems a little too out there to be true, so I quickly set out to debunk the whole thing. Oh, how fortune taunts me!

From what I can find, the harrowing ascent is real, although the destination is not a restaurant, but a temple on a peak of Huashan Mountain, one of five holy mountains in China. What's more, you can find videos of people documenting their turn on the "plank walk" - the part of the hike where you traverse a stretch of cliff on rickety boards. Take a look:

Oh my. Here's the link if you can't see the embedded video:

And of course, here's where the interwebz works its dark magic . YouTube helpfully suggested related videos that, in the spirit of "oh-my-god-i-can't-look-away", I had to watch. I kept returning to two knee-wobbly favorites. The first is a trip along "El Camino del Rey" - a 100-year old gangway built for workers at a hydroelectric plant in Spain:

Again, the link if you can't see the embed:

The other is a classic of a tower worker getting to the top of a 1700+ foot transmission tower. Unfortunately, I can't link to it because it seems to get taken down every few days. Try googling for "climbing tall tower" and are likely to stumble across it, though. What I love about this one is that it just keeps on going. Every time you think he's about to top out, there's some crux - a ledge to mount, an ever-narrow ladder to mount.

Yet again, reality is way more fun than the internet rumor mill!

Thursday, November 11, 2010

How Googley

Useless trivia: Google owns a domain called This odd name is symbolic for... wait for it... 'googol', which is, of course, 1 x 10100, or 1e100 in scientific notation. Link.

Did I say useless? Turns out this is actually useful information to have, should you have kids and hold the position "Household CIO."

Why, you might ask? I was setting up parental controls for my kids' accounts on a new computer and terribly befuddled when my daughter couldn't connect to her GMail account. Other sites worked fine... the main Google site, Webkinz, etc. Turns out it's a combination of a few different things.

Firstly, OS X website/content controls work in one of three modes: unrestricted access to websites, access only to allowed websites, and "best attempt" filtering, dynamically allowing or denying access based on the website content. I had my daughter's account set to the latter, hopefully striking a good balance between access and protection - such are the aims of the Household CIO.

Secondly, under that setting, OS X tries to examine the content of a web site to determine whether to block it (looking for naughty words and such). GMail, however, forces the connection to SSL. When accessing sites with SSL, the content is encrypted such that OS X can't read it. Thus, it refuses the connection. The solution is normally to add the site in question to the list of allowed sites. Ok, fine... add a few https URLs (, Good to go.

But wait! That doesn't fix the problem. Turns out all Google applications communicate extensively with this mysterious; haven't figured out exactly how it's used yet (working on it), but adding to the "Allow" list enables gmail to work.

Frustration gives way to enlightenment. Or at least mild amusement at a next-to-useless factoid.