twitter4j java Store the searched tweets in DB (MySQL).

A source that acquires as much as the past (up to 8 or 9 days) of a certain word and stores it in the DB (mysql).

Development environment

Eclipse 4.8   jdk 1.8

Interval to hit API

Go to hit MAX100 / 1API every 3 seconds. Hit 15 times with one instance, then wait 10 seconds. When the API limit is reached, error code 429 is returned. In that case, wait 60 seconds.

Resource class SearchWordResource

package CollectionTweet;


import java.util.Date;

import twitter4j.Status;
import twitter4j.TwitterException;
/**
 *Resource class that searches for arbitrary words and stores past tweets in the DB
 */
public class SearchWordResource {

    public static void main(String[] args) {

        //infinite loop
        for (;;) {

            TrendSearch trendSearchPRG = new TrendSearch();

                WordSearch wordSearchPRG = new WordSearch();
                Status retweetStatus = null;

                try {
                    //Search and store in DB
                    String wordSearch = "Work style reform";
                    retweetStatus = wordSearchPRG.wordSearch(wordSearch);

                } catch (TwitterException e) {
                    e.printStackTrace();
                }

            // sleep
            try {
                Date date = new Date();
                System.out.println(date.toString());
                Thread.sleep(TwitterContents.TIME_INTERVAL);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
    }
}

Word search class WordSearch

package CollectionTweet;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import twitter4j.Query;
import twitter4j.QueryResult;
import twitter4j.Status;
import twitter4j.Twitter;
import twitter4j.TwitterException;
import twitter4j.TwitterFactory;

/**
 *Class to search for a specific word
 */
public class WordSearch {

    public Status wordSearch(String seachWord) throws TwitterException {

        //Initialization
        Twitter twitter = new TwitterFactory().getInstance();
        Query query = new Query();
        //Number of Tweets to get in one request (100 is the maximum)
        query.setCount(TwitterContents.MAX_GETTWEET_NUMBER);
        //Set search word
        query.setQuery(seachWord);

        //DB connection
        ConnectionDB connectionDB = new ConnectionDB();
        Connection connection = null;
        Statement statement = null;
        try {
            connection = connectionDB.getConnection();
            statement = connection.createStatement();
        } catch (ClassNotFoundException | SQLException e3) {
            e3.printStackTrace();
        }

        String getMaxIdSQL = "select min(tweet_id) as min_tweet_id from t_tweet where search_word  =?;";

        List<String>  minUntilCreateDatetimeList = new ArrayList<String>();

        //Get the minimum value of the tweet creation date and time of the search word from DB
        try(
            PreparedStatement ps = connection.prepareStatement(getMaxIdSQL)){

            ps.setString(1,seachWord);

            try(ResultSet rs = ps.executeQuery()){
                while (rs.next()) {
                    System.out.println("◆◆ DB stored Tweet ID minimum value:" + rs.getString("min_tweet_id") + "◆◆");
                    minUntilCreateDatetimeList.add(rs.getString("min_tweet_id"));
                }
            };

        } catch (SQLException e2) {
            e2.printStackTrace();
        }

        //If you have already registered in the DB in the past, specify the minimum tweet ID in the query search condition.
        if(minUntilCreateDatetimeList.get(0) != null) {

            //Get the tweet ID before the specified tweet ID
            query.setMaxId(Long.valueOf(minUntilCreateDatetimeList.get(0)));
            System.out.println(minUntilCreateDatetimeList.get(0) + "_JST");
        }

        //Tweets with the most retweets
        Status popularTweet = null;

        //search results
        QueryResult result = null;

        //API residue
        GetAPIRestCount getAPIRestCount = new GetAPIRestCount();

        //Search execution
        for (int i = 0; i < TwitterContents.SERACH_PAGE_NUMBER; i++) {

            //Search execution
            try {
                result = twitter.search(query);
                System.out.println("Number of hits: " + result.getTweets().size());
                System.out.println("number of pages: " + i);
            } catch (TwitterException twi_e) {
                twi_e.printStackTrace();
                if (twi_e.getStatusCode() == 429) {
                    intarvalTime();
                }
                break;
            } catch (Exception ee) {
                ee.printStackTrace();
                break;
            }

            //If the acquisition result is 0, null is returned.
            if(result.getTweets().size() == 0) {
                return null;
            }

            //API hit interval: 3 seconds
            try {
                Thread.sleep(3000);
            } catch (InterruptedException e1) {
                e1.printStackTrace();
            }

            //Take a look at the search results
            for (Status tweet : result.getTweets()) {

                popularTweet = tweet;
//                System.out.println("◆◆◆◆◆◆ Searched trend words:" + seachWord + "◆◆◆◆◆◆");
                //System.out.println("Status ID:" + popularTweet.getId()); //Required for retweets
                //System.out.println("Text:" + popularTweet.getText());
                //User who spoke
//                System.out.println("User ID_num:" + popularTweet.getUser().getId());
//                System.out.println("User ID@:" + popularTweet.getUser().getScreenName());
                System.out.println("User name notation:" + popularTweet.getUser().getName());
                //Date and time of speaking
//                System.out.println("Tweet date and time:" + popularTweet.getCreatedAt());
//                System.out.println("language:" + popularTweet.getLang());
//                System.out.println("area:" + popularTweet.getGeoLocation());
//                System.out.println("Number of likes:" + popularTweet.getFavoriteCount());
//                System.out.println("Number of retweets:" + popularTweet.getRetweetCount());

                //insert execution
                try {

                    String sql = "INSERT INTO T_tweet VALUES ("
                    + "'" +  popularTweet.getId()  + "'" +  "," //Tweet ID You can retweet with this
                    + "'" + popularTweet.getUser().getScreenName() + "'" + "," //User ID
                    + "'" + popularTweet.getUser().getName() + "'" + "," //User name
                    + "'" + popularTweet.getText() + "'" + "," //Tweet body
                    + "'" + popularTweet.getGeoLocation() + "'" + "," //area
                    + "'" + popularTweet.getLang() + "'" + "," //language
                    + "'" + seachWord + "'" + "," //Search word
                    + popularTweet.getFavoriteCount() + "," //Number of favorites
                    + popularTweet.getRetweetCount() + "," //Number of retweets
                    + "'" + new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(popularTweet.getCreatedAt()) + "'"   //Tweet date and time
                    + ");";

                    int resultInsert = statement.executeUpdate(sql);
                    System.out.println("Result 1:" + resultInsert);

                } catch (SQLException e) {
                    e.printStackTrace();
                }
                query = result.nextQuery(); //See next page
            }
        }

        //As a result of searching, if there is no corresponding tweet, NULL is returned
        if(popularTweet == null ){
            return null;
        }

        return popularTweet;
    }

   //time
    public void intarvalTime() {
        Date date = new Date();
        System.out.println(date.toString());
        try {
            //60 seconds
            Thread.sleep(60000);
        } catch (InterruptedException e) {
            //TODO auto-generated catch block
            e.printStackTrace();
        }
    }
}

DB connection class ConnectionDB

package CollectionTweet;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionDB {

    public Connection getConnection() throws ClassNotFoundException {

        Class.forName("com.mysql.cj.jdbc.Driver");

        try {

            Connection connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/twitter?serverTimezone=JST", "user", "password");

                        return connection;
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
        return null;
        }
}

API residue GetAPIRestCount

package CollectionTweet;

import java.util.HashMap;
import java.util.Map;

import twitter4j.RateLimitStatus;
import twitter4j.Twitter;
import twitter4j.TwitterException;
import twitter4j.TwitterFactory;

/**
 *Get API restriction information
 *
 */
public class GetAPIRestCount {

    public static void getAPIRestCount() {

        Twitter twitter = new TwitterFactory().getInstance();

        Map<String , RateLimitStatus> helpmap = new HashMap<String,RateLimitStatus>();//Create a variable to store API restriction information
        try {
            helpmap = twitter.help().getRateLimitStatus();


                for(Map.Entry<String, RateLimitStatus> e : helpmap.entrySet()){

                    if(e.getKey().equals("/application/rate_limit_status") ||
                       e.getKey().equals("/statuses/retweeters/ids") ||
                       e.getKey().equals("/search/tweets") ||
                       e.getKey().equals("/friendships/show")){
                        System.out.println(e.getKey() + "\nremain: " + e.getValue().getRemaining());
                    }
                }
        } catch (TwitterException e1) {
            e1.printStackTrace();
        }
    }
}

Constant class TwitterContents


package CollectionTweet;

/**
 *Class that manages constants
 */
public class TwitterContents {

        //Suppress instantiation with private constructor
        private TwitterContents(){};
        //Number of trend words to search
        public static final int TRENDWORD_NUMBER = 2;
        //Minimum number of retweets
        public static final int MIN_RETWEET_NUMBER = 150;
        //Minimum number of retweets
        public static final int LOCALNUMBER_JAPAN = 23424856;
        //Processing interval time
        public static final int TIME_INTERVAL = 10000;
        //Number of Tweets to get on request (100 is the maximum)
        public static final int MAX_GETTWEET_NUMBER = 100;
        //Number of pages to search max 15 pages
        public static final int SERACH_PAGE_NUMBER = 15;
    }

DDL

CREATE TABLE `t_tweet` (
  `tweet_id` varchar(256) NOT NULL,
  `user_id` varchar(256) NOT NULL,
  `user_name` varchar(256) DEFAULT NULL,
  `tweet` text,
  `location` varchar(256) DEFAULT NULL,
  `langeuge` varchar(256) DEFAULT NULL,
  `search_word` varchar(256) NOT NULL,
  `favarite_count` bigint(20) DEFAULT NULL,
  `retweet_count` bigint(20) DEFAULT NULL,
  `create_datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`tweet_id`,`user_id`,`search_word`),
  UNIQUE KEY `tweet_id_UNIQUE` (`tweet_id`,`user_id`,`search_word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Recommended Posts

twitter4j java Store the searched tweets in DB (MySQL).
Store the Java object received in table format in the related model
Access the network interface in Java
Specify the java location in eclipse.ini
Unzip the zip file in Java
Parsing the COTOHA API in Java
Store UUID data in MySQL in 16 bytes
Call the super method in Java
[Java] When dealing with MySQL DB in Doma, insert Batch Insert into one
Implementation of DBlayer in Java (RDB, MySQL)
Get the result of POST in Java
Java reference to understand in the figure
Try using the Stream API in Java
Call the Windows Notification API in Java
[Java] Use cryptography in the standard library
Organized memo in the head (Java --Array)
ERRORCODE = -4471 occurs in Java application using Db2.
Try calling the CORBA service in Java 11+
What is the main method in Java?
How to get the date in java
[Rails] Store only checked items in DB
Console input in Java (understanding the mechanism)
Solution that gives an error when trying to connect to DB (MySQL) in Java
Sample code to get the values of major SQL types in Java + MySQL 8.0
Regarding the transient modifier and serialization in Java
The story of low-level string comparison in Java
[Java] Handling of JavaBeans in the method chain
About the confusion seen in startup Java servers
The story of making ordinary Othello in Java
About the idea of anonymous classes in Java
ChatWork4j for using the ChatWork API in Java
A story about the JDK in the Java 11 era
Organized memo in the head (Java --Control syntax)
The intersection type introduced in Java 10 is amazing (?)
The story of learning Java in the first programming
Measure the size of a folder in Java
[NCMB] I searched the data store with mbaas
Feel the passage of time even in Java
Organized memo in the head (Java --instance edition)
[Java] Read the file in src / main / resources
Organized memo in the head (Java --Data type)
Display "Hello World" in the browser using Java
[Java] Judgment by entering characters in the terminal
Display "Hello World" in the browser using Java
Try using the COTOHA API parsing in Java
[Java] Something is displayed as "-0.0" in the output
Import files of the same hierarchy in Java