Servlet -
package
jsp;import javax.servlet.ServletException;import javax.servlet.ServletConfig;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import javax.sql.DataSource;import javax.sql.RowSet;import sun.jdbc.rowset.CachedRowSet;public class ListServlet extends HttpServlet { public void init(ServletConfig config) throws ServletException { super.init(config); } public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException { doGet(req, res); } public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException { try { RowSet rs = new CachedRowSet(); rs.setDataSourceName("java:comp/env/jdbc/inventoryDB"); rs.setCommand("select * from item"); rs.execute(); req.setAttribute("rs", rs); getServletContext().getRequestDispatcher("/List.jsp"). forward(req, res); } catch(Exception ex) { throw new ServletException(ex); } }}
package jsp;import javax.servlet.ServletException;import javax.servlet.ServletConfig;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import javax.sql.DataSource;import javax.naming.InitialContext;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;public class CreateServlet extends HttpServlet { public void init(ServletConfig config) throws ServletException { super.init(config); } public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException { doGet(req, res); } public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException { Connection con = null; try { InitialContext ctx = new InitialContext(); DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/inventoryDB"); con = ds.getConnection(); PreparedStatement stmt = con.prepareStatement("insert into item " + "(name,description,price,stock) " + "values (?, ?, ?, ?)"); stmt.setString(1, req.getParameter("name")); stmt.setString(2, req.getParameter("description")); stmt.setDouble(3, Double.parseDouble(req.getParameter("price"))); stmt.setInt(4, Integer.parseInt(req.getParameter("stock"))); stmt.executeUpdate(); stmt.close(); getServletContext().getRequestDispatcher("/List"). forward(req, res); } catch(Exception ex) { throw new ServletException(ex); } finally { try { if(con != null) con.close(); } catch(Exception ex) { throw new ServletException(ex); } } }}
List.jsp
<%@page contentType="text/html"%><jsp:useBean id="rs" scope="request" type="javax.sql.RowSet" /><html> <head> <title>Inventory - List</title> </head> <body style="font-family:verdana;font-size:10pt;"> <table cellpadding="5" style="font-family:verdana;font-size:10pt;"> <tr> <th>Name</th> <th>Description</th> <th>Price</th> <th>Stock</th> <th></th> <th></th> </tr> <% while(rs.next()) { %> <tr> <td><%= rs.getString(2) %></td> <td><%= rs.getString(3) %></td> <td><%= rs.getString(4) %></td> <td><%= rs.getString(5) %></td> <td> <a href="Delete?id=<%= rs.getString(1) %>"> Delete </a> </td> <td> <a href="Edit?id=<%= rs.getString(1) %>"> Edit </a> </td> </tr> <% } %> </table> <a href="New.html">New Item</a> </body></html>
New.html
<html> <head> <title>Inventory - Add New Item</title> </head> <body style="font-family:verdana;font-size:10pt;"> <form action="Create"> <table cellpadding="5" style="font-family:verdana;font-size:10pt;"> <tr> <td><b>Name:</b></td> <td><input name="name" type="text"/></td> </tr> <tr> <td><b>Description:</b></td> <td><input name="description" type="text"/></td> </tr> <tr> <td><b>Price:</b></td> <td><input name="price" type="text"/></td> </tr> <tr> <td><b>Stock:</b></td> <td><input name="stock" type="text"/></td> </tr> <tr> <td></td> <td><input type="submit" value="Create"/></td> </tr> </table> </body></html>
inventory mysql db
CREATE DATABASE inventory;USE inventory;CREATE TABLE item ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30) NOT NULL, description VARCHAR(30) NOT NULL, price DOUBLE NOT NULL, stock INTEGER NOT NULL); INSERT INTO item (name, description, price, stock) VALUES ('Straw Hat', 'The best in town', 78.99, 9012);INSERT INTO item (name, description, price, stock) VALUES ('Polo Shirt', 'The latest fashion', 49.99, 99);
web.xml
<?xml version="1.0" encoding="ISO-8859-1"?><!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/j2ee/dtds/web-app_2_3.dtd"><web-app> <servlet> <servlet-name>List</servlet-name> <servlet-class>jsp.ListServlet</servlet-class> </servlet> <servlet> <servlet-name>Edit</servlet-name> <servlet-class>jsp.EditServlet</servlet-class> </servlet> <servlet> <servlet-name>Delete</servlet-name> <servlet-class>jsp.DeleteServlet</servlet-class> </servlet> <servlet> <servlet-name>Update</servlet-name> <servlet-class>jsp.UpdateServlet</servlet-class> </servlet> <servlet> <servlet-name>Create</servlet-name> <servlet-class>jsp.CreateServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>List</servlet-name> <url-pattern>/List</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>Edit</servlet-name> <url-pattern>/Edit</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>Delete</servlet-name> <url-pattern>/Delete</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>Update</servlet-name> <url-pattern>/Update</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>Create</servlet-name> <url-pattern>/Create</url-pattern> </servlet-mapping></web-app>
I put the mm-sql driver and rowset.jar to the classpath under /lib. I am using JBuilder and cannot run it. How should I change it not to use rowset?
I just want to use a jsp -> servlet -> mysql deployment to submit and retrieve data.
Any idea?
Andrew