CODEWITHSUNDEEP
javamysqldatabasesybase
prateek k
prateek k

Reputation: 137

copying table from one database to another(sybase to mysql) in java

I have written code to connect to sybase database and mysql database and copy one table from sybase database to mysql database. My program is working fine and i am getting done what i waned but not in sufficient time. Sybase has total around 10000 rows in table that i am copying and it is taking around 4 mins to copy. Can you guys suggest any improvement that can decrease the copying time. Following is my code:

package jdbcexmple;
import java.sql.*;
import java.util.*;




public class Jdbcexmple {

static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/alarm";
static final String JDBC_DRIVER_SECOND = "net.sourceforge.jtds.jdbc.Driver";
static final String DB_URL_SECOND = "jdbc:jtds:sybase://11.158.251.19:4100/fmdb";

static final String USER = "root";
static final String PASS = "abc";
static final String USER_SECOND = "your";
static final String PASS_SECOND = "xyz";


/**
 * @param args the command line arguments
 */
public static void main(String[] args) {
    String a;
    String b;
    String c;
    String d;
    Connection conn = null;
    Connection conn_2 = null;

    PreparedStatement stmt = null;
    try{






        Class.forName(JDBC_DRIVER);
        System.out.println("connecting to database mysql");
        conn = DriverManager.getConnection(DB_URL, USER, PASS);
        System.out.println("connected to database successfully");

        Class.forName(JDBC_DRIVER_SECOND);
        System.out.println("connecting to database SYBASE");
        conn_2 = DriverManager.getConnection(DB_URL_SECOND, USER_SECOND, PASS_SECOND);
        System.out.println("connected to database successfully");


        System.out.println("creating table in given database");



        String sql = "CREATE TABLE newtable (CSN VARCHAR(255), IsCleared VARCHAR(255), ID VARCHAR(255), IP VARCHAR(255), PRIMARY KEY ( ID ))";
        stmt = conn.prepareStatement(sql);
        stmt.executeUpdate(sql);

        System.out.println("created table in database");


        Statement stmt_1= conn_2.createStatement();  
        String sql_1 = "select tbl_alm_log_2000000000.Csn, tbl_alm_log_2000000000.IsCleared, tbl_alm_log_2000000000.Id From fmdb.dbo.tbl_alm_log_2000000000 Where IsCleared = 0";

        ResultSet rs =  stmt_1.executeQuery(sql_1);



        //below loop is taking 4 mins ie copying

        while (rs.next())
        {
                a = rs.getString(1);
                b = rs.getString(2);
                c = rs.getString(3);
                d = rs.getString(4);
                sql = "INSERT INTO newtable values "+"("+"\""+a+"\","+"\""+b+"\","+"\""+c+"\","+"\""+d+"\""+")"; 
                stmt = conn.prepareStatement(sql);
                stmt.executeUpdate(sql);
                System.out.println(a+"  "+b+"  "+c+"  "+d);



        }



      }catch(SQLException se){
        se.printStackTrace();
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try{
            if(stmt!=null)
                conn.close();
                conn_2.close();

        }catch(SQLException se){

        }
        try{
            if(conn!=null)
                conn.close();
                conn_2.close();

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



}

}

Upvotes: 1

Views: 3023

Answers (2)

Chandana Kumara
Chandana Kumara

Reputation: 2645

Use Batch execution to insert data into mysql without execute one by one. You have already used PreparedStatement. That is fine.

There are two solutions:

Solution 1:-

String sql = "INSERT INTO newtable values (col1, col2,col3) values (?, ?, ?)";
Connection connection = new getConnection();
connection.setAutoCommit(false);
PreparedStatement ps = connection.prepareStatement(sql);

final int batchSize = 1000;
int count = 0;

while (rs.next()){

  ps.setString(1, rs.getString(1));
  ps.setString(2, rs.getString(2));
  ps.setString(3, rs.getString(3));
  ps.addBatch();

  if(++count % batchSize == 0) {
    ps.executeBatch();
  }
}
ps.executeBatch(); // insert remaining records
connection.commit();
ps.close();
connection.close();

Your insert will be fast further with transaction handling. (connection.setAutoCommit(false); and connection.commit();)

http://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html#addBatch--

http://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#executeBatch--

http://viralpatel.net/blogs/batch-insert-in-java-jdbc/

Solution 2:-

rewriteBatchedStatements can be set with DB_URL this way.

jdbc:mysql://localhost:3306/alarm?rewriteBatchedStatements=true

So here rewriting to data bulk insert. Table lock once and indexes update once. This is another fastest way.

Upvotes: 1

Anoop
Anoop

Reputation: 428

You can turn on the caching or use a connection pool. Using this, the first connection call will create a cache which will save time to query database.

      OracleDataSource ods = new OracleDataSource();

// set cache properties
java.util.Properties prop = new java.util.Properties();
prop.setProperty("MinLimit", "2");
prop.setProperty("MaxLimit", "10");

// set DataSource properties
String url = "jdbc:oracle:oci8:@";
ods.setURL(url);
ods.setUser("hr");
ods.setPassword("hr");
ods.setConnectionCachingEnabled(true); // be sure set to true
ods.setConnectionCacheProperties (prop);
ods.setConnectionCacheName("ImplicitCache01"); // this cache's name


// We need to create a connection to create the cache 
Connection conn = ds.getConnection(user, pass);
Statement  stmt  = conn1.createStatement();
ResultSet  rset  = stmt.executeQuery("select user from dual");
conn1.close(); 

ods.close();

For more information, check the implicit connection caching on: http://docs.oracle.com/cd/B19306_01/java.102/b14355/concache.htm#CACFIJJB

Upvotes: 0

Related Questions