Select one random data that have a common characteristic from a sqlite database

Question

I have a database in sqlite that it has questions on it. Each question has a subject, question, answer, and level column. At the moment, I can ask for a subject and it will print me in a textField the question; however, It always selects the same question. How can I choose a random question that belongs to the subject Math? My code looks like this at the moment:

    question = new TextField();
    question.setPrefWidth(400.0);
    question.setPrefHeight(70.0);
    question.setStyle("-fx-font: 30 timesnewroman; -fx-base: #190707;-fx-text-fill: black;");




    startTestButton = new Button("START");
    startTestButton.addEventHandler(ActionEvent.ACTION, (e)-> {

        counterQuestions  = nQTextField.getText();
        System.out.println("Number of questions: " +  counterQuestions);

        try
        {
            String sql = "select * from Questions where Subject = ?";
            PreparedStatement pst = connectionQuestions.prepareStatement(sql);
            pst.setString(1,SsubjectTestField.getText());
            ResultSet rs = pst.executeQuery();
            if(rs.next())
            {
                question.setText(rs.getString("Question"));
            }
            else
            {
                System.out.println("No data for this subject");
            }
        }
        catch(Exception a)
        {
            System.out.println(a);
        }


        primaryStage.setScene(sceneMathTest);


    });
    startTestButton.setPrefWidth(200.0);
    startTestButton.setPrefHeight(50.0);
    startTestButton.setStyle("-fx-font: 30 timesnewroman; -fx-base: #2EFE64; -fx-text-fill: black;");

    createTestButtonPane.add(startTestButton,5,6);

THe frase that I use is : "select * from Questions where Subject = ?", however the * means all of them but it is choosing always the same one.

Thank you so much in advance.

score 0 · Accepted Answer · edited May 23 '17 at 11:59

You only call rs.next() once so it will only select the first one from the result set. If the database always retrieves them in the same order (which it will), then you will always get the same one. (And, by the way, * here means all columns, not all rows; the rows you get are determined by the where clause.)

If you don't have a large number of data in the database, you can just select everything into a list and choose a random element of the list:

    ResultSet rs = pst.executeQuery();
    List<String> allQuestions = new ArrayList<>();
    while(rs.next())
    {
        allQuestions.add(rs.getString("Question"));
    }
    if (allQuestions.isEmpty()) {
        System.out.println("No data for this subject");
    } else {
        Random random = new Random();
        question.setText(allQuestions.get(random.nextInt(allQuestions.size())));
    }

If you have a very large number of items in the database, this is however a bad option, as you will load them all into memory. In that case you might want to use some SQL tricks to select a specific row from the database. You can get a count of all rows first using

SELECT COUNT(*) FROM Questions WHERE Subject = ?

Then choose a random number between 1 and the returned value and use the techniques in How to select the nth row in a SQL database table? to get that specific row.

Thank you so much. Very helpfull – Carmen Zarco Lens Apr 28 '16 at 12:48 — Carmen Zarco Lens, Apr 28 '16 at 12:48

Select one random data that have a common characteristic from a sqlite database

1 Answers1