Documentation for mysqlservlet

A simple general purpose servlet to access a mysql database from a web site

Lucio Chiappetti IASF Milano 12 Jan 06 14:31

Contents

Introduction
Features and functionality
How to build a web page calling the servlet
S/W requisites
Getting the s/w

Introduction

Since long I wished to have a spreadsheet-like interface to access a
mysql database, allowing to view the content of a table, and to edit its content.
Since I found nothing which satisfied me, I decided to write a little Java servlet of my own, which allows to access any mysql database table from a web page.

Also I needed something like this for internal affairs inside our institute, so I added some specific, though general-purpose, functionalities.


Features and functionality

mysqlservlet allows, once
installed under a running Java servlet engine, to build web pages which allow to access one or more mysql database tables, to look at them, and to edit them (one record at a time).

The main features are :

I will now describe the functionality of the servlet, starting from the look and feel of a typical screen. As explained in the next section typically one will have a frameset with two frames, the upper one used for login, and the lower one for the output. I show only the lower one, since the upper one has a look which depends on the way you build your site !

The typical page for a non-guest user will look like this, with a top "keyboard" (an HTML table with several buttons and control) followed by the display of the database table (the latter can be quite long and require scrolling with the browser scroll bar).

Display: any; non-retired; non-deleted; active only;
Edit: current; duplicate; new;
Extra condition Limit from to
seq time editor anint astring adate flag unsup
1
2006-01-11 17:05:53.0
lucio
11
paraponzi
2006-01-01
Yes
UNSUPPORTED FLOAT
2
2006-01-11 17:06:33.0
lucio
22
ponziponzipo
2005-12-31
No
UNSUPPORTED FLOAT
3
2006-01-11 17:07:23.0
lucio
300
trallallero
1982-06-15
No
UNSUPPORTED FLOAT
4
2006-01-11 17:08:14.0
lucio
666
trallalla
1992-07-16
Yes
UNSUPPORTED FLOAT

The buttons and links in the above table are disabled, with the exception of the numbered links in the first (green) column labelled "seq". Clicking on them will show you what the record looks like when opened for edit. Click on the "Dont" button to close the record and return.

The above sample demonstrates also the typical cell colour coding.

For a guest user the top "keyboard" will have some functions disabled (additionally the "Display" panel can be disabled if the table does not contain a status column, irrespective of the user) as shown below.

Display: any; non-retired; non-deleted; active only;
no editing allowed no deletions allowed
Extra condition Limit from to

Below I will explain :


The colour codes for the table cells depend on the mysql type (as seen by java) plus some additional interpretation specific of the servlet. Each cell has two possible looks, one in display mode, and one in edit mode.

typesupportlook in display modelook in edit mode
auto increment special 13 URL on green couple of buttons, the second can be Save or Add according to editing mode
editor column special manuzio italic red on default editing...
duplicating...
adding new...
message depending on editing mode, direct editing not allowed
boolean planned T white on blue n/a currently unsupported
byte planned 64 darker cyan n/a currently unsupported
short planned 256 dark cyan n/a currently unsupported
long yes 1024 cyan
float yes 1.0 dark orange text area
double planned 2.0 orange n/a currently unsupported
string yes xyzzy default text area
date yes 1492-10-12 italics on light yellow text area
time planned 09:30:05 italics on yellow n/a currently unsupported
timestamp yes 2006-01-11 17:05:53.0 italic red on default editing...
duplicating...
adding new...
message depending on editing mode, direct editing not allowed
enumeration yes forse che si white menu with ENUM allowed values
set planned big,bad gray TBD currently unsupported
anything else no UNSUPPORTED TYPE message on red unchanged editing not possible

There is no syntactic check on the value input in a text area (besides the implicit ones for menus or for fields like timestamp or editor which are generated internally).
The rare case when mysql error messages are generated, typically because of incorrect editing, they will appear in red, and should be recoverable in a self-understandable way.
Note however that inputting invalid values in most cases does not generate an error, but the insertion of a null or default value in a cell. Thus, you'd better verify the content of an edited record after editing.


The sorting and hiding of specific columns is controlled by the Columns sort and hide menu button, which opens a screenful like this

Column Show/HideSort options
seq compulsory Sort: No; Asc; Desc as
time compulsory Sort: No; Asc; Desc as
editor Show ; Hide Sort: No; Asc; Desc as
anint Show ; Hide Sort: No; Asc; Desc as
astring Show ; Hide Sort: No; Asc; Desc as
adate Show ; Hide Sort: No; Asc; Desc as
flag Show ; Hide Sort: No; Asc; Desc as
unsup Show ; Hide Sort: No; Asc; Desc as

The example shown indicates that we have requested to hide the last three columns, and to sort in ascending order on columns (in this order) anint,editor,seq,flag.
Note that sorting on an hidden column is allowed.
The request will be processed as soon as the user clicks on one of the "Apply choices ..." buttons.

Note that some special columns cannot be hidden.

The top three buttons (outside of the table with the column list) are used as follows:

The meaning of the radio buttons in the table with the column list should be self explanatory (sorting can occur in ascending or descending order).
The numbered menu is used instead as a sequence number to select the priority in which the columns selected for sorting are used. It is set to blank for unsorted columns, and remembers the previous value for sorted columns. However if all columns are unsorted, it assumes the default order of the column in the table.
If you click on Asc or Desc to sort an unsorted column and leave the sequence number blank, such columns will be sorted last.
If you assign the same sequence number to more columns, they will be sequenced in alphabetic order of the name.


The other functions of the top keyboard are as follows :


How to build a web page calling the servlet

You can access any database table, or family of tables (in a mysql database which you are entitled to access) building just two web pages (at least, I do it with two) :


S/W requisites

I have developed and tested the servlet under Linux SuSE 9.2, using as Java compiler and runtime environment to be specified in the CLASSPATH (j2ee.jar) the version j2ee-1.4.

In addition I use as servlet environment jsdk2.1 (whose server.jar and servlet.jar shall be prepended to the CLASSPATH).

In addition again I use as JDBC mm.mysql.jdbc-1.2c (whose org directory shall also be prepended to the CLASSPATH).

To install the servlet

Good luck !

If you want to experiment with other java stuff you are welcome to do so, but you will be rolling on your own. Eventually keep me posted.


Getting the s/w

The source code for the servlet is available in file
mysqlservlet.java.

The documentation is represented by the current page.