// ------------------------------------------------------
// maintain.jhtml
//
// This page shows the jobs being maintained by a specific
// user. It also allows the user to create, edit, and delete
// jobs.
//
// Copyright (c) 1998. Stephen M. Bate,
// Object Computing, Inc. All rights reserved.
//
// This software is being provided by the copyright
// holder under the following license. By obtaining, using
// and/or copying this software, you agree that you have
// read, understood, and will comply with the following
// terms and conditions:
//
// Permission to use, copy, modify, and distribute this
// software and its documentation for any purpose and
// without fee or royalty is hereby granted, provided that
// the full text of this NOTICE appears on ALL copies of
// the software and documentation or portions thereof,
// including modifications, that you make.
//
// THIS SOFTWARE IS PROVIDED "AS IS," AND COPYRIGHT HOLDERS
// MAKE NO REPRESENTATIONS OR WARRANTIES, EXPRESS OR
// IMPLIED. BY WAY OF EXAMPLE, BUT NOT LIMITATION,
// COPYRIGHT HOLDERS MAKE NO REPRESENTATIONS OR WARRANTIES
// OF MERCHANTABILITY OR FITNESS FOR ANY PARTICULAR PURPOSE
// OR THAT THE USE OF THE SOFTWARE OR DOCUMENTATION WILL NOT
// INFRINGE ANY THIRD PARTY PATENTS, COPYRIGHTS, TRADEMARKS
// OR OTHER RIGHTS. COPYRIGHT HOLDERS WILL BEAR NO
// LIABILITY FOR ANY USE OF THIS SOFTWARE OR DOCUMENTATION.
//
// Title to copyright in this software and any associated
// documentation will at all times remain with copyright
// holder.
Maintain Jobs
// ------------------------------------------------------
// See index.jhtml for more details about import and extends.
java.sql.*
org.phxjug.db.DatabaseServlet
[Create New Job]
[Job Listings]
Instructions:
Use this page to create new job listing or maintain the
jobs you have previously entered. Select the edit
option to modify the title or description of a job.
Select the delete links to delete job listings.
// --------------------------------------------------
// Check for a remote user. A remote user will be
// present this page was accessed using HTTP
// authentification. Generally, this is achieved
// by setting up protection in the Java Web Server
// admin applet.
String user = null;
if ((user = request.getRemoteUser()) == null) {
// This page wasn't protected!
out.println("Error: No remote user");
log("No remote user");
}
// Display the function and include the current user id
// in the HTML.
Maintain Jobs (user: user):
//---------------------------------------------------
// Conditional HTML!
if (user != null) {
// Everything is cool. We have a remote user, so lets
// retrieve the user's jobs.
// This page also receives and process requests
// from other jhtml pages (like edit.jhtml and
// delete.jhtml). The following function (defined at
// the end of the file) is used to preprocess those
// requests before we retrieve the jobs. The preprocessing
// change the content of a job or create/delete jobs,
// so it's important to do it first.
processRequest(user, request, response);
// Declare connection, statement, and result set variables
// which will be used to access the database using JDBC.
// These variables are declared outside the try block so
// they will be available in the catch block if an error
// occurs.
Connection con = null;
PreparedStatement stmt = null;
ResultSet result = null;
try {
// Execute the SQL query to retrieve all 'active' jobs
// being maintained by this maintainer.
con = getConnection();
String query =
"select * from job where maintainer = ? and active = 'Y'";
stmt = con.prepareStatement(query);
// Set the user and execute the query
stmt.setString(1, user);
result = stmt.executeQuery();
// process each job
while (result.next()) {
// retrieve the columns we want
String id = result.getString("id");
String title = result.getString("abstract");
// Each maintainer job row contains a "button"
// editing and deleting a job.
- [edit]
[delete]
title
//
// This is the trailing Java code which wraps the row generation.
// Notice the while block started before the row generation and
// ends after this comment.
} // end of while loop
} // end of try block surrounding query
//
// Handle any exceptions that occurred during the row
// generation.
//
catch (Exception e) {
getServletContext().log(e, "query failed");
}
finally {
try {
// Close the statement and result set,
// if they were created before the exception occurred.
if (stmt != null) stmt.close();
if (result != null) result.close();
}
// Even the cleanup can cause an exception!
catch (Exception e) {
getServletContext().log(e, "query cleanup failed");
}
// Return the connection to the pool, so the next request
// can use it (implemented in the base class).
freeConnection(con);
}
} // end of if (user != null)
// ------------------------------------------------------
// Process a request for editing, creating, or deleting
// a job. Notice that this is a CLASS method.
//
void processRequest(String user,
HttpServletRequest request,
HttpServletResponse response) {
// getParameter is now deprecated, but the alternative
// is a real pain to use. According to the API, you should
// now use getParameters() which returns a string array.
// This is very inconvenient if you just need to access a
// single value parameter.
String action = request.getParameter("action");
if (action != null && user != null) {
Connection con = getConnection();
PreparedStatement stmt = null;
try {
// ----------------------------------------------
// Delete operation
//
if (action.equals("delete")) {
String id = request.getParameter("id");
if (id != null) {
stmt = con.prepareStatement("update job set active = ? " +
" where id = ? and maintainer = ?");
// set the query placeholders
// The query didn't work when I didn't use a variable for
// the active column. (???) This might just be a MySQL
// drive problem.
stmt.setString(1, "N");
stmt.setString(2, id);
stmt.setString(3, user);
// delete the job
stmt.executeUpdate();
}
}
// ----------------------------------------------
// Create operation
//
else if (action.equals("create")) {
stmt = con.prepareStatement("insert into job (abstract, " +
"description, maintainer) " +
"values (?, ?, ?)");
String title = request.getParameter("title");
String description = request.getParameter("description");
if (title == null) {
title = "";
log("null title in create operation");
}
if (description == null) {
description = "";
log("null description in create operation");
}
stmt.setString(1, title);
stmt.setString(2, description);
stmt.setString(3, user);
stmt.executeUpdate();
}
// ----------------------------------------------
// Delete operation
//
else if (action.equals("edit")) {
// To do: Put in timestamp check to implement safe
// optimistic locking. Get the current job timestamp
// from the database, compare it to the timestamp
// from the edit form parameter. If the db timestamp
// is newer than the form timestamp, then someone has
// changed the job since we started the edit. Disallow
// the edit in this case.
stmt = con.prepareStatement("update job set abstract = ?, " +
"description = ? where id = ?");
String id = request.getParameter("id");
if (id != null) {
String title = request.getParameter("title");
String description = request.getParameter("description");
if (title == null) {
title = "";
log("null title in edit operation");
}
if (description == null) {
description = "";
log("null description in edit operation");
}
stmt.setString(1, title);
stmt.setString(2, description);
stmt.setString(3, id);
stmt.executeUpdate();
}
}
}
catch (Exception e) {
getServletContext().log(e, action + " failed");
}
finally {
try {
// Close the statement
if (stmt != null) stmt.close();
}
// Even the cleanup can cause an exception!
catch (Exception e) {
getServletContext().log(e, action + " cleanup failed");
}
// Return the connection to the pool, so the next request
// can use it (implemented in the base class).
freeConnection(con);
}
} // action != null
}
// ------------------------------------------------------
// Include the common footer. The footer is placed in the
// /_private/jws_footer.html file and can be included
// from any jhtml pages on the web site.