dboperate.java

网上商城一个购物网站具有登陆会员注册添加购物车

		}
		
		return list;
	}
	
	public Order getOrder(String orderno){
		Order order = null;
		Connection conn = DBConn.getConnection();
		Statement stmt = null;
		String sql = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			sql = "select * from Orders where orderno='"+orderno+"'";
			rs = stmt.executeQuery(sql);
			
			if (rs.next()) {
				order = new Order();
				order.setAddress(rs.getString("address"));
				order.setEmail(rs.getString("email"));
				order.setId(rs.getInt("id"));
				order.setMemo(clobToString(rs.getClob("memo")));
				order.setOrderno(rs.getString("orderno"));
				order.setPayment(rs.getString("payment"));
				order.setPrice(rs.getDouble("price"));
				order.setRealname(rs.getString("realname"));
				order.setTag(rs.getInt("tag"));
				order.setTel(rs.getString("tel"));
				order.setTime(rs.getString("time"));
				order.setUserid(rs.getInt("userid"));
				order.setZip(rs.getString("zip"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs!=null){
					rs.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			try {
				if (stmt!=null){
					stmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			try {
				if (conn!=null&&!conn.isClosed()){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		
		return order;
	}
	
	public List getOrders(int  userid){
		Order order = null;
		List list = null;
		Connection conn = DBConn.getConnection();
		Statement stmt = null;
		String sql = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			sql = "select * from Orders where userid=" + userid;
			rs = stmt.executeQuery(sql);
			list = new ArrayList();
			while (rs.next()) {
				order = new Order();
				order.setAddress(rs.getString("address"));
				order.setEmail(rs.getString("email"));
				order.setId(rs.getInt("id"));
				order.setMemo(clobToString(rs.getClob("memo")));
				order.setOrderno(rs.getString("orderno"));
				order.setPayment(rs.getString("payment"));
				order.setPrice(rs.getDouble("price"));
				order.setRealname(rs.getString("realname"));
				order.setTag(rs.getInt("tag"));
				order.setTel(rs.getString("tel"));
				order.setTime(rs.getString("time"));
				order.setUserid(rs.getInt("userid"));
				order.setZip(rs.getString("zip"));
				list.add(order);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs!=null){
					rs.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			try {
				if (stmt!=null){
					stmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			try {
				if (conn!=null&&!conn.isClosed()){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
		}
		
		return list;
	}
	
	public void save(Object obj){
		String sql = "";
		Connection conn = DBConn.getConnection();
		PreparedStatement stmt = null;
		System.out.println(obj.getClass().getName());
		try {
			if ("hit.bean.Member".equals(obj.getClass().getName())){
				Member member = (Member)obj;
				String username = member.getUsername();
				String psw = member.getPassword();
				String realname = member.getRealname();
				String tel = member.getTel();
				String address = member.getAddress();
				String zip = member.getZip();
				String email = member.getEmail();			
				sql = "insert into member(username,password,realname,tel,address,zip,email) values(?,?,?,?,?,?,?)";
				
				stmt = conn.prepareStatement(sql);
				stmt.setString(1,username);
				stmt.setString(2,psw);
				stmt.setString(3,realname);
				stmt.setString(4,tel);
				stmt.setString(5,address);
				stmt.setString(6,zip);
				stmt.setString(7,email);
				stmt.executeUpdate();				
			} else if ("hit.bean.Order".equals(obj.getClass().getName())) {
				Order order = (Order)obj;
				sql = "insert into orders(orderno,userid,realname,address,zip,tel,payment,email,memo,price,time,tag) values(?,?,?,?,?,?,?,?,?,?,?,?)";
				stmt = conn.prepareStatement(sql);
				stmt.setString(1,order.getOrderno());
				stmt.setInt(2,order.getUserid());
				stmt.setString(3,order.getRealname());
				stmt.setString(4,order.getAddress());
				stmt.setString(5,order.getZip());
				stmt.setString(6,order.getTel());
				stmt.setString(7,order.getPayment());
				stmt.setString(8,order.getEmail());
				stmt.setString(9,order.getMemo());
				stmt.setDouble(10,order.getPrice());
				stmt.setString(11,order.getTime());
				stmt.setInt(12,order.getTag());
				stmt.executeUpdate();
			} else if ("hit.bean.ShopCart".equals(obj.getClass().getName())) {
				ShopCart shopcart = (ShopCart)obj;
				sql = "insert into shopcart(orderid,productid,count,price) values(" +
						shopcart.getOrderid()+"," +
								shopcart.getProduct().getId()+"," +
										shopcart.getCount()+","+shopcart.getPrice()+")";
				System.out.println(sql);
				Statement stmt1 = conn.createStatement();
				System.out.println(stmt1.executeUpdate(sql));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {		
			try {
				if (stmt!=null){
					stmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			try {
				if (conn!=null&&!conn.isClosed()){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
		}
	}
	
	public void update(Object obj){
		String sql = "";
		Connection conn = DBConn.getConnection();
		PreparedStatement stmt = null;
		System.out.println(obj.getClass().getName());
		try {
			if ("hit.bean.Product".equals(obj.getClass().getName())){
				Product product = (Product)obj;
				sql = "update product set sortid=?,name=?,price=?,saleprice=?,descript=?,contents=?," +
						"saledate=date_format(?,'%Y-%m-%d'),salecount=?,image=? where id=?";
				stmt = conn.prepareStatement(sql);
				System.out.println(product.getSortid());
				stmt.setInt(1,product.getSortid());
				stmt.setString(2,product.getName());
				stmt.setDouble(3,product.getPrice());
				stmt.setDouble(4,product.getSaleprice());
				stmt.setString(5,product.getDescript());
				stmt.setString(6,product.getContents());
				System.out.println(product.getSaledate());
				System.out.println(formatDate(product.getSaledate()));
				stmt.setString(7,formatDate(product.getSaledate()));
				stmt.setInt(8,product.getSalecount());
				stmt.setString(9,product.getImage());
				stmt.setInt(10,product.getId());
				stmt.executeUpdate();				
			} else if ("hit.bean.Order".equals(obj.getClass().getName())){
				Order order = (Order)obj;
				sql = "update Orders set orderno=?,userid=?,realname=?,address=?," +
						"zip=?,tel=?,payment=?,email=?,memo=?,price=?,time=?,tag=? " +
						"where id=?";
				stmt = conn.prepareStatement(sql);
				stmt.setString(1,order.getOrderno());
				stmt.setInt(2,order.getUserid());
				stmt.setString(3,order.getRealname());
				stmt.setString(4,order.getAddress());
				stmt.setString(5,order.getZip());
				stmt.setString(6,order.getTel());
				stmt.setString(7,order.getPayment());
				stmt.setString(8,order.getEmail());
				stmt.setString(9,order.getMemo());
				stmt.setDouble(10,order.getPrice());
				stmt.setString(11,order.getTime());
				stmt.setInt(12,order.getTag());
				stmt.setInt(13,order.getId());
				stmt.executeUpdate();				
			} else if ("hit.bean.Member".equals(obj.getClass().getName())){
				Member member = (Member)obj;
				String username = member.getUsername();
				String psw = member.getPassword();
				String realname = member.getRealname();
				String tel = member.getTel();
				String address = member.getAddress();
				String zip = member.getZip();
				String email = member.getEmail();			
				sql = "update member set username=?,password=?,realname=?,tel=?,address=?," +
						"zip=?,email=? where id=?";
				
				stmt = conn.prepareStatement(sql);
				stmt.setString(1,username);
				stmt.setString(2,psw);
				stmt.setString(3,realname);
				stmt.setString(4,tel);
				stmt.setString(5,address);
				stmt.setString(6,zip);
				stmt.setString(7,email);
				stmt.setInt(8,member.getId());
				stmt.executeUpdate();
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {		
			try {
				if (stmt!=null){
					stmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			try {
				if (conn!=null&&!conn.isClosed()){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
		}
	}
	
	public void delete(Object obj){
		String sql = "";
		Connection conn = DBConn.getConnection();
		PreparedStatement stmt = null;
		System.out.println(obj.getClass().getName());
		try{
		if ("hit.bean.Order".equals(obj.getClass().getName())){
			Order order = (Order)obj;		
			sql = "delete from shopcart where orderid=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1,order.getId());
			stmt.executeUpdate();
			sql = "delete from orders where id=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1,order.getId());
			stmt.executeUpdate();
		}
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {		
			try {
				if (stmt!=null){
					stmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			try {
				if (conn!=null&&!conn.isClosed()){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
		}
	}
	
}