CODEWITHSUNDEEP
javamysqlsqlswingjdbc
Alya
Alya

Reputation: 33

SQL Query for a search function

I want to do a Search function with multiple Java Swing components, where a user can search by (name/nationality/specialty/experience) and results will be displayed in a Jtable.

enter image description here

I'm only struggling with the SQL Query, as if a user typed a 'name' only, no data will be retrieved because it goes to database like this (name, null, null, null) and I don't have any null values in my database.

So I want to retrieve all data with that name regardless of other columns, but at the same time, if they also chose a specific specialty for example, I want to retrieve all data with the selected name AND specialty, and so on.

I hope you understand my question.

My current SQL statement:

public ArrayList<Applications> getData(String name, String nationality, String specialty, String experience) {

   ArrayList<Applications> list = new ArrayList<Applications>();
   Connection con = getConnection();
   Statement st;
   ResultSet rows;

   try {
       st = con.createStatement();

       rows = st.executeQuery("SELECT * FROM applications WHERE name LIKE '%" +  name+"%'"
            + " AND (nationality LIKE '" +  nationality+"')"
            + " AND (specialty LIKE '" +  specialty+"')"
            + " AND (experience LIKE '" +  experience+"')");

       Applications applications;

       while(rows.next()) {
           applications = new Applications(
               rows.getInt("id"),
               rows.getString("name"),
               rows.getString("nationality"),
               rows.getString("Specialty"),
               rows.getString("experience")
           );

          list.add(applications);
       }   
   } catch (SQLException ex) {
       Logger.getLogger(MyQuery.class.getName()).log(Level.SEVERE, null, ex);
   }

   return list;
}

Upvotes: 3

Views: 4434

Answers (3)

Lajos Arpad
Lajos Arpad

Reputation: 76434

Let's see your query:

   rows = st.executeQuery("SELECT * FROM applications WHERE name LIKE '%" +  name+"%'"
        + " AND (nationality LIKE '" +  nationality+"')"
        + " AND (specialty LIKE '" +  specialty+"')"
        + " AND (experience LIKE '" +  experience+"')");

Here, since only name was given, the other values are null. If you write this code for testing purpose:

String foo = null;
System.out.println(foo + "");

the output will be

"null"

so, since your values are null, the generated query will be

   SELECT * FROM applications WHERE name LIKE '%Rowan Atkinson%'
        AND (nationality LIKE 'null')
        AND (specialty LIKE 'null')
        AND (experience LIKE 'null')

First of all, let's make sure that you get empty String in case of null:

   rows = st.executeQuery("SELECT * FROM applications WHERE name LIKE '%" +  ((name == null) ? "" : name)+"%'"
        + " AND (nationality LIKE '" +  ((nationality == null) ? "" : nationality)+"')"
        + " AND (specialty LIKE '" +  ((specialty == null) ? "" : specialty)+"')"
        + " AND (experience LIKE '" +  ((experience == null) ? "" : experience)+"')");

The next problem is that you are only putting % at the name, which is also incorrect, so let's fix that:

   rows = st.executeQuery("SELECT * FROM applications WHERE name LIKE '%" +  ((name == null) ? "" : name)+"%'"
        + " AND (nationality LIKE '%" +  ((nationality == null) ? "" : nationality)+"%')"
        + " AND (specialty LIKE '%" +  ((specialty == null) ? "" : specialty)+"%')"
        + " AND (experience LIKE '%" +  ((experience == null) ? "" : experience)+"%')");

and now read YCF_L's answer so you will use Named Parameters for PreparedStatement.

Upvotes: 2

Youcef LAIDANI
Youcef LAIDANI

Reputation: 59960

In this case You have two Solution :

  1. Check the values if not null append that part to the query, else ignore it
  2. Second solution you can check the value is null or not in the query.

I will gives you an example about the second one, you can use NamedParameterStatement it is like PrepapredStatement to avoid Syntax error and SQL Injection :

String query = "SELECT * FROM applications"
        + " WHERE (:name is null or name LIKE CONCAT('%', :name, '%')) "
        + " AND (:nationality is null or nationality LIKE :nationality)"
        + " AND (:specialty is null or specialty LIKE :specialty)"
        + " AND (:experience is null or experience LIKE :experience)";

NamedParameterStatement namedParametterStatement = 
                           new NamedParameterStatement(connection, query);

namedParametterStatement.setString("name", name);
namedParametterStatement.setString("nationality", nationality);
namedParametterStatement.setString("specialty", specialty);
namedParametterStatement.setString("experience", experience);

Note the trick for example here :

(:nationality is null or nationality LIKE :nationality)

It will check if the nationality is null or the nationality like the value you pass, like this you avoid the null.

Some good references :

Upvotes: 1

Keval Pithva
Keval Pithva

Reputation: 610

Below code may give you expected Result.

StringBuilder sql=new StringBuilder("SELECT * FROM applications WHERE 1=1 ");
        if(!name.isEmpty()){
            sql.append(" AND name LIKE '%" +  name+"%'");
        }
        if(!nationality.isEmpty()){
            sql.append(" AND (nationality LIKE '" +  nationality+"')");
        }
        if(!specialty.isEmpty()){
            sql.append(" AND (specialty LIKE '" +  specialty+"')");
        }
        if(!experience.isEmpty()){
            sql.append(" AND (experience LIKE '" +  experience+"')");
        }

        rows = st.executeQuery(sql.toString());

Try this

Upvotes: 1

Related Questions