Using MySQL Like clause in javafx

Question

Say i have a 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|
        +-----------------------------+    +---------+

What I want to do is if i type "adventure and action movies" in the textfield, and after I hit the button go, the result in the tableview should be:

Jumanji
Moana
Avengers

I want to achieve this using the Like clause. so far, been trying a lot of examples and asked questions, and nothing seem to work.

the code i am using as of writing:

@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);

}

edit for sedrick:

String word = txt_search.getText();

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

Am i doing this right? What if there are mutiple words in the textfield?

Answer 1

I am not sure what part isn't working but the code below should work:

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

edit by andres below:

netbeans requires me to cast observablist otherwise the program wont run

table_title.setItems((ObservableList) results);

as for the error, everytime I press the search button, the errors are:

Caused by: java.lang.reflect.InvocationTargetException

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

the Code(I replaced colKeywords with colTitle as I try to get the keywords from this column):

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

    }