Use Apache Derby to develop Eclipse plug-ins

A demo using resource indexing

Get a demonstration on how to use resource indexing with the Apache Derby database to develop Eclipse plug-ins. The Derby database embedded in Eclipse allows you to create an SQL database on the client side with no security issues or network problems, such as an unstable connection or high latency. An SQL database and the JDBC API provide an easy way to store information and quickly search previously stored data.

Ilya Platonov (ill@isg.axmor.com), Software Engineer and Developer, Axmor

Ilya Platonov is a software engineer at Axmor Software, consulting at IBM Advanced Technology Solutions (ATS) Lab in Novosibirsk, Russia. He holds a Bachelor of Science (cum laude) degree in computer science from Novosibirsk State University. Within the last year he has participated in more than five ATS projects as a system architect and a software engineer.



Artem Papkov (artem@us.ibm.com), Solution Architect, IBM

Artem Papkov is currently a solution architect with IBM's Client Innovation Team, working with customers and Business Partners to adopt emerging technologies, such as SOA and Web services. After graduating from the Belarusian State University of Informatics and Radioelectronics in 1998 with master's degree in computer science, he joined IBM in Research Triangle Park, NC in 2000. His experience includes software development of multitier solutions using emerging technologies, architecture design, and integration of Internet-based solutions. For the past three years he has been focused on working closely with customers, helping them adopt Web services as IBM's strategic integration technology and SOA as the integration approach.



Jim Smith (jamessmi@us.ibm.com), Manager, IBM

Jim Smith has over 18 years of experience in software development. He started his career at Sandia National Labs in Livermore, California, designing high-speed data acquisition systems and distributed computing systems using a myriad of existing legacy code. With deep experience in Java language and customer-facing skills, Jim moved to the Emerging Internet Technologies team focusing on making Java solutions real for IBM customers. Jim was one of the founders of Advanced Technology Solutions (ATS), a global software services and development organization with a mission to develop, refine, and franchise advanced technologies and lightweight business processes for IBM, development labs, Business Partners, and customers, resulting in faster adoption and deployment of standard technologies and IBM products. Currently, Jim manages the organization.



Terry Finch (terryfin@us.ibm.com), IT Specialist, IBM, Intel, Microsoft,HP

Terry Finch is an IT specialist in the Customer Innovation Team responsible for leading customer projects involving new and emerging technologies. Since being hired in 2000, Terry has successfully completed numerous projects involving IBM WebSphere® Portal software, IBM Lotus® Domino® software, Java technology, XML, Web services, rich internet applications, and more. Recently, Terry has been focusing on rich user interface technologies, such as Macromedia Flex, Laszlo, IBM Workplace Client Technologies (IWCT), and Web 2.0 technologies like Ajax and QEDWiki.



16 January 2007

Also available in Chinese Russian Japanese

Apache Derby and Eclipse plug-ins

Eclipse is a powerful IDE platform that supports numerous frameworks used for the creation of GUI components (such as SWT or JFaces) or for working with data (such as the Eclipse Modeling Framework). One of the most exciting features of Eclipse is the ability to extend the IDE platform's functionality by creating new plug-ins. Eclipse supports both the above-mentioned frameworks for plug-in development and many other popular frameworks, including the Apache Derby database.

This article shows you how to use the Derby database for Eclipse plug-in development using resource indexing. The Derby database embedded in Eclipse lets you create an SQL database on the client side with no security issues or network problems, such as an unstable connection or high latency. An SQL database and the JDBC API provide an easy way of storing information and quickly searching previously stored data.

You'll get overviews of the following topics:

  • Integration of a Derby database into the Eclipse platform
  • Manipulating data in Eclipse using the Derby database
  • Resource indexing using the Eclipse Builder framework

Getting started

To start using the Derby database inside Eclipse, the first step is to download the Derby Eclipse plug-in. Choose the link under the Latest Official Release section. You'll find the following three plug-ins in the package:

  • Derby core plug-in, which provides Derby support for the Eclipse platform
  • Derby user interface (UI) plug-in, which provides UI components for working with the Derby database in the Eclipse platform
  • Derby UI documentation plug-in, which provides documentation for the UI plug-in

The Derby core plug-in allows the use of the Derby server and client libraries. Therefore, you can either create a Derby database in Eclipse or connect to an existing database. The Derby UI plug-in provides components and tools that are useful during development of an application that uses a Derby database. For example, this plug-in allows you to connect to an existing database and send SQL queries to the database (see the Resources section for a link to details).

To install these plug-ins, follow these steps:

  1. Extract files from the downloaded package into the Eclipse plug-ins directory (for example, /eclipse/plugins).
  2. Launch (or restart) Eclipse, and verify that the plug-ins were successfully installed by clicking Help > About Eclipse SDK > Plug-in details. You should see the Derby plug-ins in the list, as shown in Figure 1.
Figure 1. Derby plug-ins in Eclipse plug-ins listing
Derby plug-ins in Eclipse plug-ins listing

Note: If you want to test the example applications accompanying this article, you need at least the Derby core plug-in.

Simple code for testing Derby database plug-in functionality

Now you can create a simple plug-in to demonstrate the functionality of the core Derby plug-in. To do this, you use the Eclipse Plug-In Project wizard to create a new project called sample_derby, which is based on the Hello World template (see Figure 2).

Figure 2. Eclipse Hello World plug-in creation wizard
Eclipse Hello World plug-in creation wizard

The original Hello World plug-in simply creates an Eclipse menu element called Sample Menu with a Sample Action menu item. When a user clicks this menu item, the Hello World! dialog box pops up. The plug-in also allows calling the dialog box from the Eclipse toolbar. To see how this works, click the Run an Eclipse application link in the window of the plugin.xml file editor, or invoke it by clicking the Run button in the Eclipse toolbar and using the Eclipse application configuration.

Adding Derby support to the newly created plug-in is a matter of specifying a dependency on the Derby Core Plug-in for its configuration. You can do this by opening the plugin.xml file in the sample_derby project and selecting dependency on the org.apache.derby.core in the Required Plug-ins section of the Dependencies tab (see Figure 3). When you finish, your plug-in can use Derby classes.

Figure 3. Plug-ins dependency editor
Plug-ins dependency editor

Now you can find out how to manipulate a simple Derby database by extending the plug-in to provide a simple records counter for the Records table of the local database. This table contains information about how many records you have in the database. When a button is clicked, the number of records is increased by one, and a dialog box with the current number of records is displayed. First, open the SampleAction.java file that defines the class responsible for the Sample Action, and create a new method called queryRecords. At this point you're only connecting to an existing database. Or, if no appropriate database exists, the system creates a new one and always returns 0 (see Listing 1).

Listing 1. Derby initialization code
public class SampleAction implements IWorkbenchWindowActionDelegate {
/* ...code skipped here... */

/** driver string. */
private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
/** protocol string. */
private static final String PROTOCOL = "jdbc:derby:";
/** database name string. */
private static final String DATABASE = "sampleDB";

/** SQL script for creating Categories table. */
private static final String CREATE_TABLE = "CREATE TABLE Records" 
     + "("
     + "quantity int"
     + ")";

/* ...code skipped here... */

/**
* Connects to database, inserts one record into Records table 
* and then counts total records quantity in database.
* If database does not exist, then new database is created.  
*/
private int queryRecords()
        throws SQLException, IllegalAccessException, ClassNotFoundException,
        InstantiationException {

    Connection currentConnection = null;
    System.setProperty("derby.system.home",
        Sample_derbyPlugin.getDefault().getStateLocation().toFile().getAbsolutePath());
    Properties props = new Properties();

    try {
        Class.forName(DRIVER).newInstance();
        currentConnection = DriverManager.getConnection(PROTOCOL 
            + DATABASE, props);

    } catch (SQLException sqlException) {
        //trying to create database
        currentConnection = DriverManager.getConnection(PROTOCOL 
            + DATABASE + ";create=true", props);
        try {
            Statement s = currentConnection.createStatement();
            try {
                s.execute(CREATE_TABLE);
            } finally {
                s.close();
            }
            currentConnection.commit();
        } catch (SQLException ex) {
            currentConnection.close();
            throw ex;
        }
    }
    return 0;
}

Here, the system property derby.system.home corresponds to a location of Derby system files. In Listing 1, the system property is set to the plug-in data folder .metadata/.plugins/sample_derby inside the workspace directory.

To verify that the query function successfully established a connection, you must change the run method in the same Java source file by adding the following code (see Listing 2).

Listing 2. Showing information dialog box
MessageDialog.openInformation(
window.getShell(),
"Sample_derby Plug-in",
"We have " + queryRecords() + " Records in the database");

Note that the queryRecords method throws exceptions that you have to handle by using a try-catch construct and showing an error message dialog.

After you invoke the Sample Action program, you'll see the following displayed (see Figure 4):

Figure 4. Sample application in action
Sample application in action

Next, you need to add INSERT/SELECT queries into the application. The code shown in Listing 3 simply adds a new entry into the Records table, counts the number of records, and returns it. Add the following lines to the beginning of the Java file.

Listing 3. Derby queries declaration
/** SQL query that counts number of records in database    */
private static final String SELECT_RECORDS_QUERY = 
        "SELECT SUM(quantity) FROM Records";
    
/** SQL script that adds new record into database    */
private static final String INSERT_RECORDS_QUERY = 
        "INSERT INTO Records (quantity) VALUES(1)";

Next, add the following code to the end of the queryRecords method.

Listing 4. Derby queries usage example
int result = 0;
try {
    Statement s = currentConnection.createStatement();
    try {
        s.execute(INSERT_RECORDS_QUERY);
        ResultSet rs = s.executeQuery(SELECT_RECORDS_QUERY);
        if (rs.next()) {
            result = rs.getInt(1);
        }
    } finally {
        s.close();
    }
    currentConnection.commit();
} finally {
    currentConnection.close();
}
return result;

After Eclipse establishes a connection to the database, it inserts a new row into the Records table, counts how many records are in the table, and returns this number. Thus, the number of records grows each time you trigger the Sample Action. After several invocations of the application, you should see a dialog like the one shown in Figure 5:

Figure 5. Sample application in action
Sample application in action

Now you have a quick demonstration of how an internal Derby database can be used within an Eclipse plug-in. Let's move on to learning how to use the plug-in for more complex applications.


Resource indexing plug-in

The resource indexing feature is important for IDEs. To see why, you can take a look at the C/C++ Development Tooling (CDT) Eclipse plug-in: Install it, create a simple C++ project with a few source files, add a couple of #includes, and try using the auto-completion feature. You'll see that it takes several seconds to find all variations for auto-completion, even for small projects. Moreover, this operation consumes all of Eclipse's resources, leading to a temporary freeze.

This article is supplied with a ready-to-use sample resources-indexer application. It monitors all resources (files) of projects in an Eclipse workspace that have a specific nature, named Sample Nature, defined by the plug-in (see Resources for details). The application stores information about resources in a Derby database and provides a view named Resources View that lets you search for files.

In this section, you'll go through the main steps of application creation. You'll omit some typical steps, such as developing an Eclipse plug-in GUI, but you'll concentrate on Derby in plug-in development.

Connection pool data source manager

Because you access the Derby database through JDBC, it's a good idea to use Derby's implementation of the ConnectionPoolDataSource interface for managing database connections. ConnectionPoolDataSource always keeps several open connections to a database, so you don't need to open a new connection every time you want to manipulate your database, thus speeding up the process. Also, ConnectionPoolDataSource avoids conflicts in a situation where an application opens several connections to a database at the same time.

You'll use the class called EmbeddedConnectionPoolDataSource, which is supplied with the Derby JDBC driver. In Listing 5, you create an instance of the PerUserPoolDataSource class and set an instance of the EmbeddedConnectionPoolDataSource class as its connection pool data source.

Listing 5. Derby data source initialization
/**
* Datasource to use for connection.
*/
private static PerUserPoolDataSource datasource;

/**
* Initializes database and creates datasource instance for it.
*/
public static void initDatasource () {
    EmbeddedConnectionPoolDataSource connectionPoolDatasource;

    connectionPoolDatasource = new EmbeddedConnectionPoolDataSource(); 
    connectionPoolDatasource.setDatabaseName(
        ResourcesIndexerPlugin.getDefault().getStateLocation().
        toFile().getAbsolutePath() + "/resourcesDB");
    connectionPoolDatasource.setCreateDatabase("create");
        
    datasource = new PerUserPoolDataSource();
    datasource.setConnectionPoolDataSource(connectionPoolDatasource);
    datasource.setDefaultAutoCommit(false);
        
    try {
        Connection connection = datasource.getConnection();
        try {
            Statement statement = connection.createStatement();
            // searching for Resources table in database and 
            // if there is no one then initialize database
            try {
                statement.execute("SELECT 1 FROM  ");
            } catch (SQLException ex) {
                ResourcesDatabaseInitializer.initDatabase(connection);           
            } finally {
                statement.close();
            }
            connection.commit();
        } finally {
            connection.close();
        } catch (SQLException ex) {
            // Error handling here
        }
    }
}

In the test application described in the Simple code for testing Derby database plug-in functionality section, you used the derby.system.home system property to specify the location of the Derby system files. However, in this code, the same location is handled by the setDatabaseName method as a part of a database name. After the data source initialization is complete, the code shown in Listing 5 checks to see whether the Resources table exists in the database. If the table doesn't exist, it invokes the database initialization code, ResourcesDatabaseInitializer, which in this example is a utility class that initializes the database. The initDatasource method is called during Eclipse plug-in initialization, so the data source variable is initialized and could be used for working with a database.

Database manipulation layer

The second step of the resources indexing plug-in creation is implementing the database manipulation layer. This layer is responsible for operations, such as insertion, deletion, or querying information. Listing 6 is an example of a method that adds a new resource to the Resources database.

Listing 6. Data manipulation
/**
* Adds resource entry in database
* @param resource resource to add into database
* @throws SQLException if SQL error occurred
*/
public static final void addResource(ResourceEntity resource) throws SQLException {
    Connection connection = datasource.getConnection();
    try {
        PreparedStatement s = connection.prepareStatement(
                "INSERT INTO Resources (path, name, project) VALUES(?,?,?)");
        try {
            s.setString(1, resource.getResourcePath());
            s.setString(2, resource.getResourceName());
            s.setString(3, resource.getProjectName());
            s.execute();
        } finally {
            s.close();
        }
        connection.commit();
    } finally {
        connection.close();
    }
}

This example works with a primitive database structure and truly simple queries. Real-life applications manipulate by much more complex data, so to have considerable performance they need to use wisely designed database schemas and SQL queries.

Using a builder to index resources

The best way to index resources is to use a builder. In Eclipse, builders are associated with specific project natures. Every time a change in a project (such as creation, deletion, or resource updating) occurs, it's handled by builders. Builders also process full rebuild and clean tasks for the whole project.

To add support for a builder into an Eclipse plug-in, you'll use the Project Builder and Nature extension wizard. This creates a nature and a builder associated with it. In the sample application included with this article, the nature is named Sample Nature. To handle incremental changes of a project, you need to implement the IResourceDeltaVisitor interface (see Listing 7).

Listing 7. IResourceDeltaVisitor implementation example
public boolean visit(IResourceDelta delta) throws CoreException {
    IResource resource = delta.getResource();

    /* ...code skipped here... */

    switch (delta.getKind()) {
        case IResourceDelta.ADDED:
            DatabaseUtil.addResource(resource);
        break;
        case IResourceDelta.REMOVED:
            DatabaseUtil.removeResource(resource);
        break;
    }

    /* ...code skipped here... */

}

In Listing 7 you process two actions: adding resources to and removing resources from the workspace. Obviously, this is just an example, and you may find other, more complex uses for similar implementations.

When the builder is ready, you can add the Sample Nature to a project to index its resources. This index can be used in any of your Eclipse application code by calling respective methods discussed in the Database manipulation layer section.

Testing the sample application

To test the sample plug-in supplied with this article, install it in Eclipse (including Derby plug-ins also installed). Then associate the Sample Nature with desired projects in your workspace by right-clicking a project and using the Add/Remove Sample Nature menu item. All resources from the monitored projects will be indexed immediately.

To search for indexed files, you can use the Resource View. Bring it up by selecting the top menu command Window > Select View > Other, then find and select Resources View in the Resources views group. The search is performed by the beginnings of file names, so you should see something like Figure 6.

Figure 6. Resources indexer plug-in in action
Figure 6. Resource indexing plug-in in action

You can access resources in the Resource View by double-clicking an item in the list. The view doesn't automatically recycle after changes are made to the workspace, but you can select Views > Refresh to do that.


Conclusions

Eclipse and Apache Derby are two well-known open source projects in the industry today. The Eclipse framework allows for the creation of various GUI applications, especially IDE applications. Apache Derby allows you to create local SQL databases for any Java application. And as was demonstrated in this article, Eclipse and Derby can be easily used together.

A major example of using the Derby database to develop Eclipse plug-ins is resources indexing. Along with Eclipse Builder framework, you can store resource information in an SQL database, and then use this information by making queries. Because the Derby database is optimized for SQL queries, you get optimal performance when accessing the resources index.


Download

DescriptionNameSize
Sample projects for this articlesamples.zip173KB

Resources

Learn

Get products and technologies

  • Innovate your next open source development project with IBM trial software, available for download or on DVD.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Open source on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Open source, Information Management
ArticleID=188610
ArticleTitle=Use Apache Derby to develop Eclipse plug-ins
publish-date=01162007