📄 userdaoimpl.java

📁 一个bbs论坛系统
💻 JAVA
字号:
package com.lovo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.lovo.factory.DAOFactory;
import com.lovo.po.AreaPO;
import com.lovo.po.BlockPO;
import com.lovo.po.ChatroomPO;
import com.lovo.po.ImgPO;
import com.lovo.po.LevelPO;
import com.lovo.po.MessagePO;
import com.lovo.po.PublishPO;
import com.lovo.po.ReplyPO;
import com.lovo.po.UserPO;
import com.lovo.util.DBUtil;
import com.lovo.util.Page;

public class UserDAOImpl implements UserDAO {

	/**数据库连接对象*/
	private Connection con;

	/**预编译语句对象*/
	private PreparedStatement pre;

	/**结果集对象*/
	private ResultSet rs;

	/**
	 * Method: 添加记录 Description: 将用户信息作为一条记录添加到数据库
	 * 
	 */
	public void insert(UserPO po) throws SQLException {
		String sql = "insert into user(name, password, email, problem, result, registerTime) values(?, ?, ?, ?, ?, date_format(now(),'%Y-%m-%d %H:%i:%s'))";
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(sql);
			pre.setString(1, po.getName());
			pre.setString(2, po.getPassword());
			pre.setString(3, po.getEmail());
			pre.setString(4, po.getProblem());
			pre.setString(5, po.getPassword());
			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
	}

	/**
	 * Method: 删除记录 Description: 将用户名为name的用户记录从数据库中删除
	 * 
	 */
	public void delete(String name) throws SQLException {
		String sql = "delete from user where name = ?";
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(sql);
			pre.setString(1, name);
			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
	}

	/**
	 * Method: 删除记录 Description: 将id用户的用户记录从数据库中删除
	 * 
	 */
	public void delete(int id) throws SQLException {
		String sql = "delete from user where id = ?";
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(sql);
			pre.setInt(1, id);
			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
	}

	/**
	 * Method: 修改记录 Description: 修改用户名为name的用户在数据库中的信息
	 * 
	 */
	public void update(UserPO po) throws SQLException {
		String sql = "update user set levelNum = ?, blockflag = ?,areaflag = ?," +
				"adminflag = ?,score = ?, wealth = ?, imgid = ?, problem = ?," +
				"result = ?, email = ?, chatroom_id = ?, password = ?, lockflag = ?, prestige = ? where id = ?";
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(sql);
			
			pre.setInt(1, po.getLevel().getLevelNum());
			
			pre.setInt(2, po.getBlockFlag());
			pre.setInt(3, po.getAreaFlag());
			pre.setInt(4, po.getAdminFlag());
			pre.setInt(5, po.getScore());
			pre.setInt(6, po.getWealth());
			
			pre.setInt(7, 0);
			
			pre.setString(8, po.getProblem());
			pre.setString(9, po.getResult());
			pre.setString(10, po.getEmail());
			if(po.getChatroom() == null) {
				pre.setInt(11, 0);
			}else {
				pre.setInt(11, po.getChatroom().getId());
			}
			pre.setString(12, po.getPassword());
			pre.setInt(13, po.getLockFlag());
			pre.setInt(14, po.getPrestige());
			pre.setInt(15, po.getId());
			
			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}

	}

	/**
	 * Method: 查询记录 Description: 查询所有用户的信息
	 * 
	 */
	public List<UserPO> queryUser(Page page) throws SQLException {
		String userSql = "select * from user limit ?, ?";
		List<UserPO> list = new ArrayList<UserPO>();
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(userSql);
			pre.setInt(1, (page.getCurrPage() - 1 ) * page.getPageRows());
			pre.setInt(2, page.getPageRows());
			rs = pre.executeQuery();
			while (rs.next()) {
				UserPO po = new UserPO();
				this.initUser(po, rs);
				list.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return list;
	}

	/**
	 * Method: 查询记录 Description: 查询名为name的用户的信息
	 * 
	 */
	public UserPO queryUserById(int id) throws SQLException {
		UserPO po = null;
		String userSql = "select * from user where id = ?";
		List<UserPO> list = new ArrayList<UserPO>();
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(userSql);
			pre.setInt(1, id);
			rs = pre.executeQuery();
			if (rs.next()) {
				po = new UserPO();
				this.initUser(po, rs);
				list.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return po;
	}

	/**
	 * Method: 查询记录 Description: 查询名为name的用户的信息
	 * 
	 */
	public UserPO queryUserByName(String name) throws SQLException {
		UserPO po = null;
		String userSql = "select * from user where name = ?";
		List<UserPO> list = new ArrayList<UserPO>();
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(userSql);
			pre.setString(1, name);
			rs = pre.executeQuery();
			if (rs.next()) {
				po = new UserPO();
				this.initUser(po, rs);
				list.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return po;
	}

	/**
	 * Method: 财富排名 
	 * 
	 */
	public List<UserPO> queryUserByWealth(Page page) throws SQLException {
		String userSql = "select * from user order by wealth desc limit ?, ?";
		List<UserPO> list = new ArrayList<UserPO>();
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(userSql);
			pre.setInt(1, (page.getCurrPage() - 1 ) * page.getPageRows());
			pre.setInt(2, page.getPageRows());
			rs = pre.executeQuery();
			while (rs.next()) {
				UserPO po = new UserPO();
				this.initUser(po, rs);
				list.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}

		return list;
	}
	
	public List<UserPO> queryUserByWealth() throws SQLException {
		String userSql = "select * from user order by wealth";
		List<UserPO> list = new ArrayList<UserPO>();
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(userSql);
			rs = pre.executeQuery();
			while (rs.next()) {
				UserPO po = new UserPO();
				this.initUser(po, rs);
				list.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}

		return list;
	}

	/**
	 * Method: 新人排名 
	 * 
	 */
	public List<UserPO> queryUserByRegisterTime() throws SQLException {
		String userSql = "select * from user order by registertime desc";
		List<UserPO> list = new ArrayList<UserPO>();
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(userSql);
			rs = pre.executeQuery();
			while (rs.next()) {
				UserPO po = new UserPO();
				this.initUser(po, rs);
				list.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}

		return list;
	}

	/**
	 * Method: 查询记录 Description: 查询名子中含有name的用户的信息
	 * 
	 */
	public List<UserPO> queryUserByLike(String name) throws SQLException {
		String userSql = "select * from user where name like ?";
		List<UserPO> list = new ArrayList<UserPO>();
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(userSql);
			pre.setString(1, "%" + name + "%");
			rs = pre.executeQuery();
			while (rs.next()) {
				UserPO po = new UserPO();
				this.initUser(po, rs);
				list.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}

		return list;
	}

	/**
	 * Method: 查询记录 Description: 根据区域ID查询该区所有区主
	 * 
	 */
	public List<UserPO> queryByAreaId(int areaId) throws SQLException {
		String userSql = "select * from "
				+ " user u,area a, user_area ua"
				+ " where u.id = ua.user_id and a.id = ua.area_id and areaflag = 1 and area_id = ?";
		List<UserPO> list = new ArrayList<UserPO>();
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(userSql);
			pre.setInt(1, areaId);
			rs = pre.executeQuery();
			while (rs.next()) {
				UserPO po = new UserPO();
				this.initUser(po, rs);
				AreaPO area = new AreaPO();
				area.setId(rs.getInt("area_id"));
				po.setArea(area);
				list.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return list;
	}

	/**
	 * Method: 查询记录 Description: 根据区域名查询该区所有区主
	 * 
	 */
	public List<UserPO> queryByAreaName(String areaName) throws SQLException {
		String userSql = "select * from "
				+ " user u,area a, user_area ub"
				+ " where u.id = ub.user_id and a.id = ub.area_id and areaflag = 1 and a.name = ?";
		List<UserPO> list = new ArrayList<UserPO>();
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(userSql);
			pre.setString(1, areaName);
			rs = pre.executeQuery();
			while (rs.next()) {
				UserPO po = new UserPO();
				this.initUser(po, rs);
				AreaPO area = new AreaPO();
				area.setId(rs.getInt("area_id"));
				po.setArea(area);
				list.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}

		return list;
	}

	/**
	 * Method: 查询记录 Description: 根据版块ID查询该版所有版主
	 * 
	 */
	public List<UserPO> queryByBlockId(int blockId) throws SQLException {
		String userSql = "select * from"
				+ " user u,block b, user_block ub"
				+ " where u.id = ub.user_id and b.id = ub.block_id and blockflag = 1 and block_id = ?";
		List<UserPO> list = new ArrayList<UserPO>();
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(userSql);
			pre.setInt(1, blockId);
			rs = pre.executeQuery();
			while (rs.next()) {
				UserPO po = new UserPO();
				this.initUser(po, rs);
				BlockPO block = new BlockPO();
				block.setId(rs.getInt("block_id"));
				po.setBlock(block);
				list.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return list;
	}

	/**
	 * Method: 查询记录 Description: 根据版块名查询该版所有版主
	 * 
	 */
	public List<UserPO> queryByBlockName(String blockName) throws SQLException {
		String userSql = "select * from"
				+ " user u,block b, user_block ub"
				+ " where u.id = ub.user_id and b.id = ub.block_id and blockflag = 1 and b.name = ?";
		List<UserPO> list = new ArrayList<UserPO>();
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(userSql);
			pre.setString(1, blockName);
			rs = pre.executeQuery();
			while (rs.next()) {
				UserPO po = new UserPO();
				this.initUser(po, rs);
				BlockPO block = new BlockPO();
				block.setId(rs.getInt("block_id"));
				po.setBlock(block);
				list.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}

		return list;
	}

	public int queryAreaIdByUserId(int id) throws SQLException {
		String userSql =  "select area_id from "
			+ " user u, user_area ua"
			+ " where u.id = ua.user_id and areaflag = 1 and u.id = ?";
		int areaId = 0;
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(userSql);
			pre.setInt(1, id);
			rs = pre.executeQuery();
			if (rs.next()) {
				areaId = rs.getInt("area_id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}

		return areaId;
	}
	
	public int queryRegUserNum() throws SQLException {
		String userSql = "select count(*) maxrows from user";
		int maxRows = 0;
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(userSql);
			rs = pre.executeQuery();
			while (rs.next()) {
				maxRows = rs.getInt("maxrows");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return maxRows;
	}

	public void initUser(UserPO po, ResultSet rs) throws SQLException {
		LevelPO level = new LevelPO();
		ImgPO img = new ImgPO();
		ChatroomPO chatroom = new ChatroomPO();
		img.setId(rs.getInt("imgid"));
		level.setLevelNum(rs.getInt("levelNum"));
		chatroom.setId(rs.getInt("chatroom_id"));
		po.setId(rs.getInt("id"));
		po.setLevel(level);
		po.setAreaFlag(rs.getInt("areaflag"));
		po.setBlockFlag(rs.getInt("blockflag"));
		po.setAdminFlag(rs.getInt("adminflag"));
		po.setScore(rs.getInt("score"));
		po.setWealth(rs.getInt("wealth"));
		po.setImg(img);
		po.setName(rs.getString("name"));
		po.setPassword(rs.getString("password"));
		po.setProblem(rs.getString("problem"));
		po.setResult(rs.getString("result"));
		po.setEmail(rs.getString("email"));
		po.setLockFlag(rs.getInt("lockflag"));
		po.setChatroom(chatroom);
		po.setRegisterTime(rs.getTimestamp("registertime").toString());
		po.setPrestige(rs.getInt("prestige"));
	}

	
	
}
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -