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