How to write query in database to make search case insenstive and display multiple records based on search

Question

My SQL query

PreparedStatement ps = con.prepareStatement("select * from Application where name like '"+ name+"%'");

Could you please suggest me how to make it case insensitive and based on search it should display multiple records which starts with search keyword since I have used 'like'.

Suppose when I search by 'C' in search button. When submit it should display Car, cat etc. (in my database) in the jsp page, but it is returning only one record.

Look at http://stackoverflow.com/questions/10326952/how-to-use-preparedstatement-and-case-insensitive-search — Christoph Grimmer, Dec 11 '16 at 09:55
Possible duplicate of [Hibernate show real SQL](http://stackoverflow.com/questions/2536829/hibernate-show-real-sql) — Kitson88, Dec 11 '16 at 13:04
For the `it is returning only one record` part: [`PreparedStatement.executeQuery()`](https://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html#executeQuery--) returns a [`ResultSet`](https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html#next--) to iterate. — greybeard, Dec 11 '16 at 13:41

score 0 · Answer 1 · answered Dec 11 '16 at 09:56

0

Use this :

PreparedStatement ps = con.prepareStatement("select * from Application where LOWER(name) like '"+ name..toLowerCase() +"%'");

answered Dec 11 '16 at 09:56

saeid rastak

325
2
11

score 0 · Answer 2 · answered Dec 11 '16 at 10:02

0

You did the correct thing by trying to use a prepared statement. However, you then decided not to actually use the statement and instead use a SQL injection-prone WHERE clause. Something along these lines should solve your problem:

String sql = "select * from Application where LOWER(name) like ?");
PreparedStatement preparedStatement = dbConnection.prepareStatement(sql);
preparedStatement.setString(1, "%" + name.toLowerCase() + "%");
ResultSet rs = preparedStatement.executeQuery(sql);

However, it is not ideal from a performance point of view to be lowercasing every record in a single query. If you plan to support case insensitive searches by name often, you might want to consider storing the lowercase version of the name in the Application table. This should speed up the query.

answered Dec 11 '16 at 10:02

Tim Biegeleisen

502,043
27
286
360

Thank you . It works. I have two records that starts with 'CI ', but it is dispalying one record when i search by CI keyword – Muskan Dec 11 '16 at 10:34
Please help me in this – Muskan Dec 11 '16 at 10:34
The query should be returning both records, but you may be handling the result set incorrectly. Please include the relevant code in your question. – Tim Biegeleisen Dec 11 '16 at 10:39

score 0 · Answer 3 · answered Dec 11 '16 at 10:48

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    DBConnection db=new DBConnection();
    HttpSession hs = request.getSession();
    String name=request.getParameter("appname");
    List<Details> dbean=db.getAllDetails(name);

    hs.setAttribute("detbean",dbean);
    if(dbean.isEmpty())
    {
        response.setContentType("text/html");  
        PrintWriter out = response.getWriter();
        out.print("<caption>Sorry, No Record Found</caption>");
    }
    else
    response.sendRedirect("Application.jsp");
}

my jsp code

               <TABLE BORDER="1" >
                <TR>
                    <TH> <h4 >Application Name</h4> </TH>
                     <TH> <h4 >Abbrebation</h4> </TH>
                      <TH> <h4 >Primary Contact Name</h4> </TH>
                      <TH> <h4 >Primary Contact Number</h4> </TH>
                      <TH> <h4 >Seconday Contact Name</h4> </TH>
                      <TH> <h4 >Secondary Contact Number</h4> </TH>
                      </TR>

Collection dbean = (ArrayList)request.getSession().getAttribute("detbean"); Iterator i=dbean.iterator(); while(i.hasNext()) { beanclasses.Details item= (beanclasses.Details)i.next();
<%=item.getAPPLICATIONNAME()%> <%=item.getAPPABBR()%> <%=item.getPRIMARYPERSON()%> <%=item.getPRICONTACT()%> <%=item.getSECONDAEYPERSON() %> <%=item.getSECCONTACT() %>

in another jsp , i am passing the "appname" keyword – Muskan Dec 11 '16 at 10:49 — Muskan, Dec 11 '16 at 10:49

score 0 · Answer 4 · answered Dec 11 '16 at 10:54

ps.execute();
System.out.println("Entering in to the fucntion 13");
ResultSet rs  =ps.getResultSet();
if(rs.next())
{
System.out.println("Entering in to the fucntion 4");
Details dbean=new Details();
dbean.setAPPLICATION_ID(rs.getInt(1));
dbean.setAPPLICATIONNAME(rs.getString(2));
dbean.setAPPABBR(rs.getString(3));
dbean.setPRIMARYPERSON(rs.getString(4));
dbean.setPRICONTACT(rs.getInt(5));
dbean.setSECONDAEYPERSON(rs.getString(6));
dbean.setSECCONTACT(rs.getInt(7));

de.add(dbean);
System.out.println("Entering in to the fucntion 1" + de);
}
}
catch(SQLException e)
    {
     e.printStackTrace();
} catch (ClassNotFoundException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}
return de;

Now this is getting ridiculous. You should have pasted this code in your _question_, not as an answer. Arguably, you should have closed your current question and asked a new one. — Tim Biegeleisen, Dec 11 '16 at 10:56

score 0 · Answer 5 · edited Mar 29 '17 at 17:57

0

On databases that support it, you can use ilike in place of like for a case-insensitive search.

PreparedStatement ps = con.prepareStatement("select * from Application where name ilike '"+ name+"%'");

edited Mar 29 '17 at 17:57

OtherDevOpsGene

7,302
2
31
46

answered Mar 29 '17 at 13:21

user7786074

1

1

Welcome to StackOverflow! Please do not just dump code as answers, also provide an explanation of why it solves the problem. – arco444 Mar 29 '17 at 13:27

How to write query in database to make search case insenstive and display multiple records based on search

5 Answers5