/**
* FindById Searches the database for a Group object by the object id.
*
* @param connection The jdbc connection
* @param id The id to select by
*/
public static Post FindByID(Long post_ID) throws SQLException {
Post post = null;
PreparedStatement findPostId = null;
Connection conn = null;
String sql = String.format("SELECT * from %s where postid = ?", Post.dquote(POST), post_ID);
if (post_ID != null) {
try {
conn = DB.getConnection();
findPostId = conn.prepareStatement(sql);
findPostId.setLong(1, post_ID);
ResultSet rs = findPostId.executeQuery();
if (rs.next()) {
post =
new Post(
rs.getString(Post.CONTENT),
rs.getString(Post.TYPE),
rs.getTimestamp(Post.TIMESTAMP),
rs.getLong(Post.USER_ID),
rs.getLong(Post.WALL_ID),
rs.getLong(Post.POST_ID));
}
findPostId.close();
conn.close();
return post;
} catch (SQLException e) {
Logger.debug("Error retrieving post.", e);
}
}
return post;
}
public static void deletePost(Long post_ID) throws SQLException {
Post post = null;
String deletePostStr =
String.format("DELETE from %s where postid = ?", Post.dquote(POST), post_ID);
Connection conn = null;
PreparedStatement deleteP = null;
if (post_ID != null) {
try {
conn = DB.getConnection();
deleteP = conn.prepareStatement(deletePostStr);
ResultSet rs = deleteP.executeQuery();
if (rs.next()) {
Logger.debug("Failed to delete the Post.");
}
deleteP.close();
conn.close();
} catch (SQLException e) {
Logger.debug("Failed while trying to delete the post.");
}
} else {
Logger.debug("Post id is null.");
}
}
public static ResultSet getAllNewVolume(EnumDataSet workingSet) {
Connection connection2 = DB.getConnection();
ResultSet rs = null;
PreparedStatement preparedStatement = null;
try {
if (workingSet.equals(EnumDataSet.TWEETS)) {
preparedStatement =
connection2.prepareStatement(
"SELECT \"UniqueTweets\" FROM \"FinalProject\".\"TweetsInInterval\" WHERE \"DateTimeStart\" > '2013-06-23 19:18:25.025+01' ORDER BY \"DateTimeFinish\" ASC");
} else if (workingSet.equals(EnumDataSet.RETWEETS)) {
preparedStatement =
connection2.prepareStatement(
"SELECT \"UniqueReTweets\" FROM \"FinalProject\".\"ReTweetsInInterval\" WHERE \"DateTimeStart\" > '2013-06-23 19:18:25.025+01' ORDER BY \"DateTimeFinish\" ASC");
}
rs = preparedStatement.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
connection2.close();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public static void contents(Integer convID, Integer fromMsgID) {
if (fromMsgID == null) {
fromMsgID = 0;
}
Connection c = DB.getConnection();
try {
PreparedStatement stmt =
c.prepareStatement(
"select M.id, UR.name, U.id, M.contents, M.timeSent from `Person` as U, `Conversations` as C, `Messages` as M, `UserRoles` as UR "
+ "where UR.id = U.role_id and C.id=M.conversation_id and U.id=M.user_id and C.id = ? and M.id>? "
+ "ORDER BY M.id");
stmt.setInt(1, convID);
stmt.setInt(2, fromMsgID);
ResultSet rs = stmt.executeQuery();
rs.beforeFirst();
ArrayList<ChatMessage> msgdata = new ArrayList<ChatMessage>();
Integer maxID = fromMsgID;
while (rs.next()) {
ChatMessage msg =
new ChatMessage(
rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
if (msg.name.equals(Security.connected())) {
msg.name = "You";
msg.role = "";
}
maxID = Math.max(maxID, msg.id);
msgdata.add(msg);
}
render(msgdata, maxID);
} catch (SQLException e) {
e.printStackTrace();
}
}
public static ResultSet getTopTweetedUsers(String topTweetorAvg) {
Connection connection2 = DB.getConnection();
ResultSet rs = null;
PreparedStatement preparedStatement;
try {
if (topTweetorAvg.equals("TOP")) {
preparedStatement =
connection2.prepareStatement(
"SELECT \"User\", \"UserName\", \"Total No. Of Tweets\", \"Total Retweets\", \"AverageRetweets\", \"Followers\", \"Followees\", \"Location\", \"Timezone\" FROM \"FinalProject\".\"total_retweets_for_user\" ORDER BY \"Total Retweets\" DESC LIMIT 60");
} else {
preparedStatement =
connection2.prepareStatement(
"SELECT \"User\", \"UserName\", \"Followers\", \"Followees\", \"Location\", \"Timezone\" FROM \"FinalProject\".\"total_retweets_for_user\" ORDER BY \"Average Retweets\" DESC LIMIT 60");
}
rs = preparedStatement.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
try {
connection2.close();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public static ResultSet getLatestTweets() {
Connection connection2 = DB.getConnection();
ResultSet rs = null;
try {
PreparedStatement preparedStatement =
connection2.prepareStatement(
"("
+ "SELECT \"UserName\", \"DateTime\", \"Blog\" "
+ "FROM \"FinalProject\".\"ReTweets\" "
+ "ORDER BY \"DateTime\" DESC "
+ "LIMIT 20"
+ ")"
+ "UNION"
+ "("
+ "SELECT \"UserName\", \"DateTime\", \"Blog\" "
+ "FROM \"FinalProject\".\"Tweets\" "
+ "ORDER BY \"DateTime\" DESC "
+ "LIMIT 20"
+ ")"
+ "ORDER BY \"DateTime\" DESC "
+ "LIMIT 10"
+ ";");
rs = preparedStatement.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
try {
connection2.close();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public static ResultSet executeList(String sql, List<Object> params) throws SQLException {
PreparedStatement pst =
DB.getConnection()
.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
int index = 0;
for (Object param : params) {
pst.setObject(++index, param);
}
return pst.executeQuery();
}
private static Result publishPrivateTweet(String owner_id, String content, String device) {
TweetResult tweetResult;
content = TextContentUtil.processTweetContent(content);
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
String insertTweet =
"INSERT INTO t_private_tweet (owner_id, create_at, sysversion, content, device) VALUES( '%s', now(), 'mac', '%s', '%s')";
String queryTweet = "SELECT * FROM t_private_tweet where id = ";
String queryLastRowId = "SELECT LAST_INSERT_ID()";
content = content.replaceAll("'", "''");
device = device.replaceAll("'", "''");
insertTweet = String.format(insertTweet, owner_id, content, device);
long rowId;
try {
connection = DB.getConnection();
statement = connection.createStatement();
statement.executeUpdate(insertTweet);
resultSet = statement.executeQuery(queryLastRowId);
resultSet.next();
rowId = resultSet.getLong("LAST_INSERT_ID()");
resultSet = statement.executeQuery(queryTweet + rowId);
resultSet.next(); // attention
Maopao maopao = new Maopao(resultSet);
tweetResult = new TweetResult(0, maopao);
} catch (SQLException e) {
e.printStackTrace();
tweetResult = new TweetResult(-1, null);
} finally {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return ok(Json.toJson(tweetResult));
}
public static boolean isAlreadyPresent(String givenUrl) throws SQLException {
// System.out.println("checking given url : " + givenUrl);
Connection connection = DB.getConnection();
String query = "select fbPageId from FBPage where givenUrl = ?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, givenUrl);
ResultSet rs = statement.executeQuery();
boolean present = false;
if (rs.first()) {
present = true;
}
rs.close();
connection.close();
return present;
}
public static ResultSet getAvgRetweetsForUser() {
Connection connection2 = DB.getConnection();
ResultSet rs = null;
try {
PreparedStatement preparedStatement =
connection2.prepareStatement(
"SELECT \"AverageRetweets\" FROM \"FinalProject\".\"total_retweets_for_user\" ORDER BY \"AverageRetweets\" ASC");
rs = preparedStatement.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
try {
connection2.close();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
/** Persist Saves the post to the database, this function will create a new database connection */
public void Persist() {
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = DB.getConnection();
String sql = "UPDATE " + User.dquote(Post.POST);
sql += " set " + Post.CONTENT + " = ?, ";
sql += Post.TYPE + " = ?, ";
sql += Post.TIMESTAMP + " = ?, ";
sql += Post.USER_ID + " = ?, ";
sql += Post.WALL_ID + " = ?, ";
sql += "where " + Post.POST_ID + " = ?";
Logger.debug("Generated update: [%s]", sql);
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, this.content);
pstmt.setString(2, this.type);
pstmt.setTimestamp(3, this.time_stamp);
pstmt.setLong(4, this.user_ID);
pstmt.setLong(5, this.wall_ID);
pstmt.setLong(6, this.post_ID);
pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch (SQLException e) {
// Attempt to close the connection
Logger.debug("Error while persisting Post");
if (conn != null) {
try {
conn.close();
} catch (Exception x) {
Logger.debug("Error while closing connection during exception", x);
}
}
}
}
/**
* Busca un usuario por su documento
*
* @param documento String documento el usuario
* @return objeto de tipo Usuario encontrado o null en caso de que no se encuentre un usuario con
* ese documento.
*/
public static Usuario findByDocumento(String documento) {
Connection con = DB.getConnection();
Usuario usuario = null;
PreparedStatement p;
try {
p = con.prepareStatement(consultaBuscarPorDocumento);
p.setString(1, documento);
ResultSet rs = p.executeQuery();
if (rs.next()) {
usuario =
new Usuario(rs.getString("nombre"), rs.getString("documento"), rs.getString("id_rol"));
}
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("Excepcion : " + e.getMessage());
System.out.println(e.getLocalizedMessage());
}
return usuario;
}
/**
* Método que devuelve una lista de Usuarios realizando una búsqueda por el nombre o parte del
* nombre. Se utiliza para la función de autocompletar en la interfaz.
*
* @param termino String con el nombre o parte el nombre del usuario a buscar
* @return Lista de usuarios que cumplen el criterio de búsqueda.
*/
public static List<Usuario> findAllByNombre(String termino) {
Connection con = DB.getConnection();
List<Usuario> usuarios = new ArrayList<Usuario>();
PreparedStatement p;
try {
p = con.prepareStatement(consultaUsuarios);
p.setString(1, "%%" + termino + "%%");
ResultSet rs = p.executeQuery();
while (rs.next()) {
usuarios.add(new Usuario("", rs.getString("nombres"), "", rs.getString("documento"), ""));
}
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("Excepcion : " + e.getMessage());
System.out.println(e.getLocalizedMessage());
}
return usuarios;
}
public static Result privateTweets(long last_id, String sort, int limit) {
String result = "";
List<Maopao> maopaos = null;
Maopao maopao;
MaopaoList maopaoList = new MaopaoList();
String uid = session("id");
if (uid == null || uid.length() == 0) {
maopaoList.setCode(1000);
return ok(Json.toJson(maopaoList));
}
String orderBy = "id";
if ("hot".equals(sort)) {
orderBy = "comment_count";
}
if (limit <= 0) {
limit = 30;
}
Connection connection = null;
Statement maopaoStatement = null;
Statement commentStatement = null;
Statement likeUserStatement = null;
Statement ownerStatement = null;
ResultSet maopaoResultSet = null;
ResultSet commentResultSet = null;
ResultSet likeUsersResultSet = null;
ResultSet ownerResultSet = null;
long tweetId;
long maxTweetId;
String where = " owner_id = " + uid + " ";
String tableComment = "t_comment";
String tableMaopao = "t_private_tweet";
try {
connection = DB.getConnection();
maopaoStatement = connection.createStatement();
commentStatement = connection.createStatement();
likeUserStatement = connection.createStatement();
ownerStatement = connection.createStatement();
maxTweetId = DBUtil.queryMaxId(maopaoStatement, tableMaopao);
if (last_id > maxTweetId) {
maopaoResultSet =
DBUtil.queryLastRecord(maopaoStatement, tableMaopao, where, orderBy, limit);
} else {
maopaoResultSet =
DBUtil.queryLessLastRecord(
maopaoStatement, tableMaopao, where, orderBy, "" + last_id, limit);
}
maopaos = new ArrayList<Maopao>();
while (maopaoResultSet.next()) {
maopao = new Maopao(maopaoResultSet);
tweetId = Long.parseLong(maopao.id);
String userSql = "SELECT * FROM t_user WHERE id = " + maopao.owner_id;
ownerResultSet = ownerStatement.executeQuery(userSql);
ownerResultSet.next();
maopao.owner = new UserObject(ownerResultSet);
if (maopao.likes > 0) {
maopao.like_users = new ArrayList<UserObject>();
String sql =
"SELECT * FROM t_user WHERE id IN (SELECT owner_id FROM t_like_tweet WHERE tweet_id = '%s')";
sql = String.format(sql, tweetId);
likeUsersResultSet = likeUserStatement.executeQuery(sql);
UserObject userObject;
while (likeUsersResultSet.next()) {
userObject = new UserObject(likeUsersResultSet);
maopao.like_users.add(userObject);
if (userObject.id.equals(session("id"))) {
maopao.liked = true;
}
}
}
if (maopao.comments > 0) {
maopao.comment_list = new ArrayList<BaseComment>();
String sql =
"SELECT t_user.id, t_user.name, t_user.head_url, t_comment.id, t_comment.content, t_comment.create_at, t_user.created_at "
+ "FROM t_user INNER JOIN t_comment ON t_user.id = t_comment.owner_id WHERE tweet_id = %s";
sql = String.format(sql, tweetId);
BaseComment comment;
commentResultSet = commentStatement.executeQuery(sql);
while (commentResultSet.next()) {
// BaseComment(String id, String owner_id, String tweet_id, String content, String
// created_at)
comment =
new BaseComment(
commentResultSet.getString(4),
commentResultSet.getString(1),
tweetId + "",
commentResultSet.getString(5),
commentResultSet.getTimestamp(6).getTime());
// UserObject(long id, String name, String headImgUrl, long created_at){
comment.owner =
new UserObject(
commentResultSet.getLong(1),
commentResultSet.getString(2),
commentResultSet.getString(3),
commentResultSet.getLong(7));
maopao.comment_list.add(comment);
}
}
maopaos.add(maopao);
}
maopaoList.setCode(0);
maopaoList.setData(maopaos);
} catch (SQLException e) {
e.printStackTrace();
maopaoList.setCode(-1);
maopaoList.setData(null);
} finally {
try {
if (maopaoResultSet != null) {
maopaoResultSet.close();
}
if (commentResultSet != null) {
commentResultSet.close();
}
if (maopaoStatement != null) {
maopaoStatement.close();
}
if (commentStatement != null) {
commentStatement.close();
}
if (likeUsersResultSet != null) {
likeUsersResultSet.close();
}
if (likeUserStatement != null) {
likeUserStatement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
}
}
return ok(Json.toJson(maopaoList));
}
public static Connection getConnection() {
Connection connection = DB.getConnection();
createURLTable(connection);
return connection;
}
public QueryDB() {
connection = DB.getConnection();
}