databasecon.java

外卖管理程序

package gaasbooking;

import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import javax.swing.*;

public class DatabaseCon {
	public Statement connection()
	{
		try{
			String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ="+System.getProperty("user.dir")+"\\Takeaway.mdb";
		 	//System.out.print(System.getProperty("user.dir")+"\n");
		 	con=DriverManager.getConnection(url, "", "");
		 	stmt=con.createStatement();
		}catch(Exception e){
     		e.printStackTrace();
     	}
	 	return stmt;
	}
	public void close()
	{
		try{
			stmt.close();
			con.close();
		}catch(Exception e){
	 		e.printStackTrace();
	 	}
	}
	public void updataMenu(String choose,DefaultListModel menuMod)
	{
		try{
			String sql = "select * from items where itemcode like '"+choose+"%'";
			rs = stmt.executeQuery(sql);
			menuMod.clear();
			while(rs.next())
			{
				menuMod.addElement(String.format("%-8s%-30s%-8.4s", rs.getString(2), rs.getString(3), rs.getString(4)));
			}
		}catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	public void updataTotal(int key,JLabel total,String item)
	{
		int price = 0;
		int totalPrice = 0;
		
		try{
			rs = stmt.executeQuery("select price from items where itemcode = '"+item+"'");
			rs.next();
			price = (int)((rs.getFloat(1))*100+0.5);
		}catch(Exception e)
		{
			e.printStackTrace();
		}
			switch(key){
			case 0:	
				totalPrice = (int)(Float.parseFloat(total.getText())*100+0.5)- price;
				break;
			case 1:
				totalPrice = (int)(Float.parseFloat(total.getText())*100+0.5)+ price;
				break;
			}
			total.setText(totalPrice/100+"."+totalPrice%100);		
	}
	public void insertCustomer(String phone,String address,String surName,String firstName,String postCode){
		try{
			stmt.executeUpdate("delete from Customer where Phone = '"+phone+"'");
			String sql = "insert into Customer(Phone,Address,Postcode,Surname,Firstname) values('"+phone+"','" +
					address+"','"+postCode+"','"+surName+"','"+firstName+"')";			
			stmt.executeUpdate(sql);
		}catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	public void insertOrder(String phone,String orderTime,String orderList)
	{
		try{
			String sql = "select * from Orders where CustomerPhone = '"+phone+"'";
			rs = stmt.executeQuery(sql);
			if(rs.next())
			{
				String temp = rs.getString(4);
				stmt.executeUpdate("delete from Orders where CustomerPhone = '"+phone+"'");
				stmt.executeUpdate("insert into Orders(CustomerPhone,OrderTime,OrderedItemList) values('"+phone+"','"+orderTime+"','"+orderList+","+temp+"')");
			}else
			{
				stmt.executeUpdate("insert into Orders(CustomerPhone,OrderTime,OrderedItemList) values('"+phone+"','"+orderTime+"','"+orderList+"')");
			}
		}catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	public void updataCustomer(String phone,ShopFrame mainFrame)
	{
		try{
			mainFrame.phoneNum.setText(phone);
			String sql = "select * from Customer where Phone = '" + phone + "'";
			rs = stmt.executeQuery(sql);
			if(rs.next())
			{
				mainFrame.address.setText(rs.getString(3));
				mainFrame.postCode.setText(rs.getString(4));
				mainFrame.surName.setText(rs.getString(5));
				mainFrame.firstName.setText(rs.getString(6));
			}else
			{
				mainFrame.address.setText("");
				mainFrame.surName.setText("");
				mainFrame.firstName.setText("");
				mainFrame.postCode.setText("");
			}
		}catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	private Statement stmt = null;
	private Connection con = null;
	private ResultSet rs;
}