?? userdaoimpl.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 + -