Java Tutorial/Servlet/Database
Содержание
Read data from Database and display it in a HTML table
<source lang="java">
import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class AllEmployeesServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); out.println("<html>"); out.println("<head><title>All Employees</title></head>"); out.println("<body>");out.println("
All Employees
");Connection conn = null; Statement stmt = null; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); conn = DriverManager.getConnection("jdbc:odbc:Employees"); stmt = conn.createStatement(); String orderBy = request.getParameter("sort"); if ((orderBy == null) || orderBy.equals("")) { orderBy = "SSN"; } String orderByDir = request.getParameter("sortdir"); if ((orderByDir == null) || orderByDir.equals("")) { orderByDir = "asc"; } String query = "SELECT Employees.SSN, Employees.Name, " + "Employees.Salary, " + "Employees.Hiredate, Location.Location " + "FROM Location " + "INNER JOIN Employees " + "ON Location.Loc_Id = Employees.Loc_Id " + "ORDER BY " + orderBy + " " + orderByDir + ";"; ResultSet rs = stmt.executeQuery(query); while (rs.next()) { long employeeSSN = rs.getLong("SSN"); String employeeName = rs.getString("Name"); long employeeSalary = rs.getLong("Salary"); Date employeeHiredate = rs.getDate("Hiredate"); String employeeLocation = rs.getString("Location"); out.print(employeeSSN + "::"); out.print(employeeName + "::"); out.print(employeeSalary + "::"); out.print(employeeHiredate + "::"); out.print(employeeLocation + "::"); } } catch (SQLException e) { out.println("An error occured while retrieving " + "all employees: " + e.toString()); } catch (ClassNotFoundException e) { throw (new ServletException(e.toString())); } finally { try { if (stmt != null) { stmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException ex) { } }out.println("
out.println("</body>"); out.println("</html>"); out.close(); }
}</source>
Returns the list of the most popular flavors
<source lang="java">
/**
- Copyright (c) 2002 by Phil Hanna
- All rights reserved.
- You may study, use, modify, and distribute this
- software for any purpose provided that this
- copyright notice appears in all copies.
- This software is provided without warranty
- either expressed or implied.
- /
import java.io.*; import java.net.*; import java.sql.*; import java.util.*; import javax.servlet.*; import javax.servlet.http.*; /**
- Returns the list of the most popular flavors
- /
public class FlavorListServlet extends HttpServlet {
public static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; public static final String URL = "jdbc:mysql://localhost/IceCream"; public void doGet( HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PrintWriter out = response.getWriter(); response.setContentType("text/html"); // Get the bounds of the ranks to be listed // or use defaults int lowLimit = getLimit(request.getParameter("lowLimit"), 0); int highLimit = getLimit(request.getParameter("highLimit"), 100); Connection con = null; try { // Connect to the ice cream database Class.forName(JDBC_DRIVER); con = DriverManager.getConnection(URL); // Run a query to get the top flavors String sql = "SELECT RANK, NAME" + " FROM flavors" + " WHERE RANK BETWEEN ? AND ?" + " ORDER BY RANK" ; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setInt(1, lowLimit); pstmt.setInt(2, highLimit); ResultSet rs = pstmt.executeQuery(); // Print as an ordered listout.println("
- ");
while (rs.next()) {
int rank = rs.getInt(1);
String name = rs.getString(2);
out.println("
- " + name + " ");
}out.println("
} catch (SQLException e) { throw new ServletException(e.getMessage()); } catch (ClassNotFoundException e) { throw new ServletException(e.getMessage()); } // Close the database finally { if (con != null) { try { con.close(); } catch (SQLException ignore) {} } } } /** * Subroutine to get the integer value of one of * the limit parameters. * @param parm the parameter value, which may be null * @param defaultValue the default value */ private static int getLimit(String parm, int defaultValue) { int limit = defaultValue; if (parm != null) { try { limit = Integer.parseInt(parm); } catch (NumberFormatException ignore) { } } return limit; }
}</source>
Servlet Database Connection
<source lang="java">
import java.io.*; import java.sql.*; import javax.servlet.*; import javax.servlet.http.*; class ConnectionHolder implements HttpSessionBindingListener {
private Connection con = null; public ConnectionHolder(Connection con) { // Save the Connection this.con = con; try { con.setAutoCommit(false); // transactions can extend between web pages! } catch(SQLException e) { // Perform error handling } } public Connection getConnection() { return con; // return the cargo } public void valueBound(HttpSessionBindingEvent event) { // Do nothing when added to a Session } public void valueUnbound(HttpSessionBindingEvent event) { // Roll back changes when removed from a Session // (or when the Session expires) try { if (con != null) { con.rollback(); // abandon any uncomitted data con.close(); } } catch (SQLException e) { // Report it } }
} /* Actual Servlet */ public class MyServlet extends HttpServlet {
public void init() throws ServletException { try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e) { System.out.println("Couldn"t load OracleDriver"); throw new UnavailableException("Couldn"t load OracleDriver"); } } public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { res.setContentType("text/plain"); PrintWriter out = res.getWriter(); HttpSession session = req.getSession(true); Connection con; synchronized (session) { ConnectionHolder holder = (ConnectionHolder) session.getAttribute("servletapp.connection"); if (holder == null) { try { holder = new ConnectionHolder(DriverManager.getConnection( "jdbc:oracle:oci7:ordersdb", "user", "passwd")); session.setAttribute("servletapp.connection", holder); } catch (SQLException e) { log("Couldn"t get db connection", e); } } con = holder.getConnection(); } try { Statement stmt = con.createStatement(); stmt.executeUpdate("UPDATE INVENTORY SET STOCK = (STOCK - 10) "); stmt.executeUpdate("UPDATE SHIPPING SET SHIPPED = (SHIPPED + 10) "); res.sendRedirect(res.encodeRedirectURL(req.getContextPath() + "/servlet/CardHandler")); } catch (Exception e) { try { con.rollback(); session.removeAttribute("servletapp.connection"); } catch (Exception ignored) { } out.println("Order failed. Please contact technical support."); } }
}</source>
Servlet Database Gif Decoder
<source lang="java">
import java.io.*; import java.sql.*; import javax.servlet.*; import javax.servlet.http.*; public class MyServlet extends HttpServlet {
Connection con; public void init() throws ServletException { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); con = DriverManager.getConnection("jdbc:odbc:imagedb", "user", "passwd"); } catch (ClassNotFoundException e) { throw new UnavailableException("Couldn"t load JdbcOdbcDriver"); } catch (SQLException e) { throw new UnavailableException("Couldn"t get db connection"); } } public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { try { res.setContentType("image/gif"); ServletOutputStream out = res.getOutputStream(); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT IMAGE FROM PICTURES WHERE PID = " + req.getParameter("PID")); if (rs.next()) { BufferedInputStream gifData = new BufferedInputStream(rs.getBinaryStream("image")); byte[] buf = new byte[4 * 1024]; // 4K buffer int len; while ((len = gifData.read(buf, 0, buf.length)) != -1) { out.write(buf, 0, len); } } else { res.sendError(res.SC_NOT_FOUND); } } catch(SQLException e) { // Report it } }
}</source>
Servlet Database ResultSet Display Helper
<source lang="java">
import java.io.*; import java.sql.*; import javax.servlet.*; import javax.servlet.http.*; public class MyServlet extends HttpServlet {
private Connection con = null; public void init() throws ServletException { try { Class.forName("com.sybase.jdbc.SybDriver"); con = DriverManager.getConnection("jdbc:sybase:Tds:dbhost:7678", "user", "passwd"); } catch (ClassNotFoundException e) { throw new UnavailableException("Couldn"t load database driver"); } catch (SQLException e) { throw new UnavailableException("Couldn"t get db connection"); } } public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { res.setContentType("text/html"); PrintWriter out = res.getWriter(); out.println("<HTML><HEAD><TITLE>Phonebook</TITLE></HEAD>"); out.println("<BODY>"); HtmlSQLResult result = new HtmlSQLResult("SELECT NAME, PHONE FROM EMPLOYEES", con);out.println("
Employees:
");out.println(result); out.println("</BODY></HTML>"); } public void destroy() { try { if (con != null) con.close(); } catch (SQLException ignored) { } }
}
class HtmlSQLResult {
private String sql; private Connection con; public HtmlSQLResult(String sql, Connection con) { this.sql = sql; this.con = con; } public String toString() { // can be called at most once StringBuffer out = new StringBuffer(); // Uncomment the following line to display the SQL command at start of table// out.append("Results of SQL Statement: " + sql + "
\n"); try { Statement stmt = con.createStatement(); if (stmt.execute(sql)) { // There"s a ResultSet to be had ResultSet rs = stmt.getResultSet(); out.append("
" + rsmd.getColumnLabel(i)); out.append(" |
---|
"); // start a new data element
Object obj = rs.getObject(i); if (obj != null) out.append(obj.toString()); else out.append(" "); }out.append(" |
} else { // There"s a count to be had out.append("Records Affected: " + stmt.getUpdateCount()); } } catch (SQLException e) {out.append("</TABLE>
ERROR:
" + e.getMessage());} return out.toString(); }
}</source>
Servlet Update Database
<source lang="java">
import java.io.*; import java.sql.*; import javax.servlet.*; import javax.servlet.http.*; public class MyServlet extends HttpServlet {
public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { res.setContentType("text/plain"); PrintWriter out = res.getWriter(); Connection con = null; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); con = DriverManager.getConnection("jdbc:odbc:ordersdb", "user", "passwd"); // Turn on transactions con.setAutoCommit(false); Statement stmt = con.createStatement(); stmt.executeUpdate("UPDATE INVENTORY SET STOCK = (STOCK - 10) "); stmt.executeUpdate("UPDATE SHIPPING SET SHIPPED = (SHIPPED + 10)"); con.rumit(); out.println("Order successful! Thanks for your business!"); } catch (Exception e) { // Any error is grounds for rollback try { con.rollback(); } catch (SQLException ignored) { } out.println("Order failed. Please contact technical support."); } finally { // Clean up. try { if (con != null) con.close(); } catch (SQLException ignored) { } } }
}</source>