package org.microblog.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.microblog.po.Friend;
import org.microblog.util.DBConn;
public class FriendDao {
public boolean addFriend(final Friend friend){
//f_status
//1为u1关注u2
//2为u1与u2互相关注
//获得关注的人数量
int u_id = friend.getU_id();
int u_id2 = friend.getU_id2();
DBConn dbConn = new DBConn();
//int id = 0;
long amount = 0;
int addAffectedRows = 0;
int changeAffectedRows = 0;
String sql_u2_u1="SELECT count(*) FROM `microblog`.`friend` where u_id = ? and u_id2 = ?";
ResultSet rs = dbConn.execQuery(sql_u2_u1, new Object[]{u_id2,u_id});
try {
while(rs.next()){
//id=rs.getInt("f_id");
amount = rs.getLong("count(*)");
}
if(amount==0){
String sql_addfriend = "insert into friend values(null,?,?,?)";
addAffectedRows = dbConn.execOther(sql_addfriend, new Object[]{u_id,u_id2,1});
}else if(amount==1){
String sqlChangeStatus = "update friend set f_status = 2 where u_id = ? and u_id2 = ?";
changeAffectedRows = dbConn.execOther(sqlChangeStatus, new Object[]{u_id2,u_id});
}
return ((addAffectedRows>0)||(changeAffectedRows>0))?true:false;
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
dbConn.closeConn();
}
return false;
}
public boolean removeFriendById(final int u_id,final int u_id2){
//删除关注
String sql_removeFriendById="SELECT f_id,f_status FROM `microblog`.`friend` where u_id = ? and u_id2 = ?";
String sql_removeFriendById2="SELECT f_id,f_status FROM `microblog`.`friend` where u_id = ? and u_id2 = ?";
DBConn dbConn = new DBConn();
ResultSet rs1 = dbConn.execQuery(sql_removeFriendById, new Object[]{u_id,u_id2});
ResultSet rs2 = dbConn.execQuery(sql_removeFriendById2, new Object[]{u_id2,u_id});
int status1 = 0;
int status2 = 0;
int id1 = 0;
int id2 = 0;
int affectedRows1 = 0;
int affectedRows2 = 0;
try {
while(rs1.next()){
if(!rs1.wasNull()){
id1=rs1.getInt("f_id");
status1=rs1.getInt("f_status");
}
}
while(rs2.next()){
if(!rs2.wasNull())
id2=rs2.getInt("f_id");
status2=rs2.getInt("f_status");
}
if(status1==1){
String sql_sql_removeFriend = "delete from friend where f_id = ?";
affectedRows1 = dbConn.execOther(sql_sql_removeFriend, new Object[]{id1});
}else if(status2==1){
String sql_sql_removeFriend = "delete from friend where f_id = ?";
affectedRows1 = dbConn.execOther(sql_sql_removeFriend, new Object[]{id2});
}else if(status1==2){
String sql_sql_removeFriend2 = "update friend set u_id=?,u_id2=?,f_status=? where f_id = ?";
affectedRows2 = dbConn.execOther(sql_sql_removeFriend2, new Object[]{u_id2,u_id,1,id1});
}else if(status2==2){
String sql_sql_removeFriend2 = "update friend set f_status=? where f_id = ?";
affectedRows2 = dbConn.execOther(sql_sql_removeFriend2, new Object[]{1,id2});
}
if((affectedRows1>0)||(affectedRows2>0)){
return true;
}
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
rs1.close();
rs2.close();
} catch (SQLException e) {
e.printStackTrace();
}
dbConn.closeConn();
}
return false;
}
public int isFriend(final int u_id,final int u_id2){
//判断二人关系
/*
* u_id1关注u_id2返回 1
* u_id2关注u_id1返回 2
* 互为好友 3
*/
String sql_getStatusById="SELECT f_status FROM `microblog`.`friend` where u_id = ? and u_id2 = ?";
String sql_getStatusById2="SELECT f_status FROM `microblog`.`friend` where u_id = ? and u_id2 = ?";
DBConn dbConn = new DBConn();
ResultSet rs1 = dbConn.execQuery(sql_getStatusById, new Object[]{u_id,u_id2});
ResultSet rs2 = dbConn.execQuery(sql_getStatusById2, new Object[]{u_id2,u_id});
int status1 = 0;
int status2 = 0;
try {
while(rs1.next()){
if(!rs1.wasNull()){
status1=rs1.getInt("f_status");
}
}
while(rs2.next()){
if(!rs2.wasNull())
status2=rs2.getInt("f_status");
}
if(status1==1){
return 1;
//u_id1关注u_id2
}else if(status2==1){
return 2;
//u_id2关注u_id1
}else if((status1==2)||(status2==2)){
return 3;
//互为好友
}
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
rs1.close();
rs2.close();
} catch (SQLException e) {
e.printStackTrace();
}
dbConn.closeConn();
}
return 0;
}
public boolean existRelationship(final int u_id, final int u_id2) {
String strSql = "select f_status from friend where u_id = ? and u_id2 = ?";
DBConn dbConn = new DBConn();
ResultSet rs = dbConn.execQuery(strSql, new Object[] { u_id, u_id2 });
ResultSet rs2 = dbConn.execQuery(strSql, new Object[] { u_id2, u_id });
int f_status = 0;
int f_status2 = 0;
try {
if (rs.next()) {
f_status = rs.getInt(1);
}
if (rs2.next()) {
f_status2 = rs2.getInt(1);
}
return (f_status > 0 || f_status2>0)? true : false;
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
try {
rs.close();
rs2.close();
} catch (SQLException e) {
e.printStackTrace();
}
dbConn.closeConn();
}
}
public long getFansAmountById(final int u_id){
//f_status
//1为u1关注u2
//2为u1与u2互相关注
//获得关注的人数量
String sql_getFansAmountById="SELECT count(*) FROM `microblog`.`friend` where u_id = ? and f_status = 1";
String sql_getFansAmountById2="SELECT count(*) FROM `microblog`.`friend` where u_id = ? and f_status = 2";
String sql_getFansAmountById3="SELECT count(*) FROM `microblog`.`friend` where u_id2 = ? and f_status = 2";
DBConn dbConn = new DBConn();
ResultSet rs = dbConn.execQuery(sql_getFansAmountById, new Object[]{u_id});
ResultSet rs2 = dbConn.execQuery(sql_getFansAmountById2, new Object[]{u_id});
ResultSet rs3 = dbConn.execQuery(sql_getFansAmountById3, new Object[]{u_id});
long amount = 0;
long amount2 = 0;
long amount3 = 0;
try {
while(rs.next()){
amount=rs.getLong("count(*)");
}
while(rs2.next()){
amount2=rs2.getLong("count(*)");
}
while(rs3.next()){
amount3=rs3.getLong("count(*)");
}
return (amount+amount2+amount3);
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
rs.close();
rs2.close();
rs3.close();
} catch (SQLException e) {
e.printStackTrace();
}
dbConn.closeConn();
}
return amount;
}
public long getFansedAmountById(final int u_id){
//f_status
//1为u1关注u2
//2为u1与u2互相关注
//获得被关注的数量
String sql_getFansedAmountById="SELECT count(*) FROM `microblog`.`friend` where u_id2 = ? and f_status = 1";
String sql_getFansedAmountById2="SELECT count(*) FROM `microblog`.`friend` where u_id2 = ? and f_status = 2";
String sql_getFansedAmountById3="SELECT count(*) FROM `microblog`.`friend` where u_id = ? and f_status = 2";
DBConn dbConn = new DBConn();
ResultSet rs = dbConn.execQuery(sql_getFansedAmountById, new Object[]{u_id});
ResultSet rs2 = dbConn.execQuery(sql_getFansedAmountById2, new Object[]{u_id});
ResultSet rs3 = dbConn.execQuery(sql_getFansedAmountById3, new Object[]{u_id});
long amount = 0;
long amount2 = 0;
long amount3 = 0;
try {
while(rs.next()){
amount=rs.getLong("count(*)");
}
while(rs2.next()){
amount2=rs2.getLong("count(*)");
}
while(rs3.next()){
amount3=rs3.getLong("count(*)");
}
return (amount+amount2+amount3);
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
rs.close();
rs2.close();
rs3.close();
} catch (SQLException e) {
e.printStackTrace();
}
dbConn.closeConn();
}
return amount;
}
public long getFriendAmountById(final int u_id){
//获得互相关注的好友的数量
//f_status
//1为u1关注u2
//2为u1与u2互相关注
String sql_getFriendAmount="SELECT count(*) FROM `microblog`.`friend` where u_id = ? and f_status = 2";
String sql_getFriendAmount2="SELECT count(*) FROM `microblog`.`friend` where u_id2 = ? and f_status = 2";
DBConn dbConn = new DBConn();
ResultSet rs = dbConn.execQuery(sql_getFriendAmount, new Object[]{u_id});
ResultSet rs2 = dbConn.execQuery(sql_getFriendAmount2, new Object[]{u_id});
long amount = 0;
long amount2 = 0;
try {
while(rs.next()){
amount=rs.getLong("count(*)");
}
while(rs2.next()){
amount2=rs2.getLong("count(*)");
}
return amount+amount2;
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
rs.close();
rs2.close();
} catch (SQLException e) {
e.printStackTrace();
}
dbConn.closeConn();
}
return 0;
}
public List<Integer> getFansIdListbyUserId(final int u_id){
List<Integer> fansIdList = new ArrayList<Integer>();
String sql_getFansListbyUserId = "select u_id2 from friend where u_id = ? and f_status = 1";
String sql_getFansListbyUserId2 = "select u_id2 from friend where u_id = ? and f_status = 2";
String sql_getFansListbyUserId3 = "select u_id from friend where u_id2 = ? and f_status = 2";
DBConn dbConn = new DBConn();
ResultSet rs = dbConn.execQuery(sql_getFansListbyUserId, new Object[]{u_id});
ResultSet rs2 = dbConn.execQuery(sql_getFansListbyUserId2, new Object[]{u_id});
ResultSet rs3 = dbConn.execQuery(sql_getFansListbyUserId3, new Object[]{u_id});
try {
while(rs.next()){
fansIdList.add(rs.getInt("u_id2"));
}
while(rs2.next()){
fansIdList.add(rs2.getInt("u_id2"));
}
while(rs3.next()){
fansIdList.add(rs3.getInt("u_id"));
}
return fansIdList;
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
rs.close();
rs2.close();
rs3.close();
} catch (SQLException e) {
e.printStackTrace();
}
dbConn.closeConn();
}
return null;
}
public List<Integer> getFansedIdListbyUserId(final int u_id){
List<Integer> fansIdList = new ArrayList<Integer>();
String sql_getFansedListbyUserId = "select u_id from friend where u_id2 = ? and f_status = 2";
String sql_getFansedListbyUserId3 = "select u_id2 from friend where u_id = ? and f_status = 2";
String sql_getFansedListbyUserId2 = "select u_id from friend where u_id2 = ? and f_status = 1";
DBConn dbConn = new DBConn();
ResultSet rs = dbConn.execQuery(sql_getFansedListbyUserId, new Object[]{u_id});
ResultSet rs2 = dbConn.execQuery(sql_getFansedListbyUserId2, new Object[]{u_id});
ResultSet rs3 = dbConn.execQuery(sql_getFansedListbyUserId3, new Object[]{u_id});
try {
while(rs.next()){
fansIdList.add(rs.getInt("u_id"));
}
while(rs2.next()){
fansIdList.add(rs2.getInt("u_id"));
}
while(rs3.next()){
fansIdList.add(rs3.getInt("u_id2"));
}
return fansIdList;
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
rs.close();
rs2.close();
rs3.close();
} catch (SQLException e) {
e.printStackTrace();
}
dbConn.closeConn();
}
return null;
}
/*
public static void main(String[] args) {
Friend friend = new Friend();
friend.setU_id(1);
friend.setU_id2(6);
FriendDao friendDao = new FriendDao();
System.out.println(friendDao.addFriend(friend));
System.out.println(friendDao.getFansAmountById(1));
System.out.println(friendDao.getFansedAmountById(1));
System.out.println(friendDao.getFriendAmountById(1));
System.out.println(friendDao.isFriend(2, 5));
}
*/
}
最近下载更多
最近浏览更多
