Struts 2 + iText + MySQL Dynamic PDF Report Example

By Atul Rai | Last Updated: July 22, 2018 Previous       Next

In the previous Struts2 and iText example, I have simply put the static values to create the PDF report. But in this example, we fetch the multiple records from the database and create the dynamic PDF report.

Similar Post- Struts 2 and iText PDF Report Integration Example

To fetch the multiple records from the database, we need to create a DAO class and retrieve all the records in a ResultSet. Further, access the ResultSet in the Action class and iterate the ResultSet.

Software Used

In our example, we have used the below software/jars.

1. Eclipse IDE
2. Tomcat 8
3. JDK 8
4. MySQL Database

Required Dependencies

You can directly add all core JARs of struts2.x.x , iText5.x.x and MySQL connector jar in your application or add below to your pom.xml if your application maven based.

<dependencies>
    <dependency>
        <groupId>org.apache.struts</groupId>
        <artifactId>struts2-core</artifactId>
        <version>2.3.16</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.17</version>
    </dependency>
    <dependency>
        <groupId>com.itextpdf</groupId>
        <artifactId>itextpdf</artifactId>
        <version>5.0.6</version>
    </dependency>
</dependencies>

Project Structure in Eclipse

Add Struts 2 Filter

Define the struts 2 filters in web.xml.

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee"
         xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
         id="WebApp_ID" version="2.5">
    <display-name>Struts2iTextMySQLIntegration</display-name>
    <welcome-file-list>
        <welcome-file>report.jsp</welcome-file>
    </welcome-file-list>
    <filter>
        <filter-name>struts2</filter-name>
        <filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class>
    </filter>
    <filter-mapping>
        <filter-name>struts2</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>
</web-app>

Create DAO Class

Create a DAO class for accessing the records from the database.

package org.websparrow;

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

public class ReportDAO {
	// Create the connection
	public static Connection connect() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/websparrow", "root", "");
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	// Fetch records into ResultSet
	public static ResultSet empDetails() throws SQLException {
		ResultSet rs = null;
		try {
			Statement statement = connect().createStatement();
			rs = statement.executeQuery("select * from emp_details");
			return rs;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			connect().close();
		}
	}
}

Create Action Class

Create an action class.

package org.websparrow;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.ResultSet;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts2.ServletActionContext;
import org.apache.struts2.interceptor.ServletRequestAware;
import org.apache.struts2.interceptor.ServletResponseAware;

import com.itextpdf.text.BaseColor;
import com.itextpdf.text.Document;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.FontFactory;
import com.itextpdf.text.Image;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.Phrase;
import com.itextpdf.text.Rectangle;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfReader;
import com.itextpdf.text.pdf.PdfStamper;
import com.itextpdf.text.pdf.PdfWriter;
import com.opensymphony.xwork2.ActionSupport;

public class ReportAction extends ActionSupport implements ServletRequestAware, ServletResponseAware {
	private static final long serialVersionUID = -1517087746057956540L;
	private HttpServletRequest request;
	private HttpServletResponse response;
	private ResultSet rs = null;

	@Override
	public String execute() throws Exception {
		try {

			// Creating document and set the page size
			Document document = new Document(PageSize.A4, 20, 20, 20, 20);
			String assets = ServletActionContext.getServletContext().getRealPath("/assets");
			PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(assets + "/temp1" + ".pdf"));
			document.open();

			// Add image on the document
			Image image = Image.getInstance(assets + "/ws.jpg");
			image.scaleToFit(90f, 90f);
			image.setAlignment(Image.MIDDLE);
			image.setAbsolutePosition(70, 770);
			image.scaleAbsolute(100, 40);
			document.add(image);

			// Creating the paragraph and add it to document
			Paragraph p1 = new Paragraph(" WebSparrow Technologies ",
					FontFactory.getFont(FontFactory.HELVETICA, 14, Font.BOLD, new BaseColor(0, 0, 0)));

			Paragraph p2 = new Paragraph("Employee Details",
					FontFactory.getFont(FontFactory.HELVETICA, 10, Font.UNDERLINE, new BaseColor(0, 0, 0)));
			Paragraph p3 = new Paragraph("\n\n\n");
			p1.setAlignment(Element.ALIGN_CENTER);
			p2.setAlignment(Element.ALIGN_CENTER);
			document.add(p1);
			document.add(p2);
			document.add(p3);

			// Create table and set the width of columns
			PdfPTable t = new PdfPTable(6);
			float widths[] = { 2, 4, 5, 3, 3, 3 };
			t.setWidths(widths);
			t.setHeaderRows(1);
			t.setTotalWidth(100f);

			// Add table header row
			PdfPCell c1 = new PdfPCell(new Phrase("Emp Id", FontFactory.getFont(FontFactory.HELVETICA, 8)));
			c1.setBorderWidth((float) 0.25);
			c1.setBackgroundColor(new BaseColor(232, 232, 232));
			c1.setHorizontalAlignment(Element.ALIGN_CENTER);
			c1.setVerticalAlignment(Element.ALIGN_MIDDLE);
			t.addCell(c1);

			c1 = new PdfPCell(new Phrase("Name", FontFactory.getFont(FontFactory.HELVETICA, 8)));
			c1.setBorderWidth(0.25f);
			c1.setBackgroundColor(new BaseColor(232, 232, 232));
			c1.setHorizontalAlignment(Element.ALIGN_CENTER);
			c1.setVerticalAlignment(Element.ALIGN_MIDDLE);
			t.addCell(c1);

			c1 = new PdfPCell(new Phrase("Designation", FontFactory.getFont(FontFactory.HELVETICA, 8)));
			c1.setBorderWidth(0.25f);
			c1.setBackgroundColor(new BaseColor(232, 232, 232));
			c1.setHorizontalAlignment(Element.ALIGN_CENTER);
			c1.setVerticalAlignment(Element.ALIGN_MIDDLE);
			t.addCell(c1);

			c1 = new PdfPCell(new Phrase("Department", FontFactory.getFont(FontFactory.HELVETICA, 8)));
			c1.setBorderWidth(0.25f);
			c1.setBackgroundColor(new BaseColor(232, 232, 232));
			c1.setHorizontalAlignment(Element.ALIGN_CENTER);
			c1.setVerticalAlignment(Element.ALIGN_MIDDLE);
			t.addCell(c1);

			c1 = new PdfPCell(new Phrase("Salary", FontFactory.getFont(FontFactory.HELVETICA, 8)));
			c1.setBorderWidth((float) 0.25);
			c1.setBackgroundColor(new BaseColor(232, 232, 232));
			c1.setHorizontalAlignment(Element.ALIGN_CENTER);
			c1.setVerticalAlignment(Element.ALIGN_MIDDLE);
			t.addCell(c1);

			c1 = new PdfPCell(new Phrase("Location", FontFactory.getFont(FontFactory.HELVETICA, 8)));
			c1.setBorderWidth(0.25f);
			c1.setBackgroundColor(new BaseColor(232, 232, 232));
			c1.setHorizontalAlignment(Element.ALIGN_CENTER);
			c1.setVerticalAlignment(Element.ALIGN_MIDDLE);
			t.addCell(c1);

			// Add the table details row
			Phrase ph;
			rs = org.websparrow.ReportDAO.empDetails();
			int ctr = 0;
			if (rs != null) {
				while (rs.next()) {
					ctr++;

					c1 = new PdfPCell();
					c1.setBorderWidth((float) 0.25);
					c1.setHorizontalAlignment(Element.ALIGN_CENTER);
					ph = new Phrase(Integer.toString(ctr), FontFactory.getFont(FontFactory.HELVETICA, 8));
					c1.addElement(ph);
					t.addCell(c1);

					c1 = new PdfPCell();
					c1.setBorderWidth((float) 0.25);
					c1.setHorizontalAlignment(Element.ALIGN_CENTER);
					ph = new Phrase(rs.getString(1), FontFactory.getFont(FontFactory.HELVETICA, 8));
					c1.addElement(ph);
					t.addCell(c1);

					c1 = new PdfPCell();
					c1.setBorderWidth((float) 0.25);
					c1.setHorizontalAlignment(Element.ALIGN_CENTER);
					ph = new Phrase(rs.getString(2), FontFactory.getFont(FontFactory.HELVETICA, 8));
					c1.addElement(ph);
					t.addCell(c1);

					c1 = new PdfPCell();
					c1.setBorderWidth((float) 0.25);
					c1.setHorizontalAlignment(Element.ALIGN_CENTER);
					ph = new Phrase(rs.getString(3), FontFactory.getFont(FontFactory.HELVETICA, 8));
					c1.addElement(ph);
					t.addCell(c1);

					c1 = new PdfPCell();
					c1.setBorderWidth((float) 0.25);
					c1.setHorizontalAlignment(Element.ALIGN_CENTER);
					ph = new Phrase(Long.toString(rs.getLong(4)), FontFactory.getFont(FontFactory.HELVETICA, 8));
					c1.addElement(ph);
					t.addCell(c1);

					c1 = new PdfPCell();
					c1.setBorderWidth((float) 0.25);
					c1.setHorizontalAlignment(Element.ALIGN_CENTER);
					ph = new Phrase(rs.getString(5), FontFactory.getFont(FontFactory.HELVETICA, 8));
					c1.addElement(ph);
					t.addCell(c1);

				}
			}

			// Finally add all into the document
			response.setHeader("Content-disposition", "inline; filename=\"Employee Details.pdf\"");
			document.add(t);
			document.close();
			addPageNumber("temp1.pdf", "newName.pdf", response, request);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}

	// Method for adding page number on the document
	public static void addPageNumber(String oldFileName, String newFileName, HttpServletResponse response,
			HttpServletRequest request) {
		try {
			String realPath = ServletActionContext.getServletContext().getRealPath("/assets");
			FileInputStream fis = new FileInputStream(realPath + "/" + "temp1.pdf");
			PdfReader reader = new PdfReader(fis);
			int totalPages = reader.getNumberOfPages();
			PdfStamper stamper = new PdfStamper(reader, response.getOutputStream());
			for (int i = 1; i <= totalPages; i++) {
				getHeaderTable(i, totalPages).writeSelectedRows(0, -1, 34, 30, stamper.getOverContent(i));
			}
			stamper.close();
			fis.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static PdfPTable getHeaderTable(int x, int y) {
		PdfPTable table = new PdfPTable(2);
		try {
			table.setTotalWidth(490);
			table.setLockedWidth(true);
			table.getDefaultCell().setFixedHeight(20);
			table.getDefaultCell().setBorder(Rectangle.NO_BORDER);

			PdfPCell cell = new PdfPCell(new com.itextpdf.text.Phrase((""),
					new com.itextpdf.text.Font(com.itextpdf.text.Font.FontFamily.HELVETICA, 5)));
			cell.setBorder(Rectangle.NO_BORDER);
			table.addCell(cell);

			table.getDefaultCell().setHorizontalAlignment(Element.ALIGN_RIGHT);
			table.getDefaultCell().setBorder(Rectangle.NO_BORDER);

			cell = new PdfPCell(new com.itextpdf.text.Phrase(String.format("Page %d of %d", x, y),
					new com.itextpdf.text.Font(com.itextpdf.text.Font.FontFamily.HELVETICA, 5)));
			cell.setBorder(Rectangle.NO_BORDER);
			cell.setHorizontalAlignment(Element.ALIGN_RIGHT);

			table.addCell(cell);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return table;

	}

	@Override
	public void setServletRequest(HttpServletRequest request) {
		this.request = request;
	}

	public HttpServletRequest getServletRequest() {
		return request;
	}

	@Override
	public void setServletResponse(HttpServletResponse response) {
		this.response = response;
	}

	public HttpServletResponse getServletResponse() {
		return response;
	}
}

Create JSP Page

Create a JSP page to send the request to action class.

<%@ taglib prefix="s" uri="/struts-tags"%>
<html>
<head>
<title>Struts 2 + iText + MySQL Dynamic PDF Report Example</title>
</head>
<body>
	<h1>Struts 2 + iText + MySQL Dynamic PDF Report Example</h1>
	<s:form action="itextreport">
		<s:submit value="Generate Report"></s:submit>
	</s:form>
</body>
</html>

Map Action Class in Struts.xml

Map the action class in the struts.xml and do not configure the result.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 2.0//EN"
"http://struts.apache.org/dtds/struts-2.0.dtd">
<struts>
	<constant name="struts.devMode" value="true" />
	<package name="default" namespace="/" extends="struts-default">
		<action name="itextreport" class="org.websparrow.ReportAction">
		</action>
	</package>
</struts>

Output :

Now everything is all set. Start your server and deploy the project.