@Override
public DataSource get() {
if (this.cloud == null) {
return DB.getDataSource();
}
try {
return this.cloud.getSingletonServiceConnector(DataSource.class, null);
} catch (CloudException e) {
return DB.getDataSource();
}
}
/**
* 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 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 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 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 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 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);
}
}
}
}
/**
* 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;
}
/**
* 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;
}
@Override
public void invocationFinally() {
DB.close();
}
@Override
public void onApplicationStart() {
if (Play.mode.isProd() && SienaPlugin.dbType().contains("google")) {
try {
Properties p = Play.configuration;
if (DB.datasource != null) {
DB.destroy();
}
if (p.getProperty("db", "").startsWith("java:")) {
Context ctx = new InitialContext();
DB.datasource = (DataSource) ctx.lookup(p.getProperty("db"));
} else {
// Try the driver
String driver = p.getProperty("db.driver");
try {
Driver d = (Driver) Class.forName(driver, true, Play.classloader).newInstance();
DriverManager.registerDriver(new ProxyDriver(d));
} catch (Exception e) {
throw new Exception("Driver not found (" + driver + ")");
}
// Try the connection
Connection fake = null;
try {
if (p.getProperty("db.user") == null) {
fake = DriverManager.getConnection(p.getProperty("db.url"));
} else {
fake =
DriverManager.getConnection(
p.getProperty("db.url"), p.getProperty("db.user"), p.getProperty("db.pass"));
}
} finally {
if (fake != null) {
fake.close();
}
}
// apache basic DS
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName(p.getProperty("db.driver"));
ds.setUsername(p.getProperty("db.user"));
ds.setPassword(p.getProperty("db.pass"));
ds.setUrl(p.getProperty("db.url"));
DB.datasource = ds;
url = ds.getUrl();
Connection c = null;
try {
c = ds.getConnection();
} finally {
if (c != null) {
c.close();
}
}
// Logger.info("Connected to %s", ds.getJdbcUrl());
Logger.info("Connected to %s", url);
}
DB.destroyMethod = p.getProperty("db.destroyMethod", "");
} catch (Exception e) {
DB.datasource = null;
Logger.error(e, "Cannot connected to the database : %s", e.getMessage());
if (e.getCause() instanceof InterruptedException) {
throw new DatabaseException(
"Cannot connected to the database. Check the configuration.", e);
}
throw new DatabaseException("Cannot connected to the database, " + e.getMessage(), e);
}
}
}
@Override
public void onApplicationStop() {
if (Play.mode.isProd()) {
DB.destroy();
}
}
@Override
public void onApplicationStart() {
// must check and configure JPA for each DBConfig
for (DBConfig dbConfig : DB.getDBConfigs()) {
// check and enable JPA on this config
// is JPA already configured?
String configName = dbConfig.getDBConfigName();
if (JPA.getJPAConfig(configName, true) == null) {
// must configure it
// resolve prefix for hibernate config..
// should be nothing for default, and db_<name> for others
String propPrefix = "";
if (!DBConfig.defaultDbConfigName.equalsIgnoreCase(configName)) {
propPrefix = "db_" + configName + ".";
}
List<Class> classes = findEntityClassesForThisConfig(configName, propPrefix);
if (classes == null) continue;
// we're ready to configure this instance of JPA
final String hibernateDataSource =
Play.configuration.getProperty(propPrefix + "hibernate.connection.datasource");
if (StringUtils.isEmpty(hibernateDataSource) && dbConfig == null) {
throw new JPAException(
"Cannot start a JPA manager without a properly configured database"
+ getConfigInfoString(configName),
new NullPointerException("No datasource configured"));
}
Ejb3Configuration cfg = new Ejb3Configuration();
if (dbConfig.getDatasource() != null) {
cfg.setDataSource(dbConfig.getDatasource());
}
if (!Play.configuration
.getProperty(propPrefix + "jpa.ddl", Play.mode.isDev() ? "update" : "none")
.equals("none")) {
cfg.setProperty(
"hibernate.hbm2ddl.auto",
Play.configuration.getProperty(propPrefix + "jpa.ddl", "update"));
}
String driver = null;
if (StringUtils.isEmpty(propPrefix)) {
driver = Play.configuration.getProperty("db.driver");
} else {
driver = Play.configuration.getProperty(propPrefix + "driver");
}
cfg.setProperty("hibernate.dialect", getDefaultDialect(propPrefix, driver));
cfg.setProperty("javax.persistence.transaction", "RESOURCE_LOCAL");
cfg.setInterceptor(new PlayInterceptor());
// This setting is global for all JPAs - only configure if configuring default JPA
if (StringUtils.isEmpty(propPrefix)) {
if (Play.configuration.getProperty(propPrefix + "jpa.debugSQL", "false").equals("true")) {
org.apache.log4j.Logger.getLogger("org.hibernate.SQL").setLevel(Level.ALL);
} else {
org.apache.log4j.Logger.getLogger("org.hibernate.SQL").setLevel(Level.OFF);
}
}
// inject additional hibernate.* settings declared in Play! configuration
Properties additionalProperties =
(Properties)
Utils.Maps.filterMap(Play.configuration, "^" + propPrefix + "hibernate\\..*");
// We must remove prefix from names
Properties transformedAdditionalProperties = new Properties();
for (Map.Entry<Object, Object> entry : additionalProperties.entrySet()) {
Object key = entry.getKey();
if (!StringUtils.isEmpty(propPrefix)) {
key = ((String) key).substring(propPrefix.length()); // chop off the prefix
}
transformedAdditionalProperties.put(key, entry.getValue());
}
cfg.addProperties(transformedAdditionalProperties);
try {
// nice hacking :) I like it..
Field field = cfg.getClass().getDeclaredField("overridenClassLoader");
field.setAccessible(true);
field.set(cfg, Play.classloader);
} catch (Exception e) {
Logger.error(
e, "Error trying to override the hibernate classLoader (new hibernate version ???)");
}
for (Class<?> clazz : classes) {
cfg.addAnnotatedClass(clazz);
if (Logger.isTraceEnabled()) {
Logger.trace("JPA Model : %s", clazz);
}
}
String[] moreEntities =
Play.configuration.getProperty(propPrefix + "jpa.entities", "").split(", ");
for (String entity : moreEntities) {
if (entity.trim().equals("")) {
continue;
}
try {
cfg.addAnnotatedClass(Play.classloader.loadClass(entity));
} catch (Exception e) {
Logger.warn("JPA -> Entity not found: %s", entity);
}
}
for (ApplicationClass applicationClass : Play.classes.all()) {
if (applicationClass.isClass() || applicationClass.javaPackage == null) {
continue;
}
Package p = applicationClass.javaPackage;
Logger.info("JPA -> Adding package: %s", p.getName());
cfg.addPackage(p.getName());
}
String mappingFile = Play.configuration.getProperty(propPrefix + "jpa.mapping-file", "");
if (mappingFile != null && mappingFile.length() > 0) {
cfg.addResource(mappingFile);
}
if (Logger.isTraceEnabled()) {
Logger.trace("Initializing JPA" + getConfigInfoString(configName) + " ...");
}
try {
JPA.addConfiguration(configName, cfg);
} catch (PersistenceException e) {
throw new JPAException(
e.getMessage() + getConfigInfoString(configName),
e.getCause() != null ? e.getCause() : e);
}
}
}
// must look for Entity-objects referring to none-existing JPAConfig
List<Class> allEntityClasses = Play.classloader.getAnnotatedClasses(Entity.class);
for (Class clazz : allEntityClasses) {
String configName = Entity2JPAConfigResolver.getJPAConfigNameForEntityClass(clazz);
if (JPA.getJPAConfig(configName, true) == null) {
throw new JPAException(
"Found Entity-class ("
+ clazz.getName()
+ ") referring to none-existing JPAConfig"
+ getConfigInfoString(configName)
+ ". "
+ "Is JPA properly configured?");
}
}
}
public QueryDB() {
connection = DB.getConnection();
}
public static Connection getConnection() {
Connection connection = DB.getConnection();
createURLTable(connection);
return connection;
}
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));
}
