0

I will try to explain my title a little better but first a little background to what I'm trying to do here. I'm working on a game where I will need to search a local database for a word that the user has given the program. This is done so that it can verify that the user has actually used a word that fits the rules of the game. In this bit of code I would want to search a database of countries to verify that the user has actually given the game a country and not a random word.

Since I'm fairly new to java and just beginning to understand SQLite and JDBC I searched the internet for how to query my database and I found some useful bit of code that I tweaked to my liking. Now the problem is that, from the beginning, the main-method would establish a connection to the database and just pick out every single thing from the database. Not really my intention so I thought I could ask the user for some input, store that in a string (beneath referred as 'word') and the use the SQL command SELECT * FROM [table] WHERE [column] LIKE [condition].

When I then put my newly created string 'word' as condition and try to search the database, the program does not seem to recognize word for what it is or even use it at all. Intellij marks 'word' in gray, so supposedly it is never used.

How come that the .executeQuery(SQL-command) won't recognize that it should use word as parameter? How can I make this work so that the user can decide what to search for without explicitly typing the SQL-commmand themselves?

Code:

package SQL;

//STEP 1. Import required packages
import javax.swing.*;
import java.sql.*;

public class SQLTests {

    public static void main(String[] args) throws Exception {
        String word = JOptionPane.showInputDialog(null, "Input Country name:", 
                                                  "Country search", 1);
        Class.forName("org.sqlite.JDBC");
        Connection connection = DriverManager.getConnection("jdbc:sqlite:countries.db");
        Statement stat = connection.createStatement();


        ResultSet rs = stat.executeQuery("select * from Country where name like word;");
        while (rs.next()) {
            System.out.println(rs.getString("name"));
        }
        rs.close();
        connection.close();
    }
}
Hampus Eriksson
  • 5
  • 2
  • maybe: ResultSet rs = stat.executeQuery("select * from Country where name like " + word + ";"); – Frederic Klein Apr 13 '16 at 09:26
  • First off all when your query should search for "word" then you have to place quotes " around it, When you search exactly the hole word "word" then you can use the equals "=" parameter. If you want to search for the string part "word" in somewhere your have to use "%" as wildcard. – Rene M. Apr 13 '16 at 09:27
  • If you want that "word" is a parameter which you can replace with something else then replace it with a questionmark "?" and create a prepared statement of the query string, on which you can then set the parameters to the including questionsmarks – Rene M. Apr 13 '16 at 09:28
  • @ReneM. I noticed the quotes were missing. my bad. Since I'm always searching for the whole word I will try to use equals and I assume you mean to use it as: SELECT * FROM table WHERE name = "word". – Hampus Eriksson Apr 13 '16 at 09:30
  • I agree on the remark regarding prepared statements. If you just take the user input and run it in your current query you are vulnerable for sql injections. – Frederic Klein Apr 13 '16 at 09:31
  • 1
    Yes this would be the correct query for an exact string compare and case sensitive. But as I said learn how to use prepared statements. https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html – Rene M. Apr 13 '16 at 09:33

1 Answers1

2

Instead of using Statement use PreparedStatement

PreparedStatement stat = connection.prepareStatement("select * from Country
                          where name like ?");
stat.setString(1, word);
ResultSet rs = stat.executeQuery();

Also check Difference Between Statement and PreparedStatement

Hope it'll work.

Community
  • 1
  • 1
ELITE
  • 5,815
  • 3
  • 19
  • 29