jdbc

This document is a simple step-by-step tutorial showing you how to create a Hypersonic SQL database and accessing it with the use of Serlvets and JSP. The following steps are only a simple solution to creating dynamic web page – a guestbook. The examples in this document are source codes to my own MyCGIServer guestbook for this tutorial, which you can write and view. Give me some feedback on this tutorial there.

I’ve noticed many people on the www.mycgiserver.com discussion forum screaming for help with Hypersonic SQL, Servlets and JSPs. So I thought a tutorial would be a solution for many of you and hope this in someway will help you. Feel free to use the input.html, GuestBookServlet.java and GuestBook.jsp files used in this example as you like. Just make sure you replace ‘buman’ with whatever is your name or nick in the files.

The steps below only describe my own experience with the use of my MyCGIServer account. Please don’t ask me about the use of InstantDB, WML, PHP, RMI, XML and all that other stuff available for MyCGIServer users. NOTE: I’m not a very experienced programmer so I can’t guarantee that the tutorial will work for you as it did for me.

I would like to thank the following for their help and support:
- Janus Skallgård
- Mark Hunter
- Gushcha Stas
- Wouter Beheydt
- John S

NOTE: To complete this tutorial you should:
- Have experience with Java and the use of a JDK
- Understand the basics of HTML/Servlets/JSP
- Know something about SQL/DBMS

1. Make sure you have:

- The Hypersonic SQL DBMS. You can download it here – locate the hsqldb_v.1.61.zip file
- A development kit with the necessary Java Servlet technology, like Java 2 SDK Enterprise Edition, installed on your computer. You’ll find it at Sun’s website java.sun.com. DO NOT use JDK 1.4.x or later. 1.3.1 is recommended.
- And of course a MyCgiServer account (www.mycgiserver.com)
- FTP client like WS_FTP LE. Can be found at www.download.com

Before we continue, check that you have your development kit properly installed on your computer. Most important is to set the right path to the java commands. That means configuring your autoexec.bat file (Win9x), or add the Path user variable in Environment variables if you are using Win2000/NT.

2. Install/Run the HSQL DatabaseManager

Install: Let’s say you have unzipped the HSQL files to the directory c:\Java\hypersonicsql. Go the \src subdirectory and locate the build.bat file. Run the bat-file (double-click). This will compile a number of *.java files representing the DBMS. For documentation, also run the buildDoc.bat file.

Go to the \lib folder and find the hsqldb.jar file. This file should be added to your classpath. Alternatively, you can copy the whole file to your \jre\lib\ext subdirectory of your Java Development Kit (e.g c:\java\jdk1.3.1\jre\lib\ext).

Run: Execute the runManager.bat file in the \demo folder. Now you should get a Java applet with two windows (Connect & Hypersonic Database Manager). For easy use, I recommend creating a shortcut to your Windows desktop mapping it to the bat-file.

3. Create a database

Let’s say you want to create a database named ‘myDB’, and that you for now will manage it locally on your harddrive.

In the ‘Type:’ dropdown list select ‘HSQL Database Engine Standalone’ and type ‘jdbc:hsqldb:myDB‘ in the textbox named ‘URL’. For now don’t worry about the username and password. We’ll change those later. Press OK.

Let’s create a simple table with some suitable attributes for a guestbook:
CREATE TABLE guest (name VARCHAR(255), email VARCHAR(255), message VARCHAR(255))

Press Ctrl+R and see that your table appear in the "DB tree" (yellow box with a + sign in it)

Insert some test data:
INSERT INTO guest VALUES (‘Name 1′, ‘name1@mailadress.com’, ‘Hello 1′)
INSERT INTO guest VALUES (‘Name 2′, ‘name2@mailadress.com’, ‘Hello 2′)

View the result by typing:
SELECT * FROM guest

I would now be appropriate to change your username/password. Let’s assume that it is ‘user’ and ‘pass’. Type:

CREATE USER user PASSWORD pass ADMIN
DROP USER sa

Now look in the c:\Java\hypersonicsql\src folder (could be the c:\Java\hypersonicsql\demo folder) for your DB files. Locate:

- myDB.properties
- myDB.script
- myDB.data
- myDB.backup

4. Create access to your Hypersonic SQL database

In this step you can choose between working with your database locally (part a), on the web (part b) or both. For those of you not familiar with the use of servlet engine and working with servlets on localhost, like Tomcat Webserver – please skip part a.

a. Working with the database locally for testing.

First go to C:\Java\hypersonicsql\demo. Open the Webserver.properties file for editing. Replace ‘database=test’ with ‘database=myDB‘. Save and exit. If you can’t find this file in your \demo folder create a new file called Webserver.properties, open it and paste this content into it:

port=80
root=./
default=index.html
database=myDB
silent=true

.htm=text/html
.html=text/html
.txt=text/plain
.gif=image/gif
.class=application/octet-stream
.jpg=image/jpeg
.jgep=image/jpeg
.zip=application/x-zip-compressed

Double-click on ‘runWebServer.bat’. This will create access to your database with a local database URL. If we assume that your database files are located in the same folder you can use:

jdbc:hsqldb:http://localhost/myDB

In order to test with servletes/JSPs locally you must start your servlet engine. But first you have to define a site root (normally done in a properties- and/or XML file of your servlet engine). Or you could just copy your jsp-files and servlet class files to the default root directory of your engine (e.g. \tomcat\webapps\ROOT). Remember: Both the Hypersonic SQL Webserver (runWebServer.bat) and the servlet engine must run at the same time. To avoid a port conflict, make sure that Tomcat servlet engine uses another port than 80.

NOTE: www.mycgiserver.com is known to be very busy server. Especially if you’re working with larger databases, testing your web application on MyCGI could go very slow, or give you a "Connection timed out". So if you plan to run a bigger application on your account, I strongly recommend that work with your servlets/JSPs locally before uploading them.

b. Upload the database files to MyCGIServer

Start your FTP client and connect to your MyCGI account. If your user name is ‘buman‘, the root directory of your account is located at www.mycgiserver.com/~buman. From now on we’ll asume your username is ‘buman’.

In the FTP client create a folder named ‘myDB’. Change to this folder and transfer the myDB.* files into it. You now have a usable database URL that you can connect to using Servlets and JSPs:

String dbURL = "jdbc:hsqldb:" + getServletContext().getRealPath("/~buman/myDB/myDB");

5. Create a servlet that can handle guestbook inputs.

The following examples/source code only use the MyCGI database URL. So if you you intend to work locally, you must in all cases of ‘String dbURL = "jdbc:hsqldb:" + getServletContext().getRealPath("/~buman/myDB/myDB");‘ in the source code, replace it with ‘String dbURL = "jdbc:hsqldb:http://localhost/myDB";’.

Begin by creating a "user interface", that is a html file with a form and input text fields. Like:

Save your HTML file and name it ‘input.html‘. Notice that the action tag takes the URL to your servlet. A servlet we will create next. Do not try out the html file yet. Create a java source file named GuestBookServlet.java with the code:

Notice that the HttpServletResquest object getParameter("…") method takes the name of the input text fields in your html-file. These must match eachother. Also notice that your servlet is accessed under /servlet/username.ServletName, that is in our example http://www.mycgiserver.com/servlet/buman.GuestBookServlet.

Compile it and upload it to your MyCGi account, root directory.

Now try it out. Open the input.html and insert some test values. Press the submit button. You should now get a response sent back: "Thanks for your entry to my guestbook, <name>".

6. Create a JSP file to view the guestbook entries.

Name the file GuestBook.jsp

Be aware of the arguments that the getObject("…") method takes. Like in the html/Servlet example, the parameter must match the name of the column in the database. It’s important that you separate the declarations ( <%! …. %> ) from the logic ( <% … %>). Upload the jsp file and test it in your browser with the URL: www.mycgiserver.com/~buman/GuestBook.jsp

End of tutorial

If you have any questions you can mail me. Remember to include your (1)code, relevant (2)error messages and (3)exception stacktrace. No reply means I can’t help you. That is probably because you haven’t included all 3 things just mentioned.