JSP SQL SERVER 网上书店

package shop; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Vector; public class shop { /** * 根据用户名得到会员对象 */ public Member getMember(DB db,String username) throws Exception { Member member = null; ResultSet rs; String strSql=null; try { strSql="select * from member where username="+username; rs = db.executeQuery(strSql); while (rs.next()){ 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.setZip(rs.getString("zip")); member.setEmail(rs.getString("email")); } } catch (SQLException e) { // TODO 自动生成 catch 块 e.printStackTrace(); } return member; } /** * 根据商品ID得到商品对象 */ public Product getProduct(DB db ,int Id) throws Exception { Product product = null; ResultSet rs; String strSql=null; try { //connect = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "zgy01"); //Statement stmt = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); strSql="select * from product where id="+Id; rs = db.executeQuery(strSql); while(rs.next()){ product.setId(rs.getInt("id")); product.setSortid(rs.getInt("sortid")); product.setPrice(rs.getDouble("price")); product.setName(rs.getString("name")); product.setSaleprice(rs.getDouble("saleprice")); product.setDescript(rs.getString("descript")); product.setContents(rs.getString("contents")); product.setSaledate(rs.getString("saledate")); product.setSalecount(rs.getInt("salecount")); product.setImage(rs.getString("image")); } } catch (SQLException e) { // TODO 自动生成 catch 块 e.printStackTrace(); } return product; } /** * 得到指定数量的顶端商品对象����Ķ�����Ʒ���� */ public Vector getTopProducts(DB db,int type) throws Exception { Vector vector = new Vector(); ResultSet rs; String strSql=null; int dispNUm; try { if (type ==1) { strSql="select * from product order by saledate desc"; dispNUm=4; } else{ strSql="select * from product order by salecount desc"; dispNUm=10; } //connect = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "zgy01"); //Statement stmt = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); rs = db.executeQuery(strSql); rs.setFetchSize(dispNUm);//有问题存在呀，不懂要问的。 while(rs.next()){ Product product = new Product(); product.setId(rs.getInt("id")); product.setSortid(rs.getInt("sortid")); product.setPrice(rs.getDouble("price")); product.setName(rs.getString("name")); product.setSaleprice(rs.getDouble("saleprice")); product.setDescript(rs.getString("descript")); product.setContents(rs.getString("contents")); product.setSaledate(rs.getString("saledate")); product.setSalecount(rs.getInt("salecount")); product.setImage(rs.getString("image")); vector.add(product); } } catch (SQLException e) { e.printStackTrace(); } return vector; } /** * 得到所有符合条件商品对象 */ public Vector getMatchProducts(DB db,int sortId,String keyword) throws Exception { Vector vector=new Vector(); ResultSet rs; String strSql=null; try { if (sortId ==-1) strSql="select * from product where name like '%" + keyword + "%' "; else strSql="select * from product where sortid='" + sortId +"'"; rs = db.executeQuery(strSql); while(rs.next()){ Product product = new Product(); product.setId(rs.getInt("id")); product.setSortid(rs.getInt("sortid")); product.setPrice(rs.getDouble("price")); product.setName(rs.getString("name")); product.setSaleprice(rs.getDouble("saleprice")); product.setDescript(rs.getString("descript")); product.setContents(rs.getString("contents")); product.setSaledate(rs.getString("saledate")); product.setSalecount(rs.getInt("salecount")); product.setImage(rs.getString("image")); vector.add(product); } } catch (SQLException e) { e.printStackTrace(); } return vector; } /** * 得到所有商品类别对象 */ public Vector getSorts(DB db) throws Exception { Vector vector=new Vector(); ResultSet rs; String strSql=null; try { strSql="select * from sort"; rs = db.executeQuery(strSql); while(rs.next()){ Sort sort=new Sort(); sort.setId(rs.getInt("id")); sort.setName(rs.getString("name")); vector.add(sort); } } catch (SQLException e) { e.printStackTrace(); } return vector; } /** *根据编号得到订单对象 */ public Order getOrder(DB db,String orderno) throws Exception { Order order = null; ResultSet rs; String strSql=null; try { strSql="select * from order where orderno="+orderno; rs = db.executeQuery(strSql); while(rs.next()){ order.setId(rs.getInt("id")); order.setOrderno(rs.getString("orderno")); order.setUserid(rs.getInt("userid")); order.setRealname(rs.getString("realname")); order.setAddress(rs.getString("address")); order.setEmail(rs.getString("email")); order.setMemo(rs.getString("memo")); order.setPayment(rs.getString("payment")); order.setPrice(rs.getDouble("price")); order.setTag(rs.getInt("tag")); order.setTel(rs.getString("tel")); order.setTime(rs.getString("time")); order.setZip(rs.getString("zip")); } } catch (SQLException e) { e.printStackTrace(); } return order; } /** * 得到指定用户所有订单对象 */ public Vector getOrders(DB db,int userid) throws Exception { Vector vector=new Vector(); ResultSet rs; String strSql=null; try { strSql="select * from from order where userid=" + userid; rs = db.executeQuery(strSql); while(rs.next()){ Order order=new Order(); order.setId(rs.getInt("id")); order.setOrderno(rs.getString("orderno")); order.setUserid(rs.getInt("userid")); order.setRealname(rs.getString("realname")); order.setAddress(rs.getString("address")); order.setEmail(rs.getString("email")); order.setMemo(rs.getString("memo")); order.setPayment(rs.getString("payment")); order.setPrice(rs.getDouble("price")); order.setTag(rs.getInt("tag")); order.setTel(rs.getString("tel")); order.setTime(rs.getString("time")); order.setZip(rs.getString("zip")); vector.add(vector); } } catch (SQLException e) { e.printStackTrace(); } return vector; } /** * 修改实体对象所对应的记录 */