Upload CSV file into MySql Database based on columns using Servlets and Java ~ Odia Java Student

Home » servlet tutorials » Upload CSV file into MySql Database based on columns using Servlets and Java

Friday, July 20, 2012

Upload CSV file into MySql Database based on columns using Servlets and Java

The CSV file is having multiple values to insert in perticular columns and rows in mysql database.

Here we have two files so we have to insert in two tables .

*** one is Header part file

*** another is Detail part file

Basically these two are to upload the questions and answers into the examination portal to upload question papers and answer paper in (Merit Tracking System project).

First we have to do the things are Uploaded file has to save in perticular folder and then they have to insert the values into the database table.This is our main task to do.

The required files to this program are

#1. index.jsp
#2. succ.jsp
#3. web.xml
#4. DBConnection.java
#5. SaveFile.java
#6. UploadFile2DB.java

and Some other libraries to add

#1. mysql-connector-java-3.1.11.jar
#2. servlet-api.jar

These are enough to our requirement add this files to your "lib" folder.

#1. index.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
 pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>

<form name="fileuploadform"
 action="http://localhost:8080/UploadCSV/uploadfile">Upload CSV
File<br>
Select the header file to upload <input type="file" name="filehdr" /><br>
Select the detail file to upload <input type="file" name="filedtl" /><br>

Please select a folder to which the file has to be uploaded. <input
 type="file" name="filefolder" /><br>
<input type="submit" name="submit" value="submit"> </form>
</html>

#2. succ.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<b>The Files has been Uploaded into particular tables.</b>
</body>
</html>

#3. DBConnection.java

package com;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConnection {
static Connection con;
    
    public static Connection getConnection(){
        con=null;
        try{
            System.out.println("----------I am in DBConnection----------");
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://192.168.1.101:3306/test?user=test&password=test" );
            System.out.println("---------end of DBConnection----------");
        }catch(Exception e){
            e.getMessage();
        }
        return con;
    }
}

#4. SaveFile.java

package com;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class SaveFile extends HttpServlet {
    public void init(ServletConfig config)throws ServletException{
        super.init(config);
        System.out.println("The SaveFile iniated.^^^^^^^^^^^^^^^^^^^################");
    }
    
    public void service(HttpServletRequest request, HttpServletResponse response)throws ServletException,IOException{
        try{
            String pathheader=request.getParameter("filehdr");
            System.out.println("The pathheader is : "+pathheader);
            String pathdetail=request.getParameter("filedtl");
            System.out.println("The pathdetail is : "+pathdetail);
            
            String folderpath=request.getParameter("filefolder");    
            
            String filenamehdr=folderpath+pathheader.substring(pathheader.lastIndexOf('\\'));
            System.out.println("The file output path is : "+filenamehdr);            
            String filenamedtl=folderpath+pathdetail.substring(pathdetail.lastIndexOf('\\'));
            System.out.println("The file output path is : "+filenamedtl);
            
            FileInputStream fis=new FileInputStream(pathheader);
            FileOutputStream fos=new FileOutputStream(filenamehdr);
            
            byte buf[]=new byte[11024];
            fis.read(buf);
            fos.write(buf,0,buf.length);
            
            fis=new FileInputStream(pathdetail);
            fos=new FileOutputStream(filenamedtl);
            fis.read(buf);
            fos.write(buf,0,buf.length);
            
            if(fis!=null)
                fis.close();
            if(fos!=null)
                fos.close();
            
            System.out.println("------------------ Files are Saved in Folder-------------------");
            request.getRequestDispatcher("/uploaddata").forward(request, response);

        }catch(FileNotFoundException e){
            System.out.println(e.getMessage());
        }catch(IOException e){
            System.out.println(e.getMessage());
        }
    }


}

#5. UploadFile2DB.java

package com;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.StringTokenizer;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class UploadFile2DB extends HttpServlet {
    public void init(ServletConfig config) throws ServletException{
        super.init(config);
        System.out.println("The UploadDataServlet2 iniated.");
    }



    public void service(HttpServletRequest request, HttpServletResponse response) throws ServletException,IOException {
        
        
        String filepathhdr=request.getParameter("filehdr");
        String filepathdtl=request.getParameter("filedtl");
        Connection con=DBConnection.getConnection();
        System.out.println("connection=----------->"+con);
        PreparedStatement pstmthdr=null;
        PreparedStatement pstmtdtl=null;

        int rowshdr=0;
        BufferedReader brhdr=new BufferedReader(new FileReader(filepathhdr));
        BufferedReader brdtl=new BufferedReader(new FileReader(filepathdtl));
        System.out.println("reading the file");
        String strLineHdr="";
        String strLineDtl="";
        String hdrstr="";
        String dtlstr="";
        StringTokenizer sthdr=null;
        StringTokenizer stdtl=null;
        //        String firstColumnData[]=new String[10];
        int lineNumberHdr=0;
        int lineNumberDtl=0;
        //        int line=1;
        try{
            pstmthdr=con.prepareStatement("insert into omts_onlinehdr values (?,?,?,?,?,?,?)");
            System.out.println("statement executed");

            while((strLineHdr=brhdr.readLine())!=null){
                System.out.println("HEADERLINE"+strLineHdr);
                int i=1;
                if(!(lineNumberHdr==0)){
                    sthdr=new StringTokenizer(strLineHdr,",");
                    while(sthdr.hasMoreTokens()){
                        hdrstr=sthdr.nextToken();
                        System.out.println("HeaderString: "+hdrstr);
                        pstmthdr.setString(i++,hdrstr);
                        System.out.println("below insertion");
                    }
                    rowshdr=pstmthdr.executeUpdate();
                    System.out.println(rowshdr+" rows updated.");
                }
                lineNumberHdr++;
            }
            System.out.println("not in detail");
            pstmtdtl=con.prepareStatement("insert into omts_onlinedtl values (?,?,?,?,?,?,?)");
            System.out.println("ps executed");

            while((strLineDtl=brdtl.readLine())!=null){
                System.out.println("detailLINE"+strLineDtl);
                int i=1;
                if(!(lineNumberDtl==0)){
                    stdtl=new StringTokenizer(strLineDtl,",");
                    while(stdtl.hasMoreTokens()){
                        dtlstr=stdtl.nextToken();
                        System.out.println("detail: "+dtlstr);
                        pstmtdtl.setString(i++,dtlstr);
                        System.out.println("below insertion");
                    }
                    int rowsdtl=pstmtdtl.executeUpdate();
                    System.out.println(rowsdtl+" rows are updated.");
                }
                lineNumberDtl++;
            }
            //con.commit();
        }
        catch(Exception e){
            System.out.println(e.getMessage());
        }
        finally
        {
            try {
                con.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        response.sendRedirect("http://localhost:8080/UploadCSV/succ.jsp");
    }
}

Run your application using Tomcat webserver and it will displays index.jsp as welcome-file-list.

By selecting CSV file using browse button of header part and detail part and set the path of the folder where you want to save the uploaded files and click on submit button.

After completion of accessing two files into folders and databases it will displays as succ.jsp file as "successfully uploaded the two files".