ecmembermgr.java

这是jsp通用模块导航的程序原代码,里面有新闻,bbs等jsp与javabean结合的程序.

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

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

			// sql语句：
			String sql = "insert into ec_member(username,realname,password,"
					+ "email,gender,phone,pwd_problem,pwd_answer,province,education,"
					+ "hobbies,selfintro) values('" + member.getUsername()
					+ "','" + member.getRealname() + "','"
					+ member.getPassword() + "','" + member.getEmail() + "','"
					+ member.getGender() + "','" + member.getPhone() + "','"
					+ member.getPwd_problem() + "','" + member.getPwd_answer()
					+ "','" + member.getProvince() + "','"
					+ member.getEducation() + "','" + member.getHobbies()
					+ "','" + member.getSelfintro() + "')";
			System.out.println(sql);
			sql = new String(sql.getBytes("ISO8859-1"), "GB2312");
			System.out.println(sql);
			// 执行sql语句：
			int i=1;
			//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(ECMember member) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "update ec_member set username='"
					+ member.getUsername() + "',,realname='"
					+ member.getRealname() + "',password='"
					+ member.getPassword() + "',email='" + member.getEmail()
					+ "',gender='" + member.getGender() + "',phone='"
					+ member.getPhone() + "',pwd_problem'"
					+ member.getPwd_problem() + "',pwd_answer='"
					+ member.getPwd_answer() + "',province='"
					+ member.getProvince() + "',education='"
					+ member.getEducation() + "',hobbies='"
					+ member.getHobbies() + "',selfintro='"
					+ member.getSelfintro() + "' where id='" + member.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_member 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 ECMember 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_member where id = '" + id + "'";
			// 执行sql语句：
			rs = stmt.executeQuery(sql);
			ECMember member = null;
			if (rs.next()) {
				member = new ECMember();
				member.setId(id);
				member.setEducation(rs.getString("education"));
				member.setEmail(rs.getString("email"));
				member.setGender(rs.getInt("gender"));
				member.setHobbies(rs.getString("hobbies"));
				member.setPassword(rs.getString("password"));
				member.setPhone(rs.getString("phone"));
				member.setProvince(rs.getString("province"));
				member.setPwd_answer(rs.getString("pwd_answer"));
				member.setPwd_problem(rs.getString("pwd_problem"));
				member.setRealname(rs.getString("realname"));
				member.setSelfintro(rs.getString("selfintro"));
				member.setUsername(rs.getString("username"));

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

			// sql语句：
			String sql = "select * from ec_member where username = '"
					+ username + "'";
			// 执行sql语句：
			rs = stmt.executeQuery(sql);
			ECMember member = null;
			if (rs.next()) {
				member = new ECMember();
				member.setId(rs.getInt("id"));
				member.setEducation(rs.getString("education"));
				member.setEmail(rs.getString("email"));
				member.setGender(rs.getInt("gender"));
				member.setHobbies(rs.getString("hobbies"));
				member.setPassword(rs.getString("password"));
				member.setPhone(rs.getString("phone"));
				member.setProvince(rs.getString("province"));
				member.setPwd_answer(rs.getString("pwd_answer"));
				member.setPwd_problem(rs.getString("pwd_problem"));
				member.setRealname(rs.getString("realname"));
				member.setSelfintro(rs.getString("selfintro"));
				member.setUsername(rs.getString("username"));

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

			// sql语句：
			String sql = "select * from ec_member where username = '"
					+ username + "' and password='" + password + "'";
			// 执行sql语句：
			rs = stmt.executeQuery(sql);
			ECMember member = null;
			if (rs.next()) {
				member = new ECMember();
				member.setId(rs.getInt("id"));
				member.setEducation(rs.getString("education"));
				member.setEmail(rs.getString("email"));
				member.setGender(rs.getInt("gender"));
				member.setHobbies(rs.getString("hobbies"));
				member.setPassword(rs.getString("password"));
				member.setPhone(rs.getString("phone"));
				member.setProvince(rs.getString("province"));
				member.setPwd_answer(rs.getString("pwd_answer"));
				member.setPwd_problem(rs.getString("pwd_problem"));
				member.setRealname(rs.getString("realname"));
				member.setSelfintro(rs.getString("selfintro"));
				member.setUsername(rs.getString("username"));

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

			// sql语句：
			String sql = "select password from ec_member where username = '"
					+ username + "' and pwd_problem='" + problem
					+ "'  and pwd_answer='" + answer + "'";
			// 执行sql语句：
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				return rs.getString("password");
			}
			return null;
		} 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 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_member";
			// 执行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) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select * from ec_member order by id desc limit "
					+ (page - 1) * pagesize + "," + pagesize;

			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection c = new ArrayList();
			ECMember member = null;
			while (rs.next()) {
				member = new ECMember();
				member.setId(rs.getInt("id"));
				member.setEducation(rs.getString("education"));
				member.setEmail(rs.getString("email"));
				member.setGender(rs.getInt("gender"));
				member.setHobbies(rs.getString("hobbies"));
				member.setPassword(rs.getString("password"));
				member.setPhone(rs.getString("phone"));
				member.setProvince(rs.getString("province"));
				member.setPwd_answer(rs.getString("pwd_answer"));
				member.setPwd_problem(rs.getString("pwd_problem"));
				member.setRealname(rs.getString("realname"));
				member.setSelfintro(rs.getString("selfintro"));
				member.setUsername(rs.getString("username"));

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

}