Hot questions for Using JasperReports in mysql

Question:

I am trying to convert the English numbers to Bengali numbers before filling the report. The data i am getting from MySQL:

SELECT brand.id,brand.brand,model.model FROM brand INNER JOIN model ON brand.id=model.id AND model.id=$P{ID}

For example if model.model columns return a number 322 then it should first convert the number to Bengali version(৩২২) and then it will fill the report.Something like this:

One | two | Three ১ | ২ | ৩

Here is the 0-9 in Bengali:

০ ১ ২ ৩ ৪ ৫ ৬ ৭ ৮ ৯

I was thinking about replace method as follows:

char[] en ={'0','1','2','3','4','5','6','7','8','9'};
char[] bn ={'০','১','২','৩','৪','৫','৬','৭','৮','৯'};
en.replace(bn)

Perhaps it won't work!

I am just not sure where to start ! But here is my current Application Codes:

The Java Code:

@FXML
public TextField one;

public void click(ActionEvent event) throws JRException, SQLException, ClassNotFoundException, IllegalAccessException, UnsupportedLookAndFeelException, InstantiationException {
    String reportSrcFile = "/home/sample/learn.jrxml";

    JasperReport jasperReport1 = JasperCompileManager.compileReport(reportSrcFile);

    Connection conn = SqliConnect.getMySQLConnection();
    int two = Integer.parseInt(one.getText());
    Map<String, Object> parameters = new HashMap<String, Object>();
    parameters.put("ID",one.getText());

    JasperPrint print = JasperFillManager.fillReport(jasperReport1,
            parameters, conn);

    JasperViewer jv = new JasperViewer(print);
    jv.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    jv.setTitle("Test Report");

    jv.setVisible(true);
}

Report's template:

<subDataset name="Dataset1" uuid="2691431f-5c6f-403f-94cc-829c17ef1636">
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Learn"/>
    <parameter name="ID" class="java.lang.Integer">
        <parameterDescription><![CDATA[Get ID]]></parameterDescription>
        <defaultValueExpression><![CDATA[$P{ID}]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT brand.id,brand.brand,model.model FROM brand INNER JOIN model ON brand.id=model.id WHERE model.id=$P{ID}]]>
    </queryString>
    <field name="id" class="java.lang.Integer"/>
    <field name="brand" class="java.lang.String"/>
    <field name="model" class="java.lang.String"/>
</subDataset>
<parameter name="ID" class="java.lang.Integer" isForPrompting="false">
    <parameterDescription><![CDATA[]]></parameterDescription>
    <defaultValueExpression><![CDATA[$P{ID}]]></defaultValueExpression>
</parameter>
<queryString>
    <![CDATA[SELECT brand.id,brand.brand,model.model FROM brand INNER JOIN model ON brand.id=model.id WHERE model.id=$P{ID}]]>
</queryString>
<field name="id" class="java.lang.Integer"/>
<field name="brand" class="java.lang.String"/>
<field name="model" class="java.lang.String"/>
<title>
    <band height="79" splitType="Stretch">
        <textField>
            <reportElement x="11" y="16" width="100" height="30" uuid="d1485589-a4cf-4ab9-b896-0ef480beced4"/>
            <textFieldExpression><![CDATA[$P{ID}]]></textFieldExpression>
        </textField>
    </band>
</title>
<detail>
    <band height="250" splitType="Stretch">
        <componentElement>
            <reportElement x="0" y="0" width="555" height="250" uuid="e199bd58-8408-4711-85d5-ba76db9691b7">
                <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                <property name="com.jaspersoft.studio.table.style.table_header" value="Table_TH"/>
                <property name="com.jaspersoft.studio.table.style.column_header" value="Table_CH"/>
                <property name="com.jaspersoft.studio.table.style.detail" value="Table_TD"/>
                <property name="com.jaspersoft.studio.components.autoresize.proportional" value="true"/>
            </reportElement>
            <jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
                <datasetRun subDataset="Dataset1" uuid="fbb4dafa-4284-4118-9d2b-46d88c63a31b">
                    <datasetParameter name="ID">
                        <datasetParameterExpression><![CDATA[$P{ID}]]></datasetParameterExpression>
                    </datasetParameter>
                    <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                </datasetRun>
                <jr:column width="185" uuid="9a6b765f-b6d4-4753-909e-dd091b296654">
                    <jr:columnHeader style="Table_CH" height="30">
                        <staticText>
                            <reportElement x="0" y="0" width="185" height="30" uuid="7e644d1c-a893-49df-a18a-bf788eb6b92a"/>
                            <text><![CDATA[id]]></text>
                        </staticText>
                    </jr:columnHeader>
                    <jr:columnFooter style="Table_CH" height="30"/>
                    <jr:detailCell style="Table_TD" height="30">
                        <textField>
                            <reportElement x="0" y="0" width="185" height="30" uuid="848a53bf-2b2e-46df-a7aa-26fc0b897c85"/>
                            <textFieldExpression><![CDATA[$F{id}]]></textFieldExpression>
                        </textField>
                    </jr:detailCell>
                </jr:column>
                <jr:column width="185" uuid="514580a5-f09c-43d1-952e-d4769c2e4686">
                    <jr:columnHeader style="Table_CH" height="30">
                        <staticText>
                            <reportElement x="0" y="0" width="185" height="30" uuid="c01152e9-1547-472b-946e-92011c02bc94"/>
                            <text><![CDATA[brand]]></text>
                        </staticText>
                    </jr:columnHeader>
                    <jr:columnFooter style="Table_CH" height="30"/>
                    <jr:detailCell style="Table_TD" height="30">
                        <textField>
                            <reportElement x="0" y="0" width="185" height="30" uuid="98c84ae5-b289-459a-b34b-a9becf43b9ce"/>
                            <textFieldExpression><![CDATA[$F{brand}]]></textFieldExpression>
                        </textField>
                    </jr:detailCell>
                </jr:column>
                <jr:column width="185" uuid="5c1d2026-45bc-4ec8-8be4-c444e47d093d">
                    <jr:columnHeader style="Table_CH" height="30">
                        <staticText>
                            <reportElement x="0" y="0" width="185" height="30" uuid="81f76de4-a68b-41c3-a2e8-bbaa625b71be"/>
                            <text><![CDATA[model]]></text>
                        </staticText>
                    </jr:columnHeader>
                    <jr:columnFooter style="Table_CH" height="30"/>
                    <jr:detailCell style="Table_TD" height="30">
                        <textField>
                            <reportElement x="0" y="0" width="185" height="30" uuid="d69c690e-05a4-453a-93ea-9061523975ad"/>
                            <textFieldExpression><![CDATA[$F{model}]]></textFieldExpression>
                        </textField>
                    </jr:detailCell>
                </jr:column>
            </jr:table>
        </componentElement>
    </band>
</detail>

What is the Right way to do it?


Answer:

You also could create a replacement String in the Java code and use it in the parameters map:

   String replacedOne = one.getText().replaceAll("0","০").replaceAll("1","১").replaceAll("2","২").replaceAll("3","৩").replaceAll("4","৪").replaceAll("5","৫").replaceAll("6","৬").replaceAll("7","৭").replaceAll("8","৮").replaceAll("9","৯");
   parameters.put("replacedID",replacedOne);

Or better, you can perform the replacement directly in your jrxml file, with no need to modify the java file, by using this (very long) expression instead of $F{model}:

$F{model}.replaceAll("0","০").replaceAll("1","১").replaceAll("2","২").replaceAll("3","৩").replaceAll("4","৪").replaceAll("5","৫").replaceAll("6","৬").replaceAll("7","৭").replaceAll("8","৮").replaceAll("9","৯");

Question:

I am trying to pass 2 parameters into Jasper report and get the result. In SQL query, I am combining them with the query as below.

SELECT 
ROUND(SUM(case when Updated_Date BETWEEN $P{secondYear}+"-03-01" AND $P{secondYear}+"-03-31" then Amount else 0 end),2) AS `March` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{secondYear}+"-02-01" AND $P{secondYear}+"-02-29" then Amount else 0 end),2) AS `February` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{secondYear}+"-01-01" AND $P{secondYear}+"-01-31" then Amount else 0 end),2) AS `January` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-12-01" AND $P{firstYear}+"-12-31" then Amount else 0 end),2) AS `December` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-11-01" AND $P{firstYear}+"-11-31" then Amount else 0 end),2) AS `November` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-10-01" AND $P{firstYear}+"-10-31" then Amount else 0 end),2) AS `October` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-09-01" AND $P{firstYear}+"-09-31" then Amount else 0 end),2) AS `September` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-08-01" AND $P{firstYear}+"-08-31" then Amount else 0 end),2) AS `August` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-07-01" AND $P{firstYear}+"-07-31" then Amount else 0 end),2) AS `July` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-06-01" AND $P{firstYear}+"-06-31" then Amount else 0 end),2) AS `June`, 
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-05-01" AND $P{firstYear}+"-05-31" then Amount else 0 end),2) AS `May`,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-04-01" AND $P{firstYear}+"-04-31" then Amount else 0 end),2) AS `April` 
FROM VAT

However, In my Jasper report, what I get is 0 as the VAT for all the months, all the time. What have I done wrong?


Answer:

I found the answer. In jasper report, if you want to combine parameters like String in your SQL Query, you should not use the + sign. So the correct code is below.

SELECT 
ROUND(SUM(case when Updated_Date BETWEEN $P{secondYear}"-03-01" AND $P{secondYear}"-03-31" then Amount else 0 end),2) AS `March` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{secondYear}"-02-01" AND $P{secondYear}"-02-29" then Amount else 0 end),2) AS `February` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{secondYear}"-01-01" AND $P{secondYear}"-01-31" then Amount else 0 end),2) AS `January` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-12-01" AND $P{firstYear}"-12-31" then Amount else 0 end),2) AS `December` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-11-01" AND $P{firstYear}"-11-31" then Amount else 0 end),2) AS `November` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-10-01" AND $P{firstYear}"-10-31" then Amount else 0 end),2) AS `October` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-09-01" AND $P{firstYear}"-09-31" then Amount else 0 end),2) AS `September` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-08-01" AND $P{firstYear}"-08-31" then Amount else 0 end),2) AS `August` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-07-01" AND $P{firstYear}"-07-31" then Amount else 0 end),2) AS `July` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-06-01" AND $P{firstYear}"-06-31" then Amount else 0 end),2) AS `June`, 
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-05-01" AND $P{firstYear}"-05-31" then Amount else 0 end),2) AS `May`,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-04-01" AND $P{firstYear}"-04-31" then Amount else 0 end),2) AS `April` 
FROM VAT

If you are using Jaspersoft Studio, it will display this as an error in Query editor. However, it works with no issues.

Question:

i have table is name Mr_items i need when i select any of this items i need to print into by group by in jasper report to column it_print

item_sn  |  item_name  | it_print
---------------------------------
1        |  pepsi      | drinks

2        |  sandwiches | foods

3        |  water      | drinks

4        |  burger     | foods

i try to print by this code

///print invoice cashier
    public void PrintInvoiceSection() throws JRException, IOException {

        try {

            /////////print///////////////////
            HashMap parameters = new HashMap();

            parameters.put("nameilll", cashier_name.getText().trim());
            parameters.put("datevist", sysdate_invc.getText().trim());
            parameters.put("timevist", systime_invc.getText().trim());
            parameters.put("invoice_type", InvoiceType.getText());
            parameters.put("invoiceno", inv_id);
            parameters.put("cus_name", cusName.getText().trim());
            parameters.put("cus_mobile", cusMobile.getText().trim());
            parameters.put("cus_address", cusAddrss.getText());
            parameters.put("inv_remark", customerBehestRemark.getText());
            parameters.put("inv_remark", invoiceRemark.getText());
            InputStream ul = getClass().getResourceAsStream("/printer/Printsection.jasper");
            java.util.Vector collection = new java.util.Vector();
            collection = new Vector(itemslist);
            JasperPrint jasperPrint = JasperFillManager.fillReport(ul, parameters, new JRBeanCollectionDataSource(collection));
            JasperViewer jv = new JasperViewer(jasperPrint, false);
            jv.setVisible(false);
            PrintReportToPrinterSection(jasperPrint);
        } catch (JRException ex) {
            Logger.getLogger(SalesinterfaceController.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

and this code also for select the printer

private void PrintReportToPrinterSection(JasperPrint jp) throws JRException {
//      getSectionPrint();
        // TODO Auto-generated method stub
        PrintRequestAttributeSet printRequestAttributeSet = new HashPrintRequestAttributeSet();
        // printRequestAttributeSet.add(MediaSizeName.ISO_A4); //setting page size
        printRequestAttributeSet.add(new Copies(1));

        PrinterName printerName = new PrinterName(cashPrint.getText(), null); //gets printer 

        PrintServiceAttributeSet printServiceAttributeSet = new HashPrintServiceAttributeSet();
        printServiceAttributeSet.add(printerName);

        JRPrintServiceExporter exporter = new JRPrintServiceExporter();

        exporter.setParameter(JRExporterParameter.JASPER_PRINT, jp);
        exporter.setParameter(JRPrintServiceExporterParameter.PRINT_REQUEST_ATTRIBUTE_SET, printRequestAttributeSet);
        exporter.setParameter(JRPrintServiceExporterParameter.PRINT_SERVICE_ATTRIBUTE_SET, printServiceAttributeSet);
        exporter.setParameter(JRPrintServiceExporterParameter.DISPLAY_PAGE_DIALOG, Boolean.FALSE);
        exporter.setParameter(JRPrintServiceExporterParameter.DISPLAY_PRINT_DIALOG, Boolean.FALSE);
        exporter.getPrintService();
    }

i try this syntax

SELECT item_sn, item_name, it_print,
       IF($P{consolidate_loc}, concat(itemname, ', ', itemprice, ' ', itemprint ), Mr_items) AS groupField
FROM 
GROUP BY groupField;

i cant to include it into jasper report because i don't i know about the field in jasper i'm new bie on it :)


Answer:

Finally i found the solution

////this class for print form into printers

public void PrintReportToPrinterSection(JasperPrint jp, String pp) throws PrinterException {
try{

        // TODO Auto-generated method stub
        PrintRequestAttributeSet printRequestAttributeSet = new HashPrintRequestAttributeSet();
        // printRequestAttributeSet.add(MediaSizeName.ISO_A4); //setting page size
        printRequestAttributeSet.add(new Copies(1));

        PrinterName printerName = new PrinterName(pp, null); //gets printer 

        PrintServiceAttributeSet printServiceAttributeSet = new HashPrintServiceAttributeSet();
        printServiceAttributeSet.add(printerName);

            PrinterJob printerJob = PrinterJob.getPrinterJob();
            PrintService[] services = PrintServiceLookup.lookupPrintServices(null, null);
            PrintService selectedService = null;

            if(services.length != 0 || services != null)
            {
                for(PrintService service : services){
                    String existingPrinter = service.getName().toLowerCase();
            Object selectedPrinter = pp;
                    if(existingPrinter.equals(selectedPrinter))
                    {
                        selectedService = service;
                        break;
                    }
                }

                if(selectedService != null)
                {
                    printerJob.setPrintService(selectedService);
                    boolean printSucceed = JasperPrintManager.printReport(pp, false);
                }}
        JRPrintServiceExporter exporter = new JRPrintServiceExporter();

        exporter.setParameter(JRExporterParameter.JASPER_PRINT, jp);
        exporter.setParameter(JRPrintServiceExporterParameter.PRINT_REQUEST_ATTRIBUTE_SET, printRequestAttributeSet);
        exporter.setParameter(JRPrintServiceExporterParameter.PRINT_SERVICE_ATTRIBUTE_SET, printServiceAttributeSet);
        exporter.setParameter(JRPrintServiceExporterParameter.DISPLAY_PAGE_DIALOG, Boolean.FALSE);
        exporter.setParameter(JRPrintServiceExporterParameter.DISPLAY_PRINT_DIALOG, Boolean.FALSE);
        exporter.exportReport();
} catch (JRException e) {
    System.out.println(e);
}
    }

///print section printer form

///then i call the class into printSection form class /// then i do while loop to get for each row by row by group by printer name

public void PrintInvoiceSection() throws JRException, SQLException, PrinterException {

        try {
            String sql2 = "select pr_id as printId, pr_printer as printName from invoices_items where tr_inv_no='" + inv_id + "' Group BY pr_id";
            rs = con.createStatement().executeQuery(sql2);
            while (rs.next()) {
                int prind = rs.getInt("printId");
                String prnam = rs.getString("printName");
                JasperDesign jasperdesign = JRXmlLoader.load("C:\\Users\\Wael\\Documents\\NetBeansProjects\\resrr\\resturnopos\\src\\printer\\Printsection.jrxml");

                String sql = "SELECT resturno.mr_printsection.pr_section,\n" +
"   resturno.invoices_items.tr_it_name,\n" +
"       resturno.invoices_items.tr_date,\n" +
"       resturno.invoices_items.tr_inv_issued,\n" +
"   resturno.invoices_items.tr_it_price,\n" +
"   resturno.invoices_items.tr_vat,\n" +
"   resturno.invoices_items.tr_it_qty,\n" +
"   resturno.invoices_items.tr_it_tot,\n" +
"   resturno.invoices_items.tr_inv_no,\n" +
"   resturno.mr_invoicetyp.inv_name1,\n" +
"   resturno.invoices_items.inv_typ_id,\n" +
"   resturno.invoices_items.tr_it_name,\n" +
"   resturno.invoices_items.tr_it_price,\n" +
"   resturno.invoices_items.tr_vat,\n" +
"   resturno.invoices_items.tr_it_tot,\n" +
"   resturno.invoices_items.tr_it_qty,\n" +
"   resturno.mr_invoicetyp.inv_name1,\n" +
"   resturno.mr_invoicetyp.inv_name2,\n" +
"   resturno.mr_printsection.pr_section,\n" +
"   resturno.invoices_items.pr_id,\n" +
"   resturno.invoices_items.pr_printer\n" +
"FROM resturno.invoices_items\n" +
"   INNER JOIN resturno.mr_invoicetyp ON \n" +
"    resturno.mr_invoicetyp.inv_typ_id = resturno.invoices_items.inv_typ_id \n" +
"   INNER JOIN resturno.mr_printsection ON \n" +
"    resturno.mr_printsection.pr_id = resturno.invoices_items.pr_id \n" +
"WHERE  \n"
                    + "  resturno.invoices_items.tr_inv_no = '" + inv_id + "' \n"
                    + "  AND resturno.invoices_items.pr_id = '" + prind + "'"
                    + "  AND resturno.invoices_items.pr_printer = '" + prnam + "'";
                JRDesignQuery jrq = new JRDesignQuery();
                jrq.setText(sql);
                jasperdesign.setQuery(jrq);
                JasperReport jsp = JasperCompileManager.compileReport(jasperdesign);
                JasperPrint jasperPrint = JasperFillManager.fillReport(jsp, new HashMap(), con);
//              List jpList = new ArrayList();
//              jpList.add(j.loadObjectFromFile("build/reports/Report1.jrprint")); 
                JasperViewer jv = new JasperViewer(jasperPrint, false);
                jv.setVisible(true);
                PrintReportToPrinterSection(jasperPrint, prnam);
            }
        } catch (JRException ex) {
            System.out.println(ex);
        }
    }

Question:

I'm facing a very strange issue. I'm trying to generate report in DynamicReports API. The report is being generated without WHERE clause but is not being generated with WHERE clause. The database is MySQL. Any help is much appreciated. Here is the code:

Service class:

public class ReportSevice {
    public void createReport (String reportDate) {
        System.out.println(reportDate); 
        String sql_statement = null;
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/world","root", "password");
            sql_statement = "SELECT id, fName, lName, pickUpDate FROM reservations_db WHERE pickUpDate = " + reportDate;
        } catch (SQLException e) {
            e.printStackTrace();
            return;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return;
        } catch (Exception e) {
            e.printStackTrace();
        }

        JasperReportBuilder report = DynamicReports.report();
        report
        .columns(
                Columns.column("Customer Id", "id", DataTypes.integerType()),
                Columns.column("First Name", "fName", DataTypes.stringType()),
                Columns.column("Last Name", "lName", DataTypes.stringType()),
                Columns.column("Date", "pickUpDate", DataTypes.stringType()))
        .title(Components.text("********Report By Date********")
                .setHorizontalTextAlignment(HorizontalTextAlignment.CENTER))
        .pageFooter(Components.pageXofY())
        .setDataSource(sql_statement, connection);

        try {
            report.show();
        } catch (DRException e) {
            e.printStackTrace();
        }
    }
}

Answer:

At last I got it working. The solution is very simple. I'm sharing the code here for future users. Instead of

sql_statement = "SELECT id, fName, lName, pickUpDate FROM reservations_db WHERE pickUpDate = " + reportDate;

write this:

sql_statement = "SELECT id, fName, lName, pickUpDate FROM reservations_db WHERE pickUpDate=$P{reportDate}"