dboperate.java

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

		}
 		return list; 
	}
	
	public List getMatchSorts(String keyword) {
		Sort sort = null;
		Connection conn = DBConn.getConnection();
		Statement stmt = null;
		String sql = null;
		ResultSet rs = null;
		List list = null;
		try {
			stmt = conn.createStatement();
			sql = "select * from Sort where name like '%"  +  keyword + "%' ";
			rs = stmt.executeQuery(sql);
			list = new ArrayList();
			while (rs.next()) {
				sort = new Sort();
				sort.setId(rs.getInt("id"));
				sort.setName(rs.getString("name"));
				list.add(sort);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return list; 
	}
	
	public List getMatchMembers(String keyword) {

		String sql = "select * from Member where username like '%"  +  keyword + "%' ";
		List list=null;
		Member member = null;
		Connection conn = DBConn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			list = new ArrayList();
			while (rs.next()) {
				member = new Member();
				member.setId(rs.getInt("id"));
				member.setUsername(rs.getString("username"));
				member.setPassword(rs.getString("password"));
				member.setRealname(rs.getString("realname"));
				member.setTel(rs.getString("tel"));
				member.setAddress(rs.getString("address"));
				member.setEmail(rs.getString("email"));
				member.setZip(rs.getString("zip"));
				list.add(member);
			}
		} 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 List getList(String strSql){
		List list=null;
	
		return list;
	}

	public Order getOrder(int orderId) {
		String sql = "select * from Orders where id="+orderId;
		Order order = null;
		Connection conn = DBConn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			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 void save(Object obj) {
		String sql = "";
		Connection conn = DBConn.getConnection();
		PreparedStatement stmt = null;
		
		try {
			if ("com.hit.bean.Product".equals(obj.getClass().getName())){
				Product product = (Product)obj;
				sql = "insert into product(sortid,name,price,saleprice,descript,contents,saledate,salecount,image)" +
						" values(?,?,?,?,?,?,date_format(?,'%Y-%m-%d'),?,?)";
				stmt = conn.prepareStatement(sql);
				System.out.println(product.getSort().getId());
				stmt.setInt(1,product.getSort().getId());
				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());
				stmt.setString(7,product.getSaledate());
				stmt.setInt(8,product.getSalecount());
				stmt.setString(9,product.getImage());
				
				stmt.executeUpdate();				
			} else if ("com.hit.bean.Sort".equals(obj.getClass().getName())){
				Sort sort = (Sort)obj;
				sql = "insert into sort(name) values(?)";
				stmt = conn.prepareStatement(sql);
				stmt.setString(1,sort.getName());
				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 update(Object obj) {
		String sql = "";
		Connection conn = DBConn.getConnection();
		PreparedStatement stmt = null;
		
		try {
			if ("com.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.getSort().getId());
				stmt.setInt(1,product.getSort().getId());
				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());
				stmt.setString(7,product.getSaledate());
				stmt.setInt(8,product.getSalecount());
				stmt.setString(9,product.getImage());
				stmt.setInt(10,product.getId());
				stmt.executeUpdate();				
			} else if ("com.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();				
			}
		} 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 ("com.hit.bean.Product".equals(obj.getClass().getName())){
				Product product = (Product)obj;
				sql = "delete from product where id=?";
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1,product.getId());
				stmt.executeUpdate();
			} else if ("com.hit.bean.Member".equals(obj.getClass().getName())){
				Member member = (Member)obj;
				sql = "delete from member where id=?";
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1,member.getId());
				stmt.executeUpdate();
			} else if ("com.hit.bean.Sort".equals(obj.getClass().getName())){
				Sort sort = (Sort)obj;
				sql = "delete from Sort where id=?";
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1,sort.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 saveOrUpdate(Object obj) {
	
	}
}