package com.wxpn.tutorial.servlet;

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

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

			// sql语句：
			String sql = "insert into news_admin(UserName,UserPwd) values('"
					+ user.getUserName()+ "','" + user.getUserPwd()+"')";
			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(String userName) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "delete from news_admin where userName = '" + userName + "'";
			// 执行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 getUser(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 news_admin where userName = '" + userName + "'";
			// 执行sql语句：
			rs= stmt.executeQuery(sql);
			if(rs.next()){
				return 1;
			}
			return 0;
		} 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 Collection getAll() {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select * from news_admin";
			// 执行sql语句：
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection c = new ArrayList();
			User user = null;
			while (rs.next()) {
				user = new User();
				user.setUserName(rs.getString("username"));
				user.setUserPwd(rs.getString("userpwd"));
				
				c.add(user);
				user = 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();
			}
		}
	}

}
