📄 userinfodao.java
字号:
package com.lovo.bbs.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.naming.NamingException;
import com.lovo.bbs.po.UserInfoPo;
import com.lovo.bbs.util.DbUtil;
import com.lovo.bbs.util.MysqlDb;
import com.lovo.bbs.util.StringUtil;
/**
* 用户信息Dao
*
* @author tiancen2001
*
*/
public class UserInfoDao { //操作类型
public static final int OPER_OF_UPDATE=2;
public static final int OPER_OF_DELETE=3;
public UserInfoDao(){
}
/**
* 返回指定ID的用户Po
*
* @param userName
* @param type
* @return
* @throws NamingException
* @throws SQLException
*/
public UserInfoPo getUserInfo(String userName, int type)
throws NamingException, SQLException {
UserInfoPo po = new UserInfoPo();
MysqlDb mysqldb = new MysqlDb();
mysqldb.setPool(true);
Connection con = DbUtil.getConn(mysqldb);
String sql = "select user.userid, user.username, user.email,"
+ "user.qq, user.msn, user.blog, "
+ "user.homepage, user.birthday, user.home, user.score, "
+ "rank.rankname,rank.rankimg,"
+ "userhead.headname,userhead.headimg,user.signature,user.topicnum,userhead.custom,user.usertype "
+ "from user left join rank on user.rankid=rank.rankid "
+ "left join userhead on user.headimgid=userhead.headid "
+ "where user.username=? and usertype>=?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, userName);
ps.setInt(2, type);
ResultSet rs = ps.executeQuery();
if (rs.next()) {// 找到
po.setUserId(rs.getInt(1));
po.setUserName(StringUtil.NoNull(rs.getString(2)));
po.setEmail(rs.getString(3));
po.setQq(StringUtil.NoNull(rs.getString(4)));
po.setMsn(StringUtil.NoNull(rs.getString(5)));
po.setBlog(StringUtil.NoNull(rs.getString(6)));
po.setHomePage(StringUtil.NoNull(rs.getString(7)));
po.setBirthday(StringUtil.NoNull(rs.getString(8)));
po.setHome(StringUtil.NoNull(rs.getString(9)));
po.setScore(rs.getInt(10));
po.setRankName(rs.getString(11));
po.setRankImg(rs.getString(12));
po.setHeadName(rs.getString(13));
po.setHeadImg(rs.getString(14));
po.setSignature(rs.getString(15));
po.setTopicNum(rs.getInt(16));
po.setCustomHeadImg(rs.getInt(17) == 1 ? true : false);
po.setType(rs.getInt(18));
} else {
po = null;
}
mysqldb.closePS(ps);
mysqldb.closeRS(rs);
mysqldb.closeConnection(con);
return po;
}
/**
* 用户的积分增加2,发表数加一
*
* @param authorname
* @return
* @throws NamingException
* @throws SQLException
*/
public int addOneTopic(String authorname) throws NamingException,
SQLException {
int added = 0;
MysqlDb mysqldb = new MysqlDb();
mysqldb.setPool(true);
Connection con = DbUtil.getConn(mysqldb);
String sql = "update user set score=score+2,topicnum=topicnum+1 where username='"
+ authorname + "'";
added = con.prepareStatement(sql).executeUpdate();
mysqldb.closeConnection(con);
return added;
}
/**
* 用户积分增加一
*
* @param postAuthorName
* @return
* @throws SQLException
* @throws NamingException
*/
public int addOnePost(String postAuthorName) throws SQLException,
NamingException {
int added = 0;
MysqlDb mysqldb = new MysqlDb();
mysqldb.setPool(true);
Connection con = DbUtil.getConn(mysqldb);
String sql = "update user set score=score+1 where username='"
+ postAuthorName + "'";
added = con.prepareStatement(sql).executeUpdate();
mysqldb.closeConnection(con);
return added;
}
/**
* 修改邮件或密码
*
* @param userpo
* @param formerpassword
* @return
* @throws SQLException
* @throws NamingException
*/
public int chgMailPassword(UserInfoPo po, String formerpassword)
throws NamingException, SQLException {
int changed = 0;
MysqlDb mysqldb = new MysqlDb();
mysqldb.setPool(true);
Connection con = DbUtil.getConn(mysqldb);
if ("".equals(formerpassword)) {// 如果密码项没填,则只更新邮件
String sql1 = "update user set email='" + po.getEmail()
+ "' where username='" + po.getUserName() + "'";
changed = con.prepareStatement(sql1).executeUpdate();
} else {// 否则查询输入的原密码是否正确,正确则更新密码和邮件
String sql2 = "select password from user where username='"
+ po.getUserName() + "'";
ResultSet rs = con.prepareStatement(sql2).executeQuery();
rs.next();
if (rs.getString(1).equals(formerpassword)) {// 原密码正确
String sql3 = "update user set email='" + po.getEmail()
+ "',password='" + po.getPassword()
+ "' where username='" + po.getUserName() + "'";
changed = con.prepareStatement(sql3).executeUpdate();
}
mysqldb.closeRS(rs);
}
mysqldb.closeConnection(con);
return changed;
}
/**
* 修改联系信息
*
* @param uservo
* @return
* @throws SQLException
* @throws NamingException
*/
public int chgContactInfo(UserInfoPo po) throws NamingException,
SQLException {
int changed = 0;
MysqlDb mysqldb = new MysqlDb();
mysqldb.setPool(true);
Connection con = DbUtil.getConn(mysqldb);
String sql = "update user "
+ "set homepage=?,home=?,qq=?,msn=?,blog=? "
+ "where username=?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, po.getHomePage());
ps.setString(2, po.getHome());
ps.setString(3, po.getQq());
ps.setString(4, po.getMsn());
ps.setString(5, po.getBlog());
ps.setString(6, po.getUserName());
changed = ps.executeUpdate();
mysqldb.closePS(ps);
mysqldb.closeConnection(con);
return changed;
}
/**
*
* @param po
* 包含新头像
* @param headImg
* 原头像
* @return
* @throws NamingException
* @throws SQLException
*/
public int updateUserHead(UserInfoPo po, String headImg)
throws NamingException, SQLException {
int changed = 0;
MysqlDb mysqldb = new MysqlDb();
mysqldb.setPool(true);
Connection con = DbUtil.getConn(mysqldb);
con.setAutoCommit(false);
// 首先更新userhead表,如果原头像是上传的图像,则删除原头像记录
String tempImg = null;
if (headImg.startsWith("upload\\")) {
// 如果是上传的包含路径的图像,则必须将反单斜杠替换成反双斜杠,SQL语句才能正常识别
tempImg = headImg.replace("\\", "\\\\");
} else {
tempImg = headImg;
}
String sqlDelHead = "delete from userhead where headimg='" + tempImg
+ "' and custom='1'";
con.prepareStatement(sqlDelHead).executeUpdate();
con.commit();
// 再次更新userhead表,如果新头像是上传的头像,则插入一条记录,并查询该记录的ID值
int newID = 0;
if (po.isCustomHeadImg()) {
// 插入记录
String sqlInsertHead = "insert into userhead(headname, headimg, custom)"
+ "values(?,?,'1')";
PreparedStatement ps2 = con.prepareStatement(sqlInsertHead);
ps2.setString(1, po.getHeadImg());
ps2.setString(2, po.getHeadImg());
ps2.executeUpdate();
con.commit();
mysqldb.closePS(ps2);
// 查询记录ID
String sqlQueryMaxId = "select max(headid) from userhead";
ResultSet rs = con.prepareStatement(sqlQueryMaxId).executeQuery();
rs.next();
newID = rs.getInt(1);
mysqldb.closeRS(rs);
}
// 更新user表,此处必须用PreparedStatement,
// 否则可能遭SQL注入攻击,
// 只需在个性签名的任意地方加上一个单引号,就可使系统崩溃
String sqlUpdateUser = null;
if (po.isCustomHeadImg()) {// 如果是上传图像
sqlUpdateUser = "update user set signature=?,headimgid=? where username=?";
PreparedStatement ps3 = con.prepareStatement(sqlUpdateUser);
ps3.setString(1, po.getSignature());
ps3.setInt(2, newID);
ps3.setString(3, po.getUserName());
changed = ps3.executeUpdate();
} else {
sqlUpdateUser = "update user set signature=?, "
+ "headimgid=(select headid from userhead where headimg=?) "
+ "where username=? ";
PreparedStatement ps4 = con.prepareStatement(sqlUpdateUser);
ps4.setString(1, po.getSignature());
ps4.setString(2, po.getHeadImg());
ps4.setString(3, po.getUserName());
changed = ps4.executeUpdate();
}
con.commit();
mysqldb.closeConnection(con);
return changed;
}
/**
* 返回所有用户
*
* @throws SQLException
* @throws NamingException
*/
public ArrayList<UserInfoPo> getUsers(int page)
throws NamingException, SQLException {
ArrayList<UserInfoPo> pos = new ArrayList<UserInfoPo>();
MysqlDb mysqldb = new MysqlDb();
mysqldb.setPool(true);
Connection con = DbUtil.getConn(mysqldb);
// 数据库分页边界
int startIndex = (page - 1) * 20;
int endIndex = (page - 1) * 20 + 20;
String sql = "select user.userid, user.username, user.email,"
+ "user.qq, user.msn, user.blog, "
+ "user.homepage, user.birthday, user.home, user.score, "
+ "rank.rankname,rank.rankimg,"
+ "userhead.headname,userhead.headimg,user.signature,user.topicnum,userhead.custom,user.usertype "
+ "from user left join rank on user.rankid=rank.rankid "
+ "left join userhead on user.headimgid=userhead.headid "
+ "order by userid limit " + startIndex + "," + endIndex;
ResultSet rs = con.prepareStatement(sql).executeQuery();
while (rs.next()) {
UserInfoPo po = new UserInfoPo();
po.setUserId(rs.getInt(1));
po.setUserName(StringUtil.NoNull(rs.getString(2)));
po.setEmail(rs.getString(3));
po.setQq(StringUtil.NoNull(rs.getString(4)));
po.setMsn(StringUtil.NoNull(rs.getString(5)));
po.setBlog(StringUtil.NoNull(rs.getString(6)));
po.setHomePage(StringUtil.NoNull(rs.getString(7)));
po.setBirthday(StringUtil.NoNull(rs.getString(8)));
po.setHome(StringUtil.NoNull(rs.getString(9)));
po.setScore(rs.getInt(10));
po.setRankName(rs.getString(11));
po.setRankImg(rs.getString(12));
po.setHeadName(rs.getString(13));
po.setHeadImg(rs.getString(14));
po.setSignature(rs.getString(15));
po.setTopicNum(rs.getInt(16));
po.setCustomHeadImg(rs.getInt(17) == 1 ? true : false);
po.setType(rs.getInt(18));
pos.add(po);
}
mysqldb.closeRS(rs);
mysqldb.closeConnection(con);
return pos;
}
/**
* 计算用户数
*
* @throws SQLException
* @throws NamingException
*/
public int getUserNum() throws NamingException, SQLException {
int userNum = 0;
MysqlDb mysqldb = new MysqlDb();
mysqldb.setPool(true);
Connection con = DbUtil.getConn(mysqldb);
String sql = "select count(*) from user";
ResultSet rs = con.prepareStatement(sql).executeQuery();
rs.next();
userNum = rs.getInt(1);
mysqldb.closeRS(rs);
mysqldb.closeConnection(con);
return userNum;
}
/**
* 控制台更新
* @throws SQLException
* @throws NamingException
*/
public int updateUser(UserInfoPo po, int operType) throws NamingException, SQLException {
int updated = 0;
MysqlDb mysqldb = new MysqlDb();
mysqldb.setPool(true);
Connection con = DbUtil.getConn(mysqldb);
con.setAutoCommit(false);
if(operType == UserInfoDao.OPER_OF_UPDATE){
String sqlUpdate = "update user set email=?,score=?,topicnum=? where userid=?";
PreparedStatement ps = con.prepareStatement(sqlUpdate);
ps.setString(1, po.getEmail());
ps.setInt(2, po.getScore());
ps.setInt(3, po.getTopicNum());
ps.setInt(4, po.getUserId());
updated = ps.executeUpdate();
mysqldb.closePS(ps);
}else if(operType==TopicDao.OPER_OF_DELETE){
String sqlDelete = "delete from user where userid=?";
PreparedStatement ps = con.prepareStatement(sqlDelete);
ps.setInt(1, po.getUserId());
updated = ps.executeUpdate();
mysqldb.closePS(ps);
}
con.commit();
mysqldb.closeConnection(con);
return updated;
}
/**
* 删除用户
* @param userid
* @return
* @throws SQLException
* @throws NamingException
*/
public int delUser(int userid) throws NamingException, SQLException {
int del = 0;
MysqlDb mysqldb = new MysqlDb();
mysqldb.setPool(true);
Connection con = DbUtil.getConn(mysqldb);
String sql = "delete from user where userid="+userid;
del=con.prepareStatement(sql).executeUpdate();
mysqldb.closeConnection(con);
return del;
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -