cdstore.java

用jsp,tomcat,mysql

    PreparedStatement prepStmt=null;
    ResultSet rs =null;
    try {
      con=getConnection();
      String selectStatement = "select * " + "from customer where username = ? ";
      prepStmt = con.prepareStatement(selectStatement);
      prepStmt.setString(1, cdId);
      rs = prepStmt.executeQuery();

      if (rs.next()) {
        Customer bd = new Customer(rs.getString(1), rs.getString(2), rs.getInt(3),rs.getString(4), rs.getString(5)
          );
        prepStmt.close();

        return bd;
      }
      else {
        return null;
      }
    }finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);
    }
  }
  
  
  public boolean deleteCd (String cdId) throws Exception {
    Connection con=null;
    PreparedStatement prepStmt=null;
   
    try {
      con=getConnection();
      String updateStatement = " delete from cd where id = ? ";

      prepStmt = con.prepareStatement(updateStatement);
      prepStmt.setString(1, cdId);
      prepStmt.executeUpdate();
      prepStmt.close();

      return true;
      

    }finally{
 
      closePrepStmt(prepStmt);
      closeConnection(con);
    }
  }
    
  public boolean deleteUser (String cdId) throws Exception {
    Connection con=null;
    PreparedStatement prepStmt=null;
   
    try {
      con=getConnection();
      String updateStatement = " delete from customer where username = ? ";

      prepStmt = con.prepareStatement(updateStatement);
      prepStmt.setString(1, cdId);
      prepStmt.executeUpdate();
      prepStmt.close();

      return true;
      

    }finally{
 
      closePrepStmt(prepStmt);
      closeConnection(con);
    }
  }
  
 public boolean deleteOrder (String cdId) throws Exception {
    Connection con=null;
    PreparedStatement prepStmt=null;
   
    try {
      con=getConnection();
      String updateStatement = " delete from cdorder where id = "+cdId+" ";

      prepStmt = con.prepareStatement(updateStatement);
      
      prepStmt.executeUpdate();
      prepStmt.close();

      return true;
      

    }finally{
 
      closePrepStmt(prepStmt);
      closeConnection(con);
    }
  }
  

  public void buyCds(ShoppingCart cart,String username,String thedate)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();
        CdDetails bd = (CdDetails)sci.getItem();
        String id = bd.getCdId();
        int quantity = sci.getQuantity();
        buyCd(id, quantity,con,username,thedate);
      }
      con.commit();
      con.setAutoCommit(true);

    } catch (Exception ex) {
      con.rollback();
      throw ex;
    }finally{
       closeConnection(con);
    }
  }


  public void buyCd(String cdId, int quantity,Connection con,String username ,String thedate) throws Exception {
    PreparedStatement prepStmt=null;
    ResultSet rs=null;
    String cdname;
    
    try{
      String selectStatement = "select * " + "from cd where id = ? ";
      prepStmt = con.prepareStatement(selectStatement);
      prepStmt.setString(1, cdId);
      rs = prepStmt.executeQuery();
      cdname=getCdDetails(cdId).getTitle();
 

      if (rs.next()) {
          prepStmt.close();
          
          String updateStatement =
                  "update cd set saleamount = saleamount + ? where id = ?";
          prepStmt = con.prepareStatement(updateStatement);
          prepStmt.setInt(1, quantity);
          prepStmt.setString(2, cdId);
          prepStmt.executeUpdate();
          String updateStatement1 =
                  "update cd set warehouse = warehouse - ? where id = ?";
          prepStmt = con.prepareStatement(updateStatement1);
          prepStmt.setInt(1, quantity);
          prepStmt.setString(2, cdId);
          prepStmt.executeUpdate();
          prepStmt.close();
          String insertStatement =
                  "insert into  account (username,cdname,saleAmount,thedate) values('"+username+"','"+cdname+"',"+quantity+",'"+thedate+"')";
          prepStmt = con.prepareStatement(insertStatement);
          prepStmt.executeUpdate();
          prepStmt.close();
      
       }

    }catch (SQLException e) 
{
			e.printStackTrace();
}finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
    }
  }
  
public boolean checkMemberExist(String username) throws Exception {
		Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs =null;
    try {
      con=getConnection();
      String selectStatement = "select * from customer where username='" + username + "'";
      prepStmt = con.prepareStatement(selectStatement);
      rs = prepStmt.executeQuery();

		
		if(rs==null)return false;

		if (rs.next())
			return true;}
		 catch (SQLException e) {
			e.printStackTrace();
		}finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);
		}
		return false;

	}
public boolean addUser(Customer customer) throws Exception {


		Connection con=null;
    PreparedStatement prepStmt=null;
    con=getConnection();
    try{        
          String updateStatement =
                  "insert into customer values('"+customer.getUsername()+"','"+customer.getPassword()+"',0,'"+customer.getTel()+"','"+customer.getEmail()+"')";
          prepStmt = con.prepareStatement(updateStatement);
          prepStmt.executeUpdate();
          prepStmt.close();
          return true;}
catch (SQLException e) 
{
			e.printStackTrace();
			return false;
}
    finally{
      closeConnection(con);
      closePrepStmt(prepStmt);
    }
 }
 
 public boolean addOrder(Order order) throws Exception {


		Connection con=null;
    PreparedStatement prepStmt=null;
    con=getConnection();
    try{        
          String updateStatement =
                  "insert into cdorder (username,name,amount,thedate,isorder) values('"+order.getUsername()+"','"+order.getName()+"','"+order.getAmount()+"','"+order.getThedate()+"','1')";
          prepStmt = con.prepareStatement(updateStatement);
          prepStmt.executeUpdate();
          prepStmt.close();
          return true;}
catch (SQLException e) 
{
			e.printStackTrace();
			return false;
}
    finally{
      closeConnection(con);
      closePrepStmt(prepStmt);
    }
 }
	
public boolean verifyUser (String username,String password) throws Exception {
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs =null;
    try {
      con=getConnection();
      String selectStatement = "select * from customer where username='" + username + "'and password='"+password+"' and isadmin=0";
      prepStmt = con.prepareStatement(selectStatement);
      rs = prepStmt.executeQuery();
      if (rs.next())
      return true;
        } 
    catch (SQLException e) {
        
            e.printStackTrace();
        }finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);
    }
		return false;
	}
	
		
public boolean verifyAdmin (String username,String password) throws Exception {
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs =null;
    try {
      con=getConnection();
      String selectStatement = "select * from customer where username='" + username + "'and password='"+password+"' and isadmin=1";
      prepStmt = con.prepareStatement(selectStatement);
      rs = prepStmt.executeQuery();
      if (rs.next())
      return true;
        } 
    catch (SQLException e) {
        
            e.printStackTrace();
        }finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);
    }
		return false;
	}
	
 public boolean updateCd(String cdId,String title,String name,String description,String saletype,String warehouse,String price ,String year ,String saleamount) throws Exception {


		Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs=null;
    try{
    	con=getConnection();
      String selectStatement = "select * " + "from cd where id = ? ";
      prepStmt = con.prepareStatement(selectStatement);
      prepStmt.setString(1, cdId);
      rs = prepStmt.executeQuery();

      if (!rs.next()) {

          return false;
          
          }
          else
          {
          String updateStatement =
                  "update cd set id='"+cdId+"',title='"+title+"',name='"+name+"',description='"+description+"',saletype='"+saletype+"',warehouse="+warehouse+",price="+price+" , yr="+year+",saleAmount="+saleamount+" where id=?";
          prepStmt = con.prepareStatement(updateStatement);
          prepStmt.setString(1, cdId);
          prepStmt.executeUpdate();
          prepStmt.close();
          return true;
          }
          }
catch (SQLException e) 
{
return false;
}
    finally{
      closeConnection(con);
      closePrepStmt(prepStmt);
    }
 }
 
 public boolean insertCd(String cdId,String title,String name,String description,String saletype,String warehouse,String price ,String year ,String saleamount) throws Exception {


		Connection con=null;
    PreparedStatement prepStmt=null;
    con=getConnection();
    try{        
          String updateStatement =
                  "insert into cd values('"+cdId+"','"+name+"','"+title+"',"+price+","+year+",'"+description+"',"+saleamount+",'"+saletype+"',"+warehouse+")";
          prepStmt = con.prepareStatement(updateStatement);
          prepStmt.executeUpdate();
          prepStmt.close();
          return true;}
catch (SQLException e) 
{
			e.printStackTrace();
			return false;
}
    finally{
      closeConnection(con);
      closePrepStmt(prepStmt);
    }
 }
 
 
 public boolean updateUser(String username,String password,String isadmin,String tel ,String email) throws Exception {


		Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs=null;
    try{
    	con=getConnection();
      String selectStatement = "select * " + "from customer where username = ? ";
      prepStmt = con.prepareStatement(selectStatement);
      prepStmt.setString(1, username);
      rs = prepStmt.executeQuery();

      if (!rs.next()) {

          return false;
          
          }
          else
          {
          String updateStatement =
                  "update customer set username='"+username+"',password='"+password+"',isadmin="+isadmin+",tel='"+tel+"',email='"+email+"' where username=?";
          prepStmt = con.prepareStatement(updateStatement);
          prepStmt.setString(1, username);
          prepStmt.executeUpdate();
          prepStmt.close();
          return true;
          }
          }
catch (SQLException e) 
{
return false;
}
    finally{
      closeConnection(con);
      closePrepStmt(prepStmt);
    }
 }
 
 public boolean insertUser(String username,String password,String isadmin,String tel,String email) throws Exception {


		Connection con=null;
    PreparedStatement prepStmt=null;
    con=getConnection();
    try{        
          String updateStatement =
                  "insert into customer values('"+username+"','"+password+"',"+isadmin+",'"+tel+"','"+email+"')";
          prepStmt = con.prepareStatement(updateStatement);
          prepStmt.executeUpdate();
          prepStmt.close();
          return true;}
catch (SQLException e) 
{
			e.printStackTrace();
			return false;
}
    finally{
      closeConnection(con);
      closePrepStmt(prepStmt);
    }
 }
 

}