ticketdb.java

完整的网上商城程序

package mypack;

import java.sql.*;
import javax.naming.*;
import javax.sql.*;
import java.util.*;

public class TicketDB
{
  private ArrayList tickets;
  private String dbUrl="jdbc:mysql://latcs5.cs.latrobe.edu.au/m5wang";
  private String dbUser="m5wang";
  private String dbPwd="19821129";

  public TicketDB() throws Exception
  {
    Class.forName("com.mysql.jdbc.Driver");
  }

  public Connection getConnection() throws Exception
  {
    return java.sql.DriverManager.getConnection(dbUrl, dbUser, dbPwd);
  }

  public void closeConnection(Connection con)
  {
    try {
      if (con != null) con.close();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }

  public void closePrepStmt(PreparedStatement prepStmt)
  {
    try
    {
      if(prepStmt!=null)prepStmt.close();
    }
    catch(Exception e)
    {
      e.printStackTrace();
    }
  }

  public void closeResultSet(ResultSet rs)
  {
    try
    {
      if(rs!=null)rs.close();
    }
    catch(Exception e)
    {
      e.printStackTrace();
    }
  }

  public int getNumberOfTickets() throws Exception
  {
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs=null;
    tickets=new ArrayList();

    try
    {
      con=getConnection();
      String selectStatement="select *"+"from tickets";
      prepStmt = con.prepareStatement(selectStatement);
      rs=prepStmt.executeQuery();

      while(rs.next())
      {
        TicketDetails td=new TicketDetails(rs.getString(1), rs.getString(2),
           rs.getFloat(4), rs.getString(5), rs.getInt(6), rs.getInt(7), rs.getString(8));

        tickets.add(td);
      }
    }
    finally
    {
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);

    }
    return tickets.size();
  }

  public Collection getTickets() throws Exception
  {
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs=null;
    tickets=new ArrayList();
    try
    {
      con=getConnection();
      String selectStatement="select *"+"from tickets";
      prepStmt=con.prepareStatement(selectStatement);
      rs=prepStmt.executeQuery();

      while(rs.next())
      {
        TicketDetails td=new TicketDetails(rs.getString(1), rs.getString(2),
           rs.getFloat(4), rs.getString(5), rs.getInt(6), rs.getInt(7), rs.getString(8));

        tickets.add(td);
      }
    }
    finally
    {
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);

    }

    Collections.sort(tickets);
    return tickets;
  }

  public TicketDetails getTicketDetails(String ticketId) throws Exception
  {
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs=null;
    try
    {
      con=getConnection();
      String selectStatement="select *"+"from tickets where ticketId=?";
      prepStmt=con.prepareStatement(selectStatement);
      prepStmt.setString(1,ticketId);
      rs=prepStmt.executeQuery();

      if(rs.next())
      {
        TicketDetails td=new TicketDetails(rs.getString(1), rs.getString(2),
           rs.getFloat(4), rs.getString(5), rs.getInt(6), rs.getInt(7), rs.getString(8));
        prepStmt.close();

        return td;
      }
      else
      {
        return null;
      }
    }
    finally
    {
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);

    }
  }

  public void buyTickets(ShoppingCart cart) throws Exception
  {
    Connection con=null;
    Collection items=cart.getItems();
    Iterator i = items.iterator();
    try
    {
      con=getConnection();
      con.setAutoCommit(false);
      while(i.hasNext())
      {
        ShoppingCartItem sci=(ShoppingCartItem)i.next();
        TicketDetails td=(TicketDetails)sci.getItem();
        String id=td.getTicketId();
        int quantity=sci.getQty();
        buyTicket(id, quantity, con);
      }
      con.commit();
      con.setAutoCommit(true);
    }
    catch(Exception ex)
    {
      con.rollback();
      throw ex;
    }
    finally
    {
      closeConnection(con);
    }
  }

  public void buyTicket(String ticketId, int quantity, Connection con) throws
      Exception
  {
    PreparedStatement prepStmt=null;
    ResultSet rs=null;
    try
    {
      String selectStatement="select *"+"from tickets where ticketId=?";
      prepStmt=con.prepareStatement(selectStatement);
      prepStmt.setString(1,ticketId);
      rs=prepStmt.executeQuery();

      if(rs.next())
      {
        prepStmt.close();
        String updateStatement="update tickets set saleamount + ? where ticketId=?";
        prepStmt=con.prepareStatement(updateStatement);
        prepStmt.setInt(1, quantity);
        prepStmt.setString(2, ticketId);
        prepStmt.executeUpdate();
        prepStmt.close();
      }
    }
    finally
    {
      closeResultSet(rs);
      closePrepStmt(prepStmt);
    }
  }




}