Discussions

General J2EE: dynamic "search" query

  1. dynamic "search" query (1 messages)

    Hi there, I'm tring to build a dynamic query to search not only the exatly phrase typed by the user but even the single words. So far, I've got how to make the preparedStatement but I'm still stuck in how to make the exactly number of psmt.setString necessary to the preparedStatement. Belowe the code. Thanks in advance for your help.
    String query = "";
    int i = 0;
    for(i = 0; i < java.lang.reflect.Array.getLength(arrayWords); i++){
    String tmp = arrayWords[i];
    query = query +" OR title LIKE ? OR content LIKE ?";
    }
    connection = DriverManager.getConnection(DB_properties.getDB_url(),DB_properties.getDB_username(),DB_properties.getDB_password());
    pstmt = connection.prepareStatement("SELECT * FROM search_content WHERE title LIKE ? OR content LIKE ?"+query+";");
    pstmt.setString(1,words);
    pstmt.setString(2,words);

    //for(){}
    rs = pstmt.executeQuery();
  2. dynamic "search" query[ Go to top ]

    I can't tell _exactly_ what you're trying to do, but it looks like you want to use the same word for both title and content, so after you build your query, this would work:

    for (int i = 0, offset = 1; i < arrayWords.length; ++i) {
        pstmt.setString(offset++, arrayWords[i]);
        pstmt.setString(offset++, arrayWords[i]);
    }

    So if your words were ("foo", "bar") and your query was

    SELECT * FROM search_content WHERE title LIKE ? OR content LIKE ? OR title LIKE ? OR content LIKE ?

    This would substitute to (roughly):

    SELECT * FROM search_content WHERE title LIKE 'foo' OR content LIKE 'foo' OR title LIKE 'bar' OR content LIKE 'bar'

    Once again, I couldn't exactly tell what you were trying, but this should help.