SQL to PDF using Itext - Java Example Program

In this article, let us examine how to create a PDF file with the contents of a table in a database. We will be using iText Java library to create PDF and standard JDBC connection to read the contents of a table. This tutorial is an extension of the example, that explained generating XML from table in Java earlier. I would also recommend reading our example tutorials on iText which would refresh you before taking on this tutorial.

This tutorial requires the following components:

Java 1.4 or higher
iText 5.3.4 (You can also use lower versions)
I'm using Oracle database as my datasource. [ 11g ] You can still use a different database, but make sure you have the right driver files.

The example involves the following sections:

1. Connect to database over a JDBC connection
2. Read contents from a table
3. Extract table data into an XML using WebRowSetImpl.
4. Convert extracted data into a nice PDF document using iText

As all these four steps are linked, I have provided the full working code below.

 1 import java.sql.*; /* we need this class for JDBC connectivity */
 2 import java.io.FileOutputStream; /*to write the SQL results to a PDF file */
 3 import java.io.ByteArrayOutputStream; /*holds the XML output of the SQL results */
 4 /* Document and PdfWriter objects to create PDF */ 
 5 import com.itextpdf.text.Document;
 6 import com.itextpdf.text.pdf.PdfWriter;
 7 import com.itextpdf.text.Paragraph; /* To push XML data into PDF */
 8 import com.sun.rowset.WebRowSetImpl; /* Rowset of SQL output data */
 9 class table2pdf {
10   public static void main (String[] args) throws Exception
11   {
12    Class.forName ("oracle.jdbc.OracleDriver"); /* The driver used to connect to the database */
13 
14    Connection conn = DriverManager.getConnection
15      ("jdbc:oracle:thin:@//localhost:1521/XE", "hr", "hr");
16     /* You should change this to point to your database suitably. Otherwise, this example will not work */
17    try {
18      Statement stmt = conn.createStatement();
19      try {
20        ResultSet query_set = stmt.executeQuery("select * from departments");
21         try {
22          WebRowSetImpl my_xml_data = new WebRowSetImpl();
23          my_xml_data.populate(query_set);         
24          /* Write XML data from the SQL to the Output Stream */
25          ByteArrayOutputStream out = new ByteArrayOutputStream();
26          my_xml_data.writeXml(out); 
27          /* Create document object / pdf writer object */
28          Document sql_to_pdf=new Document();
29          PdfWriter.getInstance(sql_to_pdf, new FileOutputStream("SQL2PDF.pdf"));
30          sql_to_pdf.open();
31          /* Add SQL results to the PDF document */
32          sql_to_pdf.add(new Paragraph(out.toString()));
33          sql_to_pdf.close(); /* Close the document */
34        } 
35        finally {
36           try { query_set.close(); } catch (Exception ignore) {}
37        }
38      } 
39      finally {
40        try { stmt.close(); } catch (Exception ignore) {}
41      }
42    } 
43    finally {
44      try { conn.close(); } catch (Exception ignore) {}
45    }
46   }
47 }

This code is tested working and creates the PDF file with full query output. Wait..what you can see in the output is only XML data in PDF document. I would like to see this data as a formatted table inside the PDF document. In the next post,we will see how to convert this SQL XML output into a neat table in a PDF document.