How to read from CSV file and Writes into Oracle Database using Java

This blog explains about reading content from CSV file and writes into Oracle database table using Java

- Below code reads the data from CSV file, Ensure to give absolute path for the fileName

       

     /**
     * This method reads the file content based on given file name
     * File Name should be absolute path.
     * @param fileName
     * @return
     */
    public List readCSVFile(String fileName) {
        List content = new ArrayList();
        try {
            BufferedReader br = new BufferedReader(new FileReader(fileName));
            String strLine = "";
            StringTokenizer st = null;
            int lineNumber = 0, tokenNumber = 0;
            while ((strLine = br.readLine()) != null) {
                content.add(strLine.split(","));
            }
        } catch (Exception fnfe) {
            fnfe.printStackTrace();
        }

        return content;
    }
       
 

- Below code translates the String array to POJO

       

    /**
     * This method convert given String array to CustomerDTO
     * @param fileContent
     * @return
     */
    public List convertArryToDTO(List fileContent) {
        List custList = null;
        CustomerDTO dto = null;
        if (fileContent != null && fileContent.size() > 0) {
            custList = new ArrayList();
            for (String[] row : fileContent) {
                if (row != null) {
                    dto = new CustomerDTO();
                    dto.setFirstName(row[0]);
                    dto.setMiddleName(row[1]);
                    dto.setLastName(row[2]);
                    dto.setCity(row[4]);
                    custList.add(dto);
                }
            }
        }

        return custList;
    }
       
 

- Below code writes to Database

       

      /**
     * dbURL =jdbc:oracle:thin:@server:port:serviceID
     * @return
     */
    private Connection getDBConnection(String dbURL) {
        Connection conn=null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn =
                DriverManager.getConnection(dbURL, "hr", "hr");
        } catch (Exception cnfe) {
            cnfe.printStackTrace();
        }
        return conn;
    }

    /**
     *
     * @param custList
     * @return
     */
    public String saveCustomersToDB(List custList) {
        String result = null;
        Connection conn=null;
        String dbURL = "jdbc:oracle:thin:@localhost:1521:XE";
        PreparedStatement stmt=null;
        try {
            conn = getDBConnection(dbURL);
            String query = "INSERT INTO CUSTOMER(ID,FNAME,LNAME,CITY) VALUES (?,?,?,?)";
            stmt = conn.prepareStatement(query);
            Random random = new Random();
            for (CustomerDTO customerDTO : custList) {
                stmt.setInt(1, random.nextInt(3400));
                stmt.setString(2, customerDTO.getFirstName());
                stmt.setString(3, customerDTO.getLastName());
                stmt.setString(4, customerDTO.getCity());
                stmt.execute();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException sqle) {
                    sqle.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException sqle) {
                    sqle.printStackTrace();
                }
            }

        }
        return result;
    }
       
 

- Below method retrieves employee information from database based on given employee ID

       

   public List getCustomerList()
  {
  /**
   * This method retrieves employee information based on employee ID
   * @param empID
   * @return
   */
  public EmployeeDTO readEmployeeDetailsFromDB(int empID)
  {
    EmployeeDTO dto = null;
    Connection conn = null;
    PreparedStatement stmt = null;
    String dbURL = "jdbc:oracle:thin:@localhost:1521:XE";
    try
    {
      conn = getDBConnection(dbURL);
      String query = "SELECT EMP_ID,FNAME,LNAME,DESCRIPTION FROM SRC_EMP WHERE EMP_ID= " + empID;
      stmt = conn.prepareStatement(query);
      stmt.execute();
      ResultSet rs = stmt.executeQuery(query);
      while (rs.next())
      {
        dto = new EmployeeDTO();
        String fName = rs.getString("FNAME");
        int supplierID = rs.getInt("EMP_ID");
        String lName= rs.getString("LNAME");
        String desc = rs.getString("DESCRIPTION");
        dto.setId(supplierID);
        dto.setFName(fName);
        dto.setLName(lName);
        dto.setDesc(desc);
      }
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return dto;
  }