Thursday, December 6, 2018

Story-Read data from oracle database and write it to Excel using java

User Story-
In this we have to read data of employee from default table(HR.EMPLOYEES) of oracle database and write it to excel file using Apache POI .

Create a maven project with below dependencies:

pox.xml

<dependencies>
           <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>3.8.1</version>
                <scope>test</scope>
           </dependency>
           <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.17</version>
           </dependency>
           <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.17</version>
           </dependency>
     </dependencies>
 



Project Structure:




Let’s create a simple Employee class first. We’ll initialize a list of employees and write the list to the excel file that we’ll generate using Apache POI.

 EMPLOYEES.java

package com.dbtoexcel.model;

import java.util.Date;

public class EMPLOYEES {
     private int EMPLOYEE_ID;
     private String FIRST_NAME;
     private String LAST_NAME;
     private String EMAIL;
     private String PHONE_NUMBER;
     private Date HIRE_DATE;
     private String JOB_ID;
     private int SALARY;
     private int COMMISSION_PCT;
     private int MANAGER_ID;
     private int DEPARTMENT_ID;

     public int getEMPLOYEE_ID() {
           return EMPLOYEE_ID;
     }

     public void setEMPLOYEE_ID(int eMPLOYEE_ID) {
           EMPLOYEE_ID = eMPLOYEE_ID;
     }

     public String getFIRST_NAME() {
           return FIRST_NAME;
     }

     public void setFIRST_NAME(String fIRST_NAME) {
           FIRST_NAME = fIRST_NAME;
     }

     public String getLAST_NAME() {
           return LAST_NAME;
     }

     public void setLAST_NAME(String lAST_NAME) {
           LAST_NAME = lAST_NAME;
     }

     public String getEMAIL() {
           return EMAIL;
     }

     public void setEMAIL(String eMAIL) {
           EMAIL = eMAIL;
     }

     public String getPHONE_NUMBER() {
           return PHONE_NUMBER;
     }

     public void setPHONE_NUMBER(String pHONE_NUMBER) {
           PHONE_NUMBER = pHONE_NUMBER;
     }

     public Date getHIRE_DATE() {
           return HIRE_DATE;
     }

     public void setHIRE_DATE(Date hIRE_DATE) {
           HIRE_DATE = hIRE_DATE;
     }

     public String getJOB_ID() {
           return JOB_ID;
     }

     public void setJOB_ID(String jOB_ID) {
           JOB_ID = jOB_ID;
     }

     public int getSALARY() {
           return SALARY;
     }

     public void setSALARY(int sALARY) {
           SALARY = sALARY;
     }

     public int getCOMMISSION_PCT() {
           return COMMISSION_PCT;
     }

     public void setCOMMISSION_PCT(int cOMMISSION_PCT) {
           COMMISSION_PCT = cOMMISSION_PCT;
     }

     public int getMANAGER_ID() {
           return MANAGER_ID;
     }

     public void setMANAGER_ID(int mANAGER_ID) {
           MANAGER_ID = mANAGER_ID;
     }

     public int getDEPARTMENT_ID() {
           return DEPARTMENT_ID;
     }

     public void setDEPARTMENT_ID(int dEPARTMENT_ID) {
           DEPARTMENT_ID = dEPARTMENT_ID;
     }

}
 


 JDBCConnection.java

package com.dbtoexcel.database;

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

public class JDBCConnection {
               public static Connection getConnection() {

                              /*
                               * final String DB_URL = "jdbc:mysql://localhost:3306/loydstestdata";
                               *
                               * // Database credentials final String USER = "root"; final String PASS =
                               * "root1";
                               */

                              final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";

                              // Database credentials
                              final String USER = "SYSTEM";
                              final String PASS = "oracle";

                              Connection conn = null;
                              try {
                                             // Class.forName("com.mysql.jdbc.Driver");

                                             // STEP 3: Open a connection
                                             System.out.println("Connecting to database...");
                                             conn = DriverManager.getConnection(DB_URL, USER, PASS);
                              } /*
                                              * catch (ClassNotFoundException e) { // TODO Auto-generated catch block
                                              * e.printStackTrace(); }
                                              */ catch (SQLException e) {
                                             // TODO Auto-generated catch block
                                             e.printStackTrace();
                              }

                              return conn;
               }
}
 

 FetchRecords.java

package com.dbtoexel.dboperation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.dbtoexcel.database.JDBCConnection;
import com.dbtoexcel.model.EMPLOYEES;

public class FetchRecords {

     public List<EMPLOYEES> getEmployee() {
           List<EMPLOYEES> emps = new ArrayList<EMPLOYEES>();
           EMPLOYEES emp;
           Connection con;
           ResultSet rs = null;
           PreparedStatement ps;
           con = JDBCConnection.getConnection();
           String query = "SELECT * FROM  HR.EMPLOYEES";
           try {
                ps = con.prepareStatement(query);
                rs = ps.executeQuery();

                while (rs.next()) {
                      emp = new EMPLOYEES();

                     emp.setEMPLOYEE_ID(rs.getInt("EMPLOYEE_ID"));
                     emp.setFIRST_NAME(rs.getString("FIRST_NAME"));
                     emp.setLAST_NAME(rs.getString("LAST_NAME"));

                      emp.setEMAIL(rs.getString("EMAIL"));
                     emp.setPHONE_NUMBER(rs.getString("PHONE_NUMBER"));
                     emp.setHIRE_DATE(rs.getDate("HIRE_DATE"));
                      emp.setJOB_ID(rs.getString("JOB_ID"));

                     emp.setSALARY(rs.getInt("COMMISSION_PCT"));
                     emp.setCOMMISSION_PCT(rs.getInt("MANAGER_ID"));
                     emp.setMANAGER_ID(rs.getInt("MANAGER_ID"));
                     emp.setDEPARTMENT_ID(rs.getInt("DEPARTMENT_ID"));

                      emps.add(emp);

                }
           } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
           } finally {
                try {
                      rs.close();
                } catch (SQLException e) {
                      // TODO Auto-generated catch block
                      e.printStackTrace();
                }

           }

           return emps;

     }

}
  

Note that I’ll be using an XSSFWorkbook to create a Workbook instance. 

In the below program, we first created a workbook using the XSSFWorkbook class. Then we created a Sheet named “Employee”. Once we got a Sheet, we created the header row and columns. The header cells were styled using a different font.After creating the header row, we created other rows and columns from the employees list.

 InsertrecordsExcel.java

package com.dbtoexcel.writeexcel;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.sl.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.dbtoexcel.model.EMPLOYEES;

public class InsertrecordsExcel {

     public boolean insertRecords(List<EMPLOYEES> emp) {
           StringBuffer stringBuffer = new StringBuffer("");
           final String FILENAME = "C:\\Practice\\TPAPAANNO123.sql";
           FileOutputStream fileOut = null;

           String[] columns = { "EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "EMAIL", "PHONE_NUMBER", "HIRE_DATE", "JOB_ID" };
           List<EMPLOYEES> employees = new ArrayList<>();

           /*
            * for(EMPLOYEES e:emp) { stringBuffer.append(e.getFIRST_NAME()
            * +" "+e.getLAST_NAME()); stringBuffer.append("\n");
            *
            * BufferedWriter writer; try { writer = new BufferedWriter(new FileWriter(new
            * File(FILENAME))); writer.write(stringBuffer.toString()); writer.flush();
            * writer.close(); } catch (IOException e1) { // TODO Auto-generated catch block
            * e1.printStackTrace(); }
            *
            * }
            */

           // Create a Workbook
           Workbook workbook = new XSSFWorkbook();

           /*
            * CreationHelper helps us create instances of various things like DataFormat,
            * Hyperlink, RichTextString etc, in a format (HSSF, XSSF) independent way
            */
           CreationHelper createHelper = workbook.getCreationHelper();

           // Create a Sheet
           org.apache.poi.ss.usermodel.Sheet sheet = workbook.createSheet("Employee");

           // Create a Font for styling header cells
           Font headerFont = workbook.createFont();
           headerFont.setBold(true);
           headerFont.setFontHeightInPoints((short) 14);
           headerFont.setColor(IndexedColors.RED.getIndex());

           // Create a CellStyle with the font
           CellStyle headerCellStyle = workbook.createCellStyle();
           headerCellStyle.setFont(headerFont);

           // Create a Row
           Row headerRow = sheet.createRow(0);

           // Create cells
           for (int i = 0; i < columns.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(columns[i]);
                cell.setCellStyle(headerCellStyle);
           }

           // Create Cell Style for formatting Date
           CellStyle dateCellStyle = workbook.createCellStyle();
          dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-MM-yyyy"));

           // Create Other rows and cells with employees data
           int rowNum = 1;
           for (EMPLOYEES employee : emp) {
                Row row = sheet.createRow(rowNum++);

                row.createCell(0).setCellValue(employee.getEMPLOYEE_ID());

                row.createCell(1).setCellValue(employee.getFIRST_NAME());

                row.createCell(2).setCellValue(employee.getLAST_NAME());

                row.createCell(3).setCellValue(employee.getEMAIL());

                row.createCell(4).setCellValue(employee.getPHONE_NUMBER());

                Cell dateOfBirthCell = row.createCell(5);
                dateOfBirthCell.setCellValue(employee.getHIRE_DATE());
                dateOfBirthCell.setCellStyle(dateCellStyle);

                row.createCell(6).setCellValue(employee.getJOB_ID());

                // Write the output to a file

                try {
                      String file = "C:\\Learning\\SPRING\\Usecase1\\poi-generated-file.xlsx";
                      fileOut = new FileOutputStream(file);
                      workbook.write(fileOut);
                      fileOut.flush();

                } catch (IOException e1) {
                      // TODO Auto-generated catch block
                      e1.printStackTrace();
                }

           }

           System.out.println("File created ");
           try {
                fileOut.close();
                // Closing the workbook
                workbook.close();
           } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
           }

           return false;

     }

}
 

 EmployeeController.java

package com.dbtoexcel.controller;

import com.dbtoexcel.writeexcel.InsertrecordsExcel;
import com.dbtoexel.dboperation.FetchRecords;

public class EmployeeController {

     public static void main(String[] args) {
           FetchRecords fr = new FetchRecords();
           InsertrecordsExcel in = new InsertrecordsExcel();
           in.insertRecords(fr.getEmployee());

     }

}
 
 Console:

Connecting to database...
File created 



1 comment:

  1. AnonymousDecember 7, 2018 at 3:28 AM

    Thanks for sharing your thoughts about router setup.

    Regards

    ReplyDelete

Subscribe to: Post Comments (Atom)