java - 搜索功能的 SQL 查询
问题描述
我想用多个 Java Swing 组件做一个搜索功能,用户可以按(姓名/国籍/专业/经验)搜索,结果将显示在 Jtable 中。
我只是在 SQL 查询中苦苦挣扎,好像用户只输入了一个“名称”,不会检索任何数据,因为它像这样(名称,null,null,null)进入数据库,我没有任何 null我的数据库中的值。
因此,我想检索具有该名称的所有数据,而不考虑其他列,但同时,如果他们还选择了特定专业,例如,我想检索具有所选名称和专业的所有数据,依此类推。
我希望你能理解我的问题。
我当前的 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;
}
解决方案
让我们看看您的查询:
rows = st.executeQuery("SELECT * FROM applications WHERE name LIKE '%" + name+"%'"
+ " AND (nationality LIKE '" + nationality+"')"
+ " AND (specialty LIKE '" + specialty+"')"
+ " AND (experience LIKE '" + experience+"')");
在这里,由于只name
给出了,其他值是null
。如果您出于测试目的编写此代码:
String foo = null;
System.out.println(foo + "");
输出将是
“空值”
因此,由于您的值是null
,生成的查询将是
SELECT * FROM applications WHERE name LIKE '%Rowan Atkinson%'
AND (nationality LIKE 'null')
AND (specialty LIKE 'null')
AND (experience LIKE 'null')
首先,让我们确保在以下String
情况下您是空的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)+"')");
下一个问题是你只放%
了name
,这也是不正确的,所以让我们解决这个问题:
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)+"%')");
现在阅读 YCF_L 的答案,以便为 PreparedStatement 使用命名参数。