@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)); }