How to Put Start Date and End Date as parameters f

2019-08-08 04:24发布

站内文章 / Java
20 0 0

问题:

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>

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).

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

回答1:

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

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

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

  1. Simplifing the task
  2. You are already using it in jasper report, hence $P!{} not $P
  3. The damage someone could do is only viewing some records in your report.

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("\\'", "''")

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("\\'", "''") + "%'";
}

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 + "'";

Note the MySQL date format is yyyy-MM-dd

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...

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);

Add the parameter to your .jrxml

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

And change the query string to:

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

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)



回答2:

Use the following snippet to convert the string to date in your jsp code.

int year = 2003;
int month = 12;
int day = 12;

String date = year + "/" + month + "/" + day;
java.util.Date utilDate = null;

try {
  SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
  utilDate = formatter.parse(date);
  System.out.println("utilDate:" + utilDate);
} catch (ParseException e) {
  System.out.println(e.toString());
  e.printStackTrace();
}

and pass the date as parameter to the jasper report.

Then, in jrxml you can declare the parameter with java.util.Date type as follows,

<parameter name="startDate" class="java.util.Date">
    <parameterDescription><![CDATA[]]></parameterDescription>
</parameter>

Then you can use this date directly in your MySQL query.



标签： java jsp jasper-reports
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~