eatery.java

网上虚拟社区

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Eatery{
	private DBOperator dboperator;
	
	public Eatery(){
		dboperator = new DBOperator("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:VCDB",null,null);
	}
	public synchronized void adddish(String name,Double price){
		try{
		    Connection conn = dboperator.getConnection();
			PreparedStatement stat = conn.prepareStatement("SELECT * FROM menu WHERE dish = ?");
			stat.setString(1, name);
			ResultSet result = stat.executeQuery();
			if(result.next()){
				System.out.println("The Dish has already exist!");
			}
			else{
				String query = "insert into menu(dish, price) values ( ?, ?)";
				stat =conn.prepareStatement(query);
				stat.setString(1, name);	
				stat.setDouble(2, price);	
				stat.executeUpdate();
			}
			conn.close();
			addbusiness("add",price,"Eatery",name);
		}
		catch(SQLException ex){
			System.out.println(ex);
		}
	}
	
	public synchronized void orderdish(String name,String dish){
		try{
		    Connection conn = dboperator.getConnection();
			PreparedStatement stat = conn.prepareStatement("SELECT * FROM menu WHERE dish = ?");
			stat.setString(1, dish);
			ResultSet result = stat.executeQuery();
			if(!result.next()){
				System.out.println("The Dish are not exist!");
			}
			else{
				String query = "UPDATE menu"
					+ " SET totalnum = totalnum + 1"
					+ " WHERE dish = ?";
				stat =conn.prepareStatement(query);
				stat.setString(1, dish);	
				stat.executeUpdate();
				stat =conn.prepareStatement("UPDATE menu"
						+ " SET currentnum = currentnum + 1"
						+ " WHERE dish = ?");
				stat.setString(1, dish);	
				stat.executeUpdate();
			}
			double temp = result.getDouble("price");
			conn.close();
			addwork(dish,name,temp);
			addbusiness("order",temp,name,dish);
		}
		catch(SQLException ex){
			System.out.println(ex);
		}
	}
	
	public synchronized double settle(String name){
		try{
		    Connection conn = dboperator.getConnection();
			PreparedStatement stat = conn.prepareStatement("SELECT * FROM Eatery WHERE guest = ?");
			stat.setString(1, name);
			ResultSet result = stat.executeQuery();
			double balance=0;
			while(result.next()){
				balance += result.getDouble("price");
				String query = "UPDATE menu"
					+ " SET currentnum = currentnum - 1"
					+ " WHERE dish = ?";
				stat =conn.prepareStatement(query);
				stat.setString(1, result.getString("dish"));
				stat.executeUpdate();
				addbusiness("settle",result.getDouble("price"),name,result.getString("dish"));
				stat =conn.prepareStatement("UPDATE Eatery"
						+ " SET settled = 1"
						+ " WHERE ID = ?");
				stat.setDouble(1, result.getDouble("ID"));	
				stat.executeUpdate();
			}
//	        refreshResultSet();
			conn.close();
			return balance;
		}
		catch(SQLException ex){
			System.out.println(ex);
			return 0;
		}
	}
	
	  public  synchronized void delete(String name) {
		    try {
		      // Delete the record from the database
			    Connection conn = dboperator.getConnection();
				PreparedStatement stat = conn.prepareStatement("SELECT * FROM menu WHERE dish = ?");
				stat.setString(1, name);
				ResultSet result = stat.executeQuery();
		        result.deleteRow();
				conn.close();
				addbusiness("delete",0,"Eatery",name);
//		        refreshResultSet();

		      // Remove the row in the table
//		      tableModel.removeRow(
//		        listSelectionModel.getLeadSelectionIndex());
		    }
		    catch (SQLException ex) {
//		      jlblStatus.setText(ex.toString());
		    	System.out.println(ex);
		    }
		  }
	
	protected synchronized void addbusiness(String operator,double money,String name,String dish){
		try{
		    Connection conn = dboperator.getConnection();
		    String query = "insert into business values(?,?,?,?)";
			PreparedStatement stat = conn.prepareStatement(query);
			stat.setString(1, name);	
			stat.setString(2, dish);
			stat.setDouble(3, money);	
			stat.setString(4,operator);
			stat.executeUpdate();
			conn.close();
		}
		catch(SQLException ex){
			System.out.println(ex);
		}	
	}
	public synchronized void addwork(String dish,String name,Double price){
		try{
		    Connection conn = dboperator.getConnection();
			PreparedStatement stat = conn.prepareStatement("insert into Eatery(dish, guest, price) values ( ?, ?,?)");
			stat.setString(1, dish);	
			stat.setString(2, name);
			stat.setDouble(3, price);	
			stat.executeUpdate();
			conn.close();
		}
		catch(SQLException ex){
			System.out.println(ex);
		}
	}
}