ecordermgr.java

《JSP通用模块及典型系统开发实例导航》源代码

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 ECOrderMgr {

	public int add(ECOrder order, String[] itemid) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			int maxid = 1;
			String sql = "select max(id) as maxid from ec_order";
			 //创建数据记录集对象：
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				maxid = rs.getInt(1) + 1;
			}
			sql = "insert into ec_order(id,username,useraddress,"
					+ "userphone,usercid,status) values('" + maxid + "','"
					+ order.getUsername() + "','" + order.getUseraddress()
					+ "','" + order.getUserphone() + "','" + order.getUsercid()
					+ "','0')";
			sql = new String(sql.getBytes("ISO8859-1"), "GB2312");
			stmt.addBatch(sql);
			System.out.println(sql);
			if (itemid != null) {
				for (int i = 0; i < itemid.length; i++) {
					stmt
							.addBatch("update ec_order_item set status='1',orderid='"
									+ maxid + "' where id='" + itemid[i] + "'");
				}
			}
			// 执行sql语句：
			stmt.executeBatch();
			return maxid;
		} 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) {

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

			// 执行sql语句：
			stmt.executeUpdate("update ec_order set status='1' where id='" + id
					+ "'");
			stmt
					.executeUpdate("update ec_order_item set status='2' where orderid='"
							+ id + "'");
			return 1;
		} 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 delete(int id) {
	// // 创建数据库连接对象：
	// ConnectionPool connPool = DB.getConnPool();
	// Connection conn = connPool.getConnection();
	// Statement stmt = null;
	// try {
	// // 创建数据记录集对象：
	// stmt = conn.createStatement();
	//
	// // sql语句：
	// String sql = "delete from ec_order 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 ECOrder 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 where id = '" + id + "'";
			// 执行sql语句：
			rs = stmt.executeQuery(sql);
			ECOrder order = null;
			if (rs.next()) {
				order = new ECOrder();
				order.setId(id);
				order.setStatus(rs.getInt("status"));
				order.setUseraddress(rs.getString("useraddress"));
				order.setUsercid(rs.getString("usercid"));
				order.setUsername(rs.getString("username"));
				order.setUserphone(rs.getString("userphone"));

				return order;
			}
			return order;
		} 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 int getAll() {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select count(id) from ec_order";
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				return rs.getInt(1);
			}
			return 0;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

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

			// sql语句：
			String sql = "select count(id) from ec_order " + clause;
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				return rs.getInt(1);
			}
			return 0;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public Collection getAll(int pagesize, int page) {
		return getAll(pagesize, page, "");
	}

	public Collection getAll(int pagesize, int page, 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 " + clause
					+ " order by id desc limit " + (page - 1) * pagesize + ","
					+ pagesize;

			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection c = new ArrayList();
			ECOrder order = null;
			ECBookMgr bookMgr = new ECBookMgr();
			ECOrderItemMgr itemMgr = new ECOrderItemMgr();
			while (rs.next()) {
				order = new ECOrder();
				order.setId(rs.getInt("id"));
				order.setStatus(rs.getInt("status"));
				order.setUseraddress(rs.getString("useraddress"));
				order.setUsercid(rs.getString("usercid"));
				order.setUsername(rs.getString("username"));
				order.setUserphone(rs.getString("userphone"));
				order.setItem(itemMgr.getAll("where orderid='" + order.getId()
						+ "'"));

				c.add(order);
				order = 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();
			}
		}
	}
}