orderdao.java

网上购物源代码

package com.chinaaccpDao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import com.chinaaccpbean.OrderMessbean;
import com.chinaaccpbean.Orderbeen;


public class Orderdao {
	public Connection getCon() {
		Connection con = null;
		try {
			String sDBDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
			Class.forName(sDBDriver);
			String sConnStr = "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=shopping";
			String name = "sa";
			 con = DriverManager.getConnection(sConnStr, name, "");
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}

	public ArrayList<Orderbeen> getAllOrder() {
		ArrayList<Orderbeen> list = new ArrayList<Orderbeen>();
		Connection con = null;
		try {
			con = this.getCon();
			Statement stmt = con.createStatement();
			String sql = "select ID,OrderListNum,UserInfoId,Orderdate,Name,IDNum,Postcode,Address,Email,Tel,Totalfee,Ispay,Issend from OrderList ";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Orderbeen order = new Orderbeen();
				order.setID(rs.getInt("ID"));
				order.setOrderListNum(rs.getString("OrderListNum"));
				order.setUserInfoId(rs.getString("UserInfoId"));
				order.setOrderdate(rs.getString("Orderdate"));
				order.setName(rs.getString("Name"));
				order.setIDNum(rs.getString("IDNum"));
				order.setPostcode(rs.getString("Postcode"));
				order.setAddress(rs.getString("Address"));
				order.setEmail(rs.getString("Email"));
				order.setTel(rs.getString("Tel"));
				order.setTotalfee(rs.getString("Totalfee"));
				order.setIspay(rs.getInt("Ispay"));
				order.setIssend(rs.getInt("Issend"));

				list.add(order);
			}
			rs.close();
			stmt.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;

	}
	public ArrayList<OrderMessbean> getOrderMessbean(int Orderid){
		ArrayList<OrderMessbean> list=new ArrayList<OrderMessbean>();
		OrderMessbean om=new OrderMessbean();
		Connection con=null;
		try {
			con=this.getCon();
			String sql="select m.id,orderid,p.name,m.quantity,m.price,m.totalfee from ordermess m inner join product p on p.id=m.productid where orderid="+Orderid+"";
			Statement st=con.createStatement();
			ResultSet rs=st.executeQuery(sql);
			while(rs.next())
			{
				
				om.setId(rs.getInt(1));
				om.setOrderid(rs.getInt(2));
				om.setProductid(rs.getString(3));
				om.setQuantity(rs.getInt(4));
				om.setPrice(rs.getDouble(5));
				om.setTotalfee(rs.getDouble(6));
				list.add(om);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public OrderMessbean getOrderMessbean(int Orderid,int id){
	
		OrderMessbean om=new OrderMessbean();
		Connection con=null;
		try {
			con=this.getCon();
			String sql="select m.id,orderid,p.name,m.quantity,m.price,m.totalfee from ordermess m inner join product p on p.id=m.productid where orderid="+Orderid+"and m.id='"+id+"'";
			Statement st=con.createStatement();
			ResultSet rs=st.executeQuery(sql);
			if(rs.next())
			{
				
				om.setId(rs.getInt(1));
				om.setOrderid(rs.getInt(2));
				om.setProductid(rs.getString(3));
				om.setQuantity(rs.getInt(4));
				om.setPrice(rs.getDouble(5));
				om.setTotalfee(rs.getDouble(6));
			
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return om;
	}

	
	
	public OrderMessbean getOrderbean(int Orderid){
		
		OrderMessbean om=new OrderMessbean();
		Connection con=null;
		try {
			con=this.getCon();
			String sql="select m.id,orderid,p.name,m.quantity,m.price,m.totalfee from ordermess m inner join product p on p.id=m.productid where orderid="+Orderid+"'";
			Statement st=con.createStatement();
			ResultSet rs=st.executeQuery(sql);
			if(rs.next())
			{
				
				om.setId(rs.getInt(1));
				om.setOrderid(rs.getInt(2));
				om.setProductid(rs.getString(3));
				om.setQuantity(rs.getInt(4));
				om.setPrice(rs.getDouble(5));
				om.setTotalfee(rs.getDouble(6));
			
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return om;
	}
	
	
	
	public ArrayList<OrderMessbean> getAllOrderMessbean(){
		ArrayList<OrderMessbean> list=new ArrayList<OrderMessbean>();
		
		Connection con=null;
		try {
			con=this.getCon();
			String sql="select m.id,orderid,p.name,m.quantity,m.price,m.totalfee,o.ispay,o.issend from ordermess m inner join product p on p.id=m.productid inner join Orderlist o on m.orderid=o.id";
			Statement st=con.createStatement();
			ResultSet rs=st.executeQuery(sql);
			while(rs.next())
			{
				OrderMessbean om=new OrderMessbean();
				om.setId(rs.getInt(1));
				om.setOrderid(rs.getInt(2));
				om.setProductid(rs.getString(3));
				om.setQuantity(rs.getInt(4));
				om.setPrice(rs.getDouble(5));
				om.setTotalfee(rs.getDouble(6));
				om.setIspay(rs.getInt(7));
				om.setIssend(rs.getInt(8));
				
				list.add(om);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	
	
	
	
	public ArrayList<OrderMessbean> getAllOrderMessbean(int id){
		ArrayList<OrderMessbean> list=new ArrayList<OrderMessbean>();
		
		Connection con=null;
		try {
			con=this.getCon();
			String sql="select m.id,orderid,p.name,m.quantity,m.price,m.totalfee,o.ispay,o.issend from ordermess m inner join product p on p.id=m.productid inner join Orderlist o on m.orderid=o.id where m.Orderid='"+id+"'";
			Statement st=con.createStatement();
			ResultSet rs=st.executeQuery(sql);
			while(rs.next())
			{
				OrderMessbean om=new OrderMessbean();
				om.setId(rs.getInt(1));
				om.setOrderid(rs.getInt(2));
				om.setProductid(rs.getString(3));
				om.setQuantity(rs.getInt(4));
				om.setPrice(rs.getDouble(5));
				om.setTotalfee(rs.getDouble(6));
				om.setIspay(rs.getInt(7));
				om.setIssend(rs.getInt(8));
				
				list.add(om);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	
	public Orderbeen OrderLNum(int ID) {
		Orderbeen Ob = new Orderbeen();
		Connection con = null;
		try {
			con=this.getCon();
			String sql="select ID,OrderListNum,UserInfoId,Orderdate,Name,IDNum,Postcode,Address,Email,Tel,Totalfee,Ispay,Issend from OrderList where ID='"+ID+"'";
			Statement st=con.createStatement();
			ResultSet rs=st.executeQuery(sql);
			if(rs.next())
			{
				Ob.setID(rs.getInt(1));
				Ob.setOrderListNum(rs.getString(2));
				Ob.setUserInfoId(rs.getString(3));
				Ob.setOrderdate(rs.getString(4));
				Ob.setName(rs.getString(5));
				Ob.setIDNum(rs.getString(6));
				Ob.setPostcode(rs.getString(7));
				Ob.setAddress(rs.getString(8));
				Ob.setEmail(rs.getString(9));
				Ob.setTel(rs.getString(10));
				Ob.setTotalfee(rs.getString(11));
				Ob.setIspay(rs.getInt(12));
				Ob.setIssend(rs.getInt(13));
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return Ob;
	}
	public boolean setUpdateOrder(int id, int ispay,int issend )
	{
		Connection con=null;
		try {
			con=this.getCon();
			Statement stmt=con.createStatement();
			String sql="update OrderList set Ispay='"+ispay+"',Issend='"+issend+"'where ID='"+id+"'";
			int rs=stmt.executeUpdate(sql);
			stmt.close();
			if(rs==1)
				return true;
			else
				return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	public boolean setUpdateOrder(int id,int shul,double danj,double zongj)
	{
		Connection con=null;
		try {
			con=this.getCon();
			Statement stmt=con.createStatement();
			String sql="update OrderMess set quantity="+shul+",price="+danj+",totalfee="+zongj+" where ID='"+id+"'";
			int rs=stmt.executeUpdate(sql);
			stmt.close();
			if(rs==1)
				return true;
			else
				return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	public boolean deleteOrder(int id)
	{
		boolean bool=false;
		Connection con=null;
		try {
			con=this.getCon();
			Statement stmt=con.createStatement();
			String sql="delete from OrderMess where Orderid='"+id+"'";
			int rs=stmt.executeUpdate(sql);		
			if(rs>=1)
			{
				String sql2="delete from OrderList where ID='"+id+"'";
				int rs2=stmt.executeUpdate(sql2);
				if(rs2>=1)
					bool=true;
			}
			else
				bool=false;
		} catch (Exception e) {
			e.printStackTrace();
			return bool;
		}
		return bool;
	}
}