Training, Open Source computer languages
PerlPHPPythonMySQLApache / TomcatTclRubyJavaC and C++LinuxCSS 
Search for:
Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
JSP, Servlets and MySQL / David Harms

Posted by admin (Graham Ellis), 4 September 2002
Good book; nice to see someone covering server side Java and MySQL in the same text.  Full details at:
http://www.wellho.net/book/0-7645-4787-9.html

Caution: The web site address that the books give for source download doesn't work, and I don't know of any alternative.  Think that others have the same problem as I regularly get emails asking - and I'm not even anything to do with the author or publisher!

Addendum - January 2003 See extra post at the end of the thread - we have an alternative live URL for the book source ...
April 2003 Alternative URL no longer operational.  

Posted by admin (Graham Ellis), 4 September 2002
Here's an  example from one of our training courses, which is a single servlet class that generates a forms, and also calls both the insert and the select queries.  It includes only one explicit reference to each field name making it very easy to change in the future.

Code:
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

/*
* General class for inserting and reporting on a table
*/

public class J850staff extends HttpServlet {

   public void doGet(HttpServletRequest request,
                     HttpServletResponse response)
       throws IOException, ServletException
   {
   String action = "Unselected";
   try {
   action = request.getParameter("action");
   } catch (Exception e) {
   }
   if (action == null) action = "Unselected";

   String [] fields = {"name","job","office","hometown","title"};
   String reply = "";

   if (action.equals("Unselected")) {
   reply = "Hello and Welcome";
   } else if (action.startsWith("Insert")) {
   reply = runInsert(fields,request);
   } else if (action.startsWith("Select")) {
   reply = runSelect(fields,request);
   } else {
   reply = "Corrupt form";
   }

   reply = reply + "<BR><HR><BR>" + getForms(fields);

       response.setContentType("text/html");
       PrintWriter out = response.getWriter();

       out.println(reply);

   }

String runInsert (String [] fields,HttpServletRequest request) {

   StringBuffer fnames = new StringBuffer("");
   StringBuffer fvals = new StringBuffer("");

   for (int i=0; i<fields.length; i++) {
   String called = request.getParameter(fields[i]);
   if (i>0) fnames.append(", ");
   fnames.append(fields[i]);
   if (i>0) fvals.append(", ");
   fvals.append("'"+called+"'");
   }

   J850sjdbc.insert(fnames.toString(), fvals.toString());

   return ("Insertion Done");

   }

String runSelect (String [] fields,HttpServletRequest request) {

   StringBuffer fselect = new StringBuffer("where ");

   for (int i=0; i<fields.length; i++) {
   String called = request.getParameter(fields[i]);

   if (! called.equals(""))
   {
   if (fselect.length() > 6) {
   fselect.append(" && ");
   }
   fselect.append (fields[i] + " = '" +
   called +"' ");
   }
   }
   if (fselect.length() <= 6) {
   fselect = new StringBuffer("");
   }

   return (J850sjdbc.select(fields, fselect.toString()));

}

String getForms (String [] fields) {

   StringBuffer insert = new StringBuffer("<table border=1>");

   for (int i=0; i<fields.length; i++) {
   insert.append("<tr><td>"+fields[i]+"</td><td><input name="+
   fields[i]+"></td></tr>");
   }

   insert.append("<tr><td>then ..</td><td>"+
   "<input type=submit name=action value=Insert>"+
   "<input type=submit name=action value=Select>"+
   "</td></tr></table>");

   return ("<form>"+insert.toString()+"</form>");
   }
}


Here's the source code of that class that references the SQL database:

Code:
public class J850sjdbc {

public static String insert(String names, String values) {

   java.sql.Connection conn = linktodata();

   String todo = ("INSERT into staff " +
   "(" + names + ") values (" + values + ")");

   try {
   java.sql.Statement s = conn.createStatement();
   int r = s.executeUpdate (todo);
   }
   catch (Exception e) {
   return ("Oh oops - code 003\n"+e);
   }
       return (todo);
   }

public static String select(String [] fields, String selector) {

   java.sql.Connection conn = linktodata();

   StringBuffer reply = new StringBuffer("<table border=1>");

   String todo = ("SELECT * "+
   " from staff " + selector);

   try {
   java.sql.Statement s = conn.createStatement();
   java.sql.ResultSet r = s.executeQuery (todo);
   while(r.next()) {
   reply.append("<tr>");
   for (int i=0;i<fields.length;i++) {
   reply.append(tabit(r.getString(fields[i])));
   }
   reply.append("</tr>");
   }
   reply.append("</table>");
   }
   catch (Exception e) {
   return ("Oh oops - code 003\n"+e);
   }

   return (reply.toString());
       }

private static String tabit(String box) {
   return ("<td>"+box+"</td>");
   }

private static java.sql.Connection linktodata () {

   java.sql.Connection conn = null;
   try {
   Class.forName("org.gjt.mm.mysql.Driver").newInstance();
   }
   catch (Exception e) {
   return conn;
   // return "Oh dear - code 001 and a quarter";
   }
   try {
   conn = java.sql.DriverManager.getConnection(
   "jdbc:mysql://bhajee/J850a?user=jtest&password=");
   }
   catch (Exception e) {
   return conn;
   // return "Oh dear - code 001 and a half";
   }
   return conn;
}
}



Posted by admin (Graham Ellis), 27 January 2003
Alternative URL for the book (this one works as of the date that I'm posting )

http://www.covecomm.com/index.html

Posted by admin (Graham Ellis), 22 April 2003
An update ... the URL in the previous post is no longer operational ... if anyone knows a fresh location, please let me know!



This page is a thread posted to the opentalk forum at www.opentalk.org.uk and archived here for reference. To jump to the archive index please follow this link.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2014: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho