productbean.java

一个jsp+javabean实例的简单应用

package com.jspdev.ch12;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import com.jspdev.util.DataBaseConnection;

public class ProductBean {
	private Connection con;
	//构造方法，获得数据库的连接
	public ProductBean() {
		this.con = DataBaseConnection.getConnection();
	}
	/**
	 *检索所有商品信息，并返回Collection 
	 */
	public Collection getAllProduct() throws Exception {
		
		Statement stmt = con.createStatement();
		ResultSet rst = stmt.executeQuery("select * from products");
		Collection ret = new ArrayList(); 
		while(rst.next()) {
			Product temp = new Product();
			temp.setProductId(rst.getString("productid"));
			temp.setCategoryId(rst.getString("category"));
			temp.setName(rst.getString("name"));
			temp.setProducer(rst.getString("producer"));
			temp.setPrice(rst.getFloat("price"));
			temp.setDescription(rst.getString("descn"));
			
			ret.add(temp);
		}
		con.close();
		return ret;
	}
	/**
	 *按照商品类别ID查找商品 
	 */
	public Collection getProductByCateqory(String categoryId) throws Exception {
		Statement stmt = con.createStatement();
		ResultSet rst = stmt.executeQuery("select * from products where category='"+categoryId+"'");
		Collection ret = new ArrayList(); 
		while(rst.next()) {
			Product temp = new Product();
			temp.setProductId(rst.getString("productid"));
			temp.setCategoryId(rst.getString("category"));
			temp.setName(rst.getString("name"));
			temp.setProducer(rst.getString("producer"));
			temp.setPrice(rst.getFloat("price"));
			temp.setDescription(rst.getString("descn"));
			
			ret.add(temp);
		}
		con.close();
		return ret;
	}
	/**
	 *添加一个商品，使用Product值对象作为参数传给这个方法 
	 */
	public int addProduct(Product product) throws Exception {
		PreparedStatement pstmt = con.prepareStatement("insert into products values(?,?,?,?,?,?)");
		pstmt.setString(1, product.getProductId());
		pstmt.setString(2, product.getCategoryId());
		pstmt.setString(3, product.getName());
		pstmt.setString(4, product.getProducer());
		pstmt.setFloat(5, product.getPrice());
		pstmt.setString(6,product.getDescription());
		int count = pstmt.executeUpdate();
		return count;
	}
	/**
	 * 更改商品信息，使用Product值对象作为参数传给这个方法
	 */
	public int modifyProduct (Product product) throws Exception {
		PreparedStatement pstmt = con.prepareStatement("update products set category=?,name=?,producer=?,price=?,descn=? where productid=?");
		pstmt.setString(1, product.getCategoryId());
		pstmt.setString(2, product.getName());
		pstmt.setString(3, product.getProducer());
		pstmt.setFloat(4, product.getPrice());
		pstmt.setString(5,product.getDescription());
		pstmt.setString(6, product.getProductId());
		int count = pstmt.executeUpdate();
		return count;
	}
	/**
	 * 删除某个商品，指定商品的ID
	 * @throws SQLException 
	 */
	public int deleteProduct(String productId) throws SQLException {
		Statement stmt = con.createStatement();
		int count = stmt.executeUpdate("delete from products where productid ='"+productId+"'");
		//System.out.println("delete from products where productid ='"+productId+"'");
		return count;
	}
	/**
	 * 返回给定ProductId的商品的信息
	 * 返回的是值对象
	 */
	public Product getProductInfo(String productId) throws Exception {
		Statement stmt = con.createStatement();
		ResultSet rst = stmt.executeQuery("select * from products where productid='"+productId+"'");
		System.out.println("select * from products where productid='"+productId+"'");
		Product product = null;
		while(rst.next()) {
			product = new Product();
			product.setProductId(rst.getString("productid"));
			product.setCategoryId(rst.getString("category"));
			product.setName(rst.getString("name"));
			product.setProducer(rst.getString("producer"));
			product.setPrice(rst.getFloat("price"));
			product.setDescription(rst.getString("descn"));
		}
		return product;
	}
	
	public static void main(String[] args) {
		ProductBean a = new ProductBean();
		//查询测试语句
//		Collection b = null;
//		try {
//			b = a.getProductByCateqory("english");
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//		Iterator ite = b.iterator();
//		for(int i=0;i<b.size();i++) {
//			Product c = (Product) ite.next();
//			System.out.println(c.getProductId());
//		}
		//新增和修改测试语句
		Product product = new Product();
		int count = 0;
		product.setProductId("isbn-4-4");
		product.setCategoryId("english");
		product.setName("英语365句(大全)");
		product.setProducer("上海外文出版社");
		product.setPrice((float) 50.1);
		product.setDescription("真不错啊");
		try {
			count = a.modifyProduct(product);
			//count = a.addProduct(product);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(count>0) {
			System.out.println("操作成功");
		} else {
			System.out.println("操作失败");
		}
	}
}