How to create simple Report in jsp with MySQL database

    Prerequisite

1. Install Java SDK 1.6 or higher
2. Install eclipse” Indigo Release”.
3. Install Tomcat Apache server 6 or 7 version.
4. Install MySQL 5.5

Jar files

1. el-api.jar
2.  iText-2.1.7.jar
3.   jsp-api.jar
4.   mysql-connector-java-5.0.5.jar.
5. servlet-api.jar

Create database  “messageapps” and  “message” Table.

CREATE DATABASE messageapps ;

CREATE TABLE  message(

  massage_id  int(10) unsigned NOT NULL AUTO_INCREMENT,

  name  varchar(45) NOT NULL,

  email  varchar(45) NOT NULL,

  message  varchar(250) NOT NULL,

  time  datetime DEFAULT NULL,

  PRIMARY KEY (massage_id)

) ;

Steps to create PDF Report file 

1.  Click on File->new->project then select “Dynamic web project “.

   
   

   
   
   

   2. Paste the jars file in webContent->WebContent->WEB-INF->lib folder.

   ·         el-api.jar
   ·         iText-2.1.7.jar
   ·         jsp-api.jar
   ·         mysql-connector-java-5.0.5.jar
   ·         servlet-api.jar
   
   

   3. Create package in src folder “connector”.

   4. Right click on “connector” package

   5. Select New -> Class , after that windows show

   6. Type “DBConnector” class name.

   7. Write following DBConnector class 

   
   
   package connector;
   import java.sql.Connection;
   import java.sql.DriverManager;
   /*
         @auther :Abdul Mugeesh
         this DBConnector class created connection instance.
         getConnection() method return connection object if not exist.
   */
   public class DBConnector {
         public static String url = "jdbc:mysql://localhost:3306/";
         public static String dbName = "messageapps";
         public static String userName = "root";
         public static String password = "root";
               public static Connection getConnection() {
               Connection connection = null;
               if (connection != null)
               {
                     return connection;
               }
               try {
                          
                          Class.forName("com.mysql.jdbc.Driver").newInstance();
                           connection = DriverManager.getConnection(url+dbName,userName,password);
               } catch (Exception e) {
                     e.printStackTrace();
               }
               return connection;
         }
   }
   
   
   
   

   8.Project hierarchy 

   

   9. Right Click WebContent->new ->JSP file type JSP filename “pdfGen.jsp” or as you want.

   Write following code 
   
   
   <%@page import="javax.swing.border.TitledBorder"%>
   <%@page import="java.sql.Connection"%>
   <%@page import="java.sql.ResultSet"%>
   <%@page import="java.sql.PreparedStatement"%>
   <%@page import="com.lowagie.text.DocumentException"%>
   <%@page import="com.lowagie.text.Paragraph"%>
   <%@page import="com.lowagie.text.html.HtmlWriter"%>
   <%@page import="com.lowagie.text.pdf.PdfWriter"%>
   <%@page import="com.lowagie.text.Document"%>
   <%@page import="java.io.*"%>;
   <%@page import="java.awt.*"%>
   <%@page import="javax.servlet.*"%>
   <%@page import="javax.servlet.http.*"%>
   <%@page import="com.lowagie.text.*"%>
   <%@page import="com.lowagie.text.pdf.*"%>
   <%@page import="com.lowagie.text.html.*"%>
   <%@page import="connector.DBConnector"%>
   <%@page import="java.awt.*"%>
   <%@page import="java.io.IOException"%>
   <%@page import="javax.servlet.*"%>
   <%@page import="javax.servlet.http.*"%>
   <%@page import="com.lowagie.text.*"%>
   <%@page import="com.lowagie.text.pdf.*"%>
   <%@page import="com.lowagie.text.html.*"%>
   <%
   String str="pdf";
   
   Connection conn=null;
   PreparedStatement ps=null;
   ResultSet rs=null;
   try
   {
       Document document=new Document();
       if(str.equals("pdf"))
       {
           response.setContentType("application/pdf");
           PdfWriter.getInstance(document,response.getOutputStream());
       }
       conn= DBConnector.getConnection();
       String query = "select * from message";          //Fetching data from table
          ps=conn.prepareStatement(query);                // executing query
          rs=ps.executeQuery();
         
       document.open();
   
       /* new paragraph instance initialized and add function write in pdf file*/
       document.add(new Paragraph("---------------------------------------------------------REPORT---------------------------------------------------------\n\n"));
       document.add(new Paragraph("                              CREATED REPORT BY-Abdul Mugeesh\n\n"));
       document.add(new Paragraph("---------------------------------------------------------------------------------------------------------------------------------"));
       document.addCreationDate();
      
       while(rs.next())
       {
             // fetch & writing records in pdf files
           document.add(new Paragraph("MSG_ID ::"+rs.getString(1)+"\nTIME ::"+rs.getString(5)+"\nNAME ::"+rs.getString(2)+"\nEMAIL ::"+rs.getString(3)+"\nMESSAGE ::"+rs.getString(4)+"\n\n"));
       }
       document.add(new Paragraph("---------------------------------------------------------PAGE NO::"+document.getPageNumber()+"------------------------------------------------------"));
      
       document.close(); //document instance closed
       conn.close();  //db connection close
   }
   catch(Exception de)
   {
           de.printStackTrace();
               System.err.println("document: " + de.getMessage());
              
   }
   
   
   %>
   

   10. Start Tomcat server

   11. Run pdfGen.jsp

   It will show you following output
   
   
   
   
   bingo, Your all tables record will be displayed in PDF file and you can also save this file .