ecorderitemmgr.java

本系统用java实现网上商店的一般功能

package com.wxpn.tutorial.ec.bean;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import com.wxpn.tutorial.db.ConnectionPool;
import com.wxpn.tutorial.db.DB;

/**
 * 描述: 描述信息管理类
 * 
 * @Copyright (c) 2005-2008 Wang Xining
 * @author 王夕宁
 * @version 1.0
 */

public class ECOrderItemMgr {

	public int add(ECOrderItem orderitem) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();
			String sql = "select id from ec_order_item where bookid='"
					+ orderitem.getBookid() + "' and username='"
					+ orderitem.getUsername() + "' and status='0'";
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				sql = "update ec_order_item set booknumber=booknumber+1,goods_price='"
						+ orderitem.getGoods_price()
						+ "' where bookid='"
						+ orderitem.getBookid()
						+ "' and username='"
						+ orderitem.getUsername() + "' and status='0'";
			} else {
				sql = "insert into ec_order_item(username,"
						+ "bookid,booknumber,status,goods_price) values('"
						+ orderitem.getUsername() + "','"
						+ orderitem.getBookid() + "','1','0','"
						+ orderitem.getGoods_price() + "')";
			}
			System.out.println(sql);
			sql = new String(sql.getBytes("ISO8859-1"), "GB2312");
			System.out.println(sql);
			// 执行sql语句：
			int i = stmt.executeUpdate(sql);
			return i;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return -1;
		} catch (Exception e) {
			e.printStackTrace();
			return -2;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public int update(int id, int status) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "update ec_order_item set status='" + status
					+ "' where id='" + id + "'";
			// 执行sql语句：
			int i = stmt.executeUpdate(sql);
			return i;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return -1;
		} catch (Exception e) {
			e.printStackTrace();
			return -2;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public ECOrderItem get(int id) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select * from ec_order_item where id = '" + id + "'";
			// 执行sql语句：
			rs = stmt.executeQuery(sql);
			ECOrderItem orderitem = null;
			if (rs.next()) {
				orderitem = new ECOrderItem();
				orderitem.setId(id);
				orderitem.setBookid(rs.getInt("bookid"));
				orderitem.setBooknumber(rs.getInt("booknumber"));
				orderitem.setGoods_price(rs.getDouble("goods_price"));
				orderitem.setStatus(rs.getInt("status"));
				orderitem.setUsername(rs.getString("username"));
			}
			return orderitem;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public Collection getAll(String clause) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select * from ec_order_item "+clause;

			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection c = new ArrayList();
			ECOrderItem orderitem = null;
			ECBookMgr bookMgr = new ECBookMgr();
			while (rs.next()) {
				orderitem = new ECOrderItem();
				orderitem.setId(rs.getInt("id"));
				orderitem.setBookid(rs.getInt("bookid"));
				orderitem.setBookname(bookMgr.get(orderitem.getBookid())
						.getName());
				orderitem.setBooknumber(rs.getInt("booknumber"));
				orderitem.setGoods_price(rs.getDouble("goods_price"));
				orderitem.setStatus(rs.getInt("status"));
				orderitem.setUsername(rs.getString("username"));

				c.add(orderitem);
				orderitem = null;
			}
			return c;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}
}