How to return a single data from a database using Java?

Question

Basically there is a table with the fields: id(PK), name, total of votes. And I need to add 1 vote for this specific id per turn the program is used, I was intending to only get the total votes of this id, add plus 1 vote and store it again in the same id. I was using this code:

    String user = "root";
    String pass = "";
    String url = "jdbc:mysql://127.0.0.1:3306/provapaoo_22_06_2018";

    String takeVotes = String.format("select `Total Votes` "
            + "from `%s` where id= ?", nameOfTable);

    String addVote = String.format("update `%s` set "
            + "`Total votes` = ?", nameOfTable);
    try{
    Connection conec =
            DriverManager.getConnection(url, usuario, senha);

        PreparedStatement comand =
                conexão.prepareStatement(takeVotes);

        comando.setInt(1, id);

        ResultSet result = comand.executeQuery();

        int qtdVotes = 0;

        while(resultado.next()){
            qtdVotes = result.getInt("Total Votes");
        }

        qtdVotes++;

        comand = conec.prepareStatement(addVote);

        comand.setInt(1, qtdVotes);

        comand.executeUpdate();

        comand.close();
        conec.close();

    }catch(SQLException e){
        e.printStackTrace();
    }

The program is running good but the problem is the resultSet, it's not returning only the total votes of that id, it's returning the total votes of everyone, it's almost like the sql syntax isn't working.

How do you figure it's returning the total votes of everyone? Is qtdVotes bigger than you anticipate or is your While loop executing many times, instead of just once, and you get some arbitrary qtdVotes value? — JNevill
– JNevill, Commented Jun 21, 2018 at 17:37
@JNevill sorry, it was returning the correct amount o votes that id already had, but I simple didn't told to sql where I wanted to put it, so it was being put for every table. I was thinking that the result set was taking the votes of every id, soorry — user9772072
– user9772072, Commented Jun 21, 2018 at 17:42
AH! Yes that makes sense. I'm sorry I didn't catch that when I first read through the code. — JNevill
– JNevill, Commented Jun 21, 2018 at 17:44

user9772072 · Accepted Answer · 2018-06-21 17:38:38Z

1

Sorry, I was an idiot, It was indeed the sql syntax who was wrong. Here's the one I used now.

       String addVote = String.format("update `%s` set "
            + "`Total votes` = ? where id = ?", nameOfTable);

Collectives™ on Stack Overflow

How to return a single data from a database using Java?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related