Java Tutorial/Servlet/Database
Содержание
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(" ");
}
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) { }
}
}
}
