Getting data periodically from MySQL

Question

Basically, i've a data table in MySQL. I want to get all the data, and sent it become message to ActiveMQ (1 message for 1 row in table).

But the data table automatically updated every 5 second. So there is some new data in table for every 5 second.

How to sending the new data without double sending the data that was sent? And i think i must use the Thread.sleep(5000) for made it endless looping?

This all i got:

package testcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.jms.ConnectionFactory;
import javax.jms.Destination;
import javax.jms.JMSException;
import javax.jms.MessageProducer;
import javax.jms.Session;
import javax.jms.TextMessage;
import org.apache.activemq.ActiveMQConnection;
import org.apache.activemq.ActiveMQConnectionFactory;


public class ProducerDataBase implements Runnable {

    public String vardbtype;
    public String vardbserver;
    public String vardbuser;
    public String vardbpassword;
    public String vardbname;
    public int batchperiod2;

    public ProducerDataBase() {
        vardbtype = MYSQL;
        vardbserver = mysqltest;
        vardbuser = testadmin;
        vardbpassword = admin;
        vardbname = messages;
        batchperiod2 = 5000;
    }

    public void run(){
        ConnectionFactory factory = null;
        javax.jms.Connection connection = null;
        Session session = null;
        Destination destination = null;
        MessageProducer producer = null;
        Connection cs = null;
        try {
            factory = new ActiveMQConnectionFactory(ActiveMQConnection.DEFAULT_BROKER_URL);
            connection = factory.createConnection();
            connection.start(); 
            session = connection.createSession(false, Session.AUTO_ACKNOWLEDGE);
            destination = session.createQueue("TestQueue");
            producer = session.createProducer(destination);
            if(vardbtype.equals("MYSQL")){
                Class.forName("com.mysql.jdbc.Driver");
                System.out.println("----------------------------");
                Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+ vardbserver, vardbuser, vardbpassword);
                while(true) {
                    Statement stmts = c.createStatement();
                    String sql = ("SELECT * FROM "+ vardbname);
                    ResultSet rss = stmts.executeQuery(sql);
                    while(rss.next()) {
                        String  message = rss.getString("MESSAGE");
                        System.out.println("Message = " + message);
                        TextMessage mssg = session.createTextMessage(message);
                        System.out.println("Sent: " + mssg.getText());
                        producer.send(mssg);
                    }
                    rss.close();
                    stmts.close();

                    Thread.sleep(batchperiod2);
                }
            }
        } catch(JMSException e) {
            e.printStackTrace();
        } catch(Exception e) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
        } finally {
            if(session != null) {
                try {
                    session.close();
                } catch (JMSException ex) {
                    // ignore   
                }
            }
            if(connection != null) {
                try {
                    connection.close();
                } catch (JMSException ex) {
                    // ignore
                }
            }
        }
        System.out.println("----------------------------");
        System.out.println("Message sent successfully");
    }
}

My code works, but it didn't sending the messages again when the table was updated...

Answer 1

You need a way to distinguish rows that have been read from rows that have not been read.

You can either run your program 'forever' and use a sleep to make it wait, or you could run your program from a cron job.

You could do something like:

• store the last id that you read (you might need to write this to a file if you need to stop your program and want it to persist for next time)
• read all the available rows with id > the last id that you read
• loop / or stop and run again later

Or it might make sense to mark the rows in the database as read:

• read all the available rows
• for each row, update the database, set column 'read' = true or something - lots of update statements, might be slow
• loop / or stop and run again later