CODEWITHSUNDEEP
javamysqldatabasejdbcthread-sleep
B.dinesh
B.dinesh

Reputation: 3

Want to run thread in java database connectivity every 24 hours time interval

This is the code I'm using to insert values taken from a local database to an online database. I am getting the data from a single local database and uploading each record in that to the 17 different tables online. The code is running successfully but at the end of 24 hours, the code automatically terminates with out running for the second time after the thread sleep time. I don't know why this happens. Please give me a solution to run the code for every 24 hours.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

class online_data
{
    public static void main(String args[])
    {
        MyThread1 thread1 = new MyThread1();
        thread1.start();
    }
}
class MyThread1 extends Thread
{

    public void run()
    {
        try{  
          Class.forName("com.mysql.jdbc.Driver"); 

          //Connection1
          Connection con=DriverManager.getConnection("jdbc:mysql://sql124.main-hosting.eu/u678426119_kcg","u678426119_kcg","deepak"); 
          Statement stmt=con.createStatement();

          //Connection2
          Connection conn=DriverManager.getConnection("jdbc:mysql://localhost/kcg","root","icdipl"); 
          Statement stmt2=conn.createStatement();
          int i = 1;
          String s = "online_data";

          while(true)
          {
            String s1 = s+i;
            //Date & Time
            Date dNow = new Date( );
            SimpleDateFormat ft = new SimpleDateFormat ("yyyy.MM.dd");
            SimpleDateFormat ft1 = new SimpleDateFormat ("hh:mm:ss");
            ResultSet rs=stmt2.executeQuery("select * from online_data");  
            while(rs.next())
            {
                String q="INSERT INTO "+s1+" VALUES ("+rs.getInt(1)+","+rs.getInt(2)+",'"+rs.getString(3)+"',"+rs.getFloat(4)+","+rs.getFloat(5)+","+rs.getFloat(6)+","+rs.getFloat(7)+","+rs.getFloat(8)+","+rs.getFloat(9)+","+rs.getFloat(10)+","+rs.getFloat(11)+","+rs.getFloat(12)+","+rs.getFloat(13)+","+rs.getFloat(14)+","+rs.getFloat(15)+","+rs.getFloat(16)+","+rs.getFloat(17)+","+rs.getFloat(18)+","+rs.getFloat(19)+","+rs.getFloat(20)+","+rs.getFloat(21)+","+rs.getFloat(22)+","+rs.getFloat(23)+",'"+rs.getString(24)+"',"+rs.getFloat(25)+",'"+rs.getString(26)+"',"+rs.getFloat(27)+",'"+rs.getString(28)+"',"+rs.getFloat(29)+","+rs.getFloat(30)+","+rs.getFloat(31)+","+rs.getFloat(32)+","+rs.getFloat(33)+","+rs.getFloat(34)+","+rs.getFloat(35)+",'"+rs.getString(36)+"',"+rs.getFloat(37)+",'"+rs.getString(38)+"','"+rs.getString(39)+"',"+rs.getFloat(40)+","+rs.getFloat(41)+","+rs.getFloat(42)+","+rs.getFloat(43)+","+rs.getFloat(44)+","+rs.getFloat(45)+","+rs.getFloat(46)+","+rs.getFloat(47)+","+rs.getFloat(48)+",'"+rs.getString(49)+"','"+rs.getString(50)+"',"+rs.getFloat(51)+","+rs.getFloat(52)+",'"+rs.getString(53)+"',"+rs.getFloat(54)+","+rs.getFloat(55)+","+rs.getFloat(56)+","+rs.getFloat(57)+","+rs.getFloat(58)+","+rs.getFloat(59)+","+rs.getFloat(60)+","+rs.getFloat(61)+","+rs.getFloat(62)+","+rs.getFloat(63)+","+rs.getFloat(64)+","+rs.getFloat(65)+","+rs.getFloat(66)+","+rs.getFloat(67)+","+rs.getFloat(68)+","+rs.getFloat(69)+","+rs.getFloat(70)+","+rs.getFloat(71)+","+rs.getFloat(72)+","+rs.getFloat(73)+","+rs.getFloat(74)+","+rs.getFloat(75)+","+rs.getFloat(76)+","+rs.getFloat(77)+","+rs.getFloat(78)+","+rs.getFloat(79)+","+rs.getFloat(80)+","+rs.getFloat(81)+","+rs.getFloat(82)+","+rs.getFloat(83)+","+rs.getFloat(84)+","+rs.getFloat(85)+","+rs.getFloat(86)+",'"+rs.getString(87)+"',"+rs.getFloat(88)+",'"+rs.getString(89)+"',"+rs.getFloat(90)+",'"+rs.getString(91)+"',"+rs.getFloat(92)+",'"+rs.getString(93)+"',"+rs.getFloat(94)+",'"+rs.getString(95)+"',"+rs.getFloat(96)+",'"+rs.getString(97)+"',"+rs.getFloat(98)+",'"+rs.getString(99)+"',"+rs.getFloat(100)+",'"+rs.getString(101)+"','"+ft.format(dNow)+"','"+ft1.format(dNow)+"')";
                stmt.executeUpdate(q);
                i+=1;
            }

            randomWait();
            i=1;
          }
          }catch(Exception e){ System.out.println(e);}  
    }


    void randomWait()
    {

        try {
           sleep((long)(1000*60*60*24));
        } catch (InterruptedException x) {
            System.out.println("Interrupted!");
         }
     }
 }

Upvotes: 0

Views: 337

Answers (2)

Ari Singh
Ari Singh

Reputation: 1296

Try opening the connection inside the "while (true)", and closing the connection at the end of this while loop. This way, your connection will be opened every 24 hours, then do the select/insert and then the connection would then close immediately after doing all the database operations.

If you want the program to not terminate during a database operation failure (when an exception is raised), and try the select/insert again after 24 hours of failure - you would need an inner try/catch/finally block to catch and log/ignore the exception. Ideally the close connection should be done in a finally block (in inside try/catch/finally block) - to close the connection in either case - whether the database operations are successful or not.

Upvotes: 0

AnOccasionalCashew
AnOccasionalCashew

Reputation: 661

For starters, try moving your database connections into the while loop and closing them when you're done with them (ie before the thread goes to sleep).

Then, instead of doing all of this in a thread that sleeps for 24 hours, instead consider doing this inside a single static method, encapsulating a call to that in a TimerTask or something similar, and using a Timer or equivalent to schedule things.

Upvotes: 1

Related Questions