Search/Filter data using Java String indexOf() method

Here is an example using Java String indexOf() method to create a search similar to Google where you put a single search field on the screen and it can query the database and get results matching your search criteria. Lets say you have an employee database and would like to find any record that matches the name, city, state and/or zipcode. Here we take advantage of the indexOf() method which basically scans a string for a given substring and tells us the position and in case it doesn't find the substring then it returns a -1.

Step 1: Create a Sample Employee Table

CREATE TABLE Employee (                        
 Id int,                  
 LastName varchar(50),    
 FirstName varchar(50),   
 Address1 varchar(50),    
 Address2 varchar(50),    
 City varchar(50),        
 State char(2),           
 ZipCode char(15)         
)     

Step 2: Create a View with the Searchable Information

CREATE VIEW Employee1 as ( Select                    
 Id,                         
 LastName,                   
 FirstName,  
 Address1,                        
 Address2,                        
 City,                            
 State,                           
 ZipCode,                         
 upper ( Lastname || ' '  || FirstName || ' '  ||             
   City || ' '  || State || ' '  || ZipCode) as SKey 
 from Employee    
)     

Step 3: Java Employee Object

package com.as400samplecode;

public class Employee {
 
 int id = 0;
 String firstName = null;
 String lastName = null;
 String address1 = null;
 String address2 = null;
 String city = null;
 String state = null;
 String zipCode = null;

 public int getId() {
  return id;
 }
 public void setId(int id) {
  this.id = id;
 }
 public String getFirstName() {
  return firstName;
 }
 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }
 public String getLastName() {
  return lastName;
 }
 public void setLastName(String lastName) {
  this.lastName = lastName;
 }
 public String getAddress1() {
  return address1;
 }
 public void setAddress1(String address1) {
  this.address1 = address1;
 }
 public String getAddress2() {
  return address2;
 }
 public void setAddress2(String address2) {
  this.address2 = address2;
 }
 public String getCity() {
  return city;
 }
 public void setCity(String city) {
  this.city = city;
 }
 public String getState() {
  return state;
 }
 public void setState(String state) {
  this.state = state;
 }
 public String getZipCode() {
  return zipCode;
 }
 public void setZipCode(String zipCode) {
  this.zipCode = zipCode;
 }
 
 
}

Step 4: Sample Java function to filter Employee data

Now all you need to do take the first word in the query String and put that in our JDBC statement to eliminate as many records as possible on the query selection and then take the words in the query String and check against the search String with the help of the indexOf() method. Here is some code snippet that you can modify according to your need.

 public ArrayList<Employee> getEmployeeList(String queryString) {  

  Connection conn = null;             
  PreparedStatement stmt = null;      
  String sql = null;
  ArrayList<Employee> employeeList = new ArrayList<Employee>(); 

  //Convert the String to an Array of words separated by SPACE 
  String[] searchKeywords = queryString.toUpperCase().split(" ");  
  String firstKeyword = "";

  //Get the first word in the Search String
  if(searchKeywords.length > 0){
   firstKeyword = searchKeywords[0];
  }

  try {       
   Context ctx = (Context) new InitialContext().lookup("java:comp/env");
   conn = ((DataSource) ctx.lookup("jdbc/mydb2")).getConnection(); 

   sql = "Select * from Employee ";
   if(!firstKeyword.trim().equalsIgnoreCase("")){
    sql = sql + "where SKey like ? ";
   }

   stmt = conn.prepareStatement(sql);
   if(!firstKeyword.trim().equalsIgnoreCase("")){
    stmt.setString(1, "%" + firstKeyword.trim() + "%");
   }
   ResultSet rs = stmt.executeQuery();  

   while(rs.next()){ 

    boolean found = true;
    String searchKey = rs.getString("SKey").trim();
    if(searchKeywords.length > 1){
     for(int i=1;i<searchKeywords.length;i++){
      if(searchKey.indexOf(searchKeywords[i].trim()) == -1){
       found = false;
       break;
      }
     }
    }
    if(!found){
     continue;
    }

    Employee employee = new Employee();
    employee.setId(rs.getInt("Id"));
    employee.setLastName(rs.getString("LastName"));
    employee.setFirstName(rs.getString("FirstName"));
    employee.setAddress1(rs.getString("Address1"));
    employee.setAddress2(rs.getString("Address2"));
    employee.setCity(rs.getString("City"));
    employee.setState(rs.getString("State"));
    employee.setZipCode(rs.getString("ZipCode"));
    employeeList.add(employee);

   }                                                                          

   rs.close();                                                                
   stmt.close();                                                              
   stmt = null;                                                               


   conn.close();                                                              
   conn = null;                                                    

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

  finally {                                                        

   if (stmt != null) {                                             
    try {                                                          
     stmt.close();                                                 
    } catch (SQLException sqlex) {                                 
     // ignore -- as we can't do anything about it here            
    }                                                              

    stmt = null;                                             
   }                                                         

   if (conn != null) {                                       
    try {                                                    
     conn.close();                                           
    } catch (SQLException sqlex) {                           
     // ignore -- as we can't do anything about it here      
    }                                                        

    conn = null;                                             
   }                                                         
  }               

  return employeeList;

 }   

Syntax

• int indexOf(String str) 
• Returns the index within this string of the first occurrence of the specified substring. If it does not occur as a substring, -1 is returned. 
• int indexOf(String str, int fromIndex) 
• Returns the index within this string of the first occurrence of the specified substring, starting at the specified index. If it does not occur, -1 is returned.