CODEWITHSUNDEEP
javasqljspms-access
user3444944
user3444944

Reputation: 3

Like search not fetching all matching records

I wanted to do multitable search.I have a database in MS Access with 4 tables.So I made 1 more table Index1 which contains the field from all the 4 tables that the user will search.(sort of index/master table). The problem is that the search is not displaying as many records as ideally it should,It displays only few of them. Kindly enlighten me by finding the bug in my code. Thanks in advance !

My code:

//package searchbook;

import java.io.*;

import javax.servlet.*;
import javax.servlet.http.*;

import java.sql.*;
import java.util.*;

public class SearchBook extends HttpServlet {
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html");
        HttpSession session = request.getSession(true);
        List booklist = new ArrayList();
        Connection con = null;

        String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + "C:\\users\\ppreeti\\executive_db.accdb";

        String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
        String user = "";
        String pass = "";
        String category = "";
        category = request.getParameter("input");
        String sqlquery = "select   Index1.link_id "
                + "FROM Index1 "
                + " WHERE  Index1.index_name LIKE '%" + category + "%'  ";
        String sqlResult = null;
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url, user, pass);
            try {
                Statement st = con.createStatement();
                System.out.println("Connection created 1");
                ResultSet rs = st.executeQuery(sqlquery);
                while (rs.next()) {
                    sqlResult = rs.getString(1);

                }
                System.out.println("Result retreived  1");
                //System.out.println('"sqlquery"');
            } catch (SQLException s) {
                System.out.println("SQL statement is not executed! " + s);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("************");
        String sqlq = "";

        int flag = 0;


    /*if(sqlResult.equals("1"))
    {
        flag=1;
        System.out.println("entered if block for section!");
        sqlq="select Report.Report_Name,Report.Report_ID,Report.Section_ID,Report.Contact_ID,Report.link_id FROM  Report "
                + " where Report.Report_Name LIKE '%"+category+"%' ";
         sqlq="select  Section.Section_Name ,  Report.Report_Name , Report.Link, Contact.Contact_Name "
                + "FROM Section , Report , Contact"
                + " WHERE  Report.Section_ID=Section.Section_ID  and  Contact.Contact_ID=Report.Report_ID "
                + "and  Section.Section_Name = '"+category+"' ";
    }   */
        if (sqlResult.equals("1")) {
            flag = 1;
            System.out.println("entered if block for section!");
        /*sqlq="select Report.Report_Name,Report.Report_ID,Report.Section_ID,Report.Contact_ID,Report.link_id FROM  Report "
                + " where Report.Report_Name LIKE '%"+category+"%' ";*/
            sqlq = "select distinct Section.Section_Name ,  Report.Report_Name , Report.Link, Contact.Contact_Name "
                    + "FROM Section , Report , Contact"
                    + " WHERE  Report.Section_ID=Section.Section_ID  and  Contact.Contact_ID=Report.Contact_ID and  Section.Section_Name LIKE '%" + category + "%' ";
            // + "and  Section.Section_Name = '"+category+"' ";
        }

        if (sqlResult.equals("2")) {
            flag = 1;
            System.out.println("entered if block for report!");
        /*sqlq="select Report.Report_Name,Report.Report_ID,Report.Section_ID,Report.Contact_ID,Report.link_id FROM  Report "
                + " where Report.Report_Name LIKE '%"+category+"%' ";*/
            sqlq = "select distinct Section.Section_Name ,  Report.Report_Name , Report.Link, Contact.Contact_Name "
                    + "FROM Section , Report , Contact"
                    + " WHERE  Report.Section_ID=Section.Section_ID  and  Contact.Contact_ID=Report.Contact_ID and  Report.Report_Name LIKE '%" + category + "%' ";
            //   + "and  Report.Report_Name = '"+category+"' ";
        }
        if (sqlResult.equals("3")) {
            flag = 1;
            System.out.println("entered if block for metrics !");
        /*sqlq="select Report.Report_Name,Report.Report_ID,Report.Section_ID,Report.Contact_ID,Report.link_id FROM  Report "
                + " where Report.Report_Name LIKE '%"+category+"%' "*/
            ;

            sqlq = "select distinct Section.Section_Name ,  Report.Report_Name , Report.Link, Contact.Contact_Name "
                    + "FROM Section , Report , Contact,Metrics"
                    + " WHERE  Report.Section_ID=Section.Section_ID  and  Contact.Contact_ID=Report.Contact_ID and Metrics.Report_ID=Report.Report_ID "
                    + "and  Metrics.Metric_Name LIKE '%" + category + "%' ";
        }
        if (sqlResult.equals("4")) {
            flag = 1;
            System.out.println("entered if block for contact name!");
        /*sqlq="select Section.Section_Name ,  Report.Report_Name , Report.Link, Contact.Contact_Name, Metrics.Metric_Name "
                  + "FROM Section , Report , Contact, Metrics"
                  + " WHERE  Report.Section_ID=Section.Section_ID  and  Metrics.Report_ID=Report.Report_ID "
                  + "and Report.Report_ID IN (SELECT Report.Report_ID FROM Report  WHERE "
                  + "Contact.Contact_ID=Report.Contact_ID and Contact.Contact_Name LIKE '%"+category+"%' and Metrics.Metric_Segment = 'M') ORDER BY Report_Name ";*/


            sqlq = "select distinct Section.Section_Name ,  Report.Report_Name , Report.Link, Contact.Contact_Name "
                    + "FROM Section , Report , Contact"
                    + " WHERE  Report.Section_ID=Section.Section_ID  and  Contact.Contact_ID=Report.Contact_ID "
                    + "and  Contact.Contact_Name LIKE '%" + category + "%' ";
        }


        if (flag == 1) {
            try {
                Class.forName(driver);
                con = DriverManager.getConnection(url, user, pass);
                try {
                    Statement st = con.createStatement();
                    System.out.println("Connection created");
                    ResultSet rs = st.executeQuery(sqlq);
                    System.out.println("Result retreived for 2nd query ");
                    while (rs.next()) {
                        List<String> book = new ArrayList<String>();

                        String Name = rs.getString("Section_Name");
                        String reportName = rs.getString("Report_Name");
                        String link = rs.getString("Link");
                        String contactName = rs.getString("Contact_Name");
               /* String metricName=rs.getString("Metric_Name");*/
                        //String reportId=rs.getString("Report_ID");

                        book.add(Name);
                        book.add(reportName);
                        book.add(link);
                        book.add(contactName);
               /* book.add(metricName);*/
                        //book.add(reportId);

              /*  book.add(ind_id);
                book.add(ind_name);*/

                        booklist.add(book);
                    }
                } catch (SQLException s) {
                    s.printStackTrace();
                    System.out.println("SQL statement is not executed in 2nd query! " + s);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        System.out.println("And it came here lastly !");
        request.setAttribute("booklist", booklist);
        RequestDispatcher dispatcher = getServletContext().getRequestDispatcher("/searchbook.jsp");
        dispatcher.forward(request, response);
        System.out.println("***************************************************************************************");
    }
}

Upvotes: 0

Views: 110

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123809

Check your logic again. In this loop

while (rs.next()) {
    sqlResult = rs.getString(1);

}

you are looping through the first ResultSet, repeatedly assigning 

sqlResult = rs.getString(1);

and then doing nothing with it until you hit the end of that while loop, after which you proceed to do some other stuff with the last row fetched by that first query.

Upvotes: 1

Anthony Horne
Anthony Horne

Reputation: 2522

Access works different. Use "*" instead of "%" for the like.

Upvotes: 0

Related Questions