SQL Zone is brought to you in partnership with:

Geertjan is a DZone Zone Leader and has posted 466 posts at DZone. You can read more from them at their website. View Full User Profile

Let's Create... Our Own SQL Editor

09.04.2010
| 15325 views |
  • submit to reddit

Isn't it time you gained full control of your SQL work environment? Stop being limited by the tools foisted upon you and start creating your own. Not hard at all, either. Here's a complete tutorial for creating your very own SQL editor, which will look like this:

OK. Now, let's create it from scratch. Start up NetBeans IDE and use this template to create a basis for your application. Just click through it and you'll have new folders and files on disk that represent your project:

When you've clicked Next above, you'll be able to provide the name of your project:

And when you click Finish, the Projects window will show you your application structure:

You've now got a basic application that includes all the infrastructure you need (a module system, window system, file system, actions system, and more), without any content. Let's now add the content.

Now right-click the "SQLEditor" node above (i.e., the orange icon) and choose Properties. In the Project Properties dialog, expand the "java" node and then include the SQL Editor:

Click "Resolve" above and the IDE will include all the related modules. I.e., the SQL Editor module depends on other modules. Via the "Resolve" button, those dependencies will be identified and registered in your project.

Next, let's include support for Java DB:

Click "Resolve" again. Hurray, we're done. All the functionality for our own SQL editor is now available in our application.

Now we'll add a new module, just so that we can perform a few tweaks to our application. In other words, this will be a branding module. Right-click the "Modules" node and choose "Add New":

Name it something, such as "SQLBranding":

Provide a unique identifier for your new module and make sure to include a layer.xml file, which you'll use to mask out the default menus and toolbars you don't need in your application:

Click Finish above. Then right-click on the main package that is created in the module and choose New | Other. There you'll be able to create a new Module Install class, which will initialize the module when the application starts up:

What we want is to force the Services window in the application (i.e., this is a window in NetBeans IDE for working with databases) to open when the application starts. So, we will provide code in the Module Install class (which you created above) for finding that window and opening it. The code we will need comes from the Window System API. Right-click the Libraries node in the module, as shown below, and choose "Add Module Dependency":

Then browse to Window System API and click OK:

 

Tip: In the Projects window, right-click the module's "Libraries" node. Choose "Add Dependency" and set a dependency on the "Window System API". That's what we need to use the window system code in the snippet below:

Now, in the Module Install class, provide the following code:

public class Installer extends ModuleInstall {

    @Override
    public void restored() {
        WindowManager.getDefault().invokeWhenUIReady(
                new Runnable() {
            @Override
            public void run() {
                TopComponent svcWindow =
                        WindowManager.getDefault().
                        findTopComponent("services");
                svcWindow.open();
                svcWindow.requestActive();
            }
        });

    }

}

Now the window we need will be forced to open when the application starts.

Let's turn to some other ancillary matters now. We can change the default splash screen, via "Branding", which is a menu item that you see when you right-click on the application's node in the Projects window, producing the Branding Editor below:

And we can search all the strings in the modules that come from the NetBeans Platform, so that we can change the string "Services" to "Databases", for example. Or to some other custom string.

You can also hide the menu items and toolbar buttons that you don't need and perform similar wrap-up tasks to really customize the application to your specific business needs.

Let's now, just for fun, also include a file browser in our application. So, back in the Project Properties dialog of your application, choose Favorites under the "platform" node. While you're there, also enable the two AutoUpdate modules, so that the end user will be able to install plugins (i.e., new features and patches) that you or the community of your SQL editor will provide:

The application is now complete. Let's create a ZIP distribution for our end users, while noticing we can also create a Mac distribution or one for web starting the application:

 

After doing the above, the Files window shows your new ZIP distribution:

If you prefer, you can also create an installer for your application:

 

Once the application is unzipped or installed, click the launcher in the bin folder. Then you'll have the application with which this article started. Look in the Tools menu and, guess what? You find that you have a "Plugins" menu item, enabling extensions (i.e., features and patches) to be installed into the application.

Many thanks to Tim Sparg from CoreFreight in Johannesburg for inspiring this article.

Published at DZone with permission of its author, Geertjan Wielenga.

Comments

Tim Boudreau replied on Tue, 2010/09/07 - 1:15am

Suggestion for a follow-up: Use visual library to show the database schema, with arrows between foreign keys in one table and the column in another table that they come from. All the metadata you need to do this should be available using JDBC (just handle the case that the database is locked down and it isn't - but this usually won't be the case during development). Not only would it be a cool tutorial, but a much needed thing.

Geertjan Wielenga replied on Tue, 2010/09/07 - 10:11am in response to: Tim Boudreau

Matthew Carroll replied on Tue, 2010/09/07 - 1:38pm

Thank you for the article. Do you mean "expand 'ide'" instead of "expand 'java'" in this line near the beginning: In the Project Properties dialog, expand the "java" node and then include the SQL Editor:

Geertjan Wielenga replied on Tue, 2010/09/07 - 5:18pm in response to: Matthew Carroll

Yup, good point. Leave more messages here if you find other things, would be very helpful.

Markus Stegmaier replied on Thu, 2010/09/09 - 7:08am

Hi,

I always get some errors if I include the code above:

I have included it in SQLBranding ==> Source Packages ==> org.sqleditor.branding ==> Installer.java

Is this correkt??

 Warning: org.sqleditor.branding lacks a unit test dependency on org.netbeans.libs.junit4; using default dependencies for compatibility

 

 C:\Dokumente und Einstellungen\stegmaier\Eigene Dateien\NetBeansProjects\SQLEditor\SQLBranding\src\org\sqleditor\branding\Installer.java:21: cannot find symbol
symbol: class TopComponent
                TopComponent svcWindow =
C:\Dokumente und Einstellungen\stegmaier\Eigene Dateien\NetBeansProjects\SQLEditor\SQLBranding\src\org\sqleditor\branding\Installer.java:22: cannot find symbol
symbol: variable WindowManager
                        WindowManager.getDefault().
C:\Dokumente und Einstellungen\stegmaier\Eigene Dateien\NetBeansProjects\SQLEditor\SQLBranding\src\org\sqleditor\branding\Installer.java:17: cannot find symbol
symbol  : variable WindowManager
location: class org.sqleditor.branding.Installer
      WindowManager.getDefault().inkoveWhenUIReady(
3 errors
C:\Programme\NetBeans 6.9.1\harness\suite.xml:175: The following error occurred while executing this line:
C:\Programme\NetBeans 6.9.1\harness\common.xml:202: Compile failed; see the compiler error output for details.
BUILD FAILED (total time: 1 second)

 

Can you help me??

Greetings from Stuttgart/Germany

Tux1212

 

Geertjan Wielenga replied on Sat, 2010/09/11 - 5:43am

symbol  : variable WindowManager
location: class org.sqleditor.branding.Installer
      WindowManager.getDefault().inkoveWhenUIReady(
3 errors

 

 

From the above, you can see that you need "WindowManager" class. That's part of the Window System API. Add that module as a dependency to your module: go to the Project Properties dialog of the module and add "Window System API" as one of the APIs that the module depends on. Apologies, I should have specified that in the instructions above!

Igor Racic replied on Fri, 2010/09/17 - 10:24am

Hi, Just as feedback, I tried it with Oracle driver and it was too slow (don't know why). Even simple table select statment took around 10 seconds. (On some toy table with 10 lines) Correction: consecutive runs are faster. Still gives impression being slow... (maybe 2-3 seconds for this) Thank you and regards, Igor

Michał Szcześniak replied on Tue, 2010/11/16 - 8:01am

Hi,
the application is great however I encountered one problem when trying to change the number of displayed records from a query. The default is 20 records. When I try to change it to any other value I get the exception shown below. The same action performed in the Netbeans IDE (I use NB6.7.1) works flawlessly. Do you have any idea why this happens?
Thank you.
Michal

SEVERE [org.openide.util.RequestProcessor]: Error in RequestProcessor org.netbeans.modules.db.dataview.output.SQLExecutionHelper$5 java.lang.AssertionError: CloneableEditorSupport.getOpenedPanes() must be called from AWT thread only at org.openide.text.CloneableEditorSupport.getOpenedPanes(CloneableEditorSupport.java:1055) at org.netbeans.modules.db.sql.editor.ui.actions.ConnectionAction$ToolbarPresenter$1.getDefaultComponent(ConnectionAction.java:169) at javax.swing.SortingFocusTraversalPolicy.getFirstComponent(SortingFocusTraversalPolicy.java:447) at javax.swing.LayoutFocusTraversalPolicy.getFirstComponent(LayoutFocusTraversalPolicy.java:148) at javax.swing.SortingFocusTraversalPolicy.getDefaultComponent(SortingFocusTraversalPolicy.java:511) at javax.swing.SortingFocusTraversalPolicy.enumerateAndSortCycle(SortingFocusTraversalPolicy.java:131) at javax.swing.SortingFocusTraversalPolicy.getComponentAfter(SortingFocusTraversalPolicy.java:244) at javax.swing.LayoutFocusTraversalPolicy.getComponentAfter(LayoutFocusTraversalPolicy.java:88) at java.awt.Component.preNextFocusHelper(Component.java:7485) at java.awt.Component.nextFocusHelper(Component.java:7464) at java.awt.Container.nextFocusHelper(Container.java:3136) at java.awt.Component.nextFocus(Component.java:7460) at java.awt.Component.transferFocus(Component.java:7412) at java.awt.Component.doAutoTransfer(Component.java:7401) at java.awt.Component.autoTransferFocus(Component.java:7379) at java.awt.Component.disable(Component.java:1319) at javax.swing.JComponent.disable(JComponent.java:3579) at java.awt.Component.enable(Component.java:1300) at java.awt.Component.setEnabled(Component.java:1263) at javax.swing.JComponent.setEnabled(JComponent.java:2647) at org.netbeans.modules.db.dataview.output.DataViewUI.disableButtons(DataViewUI.java:231) at org.netbeans.modules.db.dataview.output.DataView.disableButtons(DataView.java:236) at org.netbeans.modules.db.dataview.output.SQLStatementExecutor.run(SQLStatementExecutor.java:92) at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:577) at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:1030) Caused: org.openide.util.RequestProcessor$Item: task failed due to at org.openide.util.RequestProcessor$Task.schedule(RequestProcessor.java:630) at org.netbeans.modules.db.dataview.output.SQLExecutionHelper.executeQuery(SQLExecutionHelper.java:470) at org.netbeans.modules.db.dataview.output.DataViewActionHandler.setMaxActionPerformed(DataViewActionHandler.java:114) at org.netbeans.modules.db.dataview.output.DataViewUI$2.actionPerformed(DataViewUI.java:341) at javax.swing.JTextField.fireActionPerformed(JTextField.java:492) at javax.swing.JTextField.postActionEvent(JTextField.java:705) at javax.swing.JTextField$NotifyAction.actionPerformed(JTextField.java:820) at javax.swing.SwingUtilities.notifyAction(SwingUtilities.java:1636) at javax.swing.JComponent.processKeyBinding(JComponent.java:2849) at javax.swing.JComponent.processKeyBindings(JComponent.java:2884) at javax.swing.JComponent.processKeyEvent(JComponent.java:2812) at java.awt.Component.processEvent(Component.java:5911) at java.awt.Container.processEvent(Container.java:2023) at java.awt.Component.dispatchEventImpl(Component.java:4501) at java.awt.Container.dispatchEventImpl(Container.java:2081) at java.awt.Component.dispatchEvent(Component.java:4331) at java.awt.KeyboardFocusManager.redispatchEvent(KeyboardFocusManager.java:1848) at java.awt.DefaultKeyboardFocusManager.dispatchKeyEvent(DefaultKeyboardFocusManager.java:704) at java.awt.DefaultKeyboardFocusManager.preDispatchKeyEvent(DefaultKeyboardFocusManager.java:969) at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(DefaultKeyboardFocusManager.java:841) at java.awt.DefaultKeyboardFocusManager.dispatchEvent(DefaultKeyboardFocusManager.java:668) at java.awt.Component.dispatchEventImpl(Component.java:4373) at java.awt.Container.dispatchEventImpl(Container.java:2081) at java.awt.Window.dispatchEventImpl(Window.java:2458) at java.awt.Component.dispatchEvent(Component.java:4331) at java.awt.EventQueue.dispatchEvent(EventQueue.java:599) at org.netbeans.core.TimableEventQueue.dispatchEvent(TimableEventQueue.java:104) at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269) at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184) at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161) [catch] at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)

Michał Szcześniak replied on Thu, 2010/11/18 - 3:48am

Hi,
the problem does not appear in Netbeans 6.9.1, however I had to recreate the project from scratch because it did not want to start up (it was created in NB6.7.1).

Best Regards

Hud Sef replied on Mon, 2011/02/28 - 7:26am

This is very good example, but i have most problems with low level customizations: - how to bind key (F5) to run selection or complete statement (when there is no selection) - how to add save as (and manage the files like in a project) - how to add Run post action (for example to save result set in xml) - how to add action, which will modify sql before run (to read parameters from file, remove nonstandard comments ...) - how to add custom actions (to run a script) Regards, Janez

Hud Sef replied on Mon, 2011/02/28 - 9:12am

This is very good example, but I have most problems with low level customizations
  • how to bind key (F5) to run selection or complete statement
  • how to add save as (and manage the files - project like tree)
  • how to add Run post action (for example to save result set in xml)
  • how to add action, which will modify sql before run (to read parameters from file, remove nonstandard comments ...)
  • how to add custom actions (to run a script)
Regards, Janez

Matt Coleman replied on Wed, 2012/07/18 - 2:08am

creating your own sql editor is such a great thing..thanks for the tips

 graphic designer buffalo

Faroq Al-tam replied on Wed, 2013/09/04 - 5:40pm

Hi 
Thank you for this amazing article.

Does anybody find out how to add more functionalities to the editor like:

- Keyboard-driven commands

- Additional buttons for exporting data and/or saving it from the table in the editor's window to external files.

Thanks in advance and appreaciate any help.
regards.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.