用于搜索功能的SQL查询。

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.

我想做一个带有多个Java Swing组件的搜索函数，用户可以根据(名称/国籍/专业/经验)进行搜索，结果将显示在Jtable中。

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.

我只是在处理SQL查询，如果用户只键入“name”，则不会检索任何数据，因为它会像这样进入数据库(name, null, null, null)，而且我的数据库中没有任何空值。

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:

我现在的SQL语句:

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;
}

3 个解决方案

#1

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:

这里，由于只给出了名称，所以其他值为null。如果您为测试目的编写此代码:

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

the output will be

输出将

"null"

“零”

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

因此，由于您的值为null，因此生成的查询将是

   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:

首先，让我们确保在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.

现在请阅读YCF_L的答案，以便您将为PreparedStatement使用命名参数。

#2

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

试试这个

#3

In this case You have two Solution :

在这种情况下，你有两个解决方案:

1. Check the values if not null append that part to the query, else ignore it
2. 如果不为null，请检查将该部分附加到查询的值，否则忽略它
3. Second solution you can check the value is null or not in the query.
4. 第二个解决方案可以检查查询中的值是否为null。

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 :

我将给你一个关于第二个的例子，你可以使用NamedParameterStatement它就像PrepapredStatement来避免语法错误和SQL注入:

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.

它将检查国籍是否为null或您传递的值之类的国籍，就像这样，您可以避免使用null。

---

Some good references :

一些好的参考:

• Named Parameters for PreparedStatement
• 命名参数为PreparedStatement