db_user.java

通过JSP技术实现商品房产的查询与购买

package ch14.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 ch14.JavaBean.User;
import ch14.JavaBean.DB_Conn;

public class DB_User {
	
    //创建公用数据库连接类对象
	DB_Conn db_conn=new DB_Conn(); 
	
    //添加注册用户
	public int add(User user) throws SQLException{
		db_conn.ConnectDB();
		try {
			String sql = "insert into userinfo(UserName,Pwd,Email,"
					+ "RealName,Gender,Phone,MyPage,"
					+ "Selfintro,Type) values('" + user.getUsername()
					+ "','" + user.getPassword() + "','" + user.getEmail() 
					+ "','" + user.getRealname() + "','"  + user.getGender()  
					+"','"  + user.getPhone() + "','" + user.getPage()
					+ "','" + user.getSelfintro()+ "','"+user.getType()+"')";
			//System.out.println(sql);
			sql = new String(sql.getBytes("ISO8859-1"), "GB2312");
			//System.out.println(sql);
			int i = db_conn.sm.executeUpdate(sql);     // 执行sql语句
			return i;
		} 
		catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return -1;
		} 
		catch (Exception E) {
			E.printStackTrace();
			return -2;
		} 
		finally {
			// 关闭连接，释放数据库资源：
			db_conn.CloseDB();
		}
	}

	//修改用户信息
	public int update(User user) throws SQLException{
		db_conn.ConnectDB();
		try {
			String sql = "update userinfo set UserName='"
					+ user.getUsername()   + "',Pwd='"
					+ user.getPassword()   + "',Email='"
					+ user.getEmail()      + "',RealName='" 
					+ user.getRealname()   + "',Gender='"
					+ user.getGender()     + "',Phone='"
					+ user.getPhone()      + "',MyPage='"
					+ user.getPage()       + "',Selfintro='"
					+ user.getSelfintro()  + "',Type='"
					+ user.getType()       + "' where id='" 
					+ user.getId() + "'";
			//System.out.println(sql);
			sql = new String(sql.getBytes("ISO8859-1"), "GB2312");
			//System.out.println(sql);
			int i = db_conn.sm.executeUpdate(sql);   //执行sql语句
			return i;
		} 
		catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return -1;
		} 
		catch (Exception E) {
			E.printStackTrace();
			return -2;
		} 
		finally {
			// 关闭连接，释放数据库资源：
			db_conn.CloseDB();
		}
	}

	//删除用户信息
	public int delete(int id)throws SQLException{
		db_conn.ConnectDB();
		try {
			String sql = "delete from userinfo where id = '" + id + "'";
			int i = db_conn.sm.executeUpdate(sql);     // 执行sql语句
			return i;
		} 
		catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return -1;
		} 
		catch (Exception E) {
			E.printStackTrace();
			return -2;
		} 
		finally {
			// 关闭连接，释放数据库资源：
			db_conn.CloseDB();
		}
	}
    
	//通过用户ID获取用户信息
	public User get(int id) throws SQLException{
		db_conn.ConnectDB();
		ResultSet rs = null;
		try {
			//从用户表中取
			String sql = "select * from userinfo where id = '" + id + "'";
			rs = db_conn.sm.executeQuery(sql);      // 执行sql语句
			User user = null;
			if (rs.next()) {
				user = new User();
				user.setId(id);
				user.setUsername(rs.getString("UserName"));
				user.setPassword(rs.getString("Pwd"));
				user.setEmail("Email");
				user.setRealname(rs.getString("RealName"));
				user.setGender(rs.getInt("Gender"));
				user.setPhone(rs.getString("Phone"));
				user.setPage(rs.getString("MyPage"));
				user.setSelfintro(rs.getString("Selfintro"));
				user.setType(rs.getInt("Type"));
				return user;
			}
			return user;
		} 
		catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return null;
		} 
		catch (Exception E) {
			E.printStackTrace();
			return null;
		} 
		finally {
			// 关闭连接，释放数据库资源：
			db_conn.CloseDB();
		}
	}

	//通过用户名称获取用户信息
	public User get(String username) throws SQLException{
		db_conn.ConnectDB();
		ResultSet rs = null;
		try {
			String sql = "select * from userinfo where UserName ='"
					+ username + "'";
			rs = db_conn.sm.executeQuery(sql);        // 执行sql语句
			User user = null;
			if (rs.next()) {
				user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(username);
				user.setPassword(rs.getString("Pwd"));
				user.setEmail("Email");
				user.setRealname(rs.getString("RealName"));
				user.setGender(rs.getInt("Gender"));
				user.setPhone(rs.getString("Phone"));
				user.setPage(rs.getString("MyPage"));
				user.setSelfintro(rs.getString("Selfintro"));
				user.setType(rs.getInt("Type"));
				return user;
			}
			return user;
		} 
		catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return null;
		} 
		catch (Exception E) {
			E.printStackTrace();
			return null;
		} 
		finally {
			// 关闭连接，释放数据库资源：
			db_conn.CloseDB();
		}
	}

	//通过用户名称和密码获取用户信息
	public User get(String username, String password) throws SQLException{
		db_conn.ConnectDB();
		ResultSet rs = null;
		try {
			// sql语句：
			String sql = "select * from userinfo where UserName = '"
					+ username + "' and Pwd='" + password + "'";
			// 执行sql语句：
			rs = db_conn.sm.executeQuery(sql);
			//System.out.print(sql);
			User user = null;
			if (rs.next()) {
				user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(username);
				user.setPassword(rs.getString("Pwd"));
				user.setEmail("Email");
				user.setRealname(rs.getString("RealName"));
				user.setGender(rs.getInt("Gender"));
				user.setPhone(rs.getString("Phone"));
				user.setPage(rs.getString("MyPage"));
				user.setSelfintro(rs.getString("Selfintro"));
				user.setType(rs.getInt("Type"));
				return user;
			}
			return user;
		} catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return null;
		} catch (Exception E) {
			E.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			db_conn.CloseDB();
		}
	}

	//找回密码
	public String getPassword(String username, String question,String answer) throws SQLException{
		db_conn.ConnectDB();
		ResultSet rs = null;
		try {
			// sql语句：
			String sql = "select Pwd from userinfo where username = '"
					+ username + "' and question='" + question
					+ "'  and answer='" + answer + "'";
			// 执行sql语句：
			rs = db_conn.sm.executeQuery(sql);
			if (rs.next()) {
				return rs.getString("Pwd");
			}
			return null;
		}
		catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return null;
		} 
		catch (Exception e) {
			e.printStackTrace();
			return null;
		} 
		finally {
			// 关闭连接，释放数据库资源：
			db_conn.CloseDB();
		}
	}

	//获得用户数量
	public int getCount() throws SQLException{
		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();		
		}
	}
}