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?

hi isaace, if i press the button, the tableview displays nothing. rather it displays error in the system. what should i do first and foremost though.. I am so sorry.. can you guide me. I am able to populate the tableview from the database. But i am having trouble filtering the table using the like clause. sorry for my english — Andres Magallanes, Mar 14 '18 at 19:01
The way to do this is to add a `Keyword` table and a `link` table. The `Link` table maps `colID` to `keywordID`. — SedJ601, Mar 14 '18 at 19:02
I don't recommend this but https://stackoverflow.com/questions/14290857/sql-select-where-field-contains-words — SedJ601, Mar 14 '18 at 19:05
Possible duplicate of [SQL SELECT WHERE field contains words](https://stackoverflow.com/questions/14290857/sql-select-where-field-contains-words) — SedJ601, Mar 14 '18 at 19:05
Use a debugger and see at which line you are getting an error and check what is the error. — isaace, Mar 14 '18 at 19:13
Hi sedrick, I am unfamiliar with the process of many to many, But i will try to research this also. perhaps your second suggestion will be helpful. I have a question though. I will edit my original post as i cant seem to post codes in the comments. But, what if there are multiple words in the TextField.. I can't really explain my question so much in English. I will put the code in my original post — Andres Magallanes, Mar 14 '18 at 19:20

isaace · Accepted Answer · 2018-03-14T20:47:51.780

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;

    }

Hi isaace thanks for your help. I doesn't seem to work when implemented, I edited your answer with my concern . Thank you so much — Andres Magallanes, Mar 14 '18 at 20:37
You need to convert it to an ObservableList before calling table_title.setItems(). See my edit. — isaace, Mar 14 '18 at 20:48
ObservableList observableList = FXCollections.observableList(results); — isaace, Mar 14 '18 at 20:49
Thank you so much isaace.. it now returns no error.. I know it is working because when i hit the search button, the table now shows the grid. But the problem is that it is not showing the Titles in the table. it is empty. Should there be settings or should be done to the table beforehand? — Andres Magallanes, Mar 14 '18 at 21:00
https://i.imgur.com/Xi9RxH1.png this is what the table looks like if it matches one of the words from the column. when it doesnt match any of the word.. the table doesnt show grid.. but shows only "no content in table" — Andres Magallanes, Mar 14 '18 at 21:03
Hi Isaace, in this case, should i use the tableview column's fxid and use it somewhere in the code?, or it's unnecessary?.. I know that the code is working.. becauae in my table in database, there are three titles that contain the words "development" when i search the word development, the three rows in the tableview can be highlighted. The thing is it is not showing any text.. — Andres Magallanes, Mar 14 '18 at 22:06
I edited your post. and posted the entire code in my controller. Im not sure what is wrong with this. There are no errors.. but when i press the searchbutton, no Texts appear in the tableview — Andres Magallanes, Mar 14 '18 at 23:09
I know your code is the correct answer, maybe there's something simple that i miss.. I'll accept your answer as correct. — Andres Magallanes, Mar 15 '18 at 00:18

Using MySQL Like clause in javafx

1 Answers1