	Developers
	Developers Home
	EditLive! for Java
	EditLive! for Windows
		System Requirements
		Getting Started
		Integration Samples
		Articles
		API Reference
	EditLive! for XML
	Discussion Forums

Sample Database Application for JavaServer Pages

Ephox, April 2002

Introduction
Pre-requisites
Retrieving the Sample Source Code
Files Included in this Sample
The Database
I_GLOBAL.JSP
The Index Page (start.jsp)
Adding a New Article (add.jsp and xt_add.jsp)
Editing an Existing Article (edit.jsp and xt_edit.jsp)
Deleting an Existing Article (delete.jsp and xt_delete.jsp)
Viewing an Article (view.jsp)

Introduction

This article shows how to use EditLive! as the interface for a database-driven press release center. This sample allows users to:

add new press articles;
edit existing press articles;
view the article in the web browser; and
delete existing press articles.

Pre-requisites

This example uses client-side JavaScript within HTML. JavaServer Pages technology is used for the server-side scripting. Please ensure that before working through this sample you have read Using EditLive! with an Online Database or in an Online Form.

Retrieving the Sample Source Code

The source code for this sample will have been installed on your machine when you installed the Ephox EditLive! 4.0 SDK. To view the source code please click on Start > Programs > Ephox EditLive! > SDK > Open Source Code. To view this sample, if you have a Web server installed on your local machine which runs JSP pages, please browse to the start.jsp page of the sample source code through your Web server by opening the Ephox EditLive! Samples Gallery and clicking on the JavaServer Pages link in the menu sideframe.

Files Included in this Sample

Below is a table outlining the ten files included in this sample. Please note, files that begin with i_ denote a server-side include file and files that begin with xt_ denote a hidden processing page.

i_global.jsp	This file defines the database connection string used to connect to the online database, imports the required Java packages and loads the relevant JDBC driver.
start.jsp	This file searches the database and creates a list of articles that are available for editing within EditLive!. It also supplies links to allow users to add a new article or delete any of the existing articles.
add.jsp	This file allows users to use EditLive! to create new articles to store in the database.
xt_add.jsp	This file adds the new article created using EditLive! to the database.
edit.jsp	This file loads an existing article into EditLive! for modification.
xt_edit.jsp	This file updates the modified article contents in the database.
view.jsp	This file displays the chosen article in the web browser.
delete.jsp	This file confirms that you wish to delete a specified article.
xt_delete.jsp	This file deletes an existing article from the database.

The Database

The database used in this sample is a Microsoft Access database named content.mdb. Below is a table outlining the fields within the database table articles and a description of the information each field stores.

article_id	A unique number used to identify the article record.
article_title	The title or headline of the article.
article_body	The actual article content.
article_StyleElementText	The styles used to format the article if content was pasted in to EditLive! from Microsoft Word.

Creating a Datasource

Before using this sample you will need to create an ODBC connection to the sample database. For instructions on how to do this, please see the JDBC-OBDC Sample Database Connection installation guide.

I_GLOBAL.JSP

Purpose - This file defines the database connection string used to connect to the online database, imports the required Java packages and loads the relevant JDBC driver. Therefore, it needs to be included whenever a page interacts with the database.

Step 1 - Import the relevant packages into the page.

 <%@ page import="java.io.*" %>
 <%@ page import="java.util.*" %>
 <%@ page import="java.sql.*" %>
 <%@ page import="javax.servlet.*" %>
 <%@ page import="javax.servlet.http.*" %>

Step 2 - Declare the location of the driver package to load the driver.

//Declare the location of the drivers class to load the driver 
//To change the driver package for a different DBMS
//change this line 
String driverPackage="sun.jdbc.odbc.JdbcOdbcDriver";

Step 3 - Declare the name of the driver to be used to access the database.

//Declare the name of the driver to access the database 
//To change the driver for a different DBMS change this line 
String driver="jdbc:odbc:";

Step 4 - Declare the datasource name of the EditLive! database as specified when the ODBC datasource name was created.

//Declare the DSN of the database you wish to access 
//To access a different database change this line 
String databaseDSN="ELContent";

Step 5 - Construct the string used to connect to the database.

//Construct the url to access the database from the driver
//and the database DSN 
String url=driver+databaseDSN;

Step 6 - Load the database drivers defined above, catching any exception errors that may occur.

try
{
//This code loads driver declared above 
Class.forName(driverPackage);
}catch(Exception e){}

The Index Page (start.jsp)

Purpose - The index page of the press release center lists all of the articles currently available in the database. Users are able to:

create a new article using EditLive!;
edit an existing article using EditLive!; or
delete an existing article from the press release database.

Step 1 - Define the page content type, import the relevant Java packages into the page and include the i_global.jsp file to connect to the database.

 <%@page contentType="text/html" %>
 <%@page import="java.util.*" %>
 <%/*Include the global configuration file containing the
 location of the database to connect to*/ %>
 <%@include file="i_global.jsp" %>

Step 2 - Set the response settings so that the most current information is always loaded into the Web browser.

 <%
//These three lines prevent caching of the page, meaning it
//will always be loaded with the most current information available
//to the client at the time of request 
response.setHeader("Pragma", "No-cache");
response.setDateHeader("Expires", 0);
response.setHeader("Cache-Control", "no-cache");
 %>

Step 3 - Add a link to create a new article.

<P><A href="add.jsp">Create a new article</A></P>

Step 4 - Connect to the database and retrieve all of the existing articles listed in the articles table.

 <%
//This code generates the list of articles stored in the database 
try{
//Create a connection to the database 
Connection conn=DriverManager.getConnection(url);

String sqlQuery;

//Select and display information from the database dependent on the 
//query string information 
sqlQuery="SELECT * FROM articles";
Statement stmt = conn.createStatement(ResultSet.
TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet sqlResult=stmt.executeQuery(sqlQuery);
 %>

Step 5 - Use embedded JSP to iterate through the recordset of articles to create a table which lists all of the existing articles by title. Then, for each article, create a link to the:

view page to view the article;
edit page to edit the article using EditLive!; and
delete page to delete the article from the database.

Append the article_id to all of the links so that the correct querystring is generated for the link.

 <%
//Display the results of the query if there are any 
if(sqlResult.first()){
 %>
<TABLE cellpadding=3 cellspacing=0 border=0>
<TR>
<TH>ID</TH>
<TH width="250">Title</TH>
<TH>Available Actions</TH>
</TR>
 <%
// There are records. Loop through all the records in the  
// recordset and write out a table row for each record 
sqlResult.beforeFirst();
while(sqlResult.next()){
String sqlContentID=sqlResult.getString("article_id");
 %>
<TR>
<TD> <%=sqlResult.getString("article_id") %></TD>
<TD> <%=sqlResult.getString("article_title") %></TD>
<TD><A href="view.jsp?article_id=<%=sqlContentID%>">View</A> |
<A href="edit.jsp?article_id=<%=sqlContentID%>">Edit</A> |
<A href="delete.jsp?article_id=<%=sqlContentID%>">Delete</A>
</TD>
</TR>
 <% 
}//end while 
 %>
</TABLE>

Step 6 - Add an ELSE statement to the IF statement to inform users if there are no records in the database.

 <%
}else{
%><P>There are no records in the database. Click
<STRONG>Add an Article</STRONG> to add a record to the database.
</P>
<%

Step 7 - Release the memory being used for the database connection and the article recordset.

}//end if else 
sqlResult.close();
stmt.close();
conn.close();
}catch (Exception e){out.println(e);}
%>

Adding a New Article (add.jsp and xt_add.jsp)

Purpose - The file, add.jsp, is used to create a new article using EditLive!. Once the article is complete, it can be added to the database using the processing page, xt_add.jsp.

add.jsp

Step 1 - Start with a standard HTML page containing EditLive! running in HTMLString mode. To see the steps involved in creating this page please see Using EditLive! with an Online Database or in an Online Form.

<HTML>
<HEAD>
<TITLE>Create a New Article</TITLE>
 <%
//These script files initialize the EditLive! API so that
//the EditLive! properties and methods 
//may be set to customise EditLive!. 
 %>
<SCRIPT language="JavaScript1.2" src="../../editlive/editLive4.js">
</SCRIPT>
<SCRIPT language="VBScript" src="../../editlive/editLive4.vbs">
</SCRIPT>
</HEAD>
<BODY>
 <%
//Instantiate the EditLive! object on your web page
//using client-side JavaScript 
 %>
<SCRIPT LANGUAGE="JavaScript">
//Important: set the directory where EditLive! download files
//can be found 
EditLiveGlobal.setDownloadDirectory("../../editlive/");

//Create the EditLive! object 
var editLive1 = new EditLive("Plugin1", "100%", 300);

//Assign an onload function to initialize the properties & methods
editLive1.onload = EditLive1_onload;

//This function specifies the initial EditLive! properties & methods
function EditLive1_onload()
{
//Set the user interface to database mode
editLive1.setEditLiveMode('HTMLString');

//Set the image mode to allow image insertion from the server only
editLive1.setImageMode('ServerOnly');

//Initialize the FTP server (for images)
editLive1.setFTPServer('ftp.ephox.com');
editLive1.setFTPServerPort(21);
editLive1.setFTPUsername('anon');
editLive1.setFTPPassword('anon');

//Set the base URL to render relative image addresses
editLive1.setWebRoot('http://www.ephox.com/ftp/');

//Load the initial contents of the documents BODY element
editLive1.setSource(" ");

//Load the initial contents of the documents STYLE element
//editLive1.setStyleElementText(H1 {font-size:16pt}); 
}
</SCRIPT>

</BODY>
</HTML>

Step 2 - Add a heading to the page of "Create a New Article".

<BODY>
<H1>Create a New Article</H1>

Step 3 - Create a form in the page around the EditLive! object. Enter the processing page file name, xt_add.jsp, in the action attribute of the FORM tag.

 <%
// This form contains EditLive!, a text area for the article title, 
// a submit button and a cancel button. 
 %>
<FORM action="xt_add.jsp" method="POST" name="articleForm"> <%
// This area contains the code that initiates EditLive! 
 %>
</FORM>

Step 4 - Add a text field at the beginning of the form for the article title.

 <%// Article title  %> 
<P>Title: <INPUT type="text" name="article_title" size="50"></P>

Step 5 - Add two buttons to the end of the form; a submit button to save the article to the database and a cancel button to return the browser to the index page without saving.

<P>
<INPUT type="submit" value="Save" name="Add Article">
<INPUT type="button" value="Cancel" name="Cancel"
onclick="javascript:history.back();">
</P>

Step 6 - Add two hidden form objects to store the contents of EditLive! and the style element within EditLive! for processing.

 <%// Hidden fields for storing content from EditLive!  %> 
<INPUT type="hidden" name="article_body">
<INPUT type="hidden" name="article_styleElementText">

Step 7 - Create a client-side JavaScript function to retrieve the contents of EditLive! and pass it to the hidden form object, and to get the style element for the content within EditLive! and pass it to the other hidden form object.

/* This function gets the contents out of EditLive! and assigns
it to a hidden form field when the Submit button is pressed. */
function articleForm_onSubmit()
{
document.articleForm.article_body.value = editLive1.getSource();
document.articleForm.article_styleElementText.value =
editLive1.getStyleElementText();
}

Step 8 - Call this function in the onsubmit event of the FORM tag.

<FORM action="xt_add.jsp" method="POST" name="articleForm" 
 onsubmit="articleForm_onSubmit()">

xt_add.jsp

Step 1 - Include the i_global.jsp file to connect to the database.

 <%/*Include the global configuration file
containing the location of the database to connect to*/ %>
 <%@include file="i_global.jsp" %>

Step 2 - Initialize the variables used to store the article information in the database.

 <%
//Initialise the variables 
String articleTitle="";
String articleStyleElementText="";
String articleBody="";
String articleID="";
boolean redirect=true;

Step 3 - Create a connection to the database using the information contained in the i_global.jsp file.

//Create a connection to the database 
Connection conn=DriverManager.getConnection(url);

Step 4 - Create the Prepared Statement that will form the basis of the SQL query used to add the article contents to the database.

//sql query to construct the prepared statement from
//(and thus update the database) 
String sqlQuery="INSERT INTO articles 
( article_title, article_styleElementText, article_body )
VALUES ( ?, ?, ? )";
PreparedStatement insertStatement=conn.prepareStatement(sqlQuery,
ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

Step 5 - Assign the POSTed form data to the relevant variables.

//Get the POSTed data from the form 
articleTitle=request.getParameter("article_title");
articleBody=request.getParameter("article_body");
articleStyleElementText=request.getParameter
("article_styleElementText");

Step 6 - Insert the information assigned to the variables into the Prepared Statement to complete the SQL statement in order to write the information to the database.

//Input the information to update the
//database into the prepared statement 
insertStatement.setString(1,articleTitle);
insertStatement.setString(2,articleStyleElementText);
ByteArrayInputStream bStream =
new ByteArrayInputStream(articleBody.getBytes());
bStream.reset();
insertStatement.setAsciiStream(3, bStream, bStream.available());

Step 7 - Write the information to the database by executing the Prepared Statement, catching any exception errors that may occur.

//Attempt to insert into the database,
//display an error message if insertion fails 
try{
insertStatement.execute();
}catch(Exception e){
redirect=false;
out.println("An error has occurred with insertion to the database,
check all fields and resubmit<BR><BR>");
out.println(e);
}

Step 8 - Close the connection to the database.

//Close the connection to the database 
insertStatement.close();
conn.close();

Step 9 - Redirect the Web browser back to the Index page.

//Redirect to start.jsp on successful insert operation 
if(redirect){
response.sendRedirect("start.jsp");
}
%>

Editing an Existing Article (edit.jsp and xt_edit.jsp)

Purpose - The file edit.jsp loads an existing article into EditLive! for modification. Once the article is complete, the relevant record in the database can be updated using the processing page, xt_edit.jsp.

edit.jsp

Step 1 - Again, start with a standard HTML page containing EditLive! running in HTMLString mode. To see the steps involved in creating this page please see Using EditLive! with an Online Database or in an Online Form.

<HTML>
<HEAD>
<TITLE>Editing Article ID  <%=lngArticleID %></TITLE>
 <%
//These script files initialize the EditLive! API so that
//the EditLive! properties and methods
//may be set to customise EditLive!. 
 %>
<SCRIPT language="JavaScript1.2" src="../../editlive/editLive4.js">
</SCRIPT>
<SCRIPT language="VBScript" src="../../editlive/editLive4.vbs">
</SCRIPT>
</HEAD>
<BODY>
 <%
//Instantiate the EditLive! object on your web page
//using client-side JavaScript 
 %>
<SCRIPT LANGUAGE="JavaScript">
//Important: set the directory where EditLive! download
//files can be found 
EditLiveGlobal.setDownloadDirectory("../../editlive/");

//Create the EditLive! object 
var editLive1 = new EditLive("Plugin1", "100%", 300);

//Assign an onload function to initialize the properties & methods
editLive1.onload = EditLive1_onload;

//This function specifies the initial EditLive! properties & methods
function EditLive1_onload()
{
//Set the user interface to database mode
editLive1.setEditLiveMode('HTMLString');

//Set the image mode to allow image insertion from the server only
editLive1.setImageMode('ServerOnly');

//Initialize the FTP server (for images)
editLive1.setFTPServer('ftp.ephox.com');
editLive1.setFTPServerPort(21);
editLive1.setFTPUsername('anon');
editLive1.setFTPPassword('anon');

//Set the base URL to render relative image addresses
editLive1.setWebRoot('http://www.ephox.com/ftp/');

//Load the initial contents of the documents BODY element
editLive1.setSource(" ");

//Load the initial contents of the documents STYLE element
//editLive1.setStyleElementText(H1 {font-size:16pt}); 
}
</SCRIPT>

</BODY>
</HTML>

Step 2 - Add a heading to the page of "Edit Document".

<BODY>
<H1>Edit Document</H1>

Step 3 - Create a form in the page around the EditLive! object. Enter the processing page file name, xt_edit.jsp, in the action attribute of the FORM tag.

 <%
// This form contains EditLive!, a text area for the article title, 
// a submit button and a cancel button. 
 %>
<FORM name="form1" action="xt_edit.jsp" method="POST"> <%
// This area contains the code that initiates EditLive! 
 %>
</FORM>

Step 4 - Add Save and Cancel buttons to the form, the same as for the add.jsp page.

<P><INPUT type="submit" value="Save">
<INPUT type="button" value="Cancel"
onclick="javascript:history.back();"></P>

Step 5 - At the beginning of the page, define the page content type, import the relevant Java packages into the page and include the i_global.jsp file to connect to the database.

 <%@page contentType="text/html" %>
 <%@page import="java.util.*" %>
 <%@page import="java.net.URLEncoder" %>
 <%/*Include the global configuration file
 containing the location of the database to connect to*/ %>
 <%@include file="i_global.jsp" %>

Step 6 - Initialize the variables used to store the article information retrieved from the database and other information used throughout the page.

 <%/*Initialise variables to be used throughout the page*/
ResultSet sqlResult=null;
String lngArticleID="";
String strArticleStyleElementText="";
String strArticleTitle="";
String strArticleBody="";
 %>

Step 7 - Attempt to create a connection to the database using the information contained in the i_global.jsp file.

 <%
//Attempt to establish a connection with the database 
try{
Connection conn=DriverManager.getConnection(url);

Step 8 - Assign the article ID included in the page URL to a variable, to be used to retrieve the relevant article information from the database.

lngArticleID=request.getParameter("article_id");

Step 9 - Create the SQL query string to retrieve the relevant article information from the database.

//SQL query to get required document from the database 
String sqlQuery="SELECT * FROM articles
WHERE article_id = "+lngArticleID;

Step 10 - Retrieve the information from the database and assign it to set variables, ensuring that the HTML is encoded.

//Retrieve the information from the database 
Statement stmt=conn.createStatement
(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
sqlResult=stmt.executeQuery(sqlQuery);
if(sqlResult.first()){
strArticleBody=URLEncoder.
encode(sqlResult.getString("article_body"));
strArticleStyleElementText=URLEncoder.encode(sqlResult.getString
("article_styleElementText"));
strArticleTitle=URLEncoder.encode
(sqlResult.getString("article_title"));
%>

Step 11 - Create a text element for the article title.

 <%// Article title  %> 
<P>Title: <INPUT type="text" name="article_title"
value="" size="40"></P>

Step 12 - Assign the value of the article title variable to the text element.

N.B. When the JavaServer Pages URLEncoder property is used, spaces in the string are encoded as '+'. Therefore, when using these URL encoded strings, the string must be filtered to ensure that spaces are represented correctly. The function properUnescape is used to perform this function. The article title is filtered when the value is assigned to the text element whereas the article body and style information is filtered when assigned to the relevant EditLive! properties.

<SCRIPT language="JavaScript">
<!--
//This function works around the problem with 
//unescape() treating + as a safe character instead of a space 
function properUnescape(input) {
return unescape(input.replace(/\+/g, "%20"));
}

//set the article title, since value=javascript:blah is not possible 
document.forms.form1.article_title.value = properUnescape
("<%=strArticleTitle%>");
//--> 
</SCRIPT>

Step 13 - Create hidden form objects for the other article information and give them all the initial values of the variables defined in Step 10.

 <%//Hidden field for identifying the article%>
<INPUT type="hidden" name="article_id" value="<%=lngArticleID%>">

 <%//Hidden fields for loading and storing content in EditLive!%> 
<INPUT type="hidden" name="article_styleElementText"
value="<%=strArticleStyleElementText%>">
<INPUT type="hidden" name="article_body"
value="<%=strArticleBody%>">

Step 14 - Change the arguments of the EditLive! setSource and setStyleElementText properties to load the existing article information into EditLive! from the hidden form objects.

//Load the contents of the documents STYLE element 
editLive1.setStyleElementText(properUnescape
(document.forms.form1.article_styleElementText.value));

//Load the contents of the documents BODY element 
editLive1.setSource(properUnescape
(document.forms.form1.article_body.value));

Step 15 - Add a function to the form to retrieve the contents and style information from EditLive! and assign it back to the hidden form objects when the Save button is pressed.

//This function gets the contents out of
//EditLive! and assigns it to a hidden form
//field when the Submit button is pressed. 
function form1_onsubmit()
{
document.forms.form1.article_body.value = editLive1.getSource();
document.forms.form1.article_styleElementText.value =
editLive1.getStyleElementText();
return true;
}

Step 16 - Call this function in the onsubmit event of the FORM tag.

<FORM name="form1" method="post" action="xt_edit.jsp"
onsubmit="return form1_onsubmit()">

xt_edit.jsp

Step 1 - Include the i_global.jsp file to connect to the database.

 <%/*Include the global configuration file
containing the location of the database to connect to*/ %>
 <%@include file="i_global.jsp" %>

Step 2 - Initialize the variables used to store the article information in the database.

 <%
//Initialise the variables 
String articleTitle="";
String articleStyleElementText="";
String articleBody="";
String articleID="";
boolean redirect=true;

Step 3 - Create a connection to the database using the information contained in the i_global.jsp file.

//Create a connection to the database 
Connection conn=DriverManager.getConnection(url);

Step 4 - Create the Prepared Statement that will form the basis of the SQL query used to store the altered article contents in the database.

//Create the sql query using ? where a value
//from the form is to be supplied 
String sqlQuery="UPDATE articles SET article_title=?,
article_styleElementText=?, article_body=? WHERE article_id=?";
//Create an updatable prepared statement connection to the database
//tables using the sql query
PreparedStatementps=conn.prepareStatement
(sqlQuery,ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);

Step 5 - Assign the POSTed form data to the relevant variables.

//Get values from the POSTed form
articleTitle=request.getParameter("article_title");
articleBody=request.getParameter("article_body");
articleStyleElementText=request.getParameter
("article_styleElementText");
articleID=request.getParameter("article_id");

Step 6 - Insert the information assigned to the variables into the Prepared Statement to complete the SQL statement to write the information to the database.

//Put the values from the form into the prepared statement 
ps.setString(1,articleTitle);
ps.setString(2,articleStyleElementText);
ByteArrayInputStream
bStream = new ByteArrayInputStream(articleBody.getBytes());
bStream.reset();
ps.setAsciiStream(3, bStream, bStream.available());
ps.setString(4,articleID);

Step 7 - Write the information to the database by executing the Prepared Statement, catching any exceptions that may occur.

try{
ps.executeUpdate();
}catch(Exception e){
redirect=false;
if(articleTitle.equals("")){
out.println("You must enter a title for your article,
enter a title and resubmit");
}else{
out.println("An error has occurred with the update to the database,

check all fields and resubmit<BR>");
out.println(e);
}
}

Step 8 - Close the connection to the database.

//close the prepared statement connection to
//the database and the connection to the database 
ps.close();
conn.close();

Step 9 - Redirect the Web browser back to the Index page.

//Redirect to start.jsp on successful insert operation 
if(redirect){
response.sendRedirect("start.jsp");
}
%>

Deleting an Existing Article

Purpose - The file delete.jsp displays the chosen article information to allow the user to confirm that they wish to delete the specified article. Once confirmation is obtained, the relevant record in the database is deleted using the processing page, xt_delete.jsp.

delete.jsp

Step 1 - Start with a standard HTML page add give it the heading "Delete a Document" and a message asking the user if they wish to delete the article.

<HTML>
<BODY>
<H1>Delete a Document</H1>
<P>Are you sure you wish to delete this article?</P>
</BODY>
</HTML>

Step 2 - Define the page type and include the i_global.jsp file to connect to the database.

 <%@page contentType="text/html" %>
 <%@include file="i_global.jsp" %>

Step 3 - Create a connection to the database catching any errors which may occur.

try{
Connection conn=DriverManager.getConnection(url);

Step 4 - Retrieve the article information for the article ID listed in the URL.

String sqlQuery="SELECT * FROM articles WHERE
article_id = "+request.getParameter("article_id");
Statement stmt=conn.createStatement
(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet result=stmt.executeQuery(sqlQuery);

Step 5 - Assign the article id and title retrieved from the database to the variables.

if(result.first()){
articleID=result.getString("article_id");
articleTitle=result.getString("article_title");
}

Step 6 - Close the database connection objects.

result.close();
stmt.close();
conn.close();

Step 7 - Display an error message if an exception occurs trying to connect to the database.

}catch(Exception e){
out.println("An error has occurred while trying to connect
to the database.Please see your system administrator.<BR>");
out.println(e);
}

Step 8 - Create a table in the HTML page that displays the article id and title using the variables defined in Step 5.

<TABLE>
<TR>
<TD>Article ID:</TD>
<TD> <%=articleID %></TD>
</TR>
<TR>
<TD>Title:</TD>
<TD> <%=articleTitle %></TD>
</TR>
</TABLE>

Step 9 - Create a form in the page that includes buttons to delete the article or cancel the deletion. Put the xt_delete.jsp file name in the action attribute of the FORM tag.

<FORM action="xt_delete.jsp" method="GET">
<P><INPUT type="submit" value="Delete">
<INPUT type="button" value="Cancel"
onclick="javascript:history.back();"></P>
</FORM>

Step 10 - Add a hidden form object to the form to store the article id for processing.

<INPUT type="hidden" name="article_id" value="<%=articleID%>">

xt_delete.jsp

Step 1 - Include the i_global.jsp file to connect to the database.

 <%/*Include the global configuration file
containing the location of the database to connect to*/ %>
 <%@include file="i_global.jsp" %>

Step 2 - Create a connection to the database, catching any exceptions that may occur.

 <%
try{
//Create a connection to the database using
//the url defined in i_global.jsp 
Connection conn=DriverManager.getConnection(url);

Step 3 - Create the SQL statement to delete the article from the database.

//Create the sql query to delete the required record 
String sqlQuery="DELETE FROM articles
WHERE article_id="+request.getParameter("article_id");
Statement stmt=conn.createStatement();

Step 4 - Delete the record.

//Execute the delete query 
stmt.executeUpdate(sqlQuery);

Step 5 - Redirect the web browser back to the index page.

//Redirect to the start page 
response.sendRedirect("start.jsp");

Step 6 - Close the database connection.

stmt.close();
conn.close();

Step 7 - If the database connection can not be created, print the error message encountered to the screen.

}catch (Exception e){out.println(e);}
%>

Viewing an Article (view.jsp)

Purpose - The file view.jsp displays the chosen article.

Step 1 - Start with a standard HTML page, import the relevant Java packages into the page, and include the i_global.jsp file to connect to the database.

 <%@page contentType="text/html" %>
 <%@page import="java.util.*" %>
 <%/*Include the global configuration file containing the
location of the database to connect to*/ %>
 <%@include file="i_global.jsp" %>
<HTML>
<HEAD>
</HEAD>
<BODY>
</BODY>
</HTML>

Step 2 - Initialize the variables used to store the article information retrieved from the database.

 <%/*Initialise variables to be used throughout the page*/
ResultSet sqlResult=null;
String articleID="";
String articleTitle="";
String articleStyleElementText="";
String articleBody="";
 %>

Step 3 - Create a connection to the database using the information contained in the i_global.jsp file.

 <%
//Attempt to establish a connection with the database 
try{
Connection conn=DriverManager.getConnection(url);

Step 4 - Create the SQL query to retrieve the information from the database based on the article ID included in the URL.

articleID=request.getParameter("article_id");

//SQL query to get required document from the database 
String sqlQuery="SELECT * FROM articles
WHERE article_id = "+articleID;

Step 5 - Retrieve the information from the database.

//Retrieve the information from the database 
Statement stmt=conn.createStatement
(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
sqlResult=stmt.executeQuery(sqlQuery);

Step 6 - Assign the retrieved information to the initialized variables.

if(sqlResult.first()){
articleStyleElementText=sqlResult.
getString("article_styleElementText");
articleBody=sqlResult.getString("article_body");
articleTitle=sqlResult.getString("article_title");

Step 7 - Use embedded JSP to set the STYLE tag of the page to the article style information retrieved from the database and to display the article title and content.

<HTML>
<HEAD>
<TITLE> <%=articleTitle %></TITLE>
<STYLE>
 <%=articleStyleElementText %>
</STYLE>
</HEAD>
<BODY>
<H1> <%=articleTitle %></H1>
 <%=articleBody %>
</BODY>
</HTML>

Step 8 - Catch any exceptions that occur while attempting to connect to the database.

 <%
}
}catch(Exception e){}
 %>

Copyright © 1999-2005 Ephox Corporation. All Rights Reserved. 'Ephox' is a registered trademark of Ephox Corporation.
Java and the Java Powered logo are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries.