Java Tutorial/Servlet/Database

Материал из Java эксперт
Версия от 17:44, 31 мая 2010; (обсуждение)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Read data from Database and display it in a HTML table

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("<center><h1>All Employees</h1>");
    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("</center>");
    out.println("</body>");
    out.println("</html>");
    out.close();
  }
}





Returns the list of the most popular flavors

/**
*  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 list
         out.println("<ol>");
         while (rs.next()) {
            int rank = rs.getInt(1);
            String name = rs.getString(2);
            out.println("   <li>" + name + "</li>");
         }
         out.println("</ol>");
      }
      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;
   }
}





Servlet Database Connection

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.");
    }
  }
}





Servlet Database Gif Decoder

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
    }
  }
}





Servlet Database ResultSet Display Helper

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("<H2>Employees:</H2>");
    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 + "<P>\n");
    try {
      Statement stmt = con.createStatement();
      if (stmt.execute(sql)) {
        // There"s a ResultSet to be had
        ResultSet rs = stmt.getResultSet();
        out.append("<TABLE>\n");
        ResultSetMetaData rsmd = rs.getMetaData();
        int numcols = rsmd.getColumnCount();
    
        // Title the table with the result set"s column labels
        out.append("<TR>");
        for (int i = 1; i <= numcols; i++)
          out.append("<TH>" + rsmd.getColumnLabel(i));
        out.append("</TR>\n");
        while(rs.next()) {
          out.append("<TR>");  // start a new row
          for(int i = 1; i <= numcols; i++) {
            out.append("<TD>");  // start a new data element
            Object obj = rs.getObject(i);
            if (obj != null)
              out.append(obj.toString());
            else
              out.append("&nbsp;");
            }
          out.append("</TR>\n");
        }
        // End the table
        out.append("</TABLE>\n");
      }
      else {
        // There"s a count to be had
        out.append("<B>Records Affected:</B> " + stmt.getUpdateCount());
      }
    }
    catch (SQLException e) {
      out.append("</TABLE><H1>ERROR:</H1> " + e.getMessage());
    }
    
    return out.toString();
  }
}





Servlet Update Database

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) { }
    }
  }
}


Источник — «http://jexp.ru/index.php?title=Java_Tutorial/Servlet/Database&oldid=4887»