Grouping in Jasper Reports

Question

I am using jasper reports to generate a report. I am trying to group all the records of an item under one name. But the item names are displayed multiple type in a report

My Code is

// TODO Auto-generated method stub\\
    Logger.getLogger(getClass()).info("In Sales report servlet");
    String from = request.getParameter("from");
    String to = request.getParameter("to");
    System.out.println("From "+from+" To "+to);
    Connection connection = null;
     String connectionURL = "jdbc:mysql://localhost:3306/medicaminventory";
    try {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection(connectionURL, "root", "");
        StyleBuilder boldStyle         = stl.style().bold(); 

        StyleBuilder boldCenteredStyle = stl.style(boldStyle)

                                            .setHorizontalAlignment(HorizontalAlignment.CENTER);

        StyleBuilder columnTitleStyle  = stl.style(boldCenteredStyle)

                                            .setBorder(stl.pen1Point())

                                            .setBackgroundColor(Color.LIGHT_GRAY);

        StyleBuilder CenteredStyle = stl.style().setHorizontalAlignment(HorizontalAlignment.CENTER);
        StyleBuilder titleStyle         = stl.style(boldStyle).setFontSize(15);
        JasperReportBuilder report = DynamicReports.report();//a new report
        TextColumnBuilder<Integer> rowNumberColumn = col.reportRowNumberColumn("No.");
        TextColumnBuilder<java.util.Date> columndate = col.column("Date", "Date_Of_Sale", type.dateType()).setStyle(CenteredStyle);
        TextColumnBuilder<String>    columnItem = col.column("Item Name", "Item_Name", type.stringType()).setStyle(boldStyle);
        TextColumnBuilder<String>    columnCustomer = col.column("Customer Name", "Customer_name", type.stringType()).setStyle(CenteredStyle);
        TextColumnBuilder<Double>    columnunit = col.column("Unit Price", "Item_Price", type.doubleType()).setStyle(CenteredStyle);
        TextColumnBuilder<Integer>    columnqty = col.column("Qty", "Item_qty", type.integerType()).setStyle(CenteredStyle);
        TextColumnBuilder<BigDecimal>    columnsub = columnqty.multiply(columnunit).setTitle("Subtotal").setStyle(CenteredStyle);
        report
          .columns(
                  rowNumberColumn,columnItem,columnCustomer,columndate,columnunit,columnqty,columnsub
                  )
          .title(//title of the report
              Components.text("Sale Report").setStyle(titleStyle))
              .setColumnTitleStyle(columnTitleStyle)
              .highlightDetailEvenRows()

              .pageFooter(Components.pageXofY().setStyle(boldCenteredStyle))//show page number on the page footer
              .setDataSource(createDataSource(from,to));      

        report.groupBy(columnItem).show();



    } 
    catch(DRException e)
    {
        e.printStackTrace();
    }
    catch (SQLException e1) {
        e1.printStackTrace();

    } catch (ClassNotFoundException e) {
        e.printStackTrace();

    }


private JRDataSource createDataSource(String from, String to) {

    Connection connection = null;
     String connectionURL = "jdbc:mysql://localhost:3306/medicaminventory";
     DRDataSource dataSource = new DRDataSource("Item_Name","Customer_name", "Date_Of_Sale", "Item_Price", "Item_qty");
     ResultSet rs1 = null;
     try
     {

            Class.forName("com.mysql.jdbc.Driver");
            // Get a Connection to the database
            connection = DriverManager.getConnection(connectionURL, "root", "");
            //Add the data into the database
             Statement stmt;
                     stmt=connection.createStatement();
                rs1=stmt.executeQuery("SELECT * FROM tbl_sale WHERE Date_Of_Sale >= '"+from+"' AND Date_Of_Sale <= '"+to+"'");
                rs1.beforeFirst();
                while(rs1.next())
                {
                    dataSource.add(rs1.getString("Item_Name"), rs1.getString("Customer_name"), rs1.getDate("Date_Of_Sale"), rs1.getDouble("Item_Price"), rs1.getInt("Item_qty"));

                }
                System.out.println("Done with while");

     }
     catch (SQLException e1) {
            e1.printStackTrace();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();

        }
     finally {
        // Always close the database connection.
                        try {
                            if (connection != null) connection.close();
                            }
                    catch (SQLException ignored){
                            }
                    }
     return dataSource;

       } 

My Output

I want one item name and all the records belonging to that item under that item name.

Answer 1

****Answering my own question*********

If the records in the database are random(which depends on database design) you need to order them by using ORDER BY Clause in SQL query

In this Case

Instead of

SELECT * FROM tbl_sale WHERE Date_Of_Sale >= '"+from+"' AND Date_Of_Sale <= '"+to+"'

The Query should be

SELECT * FROM tbl_sale WHERE Date_Of_Sale >= '"+from+"' AND Date_Of_Sale <= '"+to+"' ORDER BY item_name

where item_name is the name of a column in database