java - 在 javafx 中使用 MySQL Like 子句

Question

关闭。这个问题需要多问 focused 。目前不接受答案。

---

想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post .

已关闭 5 年前。

Improve this question

假设我有一张 table

+----------------+-----------------------+---------------------+
|  colID         | colTitle              | colKeywords         |
+----------------+-----------------------+---------------------+
| 1              |  Jumanji              | comedy adventure    |
| 2              |  Moana                | adventure animation |
| 3              |  Shawshank Redemption | drama tearjerker    |
| 4              |  Avengers             | action              |
+----------------+-----------------------+---------------------+

        +-----------------------------+    +---------+    
Search: | adventure and action movies |    |button GO|
        +-----------------------------+    +---------+

我想做的是，如果我在文本字段中输入“冒险和 Action 电影”，然后点击按钮后，表格 View 中的结果应该是:

Jumanji
Moana
Avengers

我想使用 Like 子句来实现这一点。到目前为止，已经尝试了很多例子并提出了问题，但似乎没有任何效果。

我在撰写本文时使用的代码:

@FXML
private void goSearch(ActionEvent event) throws IOException, SQLException{



   String sql = "SELECT * FROM table_entry WHERE colKeywords LIKE ? ";
   conn = SqlConnection.ConnectDB();
   pst=conn.prepareStatement(sql);

    String criteria = txt_search.getText();
    if (criteria.trim().length() == 0) { return; }
    List<String> results = new ArrayList<>();
    String[] arryCriterion = criteria.split(" ");
    for (int i = 0; i < arryCriterion.length; i++) {
        String criterion = "%" + arryCriterion[i] + "%";
        pst.setString(1, criterion);
        rs = pst.executeQuery();
        while (rs.next()) {
            results.add(rs.getString("colKeywords"));


        } 
    }

    table_title.setItems(results);

}

编辑塞德里克:

String word = txt_search.getText();

String sql = "SELECT * FROM table WHERE colKeywords LIKE '%word%'";

我这样做对吗？如果文本字段中有多个单词怎么办？

Answer 1

我不确定哪个部分不起作用，但下面的代码应该可以工作:

private void goSearch(ActionEvent event) throws IOException, SQLException{

    try{

        conn = SqlConnection.ConnectDB();
        String criteria = txt_search.getText();
        if (criteria.trim().length() == 0) { return; }
        String[] arryCriterion = criteria.split(" ");
        List<String> results = new ArrayList<>();

        for (int i = 0; i < arryCriterion.length; i++) {

            List<String> text = populateField(arryCriterion[i], conn);

            results.addAll(text);

        }
        table_title.setItems(results);

    }finally{
        conn.close();
    }
}   
private List<String> populateField(String s, Connection conn){

    List<String> myList = new ArrayList<>();

    String sql = "SELECT * FROM table_entry WHERE colKeywords LIKE ? ";


    pst=conn.prepareStatement(sql);

    pst.setString(1, "%" + s + "%");
    rs = pst.executeQuery();
    while (rs.next()) {
        myList.add(rs.getString("colKeywords"));
    }
    return myList;
}

由安德烈斯编辑如下:

netbeans 要求我强制转换 observablist，否则程序将无法运行

table_title.setItems((ObservableList) results);

至于错误，每次我按下搜索按钮时，错误是:

Caused by: java.lang.reflect.InvocationTargetException

Caused by: java.lang.ClassCastException: java.util.ArrayList cannot be cast to javafx.collections.ObservableList

代码(当我尝试从此列中获取关键字时，我用 colTitle 替换了 colKeywords):

@FXML
    private void goSearch(ActionEvent event) throws IOException, SQLException{


          try{

        conn = SqlConnection.ConnectDB();
        String criteria = txt_search.getText();
        if (criteria.trim().length() == 0) { return; }
        String[] arryCriterion = criteria.split(" ");
        List<String> results = new ArrayList<>();

        for (int i = 0; i < arryCriterion.length; i++) {

            List<String> text = populateField(arryCriterion[i], conn);

            results.addAll(text);

        }

        You need to convert it to an ObservableList.

 ObservableList<String> observableList = FXCollections.observableList(results);

        table_title.setItems(observableList );

    }finally{
        conn.close();
    }
}   
    @FXML
    private List<String> populateField(String s, Connection conn) throws SQLException{

    List<String> myList = new ArrayList<>();

    String sql = "SELECT * FROM table_entry WHERE colTitle LIKE ? ";


    pst=conn.prepareStatement(sql);

    pst.setString(1, "%" + s + "%");
    rs = pst.executeQuery();
    while (rs.next()) {
        myList.add(rs.getString("colTitle"));
    }
    return myList;

    }