/* * Spreadsheet.java */ /******************************************************************************* This script simulates a spreadsheet using any MySQL table with 4 columns (fields). The first column must be an auto_increment integer named "id" but the rest you can rename using the config variables below. Created by Neil Moomey www.neilmoomey.com Feel free to use it as you wish. I only ask you give me credit. Here is an example of how to set up a table and field names. SQL query to create the table phone_book: create table phone_book( id integer not null auto_increment, first_name varchar(50), last_name varchar(50), phone varchar(50), primary key (id) ) Now change these variables to fit the table you just created: String table = "phone_book"; String field1 = "first_name"; String field1_label = "First Name"; String field2 = "last_name"; String field2_label = "Last Name"; String field3 = "phone"; String field3_label = "Phone"; String db_host = "localhost"; String db_user = ""; String db_pass = ""; String db = ""; *******************************************************************************/ import java.sql.*; import javax.servlet.*; import javax.servlet.http.*; public class Spreadsheet extends HttpServlet { Connection con = null; Statement select = null; ResultSet result = null; /** Initializes the servlet. */ public void init(ServletConfig config) throws ServletException { super.init(config); } /** Destroys the servlet. */ public void destroy() { } /** Processes requests for both HTTP GET and POST methods. * @param request servlet request * @param response servlet response */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, java.io.IOException { response.setContentType("text/html"); java.io.PrintWriter out = response.getWriter(); try { out.println(""); out.println(""); out.println(""); String table = "phone_book"; String field1 = "first_name"; String field1_label = "First Name"; String field2 = "last_name"; String field2_label = "Last Name"; String field3 = "phone"; String field3_label = "Phone"; String db_host = "localhost"; String db_user = ""; String db_pass = ""; String db = ""; // End of variables definitions. No need to edit code beyond this line String query = ""; String url = "jdbc:mysql://localhost/" + db; String driver = "org.gjt.mm.mysql.Driver"; Class.forName(driver); con = DriverManager.getConnection(url, db_user, db_pass); select = con.createStatement(); String sort = request.getParameter("sort")+""; String read = request.getParameter("read")+""; String keyword = request.getParameter("keyword")+""; String update = request.getParameter("update")+""; String delete = request.getParameter("delete")+""; String insert = request.getParameter("insert")+""; String field1_value = request.getParameter("field1_value")+""; String field2_value = request.getParameter("field2_value")+""; String field3_value = request.getParameter("field3_value")+""; out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println("
"); out.println("Keyword "); out.println(""); out.println("
"); //insert into table ("field1","field2","field3") values ("field1_value","field2_value","field3_value") if (insert.equals("1")) query = "insert into " + table +"(" + field1 + "," + field2 + "," + field3 + ") values (\"" + field1_value + "\",\"" + field2_value + "\",\"" + field3_value + "\")"; //update table set field1="field1_value", field2="field2_value",field3="field3_value" where id=update"; if (!update.equals("null")) query = "update " + table + " set " + field1 + "=\"" + field1_value + "\", " + field2 + "=\"" + field2_value + "\"," + field3 + "=\"" + field3_value + "\" where id=" + update; //delete from table where id=delete if (!delete.equals("null")) query = "delete from " + table + " where id=" + delete; // Execute query if there is one. if (!query.equals("")) result = select.executeQuery(query); query = "select * from " + table; if (sort.equals("null")) sort=field1; if (sort.equals("id")) query+=" order by id"; if (sort.equals(field1)) query+=" order by " + field1; if (sort.equals(field2)) query+=" order by " + field2; if (sort.equals(field3)) query+=" order by " + field3; if (!read.equals("null")) { query = "select * from " + table + " where id=" + read; } if (!keyword.equals("null")) { query = "select * from " + table + " where " + field1 + " LIKE '%" + keyword + "%' OR " + field2 + " LIKE '%" + keyword + "%' OR " + field3 + " LIKE '%" + keyword + "%'"; } result = select.executeQuery(query); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); if (read.equals("null")) { out.println(""); out.println(""); out.println(" "); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); } if (read.equals("null")) { while(result.next()) { out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); } } else { while(result.next()) { out.println(" " + "\t "); out.println("\t "); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(" "); } } select.close(); con.close(); out.println("
ID" + field1_label + " " + field2_label + "" + field3_label + ""); if (!keyword.equals("null")) { out.println("View All"); } out.println("
New"); out.println("
"); out.print(result.getString(1)); out.println(""); out.print(result.getString(2)); out.println(""); out.print(result.getString(3)); out.println(""); out.print(result.getString(4)); out.println("EditDelete
"); out.print(result.getString(1)); out.println("
"); } catch(ClassNotFoundException e) { out.println("Couldn't load database driver: " + e.getMessage()); } catch(SQLException e) { out.println("SQLException caught: " + e.getMessage()); } finally { try { if (con != null) con.close(); } catch (SQLException ignored) { } } out.close(); } /** Handles the HTTP GET method. * @param request servlet request * @param response servlet response */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, java.io.IOException { processRequest(request, response); } /** Handles the HTTP POST method. * @param request servlet request * @param response servlet response */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, java.io.IOException { processRequest(request, response); } /** Returns a short description of the servlet. */ public String getServletInfo() { return "4 Column Spreadsheet Java Servlet using MySQL."; } }