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