import java.sql.*;
import java.io.*;
import java.time.LocalDateTime;


public class Converter {
    public static void main(String[] args) {
        String url = args[0];
        String usr = args[1];
        String pswd = args[2];
        String table = args[3];
        database(url, usr, pswd, table);
    }

    public static void database(String url, String usr, String pswd, String table) {
        String          sqlText,sqlText2            = null;
        Statement       stmt,stmt2                  = null;
        ResultSet       rset                        = null;

		try (
            //CONNECTION TO DB 
            Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@"+url, usr, pswd)) {

            if (conn != null) {
                System.out.println("Connected to the database!");
                //SELECT QUERY
                sqlText = "SELECT id,created,modified FROM " + table;
                //System.out.println(sqlText); 
                stmt = conn.createStatement();
                rset = stmt.executeQuery(sqlText);
                System.out.println("Wait until conversion is done, might take few minutes");
                while (rset.next()) {
                    Blob created = rset.getBlob("created");
                    Blob modified = rset.getBlob("modified");
                    Number id = rset.getInt("id");
    
                    try {
                        //UPDATE QUERY TO TIMESTAMP
                        if (created != null && modified != null) {
                            sqlText2 = "UPDATE " +table+ " SET created2=to_timestamp('"+converter(created)+"','yyyy-mm-dd hh24:mi:ss'), modified2=to_timestamp('"+converter(modified)+"','yyyy-mm-dd hh24:mi:ss') WHERE id="+id;
                        } else if (created != null) { 
                            sqlText2 = "UPDATE " +table+ " SET created2=to_timestamp('"+converter(created)+"','yyyy-mm-dd hh24:mi:ss') WHERE id="+id;
                        } else if (modified != null) {
                            sqlText2 = "UPDATE " +table+ " SET modified2=to_timestamp('"+converter(modified)+"','yyyy-mm-dd hh24:mi:ss') WHERE id="+id;
                        }
                        //System.out.println(sqlText2);
                        stmt2 = conn.createStatement();
                        stmt2.executeQuery(sqlText2);
                        //System.out.println("Converted "+created+" into "+converter(created));
                    } catch (Exception e) {
                        e.printStackTrace();
                    }  finally {

                        if (stmt2 != null) {
                            try { 
                                stmt2.close();
                            } catch (SQLException sqle) {}
                        }
                    }
				}
                System.out.println("Done!");
                stmt.close();
            } else {
                System.out.println("Failed to make connection!");
            }

            conn.close();

        } catch (SQLException e) {
            System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
        } catch (Exception e) {
            System.out.println("Database Exception");
            e.printStackTrace();
        } 
    }

    //CONVERT BLOB TO STRING METHOD
    public static String converter(Blob date){
        try {
            InputStream dateIS = date.getBinaryStream();
            ObjectInputStream dateOIS = new ObjectInputStream(dateIS);
            LocalDateTime dateDT = (LocalDateTime) dateOIS.readObject();

            String dateString = dateDT.toString();
            String dateSubString = dateString.substring(0, dateString.length() - 4);
            dateSubString = dateSubString.replace("T"," ");
            dateOIS.close();

            return dateSubString;
           
        } catch (Exception e){
            System.out.println("Converter exception");
            e.printStackTrace();
        }
        return null;
    }
}