registerdao.java

这个网上商城系统项目是我之前发过类似的一个大的整合

		Book book = new Book();
		String sql = "select * from book where id=?";
		try {
			conn = DriverManager.getConnection(url, "sa", "sa");
			pst = conn.prepareStatement(sql);
			pst.setInt(1, userID);
			rs = pst.executeQuery();
			while(rs.next()){
				book.setId(rs.getInt("id"));
				book.setTypeid(rs.getInt("typeid"));
				book.setName(rs.getString("name"));
				book.setPrice(rs.getFloat("price"));
				book.setSaleprice(rs.getFloat("saleprice"));
				book.setDescript(rs.getString("descript"));
				book.setContents(rs.getString("contents"));
				book.setFileName(rs.getString("fileName"));
				book.setLeavetime(rs.getString("leavetime"));
				book.setStorage(rs.getInt("storage"));
				book.setProvider(rs.getString("provider"));
				book.setMarque(rs.getString("marque"));
				book.setUnit(rs.getString("unit"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return book;
	}
	
	/*
	 * 通过ID查找出某个会员的详细信息
	 */
	public Member QueryMember(int userID) {
		Member member = new Member();
		String sql = "select * from register where id=?";
		try {
			conn = DriverManager.getConnection(url, "sa", "sa");
			pst = conn.prepareStatement(sql);
			pst.setInt(1, userID);
			rs = pst.executeQuery();
			while(rs.next()){
				member.setId(rs.getInt("id"));
				member.setUserName(rs.getString("userName"));
				member.setName(rs.getString("name"));
				member.setSex(rs.getString("sex"));
				member.setPassWord(rs.getString("passWord"));
				member.setCard(rs.getString("card"));
				member.setEmail(rs.getString("email"));
				member.setAddress(rs.getString("address"));
				member.setRegtime(rs.getString("regtime"));
				member.setState(rs.getInt("state"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return member;
	}
	/*
	 * 显示所有的会员信息
	 */
	public ArrayList<Member> ListAllMemberInformation(String sql) {
		ArrayList<Member> arrayList = new ArrayList<Member>();
		try {
			conn = DriverManager.getConnection(url, "sa", "sa");
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				Member member = new Member();
				member.setId(rs.getInt("id"));
				member.setUserName(rs.getString("userName"));
				member.setName(rs.getString("Name"));
				member.setSex(rs.getString("sex"));
				member.setPassWord(rs.getString("passWord"));
				member.setCard(rs.getString("card"));
				member.setEmail(rs.getString("Email"));
				member.setAddress(rs.getString("address"));
				member.setRegtime(rs.getString("regtime"));
				member.setState(rs.getInt("state"));
				arrayList.add(member);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return arrayList;
	}
	/*
	 * 删除某本书之前先判断是否有定单存在
	 */
	public int CheckID(String sql){
		int n = 0;
		try {
			conn = DriverManager.getConnection(url, "sa", "sa");
			pst =  conn.prepareStatement(sql);
			rs = pst.executeQuery();
			if(rs.next()){
				n = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			try {
				if(rs != null){
					rs.close();
				}
				if(pst != null){
					pst.close();
				}
				if(conn != null){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		
		
		}
		return n;
	}
	
	/*
	 * 显示所有定单的信息
	 */
	public ArrayList<Orders> ListAllOrdersInformation(String sql) {
		ArrayList<Orders> arrayList = new ArrayList<Orders>();
		try {
			conn = DriverManager.getConnection(url, "sa", "sa");
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				Orders orders = new Orders();
				orders.setId(rs.getInt("id"));
				orders.setUserID(rs.getInt("userID"));
				orders.setDatatime(rs.getString("datatime"));
				orders.setSaveName(rs.getString("saveName"));
				orders.setSavePhone(rs.getString("savePhone"));
				orders.setSaveAddress(rs.getString("saveAddress"));
				orders.setZip(rs.getString("zip"));
				orders.setDeliverymode(rs.getInt("deliverymode"));
				orders.setMoneymode(rs.getInt("moneymode"));
				orders.setState(rs.getInt("state"));
				arrayList.add(orders);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return arrayList;
	}
	
	/*
	 * 通过名字查找出人的全部信息
	 */
	public Member ToModRegister(String userName) {
		Member member = new Member();
		String sql = "select * from register where userName=?";
		try {
			conn = DriverManager.getConnection(url, "sa", "sa");
			pst = conn.prepareStatement(sql);
			pst.setString(1, userName);
			rs = pst.executeQuery();
			while(rs.next()){
				member.setId(rs.getInt("id"));
				member.setUserName(rs.getString("userName"));
				member.setName(rs.getString("name"));
				member.setSex(rs.getString("sex"));
				member.setPassWord(rs.getString("passWord"));
				member.setCard(rs.getString("card"));
				member.setEmail(rs.getString("email"));
				member.setAddress(rs.getString("address"));
				member.setRegtime(rs.getString("regtime"));
				member.setState(rs.getInt("state"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return member;
	}
	
	/*
	 * 显示所有对本书的评论
	 */
	public ArrayList<Comment> ListAllCommentInformation(String sql) {
		ArrayList<Comment> arrayList = new ArrayList<Comment>();
		try {
			conn = DriverManager.getConnection(url, "sa", "sa");
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				Comment comment = new Comment();
				comment.setId(rs.getInt("id"));
				comment.setUserName(rs.getString("userName"));
				comment.setBookid(rs.getInt("bookid"));
				comment.setDatatime(rs.getString("datatime"));
				comment.setContents(rs.getString("contents"));
				arrayList.add(comment);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return arrayList;
	}
	
	/*
	 * 通过自己定义的字段查找出能匹配的个数
	 */
	public int CheckOrder(String sql) {
		int n = 0;
		try {
			conn = DriverManager.getConnection(url, "sa", "sa");
			pst = conn.prepareStatement(sql);
			rs = pst.executeQuery();
			if(rs.next()){
				n = rs.getInt("temp");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return n;
	}
	/*
	 * 将取出的定单表的数据放到明晰表中
	 */
	public int insetorder(OrderDetail orderDetail) {
		int n = 0;
		String sql="insert into orderdetail values(?,?,?,?)";
		try {
			conn = DriverManager.getConnection(url, "sa", "sa");
			pst = conn.prepareStatement(sql);
			pst.setInt(1, orderDetail.getBookid());
			pst.setInt(2, orderDetail.getOrderid());
			pst.setInt(3, orderDetail.getNumber());
			pst.setFloat(4, orderDetail.getTotalprice());
			n  = pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return n;
	}
	
	/*
	 * 返回价格
	 */
	public float CheckPrice(String sql) {
		float n = 0;
		try {
			conn = DriverManager.getConnection(url, "sa", "sa");
			pst = conn.prepareStatement(sql);
			rs = pst.executeQuery();
			if(rs.next()){
				n = rs.getInt("temp");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return n;
	}
	
	public ArrayList<OrderBean> ListAllOrderInformation(String sql) {
		ArrayList<OrderBean> arrayList = new ArrayList<OrderBean>();
		try {
			conn = DriverManager.getConnection(url, "sa", "sa");
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				OrderBean book = new OrderBean();
				book.setId(rs.getInt("id"));
				book.setDatatime(rs.getString("datatime"));
				book.setState(rs.getInt("state"));
				book.setTotalprice(rs.getFloat("totalprice"));
				arrayList.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {