001: package example;
002:
003: import java.io.PrintWriter;
004: import java.io.IOException;
005:
006: import java.sql.Connection;
007: import java.sql.Statement;
008: import java.sql.ResultSet;
009: import java.sql.SQLException;
010:
011: import javax.sql.DataSource;
012:
013: import javax.servlet.ServletException;
014:
015: import javax.servlet.http.HttpServlet;
016: import javax.servlet.http.HttpServletRequest;
017: import javax.servlet.http.HttpServletResponse;
018:
019: import javax.webbeans.In;
020:
021: /**
022: * The BasicServlet executes a simple JDBC query.
023: *
024: * The DataSource is saved on initialization to save the JNDI lookup
025: * time.
026: */
027: public class BasicServlet extends HttpServlet {
028: /**
029: * The saved DataSource for the database
030: */
031: @In
032: private DataSource _ds;
033:
034: /**
035: * Initializes the database if necessary and fill it with the example
036: * values.
037: */
038: public void init() throws ServletException {
039: try {
040: Connection conn = _ds.getConnection();
041:
042: try {
043: Statement stmt = conn.createStatement();
044:
045: try {
046: ResultSet rs = stmt
047: .executeQuery("SELECT id FROM jdbc_basic_brooms");
048:
049: if (rs.next()) {
050: rs.close();
051: stmt.close();
052: return; // already initialized
053: }
054: } catch (SQLException e) {
055: }
056:
057: stmt.executeUpdate("CREATE TABLE jdbc_basic_brooms ("
058: + " id INTEGER PRIMARY KEY auto_increment,"
059: + " name VARCHAR(128)," + " cost INTEGER"
060: + ")");
061: stmt
062: .executeUpdate("INSERT INTO jdbc_basic_brooms (name, cost) "
063: + "VALUES ('firebolt', 4000)");
064: stmt
065: .executeUpdate("INSERT INTO jdbc_basic_brooms (name, cost) "
066: + "VALUES ('nimbus 2001', 500)");
067: stmt
068: .executeUpdate("INSERT INTO jdbc_basic_brooms (name, cost) "
069: + "VALUES ('nimbus 2000', 300)");
070: stmt
071: .executeUpdate("INSERT INTO jdbc_basic_brooms (name, cost) "
072: + "VALUES ('cleansweep 7', 150)");
073: stmt
074: .executeUpdate("INSERT INTO jdbc_basic_brooms (name, cost) "
075: + "VALUES ('cleansweep 5', 100)");
076: stmt
077: .executeUpdate("INSERT INTO jdbc_basic_brooms (name, cost) "
078: + "VALUES ('shooting star', 50)");
079:
080: stmt.close();
081: } finally {
082: conn.close();
083: }
084: } catch (SQLException e) {
085: throw new ServletException(e);
086: }
087: }
088:
089: /**
090: * Respond to a request by doing a query and returning the results.
091: */
092: public void service(HttpServletRequest req, HttpServletResponse res)
093: throws java.io.IOException, ServletException {
094: res.setContentType("text/html");
095:
096: PrintWriter out = res.getWriter();
097:
098: try {
099: doQuery(out);
100: } catch (SQLException e) {
101: throw new ServletException(e);
102: }
103: }
104:
105: /**
106: * Typical pattern for database use.
107: */
108: public void doQuery(PrintWriter out) throws IOException,
109: SQLException {
110: Connection conn = _ds.getConnection();
111:
112: try {
113: String sql = "SELECT name, cost FROM jdbc_basic_brooms ORDER BY cost DESC";
114:
115: Statement stmt = conn.createStatement();
116:
117: ResultSet rs = stmt.executeQuery(sql);
118:
119: out.println("<table border='3'>");
120:
121: while (rs.next()) {
122: out.println("<tr><td>" + rs.getString(1));
123: out.println(" <td>" + rs.getString(2));
124: }
125:
126: out.println("</table>");
127:
128: rs.close();
129: stmt.close();
130: } finally {
131: conn.close();
132: }
133: }
134: }
|