コード例 #1
0
  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();
    }
  }
コード例 #2
0
    @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();
        }
      }
    }