SQL Table to PDF Report in Java /JDBC Program

In this tutorial, we will discuss how to extract a database table data (specifically Oracle) to a PDF report in table format, using Java programming language. We will use standard JDBC tools available in Java to pull data from Oracle via SQL, and use iText to neatly format it into a report in a PDF file.

SQL Table to PDF Report – JAR Files

In order to pull the rows from a SQL table into a PDF report format in Java, you will need the following JAR files:

• ojdbc6.jar [ we have a 11gR2 database, you should use a different one for your database version / different JAR file if the database is not Oracle ]
• itextpdf-5.3.4.jar [ iText will help to convert table rows to PDF format ]

Input SQL Table

We would like to create a PDF report out of “departments” table in HR schema. A snapshot of the table data is provided below:

SQL Table to PDF Report using Java /JDBC - Input Table

The step by step guide to creating a PDF report for this example is provided below:

1: Define JDBC Query / Connection Details

In this step, we define the driver to be used for connection and define the Query / Connection details inside the Java program. These details will be used by the program to connect to the instance. A code fragment for this is provided below:

                /* Create Connection objects */
                Class.forName ("oracle.jdbc.OracleDriver"); 
                Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/xe", "hr", "hr");
                Statement stmt = conn.createStatement();
                /* Define the SQL query */
                ResultSet query_set = stmt.executeQuery("SELECT DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID FROM DEPARTMENTS");

2: Define PDF objects for Report

In this step, we will create Document and PdfWriter objects that will instantiate a PDF report file for us. We will also define a PDF table with 4 columns, and a PdfCell object that can accept the SQL data. Code snippet below:

                /* Step-2: Initialize PDF documents - logical objects */
                Document my_pdf_report = new Document();
                PdfWriter.getInstance(my_pdf_report, new FileOutputStream("pdf_report_from_sql_using_java.pdf"));
                my_pdf_report.open();            
                //we have four columns in our table
                PdfPTable my_first_table = new PdfPTable(4);
                //create a cell object
                PdfPCell table_cell;
               

3: Populate Table data to PDF Table

Here, we loop through all the rows from the database table, and add every single column data to the cell inside the PDF table. We also attach the column data to the table in this step. The code fragment that does this is given below:

                while (query_set.next()) {                
                                String dept_id = query_set.getString("DEPARTMENT_ID");
                                table_cell=new PdfPCell(new Phrase(dept_id));
                                my_report_table.addCell(table_cell);
                                String dept_name=query_set.getString("DEPARTMENT_NAME");
                                table_cell=new PdfPCell(new Phrase(dept_name));
                                my_report_table.addCell(table_cell);
                                String manager_id=query_set.getString("MANAGER_ID");
                                table_cell=new PdfPCell(new Phrase(manager_id));
                                my_report_table.addCell(table_cell);
                                String location_id=query_set.getString("LOCATION_ID");
                                table_cell=new PdfPCell(new Phrase(location_id));
                                my_report_table.addCell(table_cell);
                                }

4: Attach Report table to PDF

In this step, we add the logical report table created out of the earlier step, to the PDF document. This writes the report to the PDF and we close the document following this action.

                /* Attach report table to PDF */
                my_pdf_report.add(my_report_table);                       
                my_pdf_report.close();
                

5: Close JDBC Connection objects

As good programmers do, we close all connection objects (Query, Statement and DB objects) at this step. The report is ready!

                /* Close all DB related objects */
                query_set.close();
                stmt.close(); 
                conn.close();               

SQL Table to PDF Report – Full Java Program

The complete Java program merging all the tiny steps above is provided below:

import java.io.FileOutputStream;
import java.io.*;
import java.util.*;
import java.sql.*; 
import com.itextpdf.text.*;
import com.itextpdf.text.pdf.*;
public class jdbc_pdf_report {  
        public static void main(String[] args) throws Exception{
                
                /* Create Connection objects */
                Class.forName ("oracle.jdbc.OracleDriver"); 
                Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/xe", "hr", "hr");
                Statement stmt = conn.createStatement();
                /* Define the SQL query */
                ResultSet query_set = stmt.executeQuery("SELECT DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID FROM DEPARTMENTS");
                /* Step-2: Initialize PDF documents - logical objects */
                Document my_pdf_report = new Document();
                PdfWriter.getInstance(my_pdf_report, new FileOutputStream("pdf_report_from_sql_using_java.pdf"));
                my_pdf_report.open();            
                //we have four columns in our table
                PdfPTable my_report_table = new PdfPTable(4);
                //create a cell object
                PdfPCell table_cell;
               
                while (query_set.next()) {                
                                String dept_id = query_set.getString("DEPARTMENT_ID");
                                table_cell=new PdfPCell(new Phrase(dept_id));
                                my_report_table.addCell(table_cell);
                                String dept_name=query_set.getString("DEPARTMENT_NAME");
                                table_cell=new PdfPCell(new Phrase(dept_name));
                                my_report_table.addCell(table_cell);
                                String manager_id=query_set.getString("MANAGER_ID");
                                table_cell=new PdfPCell(new Phrase(manager_id));
                                my_report_table.addCell(table_cell);
                                String location_id=query_set.getString("LOCATION_ID");
                                table_cell=new PdfPCell(new Phrase(location_id));
                                my_report_table.addCell(table_cell);
                                }
                /* Attach report table to PDF */
                my_pdf_report.add(my_report_table);                       
                my_pdf_report.close();
                
                /* Close all DB related objects */
                query_set.close();
                stmt.close(); 
                conn.close();               
                
        }
}

JDBC Output Report – Example

A screen dump of the output PDF report created by the program is provided below:

PDF Report Created from Java JDBC Program

Sample compilation / execution example for the code is given below:

javac -classpath .;itextpdf-5.3.4.jar;ojdbc6.jar jdbc_pdf_report.java

java -classpath .;itextpdf-5.3.4.jar;ojdbc6.jar jdbc_pdf_report

That completes a breezy tutorial to convert SQL Table data to PDF in Java using JDBC and iText. If you have a question, you can post it in the comments section.