itemservice.java

使用jsp+Servlet,采用MVC模式,实现了一典型小型电子商务网站的全过程.包括前台和后台的全部功能.适合于初学者学习使用.

/**
 * 
 */
package com.eshop.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.eshop.util.DBManager;
import com.eshop.vo.Item;
import com.eshop.vo.Product;

/**
 * 访问商品条目的数据访问对象
 * @author jonson
 *
 */
public class ItemService {
	
	String selectItemById="select * from item where itemId=?";//根据ITEMID查询商品的SQL语句
	String selectItemByProductId="select * from item where productId=?";//根据产品ID查询商品信息的SQL语句
	
	
	/**
	 * 根据itemId,查询商品信息
	 * @param id(itemid)
	 * @return
	 */
	 public Item getItemById(String id){
			Item item=null;
			DBManager dbManager=new DBManager();
			Connection conn=dbManager.GetConnection();
			PreparedStatement ps;
			try {
				ps = conn.prepareStatement(selectItemById);
				ps.setString(1, id);
				ResultSet rs=ps.executeQuery();
			while(rs.next()){
			 item=new Item();
				item.setItemid(rs.getString("itemid"));
				item.setProductid(rs.getString("productid"));
				item.setListprice(rs.getFloat("listprice"));
				item.setUnitcost(rs.getFloat("unitcost"));
				item.setSupplier(rs.getInt("supplier"));
				item.setStatus(rs.getString("status"));
				item.setAttr1(rs.getString("attr1"));
				item.setAttr2(rs.getString("attr2"));
				item.setAttr3(rs.getString("attr3"));
				item.setAttr4(rs.getString("attr4"));
				item.setAttr5(rs.getString("attr5"));
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return item;		
		
	}
	 public List getItemByProductId(String productId){
			Item item=null;
			List list=new ArrayList();
			DBManager dbManager=new DBManager();
			Connection conn=dbManager.GetConnection();
			PreparedStatement ps;
			try {
				ps = conn.prepareStatement(selectItemByProductId);
				ps.setString(1, productId);
				ResultSet rs=ps.executeQuery();
				while(rs.next()){
					 item=new Item();
						item.setItemid(rs.getString("itemid"));
						item.setProductid(rs.getString("productid"));
						item.setListprice(rs.getFloat("listprice"));
						item.setUnitcost(rs.getFloat("unitcost"));
						item.setSupplier(rs.getInt("supplier"));
						item.setStatus(rs.getString("status"));
						item.setAttr1(rs.getString("attr1"));
						item.setAttr2(rs.getString("attr2"));
						item.setAttr3(rs.getString("attr3"));
						item.setAttr4(rs.getString("attr4"));
						item.setAttr5(rs.getString("attr5"));
						list.add(item);
					}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
			return list;
			 
		 }

	 public List getAllItem(){
			List list=new ArrayList();
			DBManager dbManager= new DBManager();
			Connection conn=dbManager.GetConnection();
			try {
				PreparedStatement ps=conn.prepareStatement("select * from item");
				ResultSet rs=ps.executeQuery();
				while (rs.next()){
					Item item=new Item();
					item.setItemid(rs.getString("itemid"));
					item.setProductid(rs.getString("productid"));
					item.setListprice(rs.getFloat("listprice"));
					item.setUnitcost(rs.getFloat("unitcost"));
					item.setSupplier(rs.getInt("listprice"));
					item.setStatus(rs.getString("status"));
					item.setAttr1(rs.getString("attr1"));
					item.setAttr2(rs.getString("attr2"));
					item.setAttr3(rs.getString("attr3"));
					item.setAttr4(rs.getString("attr4"));
					item.setAttr5(rs.getString("attr5"));
					list.add(item);
				}
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return list;
		}
	 /**
	  * 保存类
	  * @param product
	  * @return
	  */
	 public boolean saveItem(Item item){
	 	boolean success=false;
	 	DBManager DbManager= new DBManager();
	 	Connection conn=DbManager.GetConnection();
	 	try {
//	 		Statement stm=conn.createStatement();
	 		PreparedStatement ps=conn.prepareStatement("INSERT INTO item(itemid,productid,listprice,unitcost,supplier,status,attr1,attr2,attr3,attr4,attr5)values(?,?,?,?,?,?,?,?,?,?,?)");
	 		ps.setString(1,item.getItemid());
	 		ps.setString(2,item.getProductid());
	 		ps.setFloat(3, item.getListprice());
	 		ps.setFloat(4,item.getUnitcost());
	 		ps.setLong(5,item.getSupplier());
	 		ps.setString(6,item.getStatus());
	 		ps.setString(7,item.getAttr1());
	 		ps.setString(8,item.getAttr2());
	 		ps.setString(9,item.getAttr3());
	 		ps.setString(10,item.getAttr4());
	 		ps.setString(11,item.getAttr5());
	 		ps.executeUpdate();
	 		ps.close();
	 		conn.commit();
	 		success=true;
	 	} catch (SQLException e) {
	 		// TODO Auto-generated catch block
	 		
	 		e.printStackTrace();
	 		try{
	 			conn.rollback();
	 		}catch(SQLException ex){
	 			ex.printStackTrace();				
	 		}
	 	}
	 	return success;		
	 }

	 	/**
	 	 * 修改类
	 	 * @param product
	 	 * @return
	 	 */
	 	public boolean changeItem(Item item){
	 		boolean success=false;
	 		DBManager dbManager= new DBManager();
	 		Connection conn=dbManager.GetConnection();
	 		
	 		try {
//	 			Statement stm=conn.createStatement();
	 		//	conn.setAutoCommit(false);//设置自动提交为FALSE
	 			PreparedStatement ps=conn.prepareStatement
	 			("update item set itemid=?,productid=?,listprice=?,unitcost=?,supplier=?,status=?,attr1=?,attr2=?,attr3=?,attr4=?,attr5=? where itemid='"+item.getItemid()+"'");
	 			ps.setString(1,item.getItemid());
		 		ps.setString(2,item.getProductid());
		 		ps.setFloat(3, item.getListprice());
		 		ps.setFloat(4,item.getUnitcost());
		 		ps.setLong(5,item.getSupplier());
		 		ps.setString(6,item.getStatus());
		 		ps.setString(7,item.getAttr1());
		 		ps.setString(8,item.getAttr2());
		 		ps.setString(9,item.getAttr3());
		 		ps.setString(10,item.getAttr4());
		 		ps.setString(11,item.getAttr5());
		 		ps.executeUpdate();
	 			ps.close();
	 			conn.commit();
	 			success=true;
	 		} catch (SQLException e) {
	 			// TODO Auto-generated catch block
	 			
	 			e.printStackTrace();
	 			try{
	 				conn.rollback();
	 			}catch(SQLException ex){
	 				ex.printStackTrace();				
	 			}
	 		}
	 		return success;		
	 	}
	 	/**
	 	 * 删除类
	 	 * @param 
	 	 * @return
	 	 */
	 	public boolean deleteItem(Item item){
	 		boolean success=false;
	 		DBManager dbManager= new DBManager();
	 		Connection conn=dbManager.GetConnection();
	 		try {
//	 			Statement stm=conn.createStatement();
	 			PreparedStatement ps=conn.prepareStatement
	 			("delete from item where itemid='"+item.getItemid()+"'");
	 			ps.executeUpdate();
	 			ps.close();
	 			conn.commit();
	 			success=true;
	 		} catch (SQLException e) {
	 			// TODO Auto-generated catch block
	 			
	 			e.printStackTrace();
	 			try{
	 				conn.rollback();
	 			}catch(SQLException ex){
	 				ex.printStackTrace();				
	 			}
	 		}
	 		return success;
	 		
	 	}
	 }