如何将开始日期和结束日期作为参数从jsp页面添加到jasper报告 [英] How to Put Start Date and End Date as parameters from jsp page to jasper report

查看：100
本文介绍了如何将开始日期和结束日期作为参数从jsp页面添加到jasper报告的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

Purchase.jsp 

<form action="view2.jsp" method="post">
<select name="category">
<option value="">Make a selection</option>
<option value="company">company</option>
<option value="institution">institution</option>
<option value="hospital">hospital</option>
<option value="Others">Others</option>
</select>
<select name="status">
<option value="">Select Status</option>
<option value="close">Close</option>
<option value="open">Open</option>
<option value="pending">Pending</option>
</select>
&nbsp &nbsp &nbsp &nbsp
<select name="day">
                            <option>DD</option>
                            <option>01</option>
                            <option>02</option>
                            <option>03</option>
                            <option>04</option>
                            <option>05</option>
                            <option>06</option>
                            <option>07</option>
                            <option>08</option>
                            <option>09</option>
                            <option>10</option>
                            <option>11</option>
                            <option>12</option>
                            <option>13</option>
                            <option>14</option>
                            <option>15</option>
                            <option>16</option>
                            <option>17</option>
                            <option>18</option>
                            <option>19</option>
                            <option>20</option>
                            <option>21</option>
                            <option>22</option>
                            <option>23</option>
                            <option>24</option>
                            <option>25</option>
                            <option>26</option>
                            <option>27</option>
                            <option>28</option>
                            <option>29</option>
                            <option>30</option>
                            <option>31</option>
</select>
<select name="month">
<option>MM</option>
                            <option>01</option>
                            <option>02</option>
                            <option>03</option>
                            <option>04</option>
                            <option>05</option>
                            <option>06</option>
                            <option>07</option>
                            <option>08</option>
                            <option>09</option>
                            <option>10</option>
                            <option>11</option>
                            <option>12</option>
</select>
<select name="year">
 <option>YYYY</option>
                            <option>2012</option>
                            <option>2013</option>
                            <option>2014</option>
                            <option>2015</option>
                            <option>2016</option>
                            <option>2017</option>
                            <option>2018</option>
                            <option>2019</option>
                            <option>2020</option>
                            <option>2021</option>
                            <option>2022</option>
                            <option>2023</option>
                            <option>2024</option>
                            <option>2025</option>
                            <option>2026</option>
</select>
&nbsp &nbsp &nbsp &nbsp &nbsp
<select name="day1">
                            <option>DD</option>
                            <option>01</option>
                            <option>02</option>
                            <option>03</option>
                            <option>04</option>
                            <option>05</option>
                            <option>06</option>
                            <option>07</option>
                            <option>08</option>
                            <option>09</option>
                            <option>10</option>
                            <option>11</option>
                            <option>12</option>
                            <option>13</option>
                            <option>14</option>
                            <option>15</option>
                            <option>16</option>
                            <option>17</option>
                            <option>18</option>
                            <option>19</option>
                            <option>20</option>
                            <option>21</option>
                            <option>22</option>
                            <option>23</option>
                            <option>24</option>
                            <option>25</option>
                            <option>26</option>
                            <option>27</option>
                            <option>28</option>
                            <option>29</option>
                            <option>30</option>
                            <option>31</option>
</select>
<select name="month1">
                            <option>MM</option>
                            <option>01</option>
                            <option>02</option>
                            <option>03</option>
                            <option>04</option>
                            <option>05</option>
                            <option>06</option>
                            <option>07</option>
                            <option>08</option>
                            <option>09</option>
                            <option>10</option>
                            <option>11</option>
                            <option>12</option>
</select>
<select name="year1">
                            <option>year</option>
                            <option>2012</option>
                            <option>2013</option>
                            <option>2014</option>
                            <option>2015</option>
                            <option>2016</option>
                            <option>2017</option>
                            <option>2018</option>
                            <option>2019</option>
                            <option>2020</option>
                            <option>2021</option>
                            <option>2022</option>
                            <option>2023</option>
                            <option>2024</option>
                            <option>2025</option>
                            <option>2026</option>
</select>
<input type="submit" value="search"/>
</form>

view2.jsp 

<script type="text/javascript">

    function setAction(nPage){

        document.forms[0].action = nPage;
    }
</script>
<body>
<%
String category=request.getParameter("category");
session.setAttribute("cat",category);
String status=request.getParameter("status");
session.setAttribute("sta",status);
String day=request.getParameter("day");
session.setAttribute("da",day);
String month=request.getParameter("month");
session.setAttribute("mon",month);
String year=request.getParameter("year");
session.setAttribute("yea",year);
String day1=request.getParameter("day1");
session.setAttribute("da1",day1);
String month1=request.getParameter("month1");
session.setAttribute("mon1",month1);
String year1=request.getParameter("year1");
session.setAttribute("yea1",year1);
%>
<select onchange="setAction(this.value)">
<option value=''> Make a selection </option>
<option value='PDF_LEAD.jsp'> PDF</option>
<option value='XLS_LEAD.jsp'> XLS </option>
<option value='DOC_LEAD.jsp'> DOC </option>
<option value='XLSX_LEAD.jsp'> XLSX </option>
</select>
<input type="submit" value="search"/>
</body>

PDF_LEAD.jsp 

<body>
<% 
Connection conn = null;
String cate=(String)session.getAttribute("cat");
String stat=(String)session.getAttribute("sta");
String dayy=(String)session.getAttribute("da");
String monthh=(String)session.getAttribute("mon");
String yearr=(String)session.getAttribute("yea");
String dayy1=(String)session.getAttribute("da1");
String monthh1=(String)session.getAttribute("mon1");
String yearr1=(String)session.getAttribute("yea1");
try 
{ 
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/marketing_database","root","root");
    String jrxmlFile ="D:/dev/tools/jasper files/report10.jrxml";
    InputStream input = new FileInputStream(new File(jrxmlFile));
    JasperDesign jasperDesign = JRXmlLoader.load(input);

    System.out.println("Compiling Report Designs");
    JasperReport jasperReport = JasperCompileManager.compileReport(jasperDesign);

    System.out.println("Creating JasperPrint Object");
    HashMap<String,Object> map = new HashMap<String,Object>();
    map.put("complan",comp);
    map.put("search",sear);
    map.put("category",cate);
    map.put("status",stat);
    map.put("day",dayy);
    map.put("month",monthh);
    map.put("year",yearr);
    map.put("day1",dayy1);
    map.put("month1",monthh1);
    map.put("year1",yearr1);
    JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport,map,conn);
    byte bytes[] = new byte[10000]; 
    JRPdfExporter exporter = new JRPdfExporter();
    ByteArrayOutputStream PDFStream = new ByteArrayOutputStream(); 
    exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint); 
    exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, PDFStream); 
    exporter.exportReport(); 
    System.out.println("Size of byte array:"+PDFStream.size()); 
    bytes = PDFStream.toByteArray(); 
    response.setContentType("application/pdf"); 
    System.out.println("After JasperPrint = 1"); 
    response.setContentLength(bytes.length); 
    System.out.println("After JasperPrint = 2"); 
    PDFStream.close(); 
    System.out.println("After JasperPrint = 3"); 

    OutputStream outputStream = response.getOutputStream(); 
    System.out.println("After JasperPrint = 4"); 
    outputStream.write(bytes, 0, bytes.length); 
    outputStream.flush(); 
    outputStream.close(); 

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

%>
</body>

report10.jrxml 

<parameter name="category" class="java.lang.String">
        <parameterDescription><![CDATA[]]></parameterDescription>
    </parameter>
    <parameter name="search" class="java.lang.String">
        <parameterDescription><![CDATA[]]></parameterDescription>
    </parameter>
    <parameter name="status" class="java.lang.String">
        <parameterDescription><![CDATA[]]></parameterDescription>
    </parameter>
    <queryString>
        <![CDATA[select * from lead where Status='$P!{status}' and category ='$P!{category}' and Company_Name like '$P!{search}%']]>
    </queryString>

这里我有开始日期和结束日期。我已经通过了类别参数和status参数，但如何将日期参数从jsp传递给jasper报告？我使用选择标记名称作为开始日期的日，月，年，并使用选择标记名称作为结束日期的day1，month1，year1（在在下面的Purchase.jsp）。我的第二个问题是，在MYSQL数据库中，我没有一个名为开始日期和结束日期的列。我在我的数据库中的列是

Here i have "Start Date" and "End Date".I have passed the "Category" parameter and "status" parameter but how to pass "Dates" parameter from jsp to jasper report?? I have used select tag names as "day","month","year" for "Start Date" and have used select tag names as "day1","month1","year1" for "End Date" as (mentioned in Purchase.jsp)below.My second question is, in MYSQL database i didnt have a column named as "Start Date" and "End Date". The columns i have in my database are


（id，Name，Email，Company_Name，Contact_Person，Address，Phone，Company_Email，Review，Lead_Date ，Lead_Details，Lead_Value，Followup_Date，Status，Category）。

(id,Name,Email,Company_Name,Contact_Person,Address,Phone,Company_Email,Review,Lead_Date,Lead_Details,Lead_Value,Followup_Date,Status,Category).

那么如何将开始日期和结束日期放入我的MYSQL数据库的where子句

So how to put "Start Date" and "End Date" in my "where" clause of MYSQL Database

推荐答案

上面的答案可以解决与问题相关的问题（实际上我是upvote它） ，但是因为我看到你挣扎（第二个问题）在jasper报告中生成sql - 我会建议另一个metod

The answer above would solve your problem related to the question (in fact I upvote it), but since I see that you struggle (2nd question) generating the sql inside of jasper report -I will suggest another metod

在jsp中生成查询并传递整个查询jasper报告。

Generating the query in jsp and passing the whole query to jasper report.

要做到这一点，你需要使用可以接受sql注入的concat metodo，但我建议这样做：

To do this you need to use concat metodo that can be subject to "sql injection" but i suggest this anyway since:


  1. 简化任务

  2. 您已经在jasper报告中使用它，因此$ P！{}不是$ P

  3. 有人可以做的伤害只是v在你的报告中记录一些记录。

我唯一建议的是，如果有些用户，你可以逃避'char to not break query搜索es goo'gle。这可以通过以下简单代码完成：

The only thing I do suggest is that you escape the ' char to not break query if some user search on es goo'gle. This you can do by this simple code:

userInput.replaceAll("\\'", "''")

所以在你的jsp（PDF_LEAD.jsp）中这样做你可以创建这样的SQL

So doing this within your jsp (PDF_LEAD.jsp) you can create your sql something like this

String sql = "select * from lead where Status='" + stat.replaceAll("\\'", "''") + "'";
if (sear!=null && searc.trim().length()>0){
  sql+=" AND Company_Name like '" + searc.replaceAll("\\'", "''") + "%'";
}

等等等到达之后的日期 / code>你添加这样的东西。

etc etc arriving to your date after your if you add something like this.

sql+="DATE(the_name_of_you_date_column)>='" + year + "-" + month + "-" + day + "'";

注意MySQL日期格式为 yyyy-MM-dd

Note the MySQL date format is yyyy-MM-dd

创建查询后，将其输出到 System.out.println（sql）; ，这样你可以在MySQL Workbench中测试你的查询（没有报告），它会帮助你理解是否有什么问题...

Once you finshed creating your query ouput it to the System.out.println(sql);, that way you can test your query in MySQL Workbench (without the report), it will help you to understand if something is wrong...

你需要的最后一件事要做的是将查询传递给jasper报告：

The last thing you need to do is pass the query to jasper report:

 HashMap<String,Object> map = new HashMap<String,Object>();
 map.put("sql",sql);

将参数添加到.jrxml

Add the parameter to your .jrxml

<parameter name="sql" class="java.lang.String"/>

并将查询字符串更改为：

And change the query string to:

<queryString>
    <![CDATA[$P!{sql}]]>
</queryString>

希望这有助于缩小你如何创建正确sql的问题。 （不要复制并通过我的代码检查它是否正确我已经通过不知道您的参数和检查语法来编写它）

Hope this will help and narrow down your problem on how to create the correct sql. (Don't copy and past my code check that it's correct I have written it by not knowing exactly your parameters and checking syntax)

这篇关于如何将开始日期和结束日期作为参数从jsp页面添加到jasper报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Java开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆