Java 和 MySQL 数据库中的搜索过滤器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22144254/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Search Filter in Java and MySQL database
提问by ashlrem
I am writing a code in Java where user can type a last name in a JTextField named lastname and then search for possible match in MySQL database. Say for example, user begins to type letter "M" (case insensitive and without double quotes), all the last name that starts with letter "M*" will display on JTable. If user types a second letter, say for example "A", the results on JTable will only display last names with "MA", then if user types third letter, say for example "N", JTable will only display all the last names with "MAN***" and so on..
我正在用 Java 编写代码，其中用户可以在名为 lastname 的 JTextField 中键入姓氏，然后在 MySQL 数据库中搜索可能的匹配项。例如，用户开始键入字母“M”（不区分大小写且不带双引号），所有以字母“M* ”开头的姓氏都将显示在 JTable 上。如果用户键入第二个字母，例如“A”，JTable 上的结果将只显示带有“MA”的姓氏，然后如果用户键入第三个字母，例如“N”，JTable 将只显示所有姓氏用“MAN***”等等..
I have read about
我已经阅读了
SELECT * FROM table WHERE lastname LIKE value
SELECT * FROM table WHERE lastname LIKE 值
and tried to use it on my program, however, I am getting an error.
并尝试在我的程序中使用它，但是，出现错误。
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxError.Exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%' at line 1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxError.Exception: 你的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 1 行的“%”附近使用的正确语法
Here's my partial code in event when there's key pressed in JTextField lastname:
这是我在 JTextField 姓氏中按下键时的部分代码：
private void lastNameKeyPressed(java.awt.event.KeyEvent evt) {
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "students_dbs";
Statement stmt = null;
ResultSet result = null;
String driver = "com.mysql.jdbc.Driver";
String databaseUserName = "user1";
String databasePassword = "test";
PreparedStatement pst = null;
try{
conn = DriverManager.getConnection(url + dbName, databaseUserName, databasePassword);
stmt = conn.createStatement();
System.out.println("Connected to the database.");
}catch(Exception e){
System.out.println("Failed to connect ot the database.");
}
try{
String sql = "SELECT studentno, lastname, firstname, middlename FROM student WHERE lastname LIKE '%" + lastname.getText() + "'%";
pst = conn.prepareStatement(sql);
result = pst.executeQuery();
studentsTable.setModel(DbUtils.resultSetToTableModel(result));
}
catch(Exception e){
JOptionPane.showMessageDialog(null, e);
}
}
I googled it for a day or two now, however, I am stuck. I'm kinda new in using LIKE in MySQL. Any kind help is very much appreciated! Thank you in advance!
我现在用谷歌搜索了一两天，但是，我被卡住了。我对在 MySQL 中使用 LIKE 有点陌生。非常感谢任何帮助！先感谢您！
采纳答案by royu
The position of the last ' char is wrong:
最后一个 ' 字符的位置是错误的：
String sql = "SELECT studentno, lastname, firstname, middlename FROM student
WHERE lastname LIKE '% " + lastname.getText() + " '% ";
should be:
应该：
String sql = "SELECT studentno, lastname, firstname, middlename FROM student
WHERE lastname LIKE '% " + lastname.getText() + " %' ";
回答by kkb
put one textfield for u to type
放一个文本框让你输入
String sql = "SELECT studentno, lastname, firstname, middlename FROM student
WHERE lastname=?";
pst = conn.prepareStatement(sql);
pst.setString(1,jTextField1.getText());
result = pst.executeQuery();
回答by Rizal Amrul Hadi
you can use this code :
您可以使用此代码：
and then rename youre Jtabble, Like model in picture one. then call method in here initcomponent(){selectDataTable(""); }. then make event key released in youre textfield, and then write this in the event selectDatatable(textfield.gettext()); Done
然后重命名你的 Jtabble，就像图一中的模型。然后在这里调用方法 initcomponent(){selectDataTable(""); }. 然后在你的文本字段中释放事件键，然后将其写入事件 selectDatatable(textfield.gettext()); 完毕
回答by Sushil kumar
Try this:
尝试这个：
String SQL="Select name from tablename where like %";
pst = conn.prepareStatement(sql);
pst.setString(1, txnam.gettext()+"%");
