Uploading Bulk data into MySql database using Java

Let us see how can we upload / insert bulk data into MySql database from an excel

So that we have data ready in excel . Let's see the java program to connect and upload data.

We'll split our requirement into following sub tasks:

1. Connection to My Sql database.

2. Read the Excel using Apache POI.

3. Insert the data,by iterating row by row in the excel.

4.Close the database connection.

Here's how it is implemented:

package sample;

import java.io.FileInputStream;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Iterator;

import org.apache.poi.ss.formula.functions.Column;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Dbuploadtest {

         

         

          private static void Uploadexcel(String excelpath) throws IOException, InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException

          {

           try

           {

                   FileInputStream f1=new FileInputStream(excelpath);

                   XSSFWorkbook h1=new XSSFWorkbook(f1);

                   String ename = null;

                   int eid = 0;

                   XSSFSheet s1=h1.getSheetAt(0);

                  

                   Connection dc = DBConnect ();

                   Iterator<Row> ri=s1.iterator();

                  

                   while(ri.hasNext())

                   {

                             Row row=ri.next();

                             if(row.getRowNum()>0)

                             {       

                                      Iterator<Cell> ci=row.iterator();

                                     

                                                 while (ci.hasNext())

                                                 {

                                                           Cell cell=ci.next();

                                                           switch(cell.getCellType())

                                                           {

                                                           case Cell.CELL_TYPE_NUMERIC:

                                                                   eid=(int) cell.getNumericCellValue();

                                                                   break;

                                                           case Cell.CELL_TYPE_STRING:

                                                                    ename=cell.getStringCellValue();

                                                                    break;

                                                          }

                                                           

                                                 }

                                                 DBData (dc,eid,ename);

                             }

                            

                   }

              DBDisconnect(dc);

           }catch(IOException e)

           { 

                   e.printStackTrace(); 

           }

         

          }

          private static void DBData(Connection con, int eid,String ename) throws SQLException

          {

                   PreparedStatement st=con.prepareStatement("insert into  emp values( "+eid+",'"+ename+"')");

                  

                  

                   if(st.executeUpdate()==1)

                   {

                             System.out.println("Inserted data "+eid+","+ename);

                   }

                   else

                   {

                             System.out.println("Data not inserted");

                   }

                  

          }

         

          private static Connection DBConnect() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException

          {

                  

                    Class.forName("com.mysql.jdbc.Driver").newInstance();

                  

         

         

                    Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/data?" +

                "user=root&password=test");

                   

                  

                    return con;

                  

          }

          private static  void DBDisconnect(Connection con) throws SQLException

          {

                   con.close();

                             }

          public static void main(String[] args) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException, IOException {

         

                   Uploadexcel("E:\\Data-uploader.xlsx"); 

          }

}