How to pass the value selected from dropdown menu to jasper report and display the records

Question

I have "Company Name","Contact Person","Phone Number" in my drop down menu. When i select "Contact Person" and "Company" and enter some name in text box and press "search" button it should display the record containing that person name. If we dont enter any name in the text box and select "Contact Person" and "Company" and press "search" button it should display all the records of "Contact Person".Like wise if i select "Phone" and "Company" and enter some number in the text box it should display that record otherwise if we dont enter any number then it should display all the records of phone number.Here is my code below:

purchase.jsp

<form action="view.jsp" method="post">
<select name="complan">
<option value="">Make a selection</option>
<option value="Company Name">Company Name</option>
<option value="Contact Person">Contact Person</option>
<option value="Phone Number">Phone Number</option>
</select>
<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>
<input type="text" name="search"/>
<input type="submit" value="Submit"/>
</form>

view.jsp

<form>
<% 
String search=request.getParameter("search");
session.setAttribute("sea",search);
String category=request.getParameter("category");
session.setAttribute("cat",category);
String complan = request.getParameter("complan");
session.setAttribute("com",complan);
%>
<select onchange="setAction(this.value)">
<option value=''> Make a selection </option>
<option value='sample.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>
<br/>

<input type="submit" value="Submit">
</form>

sample.jsp

<body>
<%
Connection conn = null;
String sear=(String)session.getAttribute("sea");
String cate=(String)session.getAttribute("cat");
String comp=(String)session.getAttribute("com");

System.out.println("1 is:"+sear);
System.out.println("2 is:"+cate);
System.out.println("3 is:"+comp);
try 
{
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/marketing_database","root","root");
    String sql="select * from lead where Category='" + cate.replaceAll("\\'","''") + "'";
    if(sear!=null && sear.trim().length()>0)
    {
        sql+=" AND Company_Name like '" + sear.replaceAll("\\'","''") + "%'";
        }
    else if(sear!=null && sear.trim().length()>0)
    {
        sql+=" AND Contact_Person like '" + sear.replaceAll("\\'","''") + "%'";
    }
    else
    {
        sql+=" AND Phone like '" + sear.replaceAll("\\'","''") + "%'";
    }

    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("sql",sql);
            byte[] bytes = JasperRunManager.runReportToPdf(jasperReport, map, conn);

            response.setContentType("application/pdf");
            response.setContentLength(bytes.length);
            ServletOutputStream outStream = response.getOutputStream();
            outStream.write(bytes, 0, bytes.length);
            outStream.flush();
            outStream.close();
}
catch(Exception e) 
{e.printStackTrace();} 

        %>
</body>

report10.jrxml

<parameter name="sql" class="java.lang.String"/>
    <queryString>
    <![CDATA[$P!{sql}]]>
</queryString>

I have not understod if it is a problem of creating the sql or you like to display different fields in the report? For me its seems like just a problem creating the sql... — Petter Friberg, Oct 14 '15 at 08:09
What i want is if i select "Company Name" and enter any company in the text for example like "Google" it should display all the records containing the "Google".Like wise if i select "contact person" and enter any name in the text box it should display all the records containing the same name i have given in the text box..and another one is if i select "contact person" and i didnt enter any name in the text box then it should display all the records of the database containing different names — FIFA oneterahertz, Oct 14 '15 at 08:22
Ok, but the layout of the report is always the same (the fields do not change), so you need only to create your sql correctly,,check the answer below and get back to it ; ), with some good if statement you will arrive — Petter Friberg, Oct 14 '15 at 08:25

Petter Friberg · Accepted Answer · 2016-09-03T21:27:26.843

You got strange code:

if(sear!=null && sear.trim().length()>0)
{
    sql+=" AND Company_Name like '" + sear.replaceAll("\\'","''") + "%'";
    }
else if(sear!=null && sear.trim().length()>0)
{
    sql+=" AND Contact_Person like '" + sear.replaceAll("\\'","''") + "%'";
}

It will never enter your else if, hence

if (true){//i go here}else if (true){//How can I arrive here if it was false, 
  hence the else says it needs to be false}

So on creating your sql you need to do you if statements on the base of what you need.

if (i have comany and person) I do this...

if (i have phone) I do this ecc.

Try to improve your code some, if you have still problem pass a comment...

Since you sent some code by email I will provide additional clues to resolve

String sql="select * from lead where Category Like'" + cate.replaceAll("\\'","''") + "%'";


//We check that user has input something in the search box
if(sear!=null && sear.trim().length()>0)
{
   boolean isnumber = isNumber(sear); //isNumber(String text) is a metod that you need to create;
   //I have not really understand what excatly you like,
   //but it could be like this
   if (isnumber || complan.equals("Phone Number")){
     sql+=" AND Phone like '" + sear.replaceAll("\\'","''") + "%'";
   } else if (complan.equals("Contact Person")){
     sql+=" AND Contact_Person like '" + sear.replaceAll("\\'","''") + "%'";
   }else{
     sql+=" AND Company_Name like '" + sear.replaceAll("\\'","''") + "%'";
   }
}else{
  //we do nothing since we like to show all records 
}

In this case

if input is a number or selected Phone number it search on the phone number column.

if input is not a number and search using Contact person it selects on that column

Otherwise search on company name

I don't really like the part of the number since you have the phone number in the options to select, be aware you can never find a company named 1234, since we will search on phone number... Why not search as user have indicated?, if not indicated select a default...

If you don't want to find everything that matches the input but only exact hits you need to change

like '" + sear.replaceAll("\\'","''") + "%'"

to

= '" + sear.replaceAll("\\'","''") + "'"

An error occured that is "the method isNumber(String) is undefined" — FIFA oneterahertz, Oct 14 '15 at 13:01
No, you need to do the metod isNumber(String text), please search internet, example http://stackoverflow.com/questions/1102891/how-to-check-if-a-string-is-a-numeric-type-in-java, but if you need more advanced function hence understand prefix ecc. you need to start checking this http://www.mkyong.com/java/how-do-validate-phone-number-in-java-regular-expression/ — Petter Friberg, Oct 14 '15 at 13:02
In general do not copy and past (you should have read my comment) and also I do not compile code but write it freely in the editing box.. try to understand whats happening, I am happy to explain but not happy to give you copy and past code... — Petter Friberg, Oct 14 '15 at 13:09
For example in the Like stament we are saying that the field should start with what user has inputed, if you like it to contain it would be Like = '%' + ... + "%" check the wild flag % — Petter Friberg, Oct 14 '15 at 13:14

How to pass the value selected from dropdown menu to jasper report and display the records

1 Answers1