Monday, November 05, 2007

Using SQL Templates in IDEA SQL Query Plugin

If you are like me, a regular user of IDEA, you might also have the excellent SQL Query Plugin installed and use it daily. But you might not have noticed the little neat templates feature, that it has. It can save you a great deal of typing.

When the SQL Query Plugin pane is open, click its settings icon and choose the templates tab. See the picture below:

Say you have a table called "user", which have a column "username". If you find yourself querying this table on "username" again and again, you might create this template:

SELECT * FROM user WHERE username='$param1'

And name it something short, like "seluser" (short for "select user"). In the query editor area, where you normally write your queries, you simply type:

@seluser admin

And hit execute query (Ctrl-Enter), and it will execute your template, but with "admin" replaced into $param1. Of course, templates can have many parameters, and they are given as a comma-separated list when executing it.

Simple and easy. The example query above is very simple, and the queries you create templates for should be more complex for any real benefit to be harvested. I have one in my current project, where I need to join two tables on four (4) different columns, two get an equi-join. A template can safe me typing there.

One idea that just popped into my head, was if SQL Query Plugin could come with some pre-written templates, that matched the JDBC driver of the current connection. Like a bunch of queries to query into the valuable v$xxx data dictionary of Oracle bases. Just a thought.

No comments: