db_user.java

张新曼 精通JSP Web 开发技术与典型应用 随书光盘源码

package ch18.JavaBean;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import javax.security.sasl.SaslException;

import sun.jdbc.odbc.ee.ConnectionPool;

import ch18.JavaBean.User;
import ch18.JavaBean.DB_Conn;

public class DB_User {
	
	DB_Conn db_conn=new DB_Conn();
	
	//获得用户数量
	public int getCount() {
		db_conn.ConnectDB();
		ResultSet rs = null;
		try {     
			String sql = "select count(id) from userinfo";
			rs = db_conn.sm.executeQuery(sql);        // 执行sql语句
			if (rs.next()) {
				return rs.getInt(1);
			}
			return 0;
		} catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return 0;
		} catch (Exception E) {
		E.printStackTrace();
			return 0;
		} finally {
			// 关闭连接，释放数据库资源：
			db_conn.CloseDB();		
		}
	}
	
	//
	public Collection getAll(int pagesize, int page) {
        db_conn.ConnectDB();
		ResultSet rs = null;
		try {
			// sql语句：
			String sql = "select * from userinfo order by id desc limit "
					+ (page - 1) * pagesize + "," + pagesize;
			// 执行sql语句，返回一个记录集到rs：
			rs=db_conn.sm.executeQuery(sql);
			Collection c = new ArrayList();
			User user = null;
			while (rs.next()) {
				user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("UserName"));
				user.setPassword(rs.getString("Pwd"));
				user.setEmail(rs.getString("Email"));
				user.setRealname(rs.getString("RealName"));
				user.setGender(rs.getInt("Gender"));
                user.setQuestion(rs.getString("Question"));
                user.setAnswer(rs.getString("Answer"));
				user.setPhone(rs.getString("Phone"));
				user.setPage(rs.getString("MyPage"));
				user.setHobbies(rs.getString("Hobbies"));
				user.setSelfintro(rs.getString("Selfintro"));

				c.add(user);
				user = null;
			}
			return c;
		} catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return null;
		} catch (Exception E) {
			E.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			db_conn.CloseDB();
		}
	}

}