Now we are going to see how we can use a data dump or sql file to insert data in another database. In this case I'm using a Oracle SQL dump and I will be inserting that data to a mysql database.
Data Dump to MySQL Database Migration:
First we will need a data dump from a database to migrate the data into the next database. I'm not going into the very details about how we can get the data dump from the database. My giving the following command in the below we can get the dump of a table from a database.
In this command in the <databaseName>, we will give our name of the oracle database. Then in <directoryOfTheFile>, we will give the directory name where we will save our file or in our case the dump. In <fileName>, we will give the file name of the dump and Finally in <tableName>, we will give the name of the table of which we will get the dump. This is just for one table but we can take dumps more multiple tables too. But in my java code I will use regular expression to get the data from dump to insert it into the table of the migrated database. That's why I prefer dumps of single table.
In my case, the commands looks like this;
expdp demo_database1 DIRECTORY=D:/Datadump DUMPFILE=demoDump.sql TABLES=EXPORT_TABLE
In the above image we can see inside of the data dump that I have created from the oracle database.
Basically that's what we need to start migration of the database. Now we will start the coding part in java for migration. First we need to create the persistence unit of the database in which we will migrate the data and the Entity class of the table in which the data will be migrated. In below I have given an image for both persistence unit and also the Entity class of the database in which we will insert the data.
In the Entity class we can see that I have used the same demo_table3 for the class as I have used it in the part 1 of the series. If anyone want to know more about the database structure, please go back to part 1 of the blog series.
Now let us start the coding for migration. I have used regular expression(regex) to get the appropriate data for migration. So if you don't know much about regex, then please try to get some basic idea about regex.
In this image above, first we need to put a regex pattern in a string and make it a Pattern Object by compiling it in the next line. Then I declared a Matcher variable to match the pattern and the string we will get from the file later on. Then we started a try catch block and in the try catch block we created a EntityManagerFactory of the persistence unit of demo_database2 in which we will put all the data. Then we get the sql dump file in a FileInputStream. Then we put the input stream into a BufferedReader to read the input stream.
Then we take a declare a String strLine and then take a counter and set it to 0. Then we take an ArrayList of Entity class of demo_table3 (DemoTable3). Then we also take an instance of that Entity class. Then we start a while loop based on the BufferedReader and put the line in the strLine. Then we match the pattern with String that we have found from the line. Now if the match is found the take an instance of the DemoTable3 (Entity class) and put the data that we have found in the String into the instance. Then we add it into the list and after all that we set the counter to +1.
Now we check if the counter is get to the point when it's value has reached to 10000. I did that because we need to insert file in batch, because it's way more faster then inserting 1 by 1. In here it's a batch of 10000 data to be inserted at a time. Now we create a EntityManager based on the EntityManagerFactory that we have created from the persistence unit. Now we start the transaction of the EntityManager. Then we start a loop on the list of data and we persist (insert in general) the data into the database from the list one by one. After the loop we commit the whole transaction and then we close the EntityManager instance. We also clear out the whole list of data because in next time we don't want to insert the same data twice. There is no else block here. Then we see another else block which is for the first if condition. This else block will trigger if any line doesn't match with the regex pattern that I have given at the start of the code.
When the while loop ends, there will be a check that if the list size is 0 or not. This check is needed because when we insert the data into the database, the count might not be mod of 10000. If the data list size is less then 10000 or not mod of 10000 like 56890, then the transaction inside the loop won't work for the rest. Like in last case of 56890 data, 6890 data will not get inserted. We basically do the same transactions as we did in the loop. After the whole transaction is completed, we close the BufferedReader instance alongside with the EntityManagerFactory instance.
In the above pic we can see the demo_database2 before the data migration.
In the above pic we can see the demo_database2 after the data migration. We can see that data has been successfully migrated.
Here's the whole code below in one snippet for good understanding.
Data Dump to MySQL Database Migration:
First we will need a data dump from a database to migrate the data into the next database. I'm not going into the very details about how we can get the data dump from the database. My giving the following command in the below we can get the dump of a table from a database.
expdp
<databaseName> DIRECTORY=<directoryOfTheFile> DUMPFILE=<fileName>
TABLES=<tableName>
In this command in the <databaseName>, we will give our name of the oracle database. Then in <directoryOfTheFile>, we will give the directory name where we will save our file or in our case the dump. In <fileName>, we will give the file name of the dump and Finally in <tableName>, we will give the name of the table of which we will get the dump. This is just for one table but we can take dumps more multiple tables too. But in my java code I will use regular expression to get the data from dump to insert it into the table of the migrated database. That's why I prefer dumps of single table.
In my case, the commands looks like this;
expdp demo_database1 DIRECTORY=D:/Datadump DUMPFILE=demoDump.sql TABLES=EXPORT_TABLE
In the above image we can see inside of the data dump that I have created from the oracle database.
Basically that's what we need to start migration of the database. Now we will start the coding part in java for migration. First we need to create the persistence unit of the database in which we will migrate the data and the Entity class of the table in which the data will be migrated. In below I have given an image for both persistence unit and also the Entity class of the database in which we will insert the data.
In the Entity class we can see that I have used the same demo_table3 for the class as I have used it in the part 1 of the series. If anyone want to know more about the database structure, please go back to part 1 of the blog series.
Now let us start the coding for migration. I have used regular expression(regex) to get the appropriate data for migration. So if you don't know much about regex, then please try to get some basic idea about regex.
Here's the whole code below in one snippet for good understanding.
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package parseoraclesql;
import database.entity.DemoTable3;
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
/**
*
* @author shakil
*/
public class ParseOracleSql {
public ParseOracleSql() {
}
public static void parse() {
String rgx = "^Insert.*?values[^\\(]+\\(\\s*(?\\d+)\\s*,\\s*'(?[^']*)'\\s*,"
+ "\\s*'(?[^']*)'\\s*,\\s*(?\\d+)\\s*\\)\\s*;$";
Pattern ptrn = Pattern.compile(rgx, Pattern.MULTILINE);
Matcher matcher;
try {
EntityManagerFactory emfactory = Persistence.createEntityManagerFactory("ParseOracleSqlPU");
FileInputStream fstream = new FileInputStream("C:/Users/TigerIT/Desktop/demoDump.sql");
BufferedReader br = new BufferedReader(new InputStreamReader(fstream));
String strLine;
int counter = 0;
List list = new ArrayList<>();
DemoTable3 demoData = new DemoTable3();
while ((strLine = br.readLine()) != null) {
matcher = ptrn.matcher(strLine);
if (matcher.find()) {
demoData = new DemoTable3();
demoData.setName(matcher.group("NAME"));
demoData.setDescription(matcher.group("DISCRIPTION"));
demoData.setQuantity(Integer.parseInt(matcher.group("QUANTITY")));
demoData.setMigrationType(true);
list.add(demoData);
counter++;
if (counter % 10000 == 0) {
EntityManager entitymanager = emfactory.createEntityManager();
entitymanager.getTransaction().begin();
for (DemoTable3 information : list) {
entitymanager.persist(information);
}
entitymanager.getTransaction().commit();
entitymanager.close();
list.clear();
}
} else {
System.out.println("LINE ERROR: " + strLine);
}
}
if (list.size() > 0) {
EntityManager entitymanager = emfactory.createEntityManager();
entitymanager.getTransaction().begin();
for (DemoTable3 information : list) {
entitymanager.persist(information);
}
entitymanager.getTransaction().commit();
entitymanager.close();
list.clear();
}
br.close();
emfactory.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
public static void main(String[] args) {
parse();
}
}
Comments
Post a Comment