ecbookmgr.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 ECBookMgr {

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

			// sql语句：
			String sql = "insert into ec_book(name,author,language,"
					+ "content,category,commend,publish_name,publish_address,cdrom,price,"
					+ "on_sale_time,discount,stock) values('" + book.getName()
					+ "','" + book.getAuthor() + "','" + book.getLanguage()
					+ "','" + book.getContent() + "','" + book.getCategory()
					+ "','" + book.getCommend() + "','"
					+ book.getPublish_name() + "','"
					+ book.getPublish_address() + "','" + book.getCdrom()
					+ "','" + book.getPrice() + "','" + book.getStr_on_sale_time()
					+ "','" + book.getDiscount() + "','" + book.getStock()
					+ "')";
			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(ECBook book) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "update ec_book set name='" + book.getName()
					+ "',,author='" + book.getAuthor() + "',language='"
					+ book.getLanguage() + "',content='" + book.getContent()
					+ "',category='" + book.getCategory() + "',commend='"
					+ book.getCommend() + "',publish_name'"
					+ book.getPublish_name() + "',publish_address='"
					+ book.getPublish_address() + "',cdrom='" + book.getCdrom()
					+ "',price='" + book.getPrice() + "',on_sale_time='"
					+ book.getStr_on_sale_time() + "',discount='"
					+ book.getDiscount() + "',stock='" + book.getStock()
					+ "' where id='" + book.getId() + "'";
			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 delete(int id) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "delete from ec_book 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 ECBook 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_book where id = '" + id + "'";
			// 执行sql语句：
			rs = stmt.executeQuery(sql);
			ECBook book = null;
			if (rs.next()) {
				book = new ECBook();
				book.setId(id);
				book.setAuthor(rs.getString("author"));
				book.setCategory(rs.getInt("category"));
				book.setCdrom(rs.getInt("cdrom"));
				book.setCommend(rs.getInt("commend"));
				book.setContent(rs.getString("content"));
				book.setDiscount(rs.getInt("discount"));
				book.setLanguage(rs.getString("language"));
				book.setName(rs.getString("name"));
				book.setOn_sale_time(rs.getDate("on_sale_time"));
				book.setPrice(rs.getDouble("price"));
				book.setPublish_address(rs.getString("publish_address"));
				book.setPublish_name(rs.getString("publish_name"));
				book.setStock(rs.getInt("stock"));

				return book;
			}
			return book;
		} 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_book";
			// 执行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() {
		// 创建数据库连接对象：
		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_book";
			// 执行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_book "+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_book "+clause+" order by id desc limit "
					+ (page - 1) * pagesize + "," + pagesize;
System.out.println(sql);
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection c = new ArrayList();
			ECBook book = null;
			while (rs.next()) {
				book = new ECBook();
				book.setId(rs.getInt("id"));
				book.setAuthor(rs.getString("author"));
				book.setCategory(rs.getInt("category"));
				book.setCdrom(rs.getInt("cdrom"));
				book.setCommend(rs.getInt("commend"));
				book.setContent(rs.getString("content"));
				book.setDiscount(rs.getInt("discount"));
				book.setLanguage(rs.getString("language"));
				book.setName(rs.getString("name"));
				book.setOn_sale_time(rs.getDate("on_sale_time"));
				book.setPrice(rs.getDouble("price"));
				book.setPublish_address(rs.getString("publish_address"));
				book.setPublish_name(rs.getString("publish_name"));
				book.setStock(rs.getInt("stock"));

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