public int unfolw(User user) {
int result = 0;
String sql =
"DELETE FROM tb_follow WHERE user_id = "
+ user.GetId()
+ " AND userD_id = "
+ user.getDid()
+ ";";
System.out.println(sql);
try {
result = connDB.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
connDB.close();
}
return result;
}
public int unlike(Reply reply) {
int result = 0;
String sql =
"DELETE FROM tb_like WHERE user_id="
+ reply.getUserId()
+ " AND rep_id="
+ reply.getId()
+ ";";
System.out.println(sql);
try {
result = connDB.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
connDB.close();
updateData();
}
return result;
}
public int like(Reply reply) {
int result = 0;
String sql =
"INSERT INTO tb_like(`user_id`,`rep_id`)"
+ "VALUES("
+ reply.getUserId()
+ ","
+ reply.getId()
+ ");";
System.out.println(sql);
try {
result = connDB.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
connDB.close();
updateData();
}
return result;
}
public int follow(User user) {
int result = 0;
String sql =
"INSERT INTO tb_follow(`user_id`,`userD_id`)"
+ "VALUES("
+ user.GetId()
+ ","
+ user.getDid()
+ ");";
System.out.println(sql);
try {
result = connDB.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
connDB.close();
}
return result;
}
public int queryFolw(User user) {
int result = 0;
String sql =
"SELECT COUNT(user_id) FROM tb_follow WHERE "
+ "user_id="
+ user.GetId()
+ " AND userD_id="
+ user.getDid()
+ ";";
System.out.println(sql);
ResultSet resultSet = connDB.executeQuery(sql);
try {
while (resultSet.next()) {
result = resultSet.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
connDB.close();
}
return result;
}
public int queryLike(Reply reply) {
int result = 0;
String sql =
"SELECT COUNT(user_id) FROM tb_like WHERE "
+ "user_id="
+ reply.getUserId()
+ " AND rep_id="
+ reply.getId()
+ ";";
System.out.println(sql);
ResultSet resultSet = connDB.executeQuery(sql);
try {
while (resultSet.next()) {
result = resultSet.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
connDB.close();
}
return result;
}
public void updateData() {
String sqlLike1 = "DROP TABLE IF EXISTS tmp_like;";
String sqlLike2 =
"CREATE TABLE tmp_like "
+ "SELECT r.rep_id,count(l.user_id) "
+ "FROM tb_like l JOIN tb_reply r "
+ "on l.rep_id=r.rep_id "
+ "group by l.rep_id;";
String sqlLike = "UPDATE tb_reply SET rep_like = 0;";
String sqlLike3 =
"UPDATE tb_reply r INNER JOIN tmp_like l "
+ "ON r.rep_id=l.rep_id "
+ "SET r.rep_like=l.`count(l.user_id)`;";
String sqlRep1 = "DROP TABLE IF EXISTS tmp_Rep;";
String sqlRep2 =
"CREATE TABLE tmp_Rep "
+ "SELECT p.post_id,count(r.user_id) "
+ "FROM tb_reply r JOIN tb_post p "
+ "on r.post_id=p.post_id "
+ "group by r.post_id;";
String sqlRep = "UPDATE tb_post SET post_reply = 0";
String sqlRep3 =
"UPDATE tb_post p INNER JOIN tmp_Rep r "
+ "ON p.post_id=r.post_id "
+ "SET p.post_reply=r.`count(r.user_id)`;";
System.out.println(sqlLike1 + "\n" + sqlLike2 + "\n" + sqlLike3);
System.out.println(sqlRep1 + "\n" + sqlRep2 + "\n" + sqlRep3);
try {
connDB.executeUpdate(sqlLike1);
connDB.executeUpdate(sqlLike2);
connDB.executeUpdate(sqlLike);
connDB.executeUpdate(sqlLike3);
connDB.executeUpdate(sqlRep1);
connDB.executeUpdate(sqlRep2);
connDB.executeUpdate(sqlRep);
connDB.executeUpdate(sqlRep3);
} catch (Exception e) {
e.printStackTrace();
// TODO: handle exception
} finally {
connDB.close();
}
}
public void updateDataFolw() {
String sqlFolw1 = "DROP TABLE IF EXISTS tmp_folw;";
String sqlFolw2 =
"CREATE TABLE tmp_folw "
+ "SELECT u.user_id,count(f.user_id) "
+ "FROM tb_follow f JOIN tb_user u "
+ "on f.userD_id=u.user_id "
+ "group by u.user_id;";
String sqlFolw = "UPDATE tb_user SET userD_folw = 0;";
String sqlFolw3 =
"UPDATE tb_user u INNER JOIN tmp_folw f "
+ "ON u.user_id=f.user_id "
+ "SET u.userD_folw=f.`count(f.user_id)`;";
String sqlFolwD1 = "DROP TABLE IF EXISTS tmp_folwD;";
String sqlFolwD2 =
"CREATE TABLE tmp_folwD "
+ "SELECT u.user_id,count(f.userD_id) "
+ "FROM tb_follow f JOIN tb_user u "
+ "on f.user_id=u.user_id "
+ "group by u.user_id;";
String sqlFolwD = "UPDATE tb_user SET user_folw = 0;";
String sqlFolwD3 =
"UPDATE tb_user u INNER JOIN tmp_folwD f "
+ "ON u.user_id=f.user_id "
+ "SET u.user_folw=f.`count(f.userD_id)`;";
System.out.println(sqlFolwD1 + "\n" + sqlFolwD2 + "\n" + sqlFolwD3);
System.out.println(sqlFolw1 + "\n" + sqlFolw2 + "\n" + sqlFolw3);
try {
connDB.executeUpdate(sqlFolw1);
connDB.executeUpdate(sqlFolw2);
connDB.executeUpdate(sqlFolw);
connDB.executeUpdate(sqlFolw3);
connDB.executeUpdate(sqlFolwD1);
connDB.executeUpdate(sqlFolwD2);
connDB.executeUpdate(sqlFolwD);
connDB.executeUpdate(sqlFolwD3);
} catch (Exception e) {
e.printStackTrace();
// TODO: handle exception
} finally {
connDB.close();
}
}