A Visual Datbase Explorer for NetBeans

NetBeans provides a very nice Database Explorer, and in NB 6.0 the API will be more accessible for extensions (most important D&D support ...), but there is one thing that I've been missing: A visual component, that shows the database structure, like in DbVisualizer. DbVisualizer has many more features, but that is the one I was missing the most. The main asset of such a visual component is that it can show how the tables are related regarding foreign keys. Everything else can be shown in the available tree view as well. The tables and foreignkeys can be shown as a graph with the tables being the nodes, and the foreign keys building the edges; a perfect job for the Visual library:

The Visual Library ships with a lot of examples, and we can choose one to get started and adjust it to our needs. So lets go:

APIs used in this project:

• Actions API: To create a new Action in the context menu of the Connection node

• Nodes API: to add the action to the Connection we would like to analyze

• Visual Library: (Not an official API yet, but soon) for the graphical display

• Lookup API: to provide properties for the nodes

• Database Explorer API: to get the data

Prerequisites:

1. Netbeans 5.5
2. Download and unpack the visual library: http://graph.netbeans.org/files/documents/188/1292/Visual-Library-2.0-pre2.zip
3. Create a module suite "DBVisualExplorer" (I borrowed this from one of Tim Boudreau's excellent tutorials ):
   1. Choose File > New Project (Ctrl-Shift-N). Under Categories, select NetBeans Plug-in Modules. Under projects, select Module Suite Project and click Next.
   2. In the Name and Location panel, type "DBVisualExplorer" in Project Name. Change the Project Location to any directory on your computer, such as c:\mymodules. Click Finish.
4. Create a library wrapper module belonging to the DBVisualExplorer suite:
   1. use File -> New Project -> NetBeans Plug-in Modules -> Library Wrapper Module Project to launch the wizard.
   2. select Visual-Library-2.0-pre2\dist\org-netbeans-api-visual.jar as the library
   3. In the nextpanel select the "DBVisualExplorer" suite for Add to module suite
   4. click finish
5. Create the module where the action happens (I adapted this from one of Tim Boudreau's excellent tutorials ):
   1. Choose File > New Project (Ctrl-Shift-N) again. Under Categories, select NetBeans Plug-in Modules. Under projects, select Module Project and click Next.
   2. In the Name and Location panel, type VisualExplorer in Project Name. The default in the wizard should be to create the module underneath the directory where you just created the suite, which is fine. Click Next.
   3. In the Basic Module Configuration panel, replace yourorg in Code Name Base with myorg, so that the whole code name base is org.myorg.dbvisualexplorer. Click Finish
   4. Right click the project node > Properties > Sources> Source Level > 1.5

Create a Cookie Action

Now lets go where the action is:

1. Right click the package node of your and choose New > File/Folder
2. In the dialog, choose NetBeans Module Development > Action and click Next
3. Choose Contditionally enabled and add org.netbeans.modules.db.explorer.nodes.ConnectionNode as Cookie Class > click next
4. Choose global Menuitem > click next
5. Add "ShowDatabaseStructureAction" as name, "Show Database Structure " as Display name and choose an Icon >click finish

The Wizard automatically adds the Nodes and Actions API, but it won't add the API that contains the ConnectionNode, so we will have to do that to fix the imports:

1. Right click the project node and choose > Properties
2. Choose libraries and > add Dependency
3. Choose "Database Explorer" > click ok
4. In Module Dependencies > select "Database Explorer" and > click edit
5. Enter 0 for Major version, select Implementation Version and > click ok

Implementation Version means that we use the whole library, not just the public part. Normally you wouldn't do that, because the non public part of the library might change and this would break our code. Unfortunately the part of the API we need is not (yet?) public. So for the scope of a tutorial we will just ignore that rule.

1. go to your Action and fix the imports ( press Alt-Shift-F to fix imports)
2. Our Action is ready; you can now build and run your project. Right click project > Run Project

You will see a grayed out icon in the toolbar. If you go to the Runtime tab and click on a connection, the Toolbar button will be activated.

Create a TopComponent

Now that we have our Action we want it to do something. In our case it should launch a TopComponent that shows the DatabaseStructure. So lets create a topcomponentand link it with our action:

1. Right click the package node of your and choose New > File/Folder
2. In the dialog, choose NetBeans Module Development > Window Component
3. For Window Position select editor > click next
4. For the class name prefix enter "DBVisualExplorer" > click finish
5. Classes are generated and Matisse comes up, drop a JScrollPane on the DBVisualExplorer Window
6. Make it fit the Window and > drop a JPanel to it
7. In the Navigator right click jPanel1 > setLayout >BorderLayout
8. Switch to the source code and change getPersistenceType to return TopComponent.PERSISTENCE_NEVER;

The JPanel is going to hold our view later on, now we will link our action to the TopComponent. Two classes were generated by the wizard: DBVisualExplorerTopComponent and DBVisualExplorerAction. We won't use the latter, but we will ransack it for some useful code:

1. Switch to the DBVisualExplorerAction and and copy the content of the actionPerformed
2. Go to your ShowDatabaseStructureAction and add the code to the end of your performAction method
3. Do a test run: Clicking the button should now launch the DBVisualExplorerTopComponent

You might want to clean up a bit now. There are two entries related to the generated DBVisualExplorerAction in the layer.xml:

1. In your project tree open Important Files > XML Layer > This layer in context
2. In Actions > Window > right click de-eppleton-visualexplorer-DBVisualExplorerAction.instance > delete
3. In Menu > Window > > right click de-eppleton-visualexplorer-DBVisualExplorerAction.shadow > delete
4. In the source packages right click DBVisualExplorerAction.java > delete

Create a Graph View

Everything up to now was boilerplate code, things you probably have done before, or will do like this or similar a lot of times when developing NetBeans modules. Now for the fun part! We will use the Visual Library to create a GraphScene that we can use later on to display the DatabaseStructure. First we will use one of David Kaspar's cool examples to get started and create a scene. Then we will get a view from the scene and add it to our TopComponent.

So first we make the visual library visible for our project:

1. Right click the project node and choose > Properties
2. Choose libraries and > add Dependency
3. Choose "org-netbeans-api-visual" > click ok

Now we can create our Scene. We will base ours on VMDGraphScene.

1. Right click your package and create a New > Java Class
2. Call it DBGraphScene and > click finish
3. Replace the content with this:

package your.package.here 

import java.awt.Image;

import java.awt.Point;
import java.util.Arrays;
import java.util.List;
import org.netbeans.api.visual.vmd.VMDGraphScene;
import org.netbeans.api.visual.vmd.VMDNodeWidget;
import org.netbeans.api.visual.vmd.VMDPinWidget;
import org.openide.util.Utilities;

public class DBGraphScene extends VMDGraphScene{
    private static final Image IMAGE_LIST = Utilities.loadImage ("de/eppleton/visualexplorer/resources/list_16.png"); // NOI18N
    private static final Image IMAGE_CANVAS = Utilities.loadImage ("de/eppleton/visualexplorer/resources/custom_displayable_16.png"); // NOI18N
    private static final Image IMAGE_COMMAND = Utilities.loadImage ("de/eppleton/visualexplorer/resources/command_16.png"); // NOI18N
    private static final Image IMAGE_ITEM = Utilities.loadImage ("de/eppleton/visualexplorer/resources/item_16.png"); // NOI18N
    private static final Image GLYPH_PRE_CODE = Utilities.loadImage ("de/eppleton/visualexplorer/resources/preCodeGlyph.png"); // NOI18N
    private static final Image GLYPH_POST_CODE = Utilities.loadImage ("de/eppleton/visualexplorer/resources/postCodeGlyph.png"); // NOI18N
    private static final Image GLYPH_CANCEL = Utilities.loadImage ("de/eppleton/visualexplorer/resources/cancelGlyph.png"); // NOI18N
    private static int nodeID = 1;
    private static int edgeID = 1;
    /** Creates a new instance of DBGraphScene */

    public DBGraphScene() {
        String mobile = createNode (this, 100, 100, IMAGE_LIST, "menu", "List", null);
        createPin (this, mobile, "start", IMAGE_ITEM, "Start", "Element");
        String game = createNode (this, 600, 100, IMAGE_CANVAS, "gameCanvas", "MyCanvas", Arrays.asList (GLYPH_PRE_CODE, GLYPH_CANCEL, GLYPH_POST_CODE));
        createPin (this, game, "ok", IMAGE_COMMAND, "okCommand1", "Command");
        createEdge (this, "start", game);
        createEdge (this, "ok", mobile);
    }

    private static String createNode (VMDGraphScene scene, int x, int y, Image image, String name, String type, List<Image> glyphs) {
        String nodeID = "node" + DBGraphScene.nodeID ++;
        VMDNodeWidget widget = (VMDNodeWidget) scene.addNode (nodeID);
        widget.setPreferredLocation (new Point (x, y));
        widget.setNodeProperties (image, name, type, glyphs);
        scene.addPin (nodeID, nodeID + VMDGraphScene.PIN_ID_DEFAULT_SUFFIX);
        return nodeID;
    }


    private static void createPin (VMDGraphScene scene, String nodeID, String pinID, Image image, String name, String type) {
        ((VMDPinWidget) scene.addPin (nodeID, pinID)).setProperties (name, null);
    }


    private static void createEdge (VMDGraphScene scene, String sourcePinID, String targetNodeID) {
        String edgeID = "edge" + DBGraphScene.edgeID ++;
        scene.addEdge (edgeID);
        scene.setEdgeSource (edgeID, sourcePinID);
        scene.setEdgeTarget (edgeID, targetNodeID + VMDGraphScene.PIN_ID_DEFAULT_SUFFIX);
    }
}

This class is based on David's VMDTest. Only three methods are necessary to create the graph. createNode creates a VMDNodeWidget, an advanced Widget that can hold a dropdown list with so called "Pins". The Pins are added to the nodes with the createPin method. They can be used as source or target for a connection (edge) between two nodes. And at last the connection iis established via the createEdge method. At the top there are some Images declared. We will need to create a folder for the images and copy them from the Visual library (Visual-Library-2.0-pre2/sources/graph/examples/src/test/resources). We will use it to simply provide something to show. Don't care about the content... Let's see if it works:

1. Add an instance variable to DBVisualExplorerTopComponent: private DBGraphScene scene = new DBGraphScene();
2. In the constructor add this as the last line: jPanel1.add( scene.createView());
3. Right click DBVisualExplorer > Clean and Build all
4. Right click DBVisualExplorer > Run
5. You should see this:

Beautiful, isn't it? We have linked the TopComponent to our DBGraphScene. In the next section we'll use this mechanism to add real data.

Add Real Data

Our TopComponent looks nice, but it's got nothing to do with our DatabaseConnection. Let's fix that. We will get the Database information from our ConnectionNode:

1. Go to ShowDatabaseStructureAction replace the performAction method with this:

 protected void performAction(Node[] activatedNodes) {
        ConnectionNode connectionNode = (ConnectionNode) activatedNodes[0].getLookup().lookup(ConnectionNode.class);
        DatabaseConnection databaseConnection = connectionNode.getInfo().getDatabaseConnection().getDatabaseConnection();
        if (databaseConnection.getJDBCConnection() == null) {
            ConnectionManager.getDefault().showConnectionDialog(databaseConnection);
        }
        Connection connection = databaseConnection.getJDBCConnection();
        DBVisualExplorerTopComponent win = DBVisualExplorerTopComponent.findInstance();
        win.open();
        win.requestActive();
        win.setConnection(connection);
    }

1. Fix imports (java.sql.Connection) > The IDE will complain, that there is no such method as setConnection(java.sql.Connection)
2. Go to DBVisualExplorerTopComponent change the constructor and add the missing method > fix imports

 
private DBVisualExplorerTopComponent() {
        System.out.println("initComponents");
        initComponents();
        setName(NbBundle.getMessage(DBVisualExplorerTopComponent.class, "CTL_DBVisualExplorerTopComponent"));
        setToolTipText(NbBundle.getMessage(DBVisualExplorerTopComponent.class, "HINT_DBVisualExplorerTopComponent"));
        //        setIcon(Utilities.loadImage(ICON_PATH, true));
    }

    public void setConnection(Connection connection){
        scene = new DBGraphScene(connection);
        jPanel1.removeAll();
        JComponent component =  scene.createView();
        jPanel1.add(component);
    }

Again the IDE will complain that there is no such Constructor for DBGraphScene

1. Go to DBGraphScene, and replace the content with this:

import java.awt.Image;
import java.awt.Point;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.netbeans.api.visual.vmd.VMDGraphScene;
import org.netbeans.api.visual.vmd.VMDNodeWidget;
import org.netbeans.api.visual.vmd.VMDPinWidget;
import org.openide.util.Utilities;

/**
 *
 * @author Anton Epple
 */

public class DBGraphScene extends VMDGraphScene{

    private static final Image IMAGE_LIST = Utilities.loadImage ("de/eppleton/visualexplorer/resources/list_16.png"); // NOI18N
    private static final Image IMAGE_ITEM = Utilities.loadImage ("de/eppleton/visualexplorer/resources/item_16.png"); // NOI18N
    private static int edgeID = 1;

    /** Creates a new instance of DBGraphScene */

    public DBGraphScene(Connection connection) {
        try{
            createSceneFromConnection(connection);
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    private  void createSceneFromConnection(Connection jdbcConnection) throws SQLException{
        ArrayList <String>tables = new ArrayList<String>();
        DatabaseMetaData databaseMetaData =  jdbcConnection.getMetaData();
        String[] names = {"TABLE"};
        ResultSet resultSet = databaseMetaData.getTables(null, "%", "%", names);
        while(resultSet.next()) {
             String table  = resultSet.getString("TABLE_NAME" );
             tables.add(table);
             createNode (this, (int)(Math.random()*800), (int)(Math.random()*800), IMAGE_LIST, table, "Table", null);
             ResultSet columns = jdbcConnection.getMetaData().getColumns(null, null, table.toUpperCase(), "%");
             while (columns.next()){
                String columnName = columns.getString("COLUMN_NAME");
                createPin (this, table, table+":"+columnName, IMAGE_ITEM, columnName, columnName);
            }
        }

        for (String string : tables) {
            ResultSet resultSet1 = databaseMetaData.getExportedKeys(null, null, string);
            while(resultSet1.next()){
               String pkTable = resultSet1.getString("PKTABLE_NAME");
               String pkColumn = resultSet1.getString("PKCOLUMN_NAME");
               String fkTable =   resultSet1.getString("FKTABLE_NAME");  
               String fkColumn = resultSet1.getString("FKCOLUMN_NAME");
               createEdge (this,  fkTable+":"+fkColumn, pkTable+":"+pkColumn );
            }
        }
        this.moveTo(null);
    }

    

    private static String createNode (VMDGraphScene scene, int x, int y, Image image, String name, String type, java.util.List<Image> glyphs) {
        String nodeID = name;
        VMDNodeWidget widget = (VMDNodeWidget) scene.addNode (nodeID);
        widget.setPreferredLocation (new Point (x, y));
        widget.setNodeProperties (image, name, type, glyphs);
        return nodeID;
    }

    private static void createPin (VMDGraphScene scene, String nodeID, String pinID, Image image, String name, String type) {
        ((VMDPinWidget) scene.addPin (nodeID, pinID)).setProperties (name, null);
    }

    private static void createEdge (VMDGraphScene scene, String sourcePinID, String targetPinID) {
        String edgeID = "edge" + DBGraphScene.edgeID ++;
        scene.addEdge (edgeID);
        System.out.println("createEdge "+sourcePinID+"<->"+targetPinID);
        scene.setEdgeSource (edgeID, sourcePinID);
        scene.setEdgeTarget (edgeID, targetPinID);
    } 

    private void moveTo (Point point) {
        int index = 0;
        for (String node : getNodes ())
            getSceneAnimator ().animatePreferredLocation (findWidget (node), point != null ? point : new Point (++ index * 100, index * 100));
    }
}

The createSceneFromConnection method gets the DatabaseMetadata ( jdbcConnection.getMetaData() ) from the Connection. With the subsequent calls to getTables table structure is retrieved and the nodes of the graph are created. For every table getColumns is called and a Pin is added for every column to the table node. These pins can be used in the next step to create a connection between two tables. Now we iterate through the tables and call getExportedKeys to get hold of the ForeignKeys. For every exported key an edge is created between the pins of the related columns. The moveTo method does the animation, when the window is opened.

If you run the application again, you should see something like this:

We could enhance that with a nice graph layout algorithm ( I beautified the layout for the screenshot manually, but as far as I know David is working on a orthogonal layout, so this might be there out of the box quite soon also).

contributed by Toni Epple