Tutorial 3 – Extended 1:1 View Generator (Xtend, v0.2.0, deprecated)

In this tutorial we build a JAR file with a client generator implementing the Java interface. A client generator runs in the same JVM as SQL Developer and does not require a server side installation. Hence a client generator is immediately ready for use within all database connections.

In tutorial 2 we’ve written a generator in PL/SQL. We rewrite this generator now in Xtend. In this tutorial we use command line tools and simple text editors as common denominators. However, feel free to use the Java IDE of your choice.

1. Download Maven

Download Apache Maven 3.3.9 or later from here.

2. Install Maven

Install Maven as described here.

3. Create directories

We use a standard Maven directory layout. Let’s create the directory structure for our POM and Xtend source file.

On *nix open a terminal window in any directory and enter the following command:

On Windows open a command window in any directory and enter the following command:

4. Create POM

Save the following POM as pom.xml in the previous created directory tutorial using UTF-8 encoding without BOM.

All dependencies are available in public Maven repositories. For a real plug-in you just have to change the highlighted lines to fit your needs.

5. Create generator

Save the following client generator as ExtendedView.xtend in the previous created directory tutorial-3/src/main/java  using UTF-8 encoding without BOM.

The implementation looks very similar to the final PL/SQL variant of tutorial-2. In difference to the PL/SQL interface we have to implement the Java interface completely. There is no default behaviour for getName, getDescription, getObjectTypes, etc.

Another difference is, that every method gets the active database connection as parameter conn. See lines 72 and 73 how this connection is used to access the database via Spring’s JdbcTemplate.

A further difference is that the getParams method returns a LinkedHashMap and the parameters are sorted by entry (See line 78). Hence no additional method to sort parameters is required.

But the most significant difference is the use of template expressions. The following screenshot shows the lines 110 to 114 in the Xtend editor within the Eclipse IDE:


On line 110 the template is initiated with tripple single quotes and terminated on line 114 with tripple single quotes. Within the template boilerplate text such as “CREATE OR REPLACE VIEW” is shown in grey background colour. Please note that the whitespaces before “CREATE OR REPLACE VIEW” are ignored. This allows a reasonable formatting of code. Xtend expressions are put in guillemets. For example «col.toLowerCase» on line 112. In this case the col variable of the for loop is converted to lower case using the standard java.lang.String method toLowerCase(). Xtend expressions give you access to the full Java stack and are compiled, this means better performance and less runtime errors.

6. Build JAR

Open a *nix terminal window or a Windows command window in the previous created tutorial-3 directory and execute the following command:

It will take a while to download all the dependencies into your local Maven repository. However, it will be faster for subsequent calls.

At the end of the build you should see a success message similar to the following:

After a successful build the directory structure produced by find . -type f -exec ls -lh {} \; | awk '{printf "%6s %s\n", $5, $9}' on a *nix system should look as follows:

On line 7 you see the resulting org.oddgen.plugin.extendedview-1.0.0-SNAPSHOT.jar in the target directory. It’s just 6 kilobytes since all dependent libraries are tagged as “provided” in the POM.

On line 11 you see the intermediate Java file which has been produced from our Xtend source and was used by the Java compiler.

7. Install JAR

SQL Developer 4.x is installed by extracting an archive file to a location of your choice. The location of the installation directory is neither standardised nor registered somewhere. However, typical locations of the SQL Developer root directory are:

  • On macOS (fka OS X):
    • /Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper
    • $HOME/Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper
  • On Windows
    • C:\app\sqldeveloper
    • %HOME%\app\sqldeveloper
    • C:\Program Files\sqldeveloper
    • C:\Program Files (x86)\sqldeveloper

We call this directory from now on SQLDEV_HOME. The SQL Developer extensions are stored in SQLDEV_HOME/sqldeveloper/extensions which we call SQLDEV_EXTENSION_HOME from now on. You find the extension oracle.sqldeveloper.worksheet.jar in this directory. Searching for this file might be another option to identify the location of SQLDEV_EXTENSION_HOME.

To install the client generator copy the previously created org.oddgen.plugin.extendedview-1.0.0-SNAPSHOT.jar file into SQLDEV_EXTENSION_HOME. For example

on macOS:

and on Windows:

You do not need to restart SQL Developer, just open or refresh a connection in the Generators window and the new client generator should show up as in the following screenshot:


8. Generate from user HR

Establish a connection to any user in the database such as the demo user HR and generate the views as shown in the next images.




9. Wrap-up

Powerful Template Expressions

Xtend template expressions leverage the underlying Java ecosystem and compile into Java. Templates do not need to be interpreted at runtime, this makes the execution faster and leads to less runtime errors. The excellent IDE support in Eclipse and IntelliJ IDEA makes the development of generators a pleasure.

oddgen in Maven Central

oddgen and all dependencies for the plugin development are available in public Maven repositories such as Maven Central. This makes it easier to build oddgen plugins with Maven or any other build system.

Copy JAR to Install

To install an oddgen plugin, you just need to copy the JAR file with the client generator into the SQL Developer extension directory.

Ready for all Connections

A client generator does not require a server side installation. Hence a client generator is immediately ready for use on all database instances.