我正在使用jasper报告来生成报告。我试图将一个项目的所有记录归为一个名称。但是项目名称在报告中显示为多种类型
我的代码是
// 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;
}
我的输出
我想要一个项目名称以及该项目名称下属于该项目的所有记录。
最佳答案
****回答我自己的问题*********
如果数据库中的记录是随机的（取决于数据库设计），则需要在SQL查询中使用ORDER BY子句对它们进行排序
在这种情况下
代替
SELECT * FROM tbl_sale WHERE Date_Of_Sale >= '"+from+"' AND Date_Of_Sale <= '"+to+"'
查询应该是
SELECT * FROM tbl_sale WHERE Date_Of_Sale >= '"+from+"' AND Date_Of_Sale <= '"+to+"' ORDER BY item_name
其中，
item_name是数据库中列的名称