Learn Eclipse Keyboard Shortcuts Easily

Disclaimer: Some users experienced this plugin to break their Eclipse Juno (see comments). Use at own risk!

Using keyboard shortcuts can vastly increase your productivity. Instead of switching between keyboard and mouse all the times, trying to figure out where that mouse cursor is, left/right clicking your way through context menus nested several levels deep, you could’ve just hit AltShift-I, for instance, for inlining constants, variables, methods, etc… But how to remember the most important ones? Tricky…

Except if you’re using the mousefeed Eclipse plugin:
http://www.mousefeed.com/ (ignore the bad page layout)

This plugin will show a very annoying and obnoxious, big popup indicating the keyboard shortcut that you could’ve used just now instead of clicking through Eclipse menus with your mouse. For instance:

A shortcut reminder

A shortcut reminder

This will eventually annoy you so much that you’ll willingly use the suggested keyboard shortcut just to make the popup go away! And the best thing is, you’ll only learn the shortcuts for those actions that you really execute very often.

Be productive again!

The IS DISTINCT FROM predicate

The SQL-1999 standard specifies a useful IS DISTINCT FROM predicate, that behaves slightly different from the regular not equal comparison predicate. Here is its simple definition:

8.13 <distinct predicate>

Format
<distinct predicate> ::=
  <row value expression 3> IS DISTINCT FROM <row value expression 4>
  <row value expression 3> ::= <row value expression>
  <row value expression 4> ::= <row value expression>

Purpose of the DISTINCT predicate

The purpose of this DISTINCT predicate is to alleviate handling NULL / UNKNOWN values in predicates. This is best explained using the following truth tables:

Not equal comparison predicate

To many SQL beginners, the following truth table might appear a bit awkward to memorise, as it is not really intuitive if you know NULL from other languages.

  • NULL != NULL yields NULL (not FALSE!)
  • NULL != [ANY] yields NULL (not TRUE!)
  • [ANY] != NULL yields NULL (not TRUE!)
  • [ANY] != [ANY] yields TRUE/FALSE

DISTINCT predicate

The DISTINCT predicate treats NULL like most other languages do

  • NULL IS DISTINCT FROM NULL yields FALSE
  • NULL IS DISTINCT FROM [ANY] yields TRUE
  • [ANY] IS DISTINCT FROM NULL yields TRUE
  • [ANY] IS DISTINCT FROM [ANY] yields TRUE/FALSE
  • NULL IS NOT DISTINCT FROM NULL yields TRUE
  • NULL IS NOT DISTINCT FROM [ANY] yields FALSE
  • [ANY] IS NOT DISTINCT FROM NULL yields FALSE
  • [ANY] IS NOT DISTINCT FROM [ANY] yields TRUE/FALSE

Support of the DISTINCT predicate in various SQL dialects

Not all SQL dialects natively support this predicate. As a matter of fact, only these few open source ones do:

  • Firebird
  • H2
  • HSQLDB
  • Postgres

MySQL

MySQL doesn’t support the SQL standard DISTINCT predicate, but it knows a special equal-to (<=>) operator that behaves the same way

The others

The other databases currently supported by jOOQ do not implement the DISTINCT predicate. These include

  • CUBRID
  • DB2
  • Derby
  • Ingres
  • Oracle
  • SQL Server
  • SQLite
  • Sybase ASE
  • Sybase SQL Anywhere

However, the DISTINCT predicate is simulated by jOOQ as follows:

CASE WHEN [this] IS     NULL AND [field] IS     NULL THEN FALSE
     WHEN [this] IS     NULL AND [field] IS NOT NULL THEN TRUE
     WHEN [this] IS NOT NULL AND [field] IS     NULL THEN TRUE
     WHEN [this] =               [field]             THEN FALSE
     ELSE                                                 TRUE
END

Publicly available SQL standard documents

For your reference, here is a list of publicly available SQL standard documents. Some are very late draft versions, where the final version is closed source and has to be purchased from ISO or ANSI directly:

The links were taken from here:

http://wiki.postgresql.org/wiki/Developer_FAQ

How to get Oracle execution plans with Starts, E-Rows, A-Rows and A-Time columns

This can probably be found elsewhere as well, but here’s a short wrap-up how to get the most out of your execution plans, quickly

1. Be sure the actual rows and time statistics are collected.

You can do this with

-- login as user sys
alter system set statistics_level = all;

2. Execute your bad SQL.

I can’t give you an example, because I don’t write bad SQL.

3. ūüėČ

4. Find your sql_id with this statement

-- these are the most important columns
select last_active_time, sql_id, child_number, sql_text
from v$sql
-- filter for your statement
where upper(sql_fulltext) like
  upper('%[put some text from your SQL statement here]%')
-- this orders by the most recent activity
order by last_active_time desc;

5. Get the cursor and plan for that statement

select rownum, t.* from table(dbms_xplan.display_cursor(
  -- Put the previously retrieved sql_id here
  sql_id => '6dt9vvx9gmd1x',
  -- The cursor child number, in case there are
  -- several plans per cursor
  cursor_child_no => 0,
  -- Some formatting instructions to get Starts,
  -- E-Rows, A-Rows and A-Time
  FORMAT => 'ALL ALLSTATS LAST')) t;

6. Purge the cursors, if needed:

select address || ',' || hash_value from v$sqlarea
where sql_id = '6dt9vvx9gmd1x';

begin
  sys.dbms_shared_pool.purge(
    '00000000F3471988,2224337167','C',1);
end;

7. Delete all execution plans

-- login as user sys
alter system flush shared_pool;

8. Delete buffer cache (IO cache)

-- login as user sys
alter system flush buffer_cache;

A nice way of using jOOQ with Spring

This blog post is outdated. For a more up-to-date example of how to integrate jOOQ with Spring, please consider the relevant sections of the jOOQ manual!

A nice way of using jOOQ with Spring was recently discussed on Stack Overflow by Adam Gent:
http://adamgent.com/post/31128631472/getting-jooq-to-work-with-spring-correctly

The essence of it was given here in this gist:

package com.snaphop.jooq;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.jooq.ExecuteContext;
import org.jooq.impl.DefaultExecuteListener;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
import org.springframework.jdbc.support.SQLExceptionTranslator;
import org.springframework.jdbc.support.SQLStateSQLExceptionTranslator;

/**
 * Example provided by Adam Gent
 */
public class SpringExceptionTranslationExecuteListener
extends DefaultExecuteListener {

    @Override
    public void start(ExecuteContext ctx) {
        DataSource dataSource = ctx.getDataSource();
        Connection c = DataSourceUtils.getConnection(dataSource);
        ctx.setConnection(c);
    }

    @Override
    public void exception(ExecuteContext ctx) {
        SQLException ex = ctx.sqlException();
        Statement stmt = ctx.statement();
        Connection con = ctx.getConnection();
        DataSource dataSource = ctx.getDataSource();
        // This note and code below comes from
        // JdbcTemplate.execute(StatementCallback)
        // Release Connection early, to avoid potential connection pool
        // deadlock in the case when the exception translator hasn't
        // been initialized yet.
        JdbcUtils.closeStatement(stmt);
        stmt = null;
        DataSourceUtils.releaseConnection(con, dataSource);
        con = null;
        ctx.exception(getExceptionTranslator(dataSource)
                        .translate("jOOQ", ctx.sql(), ex));
    }

    /**
     * Return the exception translator for this instance.
     *
Creates a default {@link SQLErrorCodeSQLExceptionTranslator}
     * for the specified DataSource if none set, or a
     * {@link SQLStateSQLExceptionTranslator} in case of no DataSource.
     * @see #getDataSource()
     */
    public synchronized SQLExceptionTranslator
    getExceptionTranslator(DataSource dataSource) {
        // This method probably does not need to be synchronized but in
        // Spring it was because of a mutable field on the JdbcTemplate.
        // Also I have no idea how expensive it is to create a translator
        // as one will get created on every exception.
        final SQLExceptionTranslator exceptionTranslator;
        if (dataSource != null) {
            exceptionTranslator =
                new SQLErrorCodeSQLExceptionTranslator(dataSource);
        }
        else {
            exceptionTranslator = new SQLStateSQLExceptionTranslator();
        }
        return exceptionTranslator;
    }
}

See the relevant Stack Overflow answer for more details:
http://stackoverflow.com/a/12326885/521799

Throw checked exceptions like runtime exceptions in Java

How to throw a checked exception without catch block or throws clause in Java? Simple!

public class Test {

    // No throws clause here
    public static void main(String[] args) {
        doThrow(new SQLException());
    }

    static void doThrow(Exception e) {
        Test.<RuntimeException> doThrow0(e);
    }

    @SuppressWarnings("unchecked")
    static <E extends Exception> void doThrow0(Exception e) throws E {
        throw (E) e;
    }
}

Due to generic type erasure, the compiler will compile something here that really shouldn’t compile. Crazy? Yes. Scary? Definitely!

The generated bytecode for doThrow() and doThrow0() can be seen here:

  // Method descriptor #22 (Ljava/lang/Exception;)V
  // Stack: 1, Locals: 1
  static void doThrow(java.lang.Exception e);
    0  aload_0 [e]
    1  invokestatic Test.doThrow0(java.lang.Exception) : void [25]
    4  return
      Line numbers:
        [pc: 0, line: 11]
        [pc: 4, line: 12]
      Local variable table:
        [pc: 0, pc: 5] local: e index: 0 type: java.lang.Exception

  // Method descriptor #22 (Ljava/lang/Exception;)V
  // Signature: <E:Ljava/lang/Exception;>(Ljava/lang/Exception;)V^TE;
  // Stack: 1, Locals: 1
  static void doThrow0(java.lang.Exception e) throws java.lang.Exception;
    0  aload_0 [e]
    1  athrow
      Line numbers:
        [pc: 0, line: 16]
      Local variable table:
        [pc: 0, pc: 2] local: e index: 0 type: java.lang.Exception

As can be seen, the JVM doesn’t seem to have a problem with the checked exception thrown from doThrow0(). In other words, checked and unchecked exceptions are mere syntactic sugar

Alvor: Static SQL analysis in Strings passed to JDBC

I have recently discovered this nice Eclipse plugin here:
http://code.google.com/p/alvor/

It evaluates String, StringBuilder, StringBuffer, CharSequence and¬†many other types passed to JDBC method for subsequent execution. It¬†doesn’t do a bad job at this, even if it is in beta mode. The rate of¬†false positives¬†that I have experienced is around 20% for regular SQL statements, and 100% for¬†stored procedure calls (which seem not to be supported). Checks¬†include:

  • Syntax correctness
  • Semantics correctness
  • Object availability

It does so by

  • Comparing SQL against its own internal SQL grammar
  • Checking SQL statements against an actual database (provided a JDBC¬†driver, JDBC URL, user, password)

This is extremely powerful, as it can find common bugs resulting from¬†bad SQL string concatenation, misspelled table / column names, type¬†mismatches, etc. With findbugs’ capabilities of analysing control¬†flows, this could be made even better to detect even remote¬†corner-cases or SQL passed to methods for the concatenation of¬†sub-clauses and sub-selects. An example screenshot is given on their website:

An Alvor Screenshot

 

Check out Alvor for yourself here:
http://code.google.com/p/alvor/

Note, I have also posted a request to the FindBugs mailing list here:
https://mailman.cs.umd.edu/pipermail/findbugs-discuss/2012-September/003634.html

Static SQL analysis of this sort would be a great addition to FindBugs. If you think so, too, please support my request on the FindBugs mailing list