Today I am going to write about database migration in MySQL using Java as the language for the migrator. There's two way we can migrate data in a database. On one way what we can do is, migrate data from one database to another. Another way is that, we can take the SQL data dump out from the database and then migrate the database from the data dump.
1. Database to Database Migration:
In this example, I'm gonna show how to migrate data from one database tables to another in MySQL. We're going to use Java Persistence Api (JPA - Eclipselink) in this example for database connection. So, if you do not have the idea about how to work with JPA, then you may want to watch some basic tutorials on JPA. I am going to write another blog about JPA later if you do not have the time to look it up. There's also a link below to get some basic ideas about JPA.
link: https://www.tutorialspoint.com/jpa/
So, first, we are going to create a persistence.xml which will contain two persistence unit for the two databases. One is for the database from whom we are going to pull the data and another one which we will insert all the data from the first database. These persistence unit will contain basically the general connection information like the database url, driver, username, password etc. Let's see our tables from databases and also the persistence unit.
We can see the databases, their table structures and also the data which those tables are containing currently. In this example there's two databases, demo_database1 and demo_database2. The demo_database1 contains two tables, demo_table1 and demo_table2. The demo_database2 contains only one table, demo_table3.
Now we need to open a new project on our IDE. Then we will have to pull down the Entity classes from database based on their tables. We can use class generator from the IDE or we can manually write the code ourselves. In here, I am going to use class generator from Netbeans. We can easily generate persistence unit in Netbeans by right clicking the Project > New > Others.. > Persistence > Persistence Unit.
In the above picture we can see that we need to choose our database connection for the persistence unit. Then we have to fill up some information like database host, port, database name, username and password. We can also check the database connection from this panel and then create the persistence unit for that database. We can create many persistence unit as we like from this option based on how many database we will need in that particular project.
In the above image, we can see the two persistence unit. DemoJPAProjectPU and DemoJPAProjectPU2. The first one is for demo_database1 and the second one is for demo_database2 as you can see from the database URL. This file basically contains the connection of those two databases.
Then our persistence unit will add the classes automatically if we use the class generator or we can add them manually in the persistence unit. If we want to generate the database entity classes from Netbeans, just right click the package in which we want to store those classes and then New>Others.. > Persistence > Entity classes from database
In above image, we can see that when we choose to create Entity classes from a database it will go us through the connection and after secure the connection we will get the Entity classes of the table.
In the above panel, we can show the database connection or to create a new one if the database connection not existed from the dropdown menu which is in the top. After selecting the database, we will see it's table list in left. We can add all or some tables based on our needs. In this way we will create entity classes from both of the database, one by one.
Here's an image of an generated Entity Class for demo_table1 of demo_database1.
After we generate the classes, the persistence unit will automatically add those classes in the xml. We can see from the image below.
Now the coding part will begin. First we need to create EntityFactoryManager for the two persistence unit that contains the two databases. Then we're going to create one EntityManager for those two EntityFactoryManager.
In the above image, we can see that I had given all the code to migrate the database in a single function. Also, I managed to blocked out the different parts to understand the code better. Please keep in mind that this is not a JPA tutorial, so I will not go into much details about it.
In the black rectangle block, I created two different EntityFactoryManager for two different databases for migration. Then in the green rectangle block, I created two different EntityManager from those two EntityFactoryManager.
Now we are going to pull data all the data from one databases table in it's according class. In the red rectangle block, I did exactly that. I have written a JPQL (Java Persistance Query Language) query in String and then create that query in the first database's EntityManager. Here I also mentioned in which Entity class it will return the result list. It's the second line of the block. After that we run the query and put the result on a list of that same database's Entity class. After that we now have all the data from the demo_table1 of the demo_database1. Then we will close demo_database1's EntitryManager and also EntityFactoryManager for that database.
Now we are going to insert one by one data from one Entity class's List to another. I had done that in the blue rectangle block. In the first line of the block we start the transaction of the Entity Manager of the demo_database2. Then we start a loop on the list we have found from the demo_database1 on demo_table1. In that loop we create an instance of the Entity class of demo_table3. Then we set the data based on the data found on demo_table1. Here we set the migration type to true because of the fact that these data is migrated from demo_table1. Then we persist and commit the data into the database. Here each row will get entry from the list of demo_table1 to the demo_table3. When the loop will end all the data will migrate from one database to another. Then we will close the EntityManager and EntityFactoryManager of the demo_database2.
Here's the image of demo_table3 before database migration
Now, here's the image of demo_table3 after database migration
For multiple tables, the process is the same. Just we need to do this process multiple times for multiple tables, as required.
So, that's all for today. Data migration is quite interesting and can be done in many ways. Using JPA makes this task kind of easier. So, I prefer it over conventional techniques. I will continue data dump to database migration in my next blog.
Peace and Love
1. Database to Database Migration:
In this example, I'm gonna show how to migrate data from one database tables to another in MySQL. We're going to use Java Persistence Api (JPA - Eclipselink) in this example for database connection. So, if you do not have the idea about how to work with JPA, then you may want to watch some basic tutorials on JPA. I am going to write another blog about JPA later if you do not have the time to look it up. There's also a link below to get some basic ideas about JPA.
link: https://www.tutorialspoint.com/jpa/
So, first, we are going to create a persistence.xml which will contain two persistence unit for the two databases. One is for the database from whom we are going to pull the data and another one which we will insert all the data from the first database. These persistence unit will contain basically the general connection information like the database url, driver, username, password etc. Let's see our tables from databases and also the persistence unit.
We can see the databases, their table structures and also the data which those tables are containing currently. In this example there's two databases, demo_database1 and demo_database2. The demo_database1 contains two tables, demo_table1 and demo_table2. The demo_database2 contains only one table, demo_table3.
Now we need to open a new project on our IDE. Then we will have to pull down the Entity classes from database based on their tables. We can use class generator from the IDE or we can manually write the code ourselves. In here, I am going to use class generator from Netbeans. We can easily generate persistence unit in Netbeans by right clicking the Project > New > Others.. > Persistence > Persistence Unit.
In the above picture we can see that we need to choose our database connection for the persistence unit. Then we have to fill up some information like database host, port, database name, username and password. We can also check the database connection from this panel and then create the persistence unit for that database. We can create many persistence unit as we like from this option based on how many database we will need in that particular project.
In the above image, we can see the two persistence unit. DemoJPAProjectPU and DemoJPAProjectPU2. The first one is for demo_database1 and the second one is for demo_database2 as you can see from the database URL. This file basically contains the connection of those two databases.
Then our persistence unit will add the classes automatically if we use the class generator or we can add them manually in the persistence unit. If we want to generate the database entity classes from Netbeans, just right click the package in which we want to store those classes and then New>Others.. > Persistence > Entity classes from database
In above image, we can see that when we choose to create Entity classes from a database it will go us through the connection and after secure the connection we will get the Entity classes of the table.
In the above panel, we can show the database connection or to create a new one if the database connection not existed from the dropdown menu which is in the top. After selecting the database, we will see it's table list in left. We can add all or some tables based on our needs. In this way we will create entity classes from both of the database, one by one.
Here's an image of an generated Entity Class for demo_table1 of demo_database1.
After we generate the classes, the persistence unit will automatically add those classes in the xml. We can see from the image below.
Now the coding part will begin. First we need to create EntityFactoryManager for the two persistence unit that contains the two databases. Then we're going to create one EntityManager for those two EntityFactoryManager.
In the above image, we can see that I had given all the code to migrate the database in a single function. Also, I managed to blocked out the different parts to understand the code better. Please keep in mind that this is not a JPA tutorial, so I will not go into much details about it.
In the black rectangle block, I created two different EntityFactoryManager for two different databases for migration. Then in the green rectangle block, I created two different EntityManager from those two EntityFactoryManager.
Now we are going to pull data all the data from one databases table in it's according class. In the red rectangle block, I did exactly that. I have written a JPQL (Java Persistance Query Language) query in String and then create that query in the first database's EntityManager. Here I also mentioned in which Entity class it will return the result list. It's the second line of the block. After that we run the query and put the result on a list of that same database's Entity class. After that we now have all the data from the demo_table1 of the demo_database1. Then we will close demo_database1's EntitryManager and also EntityFactoryManager for that database.
Now we are going to insert one by one data from one Entity class's List to another. I had done that in the blue rectangle block. In the first line of the block we start the transaction of the Entity Manager of the demo_database2. Then we start a loop on the list we have found from the demo_database1 on demo_table1. In that loop we create an instance of the Entity class of demo_table3. Then we set the data based on the data found on demo_table1. Here we set the migration type to true because of the fact that these data is migrated from demo_table1. Then we persist and commit the data into the database. Here each row will get entry from the list of demo_table1 to the demo_table3. When the loop will end all the data will migrate from one database to another. Then we will close the EntityManager and EntityFactoryManager of the demo_database2.
Here's the image of demo_table3 before database migration
Now, here's the image of demo_table3 after database migration
For multiple tables, the process is the same. Just we need to do this process multiple times for multiple tables, as required.
So, that's all for today. Data migration is quite interesting and can be done in many ways. Using JPA makes this task kind of easier. So, I prefer it over conventional techniques. I will continue data dump to database migration in my next blog.
Peace and Love
Comments
Post a Comment