Skip to main content

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

The first time you sign into developerWorks, a profile is created for you. Select information in your developerWorks profile is displayed to the public, but you may edit the information at any time. Your first name, last name (unless you choose to hide them), and display name will accompany the content that you post.

All information submitted is secure.

  • Close [x]

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.

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

All information submitted is secure.

  • Close [x]

Select a language:

  • Close [x]
  • Close [x]

Create a wiki system using Derby, Part 2: Java functions, procedures, and triggers

Daniel Wintschel (daniel@humandoing.net), Author and developer
Daniel Wintschel is a regular guy who derives great excitement from solving business problems, streamlining processes, and writing all sorts of Java code. He loves coffee and is currently seeking development opportunities on a telecommuting basis from Singapore. If you are bored one day, you can read more about him.

Summary:  This two-part tutorial series demonstrates how to create a simple wiki system by embedding Apache Derby in a Web application server. Part 1 gave you an overview of the basic system and regular expressions. This tutorial shows you how to use Java™ functions, stored procedures, and triggers within the Derby database and outlines the advantages of using these features, including reducing the coupling between database and middle-tier code, minimizing the number of transactions opened, and increasing performance by reducing network traffic.

View more content in this series

Date:  25 Oct 2005
Level:  Intermediate
PDF:  A4 and Letter (403 KB | 22 pages)Get Adobe® Reader®

Activity:  7399 views
Comments:  

Before you start

This second part in a two-part tutorial series that demonstrates the creation of a wiki system using Derby. Part 1 discussed the setup, configuration, and how to embed Derby as a database within a Web application server. It also covered some basics of regular expressions for the purposes of parsing wiki text.

This tutorial examines functionality within the Derby database, including examples of how to use functions and stored procedures to provide additional functionality within your database and how to use triggers to provide preprocessing or post-processing to your SQL Insert/Update/Delete statements.

About this tutorial

This tutorial is designed to provide you with a basic understanding of and overall perspective on how to implement functions, stored procedures, and triggers within your applications when using Derby as a database. Many developers don't know when or why to use these database features, so this tutorial provides a few ideas about how to take advantage of a function or a trigger. The tutorial is structured as follows:

  • Overview -- Defines database functions, stored procedures, and triggers.
  • Java functions -- Explains what Java™ functions in the database can be used for and provides an example from the wiki application.
  • Java procedures -- Covers Java stored procedures in the database, how to define input and output parameters in Derby, and an example of a procedure from the wiki application.
  • Database triggers -- Identifies different types of triggers supported by Derby and shows an example of what types of triggers can be used within the wiki application.

Back to top

Prerequisites

This tutorial assumes you have a basic knowledge of the Java Database Connectivity (JDBC) API. It's also beneficial to have some familiarity with the concepts of database triggers, functions, and procedures. But if you don't, this tutorial defines and describes each of these in enough detail to ensure that you feel comfortable with these topics.

To run the example code in this tutorial, perform the following steps:

  1. Download and install the following applications
  2. Make sure the environmental variables outlined in Table 1 are defined in your shell.
    Table 1. Set the environmental variables
    Variable nameRequired setting
    TOMCAT_HOMESet to the root folder of your Tomcat installation
    DERBY_HOMESet to the root folder of your Derby installation
    ANT_HOMESet to the root folder of your Ant installation
    JAVA_HOMESet to the root folder of your Java installation
    PATHEnsure that ANT_HOME/bin is in your PATH

  3. Extract the supplied .zip file (see the Download section for the file) to your preferred location (this will be the project root). The project is laid out as follows: 
    /Wiki/
     /conf/   > contains DDL (data definition language)
     /deploy/ > created by ANT (will contain WAR file)
     /src/    > contains Java source code
     /web/    > contains JSPs and web.xml, etc.
     /build.xml > ANT build file

  4. The last thing you need to do is modify the /web/WEB-INF/web.xml file to specify where you want Derby to create your wiki database. In my case, I've set the derby.system.home property in the web.xml file to be W:\Development\WikiDB. See the following example taken from the web.xml file: 
    <servlet>
  <servlet-class>net.humandoing.wiki.WikiServlet</servlet-class>
  <servlet-name>WikiServlet</servlet-name>
  <init-param>
    <param-name>derby.system.home</param-name>
    <param-value>W:\Development\WikiDB</param-value>
  </init-param>
</servlet>

Note: Make sure that the value you specify is a directory and that it already exists. Second, you don't need to put a trailing slash. Third, if you are on a *nix-based system, your path would look something like /development/WikiDB, but you can create this directory wherever you want to.

If you don't specify a value or a valid value for this property, the application creates the wiki database under the Tomcat directory webapps/wiki/WEB-INF/WikiDB. This is OK, but it means that if you redeploy the wiki, any data that you entered into your wiki will be gone.

Back to top

Default wiki index

Let's continue where we left off from Part 1 and start the wiki (see the Download section for the application) by pointing your browser to http://localhost:8080/wiki. You should receive the default wiki homepage as shown in Figure 1.


Figure 1. Default index page of the sample wiki application
Default index page of the sample wiki application

Now that you've seen what the wiki index looks like when it's first started up with no data, let's look at the functions, procedures, and triggers you're going to add.

1 of 9 | Next

Comments

Back to top

Close [x]

Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Close [x]

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Open source
ArticleID=133175
TutorialTitle=Create a wiki system using Derby, Part 2: Java functions, procedures, and triggers
publish-date=10252005
author1-email=daniel@humandoing.net
author1-email-cc=

Table of contents

1 of 9 | Next

Local resources

Tags

Help
Use the search field to find all types of content in My developerWorks with that tag.

Use the slider bar to see more or fewer tags.

Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).

My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).

Use the search field to find all types of content in My developerWorks with that tag. Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere). My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).

 

Dig deeper into Open source on developerWorks

Try IBM PureSystems. No charge.

IBM PureSystems on a kaleideoscope background

Experience today!

Get started with the cloud-based trial and pattern development kit.

Share this page:

  • Close [x]

Follow developerWorks:

  • Close [x]