@SuppressWarnings("unchecked") private void deleteOperation(DataStoreOperation op) throws SQLException { Comparable key = ((DeleteOperation) op).getKey(); String tablename = ((DeleteOperation) op).getTableName(); java.sql.Connection conn = null; // System.out.println("OP:"+op.toString()); if (tablename.equals("friendsTimeLine")) { List<String> pos = ((DeleteOperation) op).getColumns(); conn = this.borrowClient(); String query = "delete from " + tablename + " where userID = '" + (String) key + "' and date = ?"; PreparedStatement stmt = null; try { stmt = conn.prepareStatement(query); } catch (SQLException e) { // TODO Auto-generated catch block // e.printStackTrace(); System.out.println("prepare"); } // System.out.println(query); // System.out.println(pos.toString()); for (String str : pos) { // System.out.println("delete 2"); try { stmt.setString(1, str); } catch (SQLException e) { System.out.println("setString" + query + "=" + str); } // System.out.println(query+"="+str); // System.out.println(stmt.toString()); try { stmt.executeUpdate(); } catch (SQLException e) { // e.printStackTrace(); System.out.println("EXCEP" + query + "=" + str); System.out.println("EXCEP" + stmt.toString()); } } try { stmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { this.releaseClient(conn); } } ((DeleteOperation) op).setResult(true); op.notifyListeners(); }
@SuppressWarnings("unchecked") private void getByTagsOperation(DataStoreOperation op) throws UnsupportedEncodingException, SQLException { Set<String> tags = ((GetByTagsOperation) op).getTags(); Set<Map<String, String>> result = new HashSet<Map<String, String>>(); // int i=0; // String auxTag; for (String tag : tags) { if (tag != null) { /*if(i==0){ } else {*/ String query = null; if (tag.startsWith("topic")) { // TOPIC // auxTag = "#"+tag.toString(); query = "select tweets.tweetID,id,text,date,user from (select tweetID from tweetsTags where topic = ?) as res inner join tweets on res.tweetID = tweets.tweetID order by date DESC"; } else if (tag.startsWith("user")) { // USER // auxTag = "@"+tag.toString(); query = "select tweets.tweetID,id,text,date,user from (select tweetID from tweetsTags where user = ?) as res inner join tweets on res.tweetID = tweets.tweetID order by date DESC"; } java.sql.Connection conn = null; conn = this.borrowClient(); PreparedStatement stmt = conn.prepareStatement(query); ResultSet results = null; try { stmt.setString(1, tag); results = stmt.executeQuery(); while (results.next()) { Map<String, String> tweet = new HashMap<String, String>(); tweet.put("id", results.getString("id")); tweet.put("text", results.getString("text")); tweet.put("date", results.getString("date")); tweet.put("user", results.getString("user")); result.add(tweet); } } finally { try { stmt.close(); } catch (Exception e) { } this.releaseClient(conn); } // } } // i++; } ((GetByTagsOperation) op).setResult(result); op.notifyListeners(); }
@SuppressWarnings("unchecked") private void getRangeOperation(DataStoreOperation op) throws UnsupportedEncodingException, SQLException { /////////////// POR ISTO EM PARALELO COMO O MULIPUT Comparable keyMax = ((GetRangeOperation) op).getMax(); Comparable keyMin = ((GetRangeOperation) op).getMin(); String tablename = ((GetRangeOperation) op).getTableName(); int idMax = this.getTweetID((String) keyMax); int idMin = this.getTweetID((String) keyMin); String userID = this.getUserID((String) keyMax); if (tablename.equals("tweets")) { java.sql.Connection conn = null; conn = this.borrowClient(); // String query = "select id,text,date,user from "+tablename+" tweetID between ? AND ?"; String query = "select id,text,date,user from " + tablename + " where tweetID = ?"; PreparedStatement stmt = conn.prepareStatement(query); ResultSet results = null; Set<Map<String, String>> result = new HashSet<Map<String, String>>(); for (int i = idMin; i <= idMax; i++) { try { stmt.setString(1, new String(userID + "-" + this.getTweetPadding(i))); // System.out.println(stmt.toString()); results = stmt.executeQuery(); while (results.next()) { Map<String, String> value = new HashMap<String, String>(); value.put("id", results.getString("id")); value.put("text", results.getString("text")); value.put("date", results.getString("date")); value.put("user", results.getString("user")); result.add(value); } } finally { try { // stmt.close(); } catch (Exception e) { } } } stmt.close(); this.releaseClient(conn); ((GetRangeOperation) op).setResult(result); op.notifyListeners(); } }
private void releaseClient(java.sql.Connection conn) { try { conn.close(); // System.out.println("Disconnected from database"); } catch (Exception e) { e.printStackTrace(); } }
@SuppressWarnings("unchecked") private void getTimeLineAndTweetsOperation(DataStoreOperation op) throws UnsupportedEncodingException, SQLException { java.sql.Connection conn = null; String userid = ((GetTimeLineAndTweetsOperation) op).getUserid(); int start = ((GetTimeLineAndTweetsOperation) op).getStart(); int count = ((GetTimeLineAndTweetsOperation) op).getCount(); String tablename = ((GetTimeLineAndTweetsOperation) op).getTableName(); conn = this.borrowClient(); String query = "select tweets.tweetID,id,text,date,user from (select tweetID from friendsTimeLine where userID = ?) as res inner join tweets on res.tweetID = tweets.tweetID order by date DESC limit ?,? "; PreparedStatement stmt = conn.prepareStatement(query); ResultSet results = null; LinkedHashMap<String, Map<String, String>> result; try { stmt.setString(1, (String) userid); // stmt.setString(2, new String().valueOf(start)); stmt.setInt(2, start); // stmt.setString(3, new String().valueOf(start+count)); stmt.setInt(3, start + count); // System.out.println(start+count); // System.out.println("QERy:"+stmt.toString()); results = stmt.executeQuery(); result = new LinkedHashMap<String, Map<String, String>>(); while (results.next()) { Map<String, String> value = new HashMap<String, String>(); value.put("id", results.getString("id")); value.put("text", results.getString("text")); value.put("date", results.getString("date")); value.put("user", results.getString("user")); result.put(results.getString("tweetID"), value); } } finally { try { stmt.close(); } catch (Exception e) { } this.releaseClient(conn); } ((GetTimeLineAndTweetsOperation) op).setResult(result); op.notifyListeners(); }
public void initialize() throws Exception { Configuration config = new PropertiesConfiguration("mysql.properties"); String host = config.getString("host.name"); int port = config.getInt("host.port"); this.url = "jdbc:mysql://" + host + ":" + port + "/"; this.dbName = config.getString("dbName"); this.driver = "com.mysql.jdbc.Driver"; this.userName = config.getString("userName"); this.password = config.getString("password"); /// CREATE TABLES try { Class.forName(driver).newInstance(); DataSource ds_unpooled = DataSources.unpooledDataSource(url + dbName, userName, password); Map<String, Comparable> overrides = new HashMap<String, Comparable>(); overrides.put("maxStatements", "200"); // Stringified property values work overrides.put("maxPoolSize", new Integer(3)); // "boxed primitives" also work // create the PooledDataSource using the default configuration and our overrides this.pooled = DataSources.pooledDataSource(ds_unpooled, overrides); // The DataSource ds_pooled is now a fully configured and usable pooled DataSource, // with Statement caching enabled for a maximum of up to 200 statements and a maximum // of 50 Connections. java.sql.Connection conn = pooled.getConnection(); System.out.println("Connected to the database"); String users = "create table users (" + "userID VARCHAR(50) PRIMARY KEY, " + "name VARCHAR(50), " + "password VARCHAR(50), " + "following VARCHAR(500), " + "followers VARCHAR(500), " + "username VARCHAR(50), " + "lastTweet VARCHAR(50), " + "created VARCHAR(50) )" + "ENGINE=NDBCLUSTER"; String tweets = "create table tweets (" + "tweetID VARCHAR(50) PRIMARY KEY, " + "id VARCHAR(50), " + "text VARCHAR(50), " + "date VARCHAR(50), " + "user VARCHAR(50) )" + "ENGINE=NDBCLUSTER"; String friendsTimeLine = "create table friendsTimeLine (" + "tweetID VARCHAR(50)," + "userID VARCHAR(50), INDEX(userID)," + "date VARCHAR(50), " + "PRIMARY KEY (userID,tweetID)" + ")" + "ENGINE=NDBCLUSTER"; String tweetsTags = "create table tweetsTags(" + "tweetID VARCHAR(50) PRIMARY KEY, " + "topic VARCHAR(50), " + "user VARCHAR(50) )" + "ENGINE=NDBCLUSTER"; try { Statement stmt = conn.createStatement(); if (logger.isInfoEnabled()) logger.info("Creating table users"); stmt.executeUpdate(users); if (logger.isInfoEnabled()) logger.info("Creating table tweets"); stmt.executeUpdate(tweets); if (logger.isInfoEnabled()) logger.info("Creating table friendsTimeLine"); stmt.executeUpdate(friendsTimeLine); if (logger.isInfoEnabled()) logger.info("Creating table tweet's tags"); stmt.executeUpdate(tweetsTags); stmt.close(); conn.close(); } catch (SQLException ex) { logger.error("SQLException ", ex); } } catch (Exception e) { e.printStackTrace(); } }
@SuppressWarnings("unchecked") private void putOperation(DataStoreOperation op) throws UnsupportedEncodingException, SQLException { // long timestamp; boolean res = false; java.sql.Connection conn = null; Comparable key = ((PutOperation) op).getKey(); String tablename = ((PutOperation) op).getTableName(); if (tablename.equals("friendsTimeLine")) { // SERA QUASE UM MULTIPUT DE TIMELINE conn = this.borrowClient(); String query = "insert into " + tablename + " (tweetID,userID,date) values (?,?,?)"; // on duplicate key udpdate userID = // values(userID), date = values(date)"; PreparedStatement stmt = conn.prepareStatement(query); List<Pair<String, String>> value = (List<Pair<String, String>>) ((PutOperation) op).getData(); for (Pair<String, String> pair : value) { String date = pair.getFirst(); String tweetID = pair.getSecond(); stmt.setString(1, tweetID /*+":"+date*/); stmt.setString(2, (String) key); stmt.setString(3, date); stmt.executeUpdate(); } stmt.close(); this.releaseClient(conn); res = true; ((PutOperation) op).setResult(res); op.notifyListeners(); } else { if (tablename.equals("users")) { Map<String, String> value = (Map<String, String>) ((PutOperation) op).getData(); String query; int i; // String query = "insert into "+tablename+" // (userID,name,password,following,followers,username,lasttweet,created) values // (?,?,?,?,?,?,?,?)"; if (value.size() < 7) { i = 0; query = "update " + tablename + " set "; } else { i = 1; query = "insert into " + tablename + " "; // (userID,name,password,following,followers,username,lasttweet,created) // values (?,?,?,?,?,?,?,?)"; } Set<String> keys = value.keySet(); Iterator<String> iter = keys.iterator(); ArrayList<String> columnNames = new ArrayList<String>(); ArrayList<String> columnValues = new ArrayList<String>(); while (iter.hasNext()) { // FUNCIONA PARA O INSERT e UPDATE String columnName = iter.next(); columnNames.add(columnName); columnValues.add(value.get(columnName)); } conn = this.borrowClient(); Statement stmt = conn.createStatement(); try { if (i == 0) query += this.buildUpdateQuery("userID", (String) key, columnNames, columnValues); else query += this.buildInsertNamesQuery("userID", columnNames) + " values " + this.buildInsertValuesQuery((String) key, columnValues) + " on duplicate key update " + this.buildDuplicateNamesQuery(columnNames); // System.out.println("QUERY:" +query); stmt.executeUpdate(query); } finally { try { stmt.close(); } catch (Exception e) { } this.releaseClient(conn); } res = true; // System.out.println("PUTOP SETRESULT"); ((PutOperation) op).setResult(res); op.notifyListeners(); } else { Map<String, String> value = (Map<String, String>) ((PutOperation) op).getData(); // conn = this.borrowClient(op); // String query = "insert into "+tablename+" (tweetID,id,text,date,user) values // (?,?,?,?,?,?,?,?)"; String query = "insert into " + tablename + " "; // tweets ArrayList<String> columnNames = new ArrayList<String>(); ArrayList<String> columnValues = new ArrayList<String>(); Set<String> keys = value.keySet(); Iterator<String> iter = keys.iterator(); while (iter.hasNext()) { String columnName = iter.next(); columnNames.add(columnName); columnValues.add(value.get(columnName)); } conn = this.borrowClient(); Statement stmt = conn.createStatement(); try { query += this.buildInsertNamesQuery("tweetID", columnNames) + " values " + this.buildInsertValuesQuery((String) key, columnValues) + " on duplicate key update " + this.buildDuplicateNamesQuery(columnNames); stmt.executeUpdate(query); // System.out.println(stmt.executeUpdate(query)); } finally { try { stmt.close(); } catch (Exception e) { } // this.releaseClient(conn); } //// INSERT IN TWEET TAGS Set<String> tags = ((PutOperation) op).getTags(); query = "insert into tweetsTags "; // topic,user) values (?,?,?) on duplicate key update // topic=values(topic),user=values(user)"; PreparedStatement st = conn.prepareStatement(query); columnNames = new ArrayList<String>(3); columnValues = new ArrayList<String>(2); // columnNames.add("tweetID"); // columnValues.add((String) key); ///TWEETID columnNames.add("topic"); columnNames.add("user"); if (!tags.isEmpty()) { Iterator<String> it = tags.iterator(); while (it.hasNext()) { String tag = it.next(); if (tag.startsWith("topico")) { columnValues.add(tag); // TOPIC } else if (tag.startsWith("user")) { columnValues.add(tag); // USER } } } query += this.buildInsertNamesQuery("tweetID", columnNames) + " values " + this.buildInsertValuesQueryForTweetsTags((String) key, columnValues) + " on duplicate key update " + this.buildDuplicateNamesQuery(columnNames); // System.out.println("TAGS PUTOP QUERY:"+query); try { // System.out.println(st.toString()); st.executeUpdate(query); } finally { try { st.close(); } catch (Exception e) { } this.releaseClient(conn); } ///// FINISH TAGS res = true; ((PutOperation) op).setResult(res); op.notifyListeners(); } } }
@SuppressWarnings("unchecked") private void getOperation(DataStoreOperation op) throws UnsupportedEncodingException, SQLException { java.sql.Connection conn = null; Comparable key = ((GetOperation) op).getKey(); String tablename = ((GetOperation) op).getTableName(); if (tablename.equals("friendsTimeLine")) { // int count = ((GetOperation) op).getCount(); conn = this.borrowClient(); String query = "select tweetID,date from " + tablename + " where userID = ?"; PreparedStatement stmt = conn.prepareStatement(query); ResultSet results = null; // Map<String,String> res; ArrayList<String> res; try { stmt.setString(1, (String) key); results = stmt.executeQuery(); // res = new HashMap<String,String>(); res = new ArrayList<String>(); while (results.next()) { // res.put(results.getString("tweetID"), results.getString("date")); res.add(results.getString("tweetID") + ":" + results.getString("date")); } } finally { try { stmt.close(); } catch (Exception e) { } this.releaseClient(conn); } ((GetOperation) op).setResult(res); op.notifyListeners(); } else { if (tablename.equals("users")) { String columns = new String(); List<String> cols = ((GetOperation) op).getColumns(); if (cols.isEmpty()) columns = this.userColumns; // /////SE FOR VAZIO TRAZ COLUNAS TODAS else columns = this.listToString((ArrayList<String>) cols); Map<String, String> value = new HashMap<String, String>(); String query = "select " + columns + " from " + tablename + " where userID = ?"; conn = this.borrowClient(); PreparedStatement stmt = conn.prepareStatement(query); ResultSet results = null; try { stmt.setString(1, (String) key); // System.out.println("Query select:"+stmt.toString()); results = stmt.executeQuery(); String[] arrStr = columns.split(","); int size = arrStr.length; while (results.next()) { for (int i = 0; i < size; i++) { value.put(arrStr[i], results.getString(arrStr[i])); } } } finally { try { stmt.close(); } catch (Exception e) { } this.releaseClient(conn); } ((GetOperation) op).setResult((Map<String, String>) value); op.notifyListeners(); } } }