Result set from database to itextpdf

Question

I'm new in java and I use itextpdf as my output.
Right now I am so flustered by this problem.

My problem is that I want to display the resultset from the database in pdf format with a twist.

for example, i will set a table with 3 columns,
and these are the resultset from the database,

Name
John
Jane
Mary
Sonny
Kiel

so now the output in itextpdf should be viewed like this

I want the results to be inserted in every column and i don't have any idea how to do this.

anyone? it would be nice if someone can guide me.

Document document = new Document(PageSize.A4, 25, 25, 25, 25);
PdfWriter writer = PdfWriter.getInstance(document,new FileOutputStream("D:\\PURCHASEORDER\\"+see+".pdf"));
document.open();
try {
    Class.forName(driver);
    conn = DriverManager.getConnection(url+db, user, pass);
    Statement st = conn.createStatement();
    String zero = dates.getSelectedItem().toString();
    String sql = "select name as hehe from names where  servedate = '"+zero+"'";
    pst=conn.prepareStatement(sql);
    rs=pst.executeQuery();

    Rectangle react = writer.getPageSize();
    PdfPTable table2 = new PdfPTable(new float[] { 3,3,3});
    table2.setTotalWidth(527);
    table2.getDefaultCell().setBorder(Rectangle.NO_BORDER);
    PdfPCell cell = new PdfPCell(new Paragraph(""));
    cell.setColspan(8);
    cell.setHorizontalAlignment(Element.ALIGN_LEFT);
    cell.setBackgroundColor(BaseColor.GRAY);
    table2.addCell(cell);
    table2.getDefaultCell().setHorizontalAlignment(Element.ALIGN_CENTER);

    while(rs.next()){
        String v1 = rs.getString("hehe");
        FontFactory.getFont(FontFactory.TIMES_BOLD,14,BaseColor.BLACK)));
        table2.addCell(new Paragraph("TOTAL Number: "+v1+"", FontFactory.getFont(FontFactory.TIMES_ROMAN,12,BaseColor.BLACK)));
        table2.addCell(new Paragraph("TOTAL Number: "+v1+"", FontFactory.getFont(FontFactory.TIMES_ROMAN,12,BaseColor.BLACK)));
        table2.addCell(new Paragraph("TOTAL Number: "+v1+"", FontFactory.getFont(FontFactory.TIMES_ROMAN,12,BaseColor.BLACK)));

    }
    table2.setWidthPercentage(100);
    document.add(table2);

} catch (Exception e) {
    JOptionPane.showMessageDialog(null, e);
}
document.close();
String pdfFile="D:\\PURCHASEORDER\\"+see+".pdf";
File f = new File(pdfFile);
if (pdfFile.toString().endsWith(".pdf")) {
    Runtime.getRuntime().exec("rundll32 url.dll,FileProtocolHandler " + pdfFile);
} else {
    //For cross platform use
    Desktop desktop = Desktop.getDesktop();

    desktop.open(f);
}

Have you looked into using the PreparedStatement and ResultSet classes provided by java? — beastlyCoder, Dec 19 '16 at 02:11
yes i already know them, the only part I dont know is that how can i display them that each resultset will be inserted in each column. — The Newbie, Dec 19 '16 at 02:21

Bruno Lowagie · Answer 1 · 2016-12-19T08:10:06.930

This is wrong:

while(rs.next()){
    String v1 = rs.getString("hehe");
        FontFactory.getFont(FontFactory.TIMES_BOLD,14,BaseColor.BLACK)));
    table2.addCell(new Paragraph("TOTAL Number: "+v1+"", FontFactory.getFont(FontFactory.TIMES_ROMAN,12,BaseColor.BLACK)));
    table2.addCell(new Paragraph("TOTAL Number: "+v1+"", FontFactory.getFont(FontFactory.TIMES_ROMAN,12,BaseColor.BLACK)));
    table2.addCell(new Paragraph("TOTAL Number: "+v1+"", FontFactory.getFont(FontFactory.TIMES_ROMAN,12,BaseColor.BLACK)));
}

First of all, let's clean up the dirty coding by creating a Font object that can be reused:

Font font = FontFactory.getFont(FontFactory.TIMES_ROMAN,12,BaseColor.BLACK);

Second, let's fix the problem that you are adding the same value to each row:

String v1;
while(rs.next()){
    v1 = rs.getString("hehe");
    table2.addCell(new Paragraph("TOTAL Number: " + v1, font));
}

Finally, we know that iText only adds complete rows to a table, and in your example, Sonny and Kiel only fill two cells in a row of three, hence we need to complete the final row:

table2.completeRow();

Now we can add table2 to the document.

Some other remarks:

This is overkill:

PdfPTable table2 = new PdfPTable(new float[] { 3,3,3});

If you want a column with 3 columns that each have the same width, it's not necessary to pass a float[] with equal values, you can just do:

PdfPTable table2 = new PdfPTable(3);

This line doesn't make sense:

table2.setTotalWidth(527);

It doesn't make sense because:

You don't lock the width, hence the width of 527 will be ignored. You could add table.setLockedWidth(true);
You also have table2.setWidthPercentage(100); which defines the width as 100% of the available width.

You have to choose one or the other: absolute width or relative width. Having both doesn't make sense. Please read How to define the width of a cell?

This is totally wrong:

cell.setColspan(8);

You are defining a table with 3 columns, hence you set the colspan as if there are 8 columns. In a table with 3 columns, the maximum colspan is 3.

Finally:

You're a newbie and you're using iText 5. Why not use iText 7 since you're just starting? iText 7 is a rewrite of iText 5. It has a different, more future-proof API. Tables are explained here.

Ok thank you so much i will try this. sorry im really new to this. now im studying itext7. — The Newbie, Dec 28 '16 at 02:25

score 0 · Answer 2 · edited May 23 '17 at 12:33

0

Why don't you use resultsetMetadata option to get the count of fields in a row, iterate and tab or pipe separate each value. Append newline character at the end of the row. hasNext of resultset will run through all the records doing the same operation. Save the entire thing in a list. Use that list to write into PDF.

OpenCsv | SQL | Writes only the header and not the table content

edited May 23 '17 at 12:33

Community

1
1

answered Dec 19 '16 at 03:20

Shreyas SG

368
3
6
21

Result set from database to itextpdf

2 Answers2