How do I fill my HashMap with information from my

Question

I am trying to get my HashMap parameters to be populated with information from my database. I have created a SQL query that retrieves the information, and a ResultSet to display the information. I then use the ResultSet to break the information down in a way that will allow me to assign each value to a string. Here is my current code:

    Connection connection;
    Statement stmt;
    ResultSet rs;

    try {

        Class.forName("org.h2.Driver");
        connection = DriverManager.getConnection("jdbc:h2:./GiftCertificateManagerDatabase;AUTO_SERVER=TRUE");
        String query = "SELECT CertificateCode, FirstName, LastName, IssueDate, ExpirationDate, Used FROM giftcertificates";
        stmt = connection.createStatement();
        rs = stmt.executeQuery(query);

        JRResultSetDataSource rsdt = new JRResultSetDataSource(rs);
        JasperReport jasperReport;
        JasperPrint jasperPrint;

        jasperReport = JasperCompileManager.compileReport("Reports/GiftCertificateReport.jrxml");
        HashMap parameters = new HashMap();

        rs.next();

        String CertificateCode = rs.getString("CertificateCode");
        String FirstName = rs.getString("FirstName");
        String LastName = rs.getString("LastName");
        String IssueDate = rs.getString("IssueDate");
        String ExpirationDate = rs.getString("ExpirationDate");
        String Used = rs.getString("Used");

        parameters.put("CertificateCode", CertificateCode);
        parameters.put("FirstName", FirstName);
        parameters.put("LastName", LastName);
        parameters.put("IssueDate", IssueDate);
        parameters.put("ExpirationDate", ExpirationDate);
        parameters.put("Used", Used);

        jasperPrint = JasperFillManager.fillReport(jasperReport, parameters, rsdt);

        JasperViewer view = new JasperViewer(jasperPrint);

        view.setVisible(true);

        System.out.println(rs);
        connection.close();
    } catch (ClassNotFoundException | SQLException | JRException ex) {
        ex.printStackTrace();
        JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
    }

As you can see, I have used rs.next(); to go to the first row of the database. It pulls the information in, and is actually sent to my report, which is great. It shows up on my report twice, however. Here is how my report looks (click here for larger image):

Here is my JRXML code:

<?xml version="1.0" encoding="UTF-8"?> <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="report name" pageWidth="595" pageHeight="842" columnWidth="535" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="29a25e63-6738-4f28-a9b1-84d9571ae46e"> <property name="ireport.zoom" value="1.0"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> <parameter name="CertificateCode" class="java.lang.String"/> <parameter name="FirstName" class="java.lang.String"/> <parameter name="LastName" class="java.lang.String"/> <parameter name="IssueDate" class="java.lang.String"/> <parameter name="ExpirationDate" class="java.lang.String"/> <parameter name="Used" class="java.lang.String"/> <background> <band splitType="Stretch"/> </background> <columnHeader> <band height="20" splitType="Stretch"> <staticText> <reportElement x="0" y="0" width="100" height="20" uuid="bb628493-cab6-4d39-acff-79669009ac6b"/> <text><![CDATA[Certificate Code]]></text> </staticText> <staticText> <reportElement x="100" y="0" width="100" height="20" uuid="2c18a226-2bdf-45e1-b286-f7795cf1d17f"/> <text><![CDATA[First Name]]></text> </staticText> <staticText> <reportElement x="200" y="0" width="100" height="20" uuid="e6b35970-0592-4d97-a054-fb0c65244618"/> <text><![CDATA[Last Name]]></text> </staticText> <staticText> <reportElement x="300" y="0" width="100" height="20" uuid="ee345d19-12f4-4ea7-a9d9-924c50904cb9"/> <text><![CDATA[Issue Date]]></text> </staticText> <staticText> <reportElement x="400" y="0" width="100" height="20" uuid="c5d69f5e-e716-4393-9815-d9cee8bab88b"/> <text><![CDATA[Expiration Date]]></text> </staticText> <staticText> <reportElement x="500" y="0" width="55" height="20" uuid="9760f598-6e30-45ad-9d05-2696f6d45c8e"/> <text><![CDATA[Used?]]></text> </staticText> </band> </columnHeader> <detail> <band height="20" splitType="Stretch"> <textField> <reportElement x="0" y="0" width="100" height="20" uuid="34b20574-2e15-41c6-928d-2dc52b9c4e7e"/> <textFieldExpression><![CDATA[$P{CertificateCode}]]></textFieldExpression> </textField> <textField> <reportElement x="100" y="0" width="100" height="20" uuid="1652ab96-50cd-4ce3-84b1-83bf074b480c"/> <textFieldExpression><![CDATA[$P{FirstName}]]></textFieldExpression> </textField> <textField> <reportElement x="200" y="0" width="100" height="20" uuid="bcfec07b-d07d-474b-a397-781b9ce0254f"/> <textFieldExpression><![CDATA[$P{LastName}]]></textFieldExpression> </textField> <textField> <reportElement x="300" y="0" width="100" height="20" uuid="cdd427e7-b1cf-4ed3-9cfe-8a13e71c0e0d"/> <textFieldExpression><![CDATA[$P{IssueDate}]]></textFieldExpression> </textField> <textField> <reportElement x="400" y="0" width="100" height="20" uuid="1f64f071-8d53-4de2-be05-23e78753f467"/> <textFieldExpression><![CDATA[$P{ExpirationDate}]]></textFieldExpression> </textField> <textField> <reportElement x="500" y="0" width="55" height="20" uuid="38402e3c-3279-4a76-85f8-0266d77e34e1"/> <textFieldExpression><![CDATA[$P{Used}]]></textFieldExpression> </textField> </band> </detail> </jasperReport>

Here is the image of my iReport designer (click here for larger image):

I obviously need to use a while loop, such as while (rs.next()) { }, but where do I put it, and what code do I put inside of the loop, in order to get all of the rows from the database to be inserted into the report?

As always, thank you in advance for any helpful answers.

Answer 1

I think you are confusing the report detail provided by JRDataSource with the parameter map.

The parameter map is a set of key-values you can display on the report.

The JRDataSource is useful to fill the detail data.

What is the difference between variable, parameter and field in JasperReports?

Answer 2

List<?> list=new ArrayList<HashMap<String,String>>();
while(rs.hasNext()){
    rs.next();
    HashMap<String,String> parameters=new HashMap<>();

    String CertificateCode = rs.getString("CertificateCode");
    String FirstName = rs.getString("FirstName");
    String LastName = rs.getString("LastName");
    String IssueDate = rs.getString("IssueDate");
    String ExpirationDate = rs.getString("ExpirationDate");
    String Used = rs.getString("Used");

    parameters.put("CertificateCode", CertificateCode);
    parameters.put("FirstName", FirstName);
    parameters.put("LastName", LastName);
    parameters.put("IssueDate", IssueDate);
    parameters.put("ExpirationDate", ExpirationDate);
    parameters.put("Used", Used);

  list.add(parameters);

}

Result;

[

{Certificatecode:lskff,Firstname:asdkff,lastname:lsdkfs,issuedate:sdjflksf,expirationdate:lsdfjlsdf,used:skdfjl},

{Certificatecode:lskff,Firstname:asdkff,lastname:lsdkfs,issuedate:sdjflksf,expirationdate:lsdfjlsdf,used:skdfjl},

{Certificatecode:lskff,Firstname:asdkff,lastname:lsdkfs,issuedate:sdjflksf,expirationdate:lsdfjlsdf,used:skdfjl}

]