How to show "count" MySQL query into JTextField?

Question

Right now, I'm stuck with this problem: I want to show the total amount of songs in my database inside the GUI (through the jtextfield).

This is what I got so far:

txtTotalSongs = new JTextField();
txtTotalSongs.addActionListener(new ActionListener() {
    public void actionPerformed(ActionEvent arg0) {
        try {
            String q = "select count (title) from songs";
            PreparedStatement ps=conn.prepareStatement(q);

            ps.setInt(1, 20);

            ResultSet rs = ps.executeQuery();

            while(rs.next())
            {
                txtTotalSongs.setText(String.valueOf("title"));
            }

        } catch (Exception e) {
            // TODO: handle exception
        }

    }
});
txtTotalSongs.setBounds(591, 458, 86, 20);
contentPane.add(txtTotalSongs);

what is the action you expect to be called on the JTextField, in response to which you will run actionPerformed? — Joeblade, Dec 05 '14 at 20:30
well, i want the total number of rows from the database to be shown in the JTextField. does that answer your question? sorry, still a noob. @Joeblade — Izzie Ruslim, Dec 05 '14 at 20:40
No the reason I asked was that you are running this code in the 'actionPerformed' part of JTextField. What action will be performed on JTextField? Ususally this is a text change or similar. on a button actionperformed would be a click. Are you sure you don't want to run this code after contentPane.add(....) instead? — Joeblade, Dec 05 '14 at 20:53

score 1 · Answer 1 · answered Dec 05 '14 at 20:35

Instead of the while loop, try

if(rs.first())
    xtTotalSongs.setText(rs.getString("title"));

rs.first() returns true (and moves the pointer to the first) if there is a row in the result set. getString returns the value of the column "title" in the first row.

Since there should only be one row for "Count" (at least I guess so), this should work.

Salih Erikci · Answer 2 · 2014-12-05T20:46:53.433

0

while(rs.next()){
    txtTotalSongs.setText(String.valueOf("title"));
    // This doesn't get any value from rs
    // It just get value "title".
}

Change your while loop as below:

while(rs.next())
{
    txtTotalSongs.setText(rs.getInt(1));
}

edited Dec 05 '14 at 20:46

answered Dec 05 '14 at 20:31

Salih Erikci

5,076
12
39
69

still didnt got what i wanted :/ – Izzie Ruslim Dec 05 '14 at 20:41

score 0 · Answer 3 · edited May 23 '17 at 12:22

I would set this outside of the actionPerformed.

txtTotalSongs = new JTextField();
txtTotalSongs.setBounds(591, 458, 86, 20);
contentPane.add(txtTotalSongs);

txtTotalSongs.setText(getTotalSongs());

and then add a method somewhere:

// not ideal but trying to use most of your original code.
public int getTotalSongs() {
    try {
        String q = "select count (title) from songs";
        PreparedStatement ps=conn.prepareStatement(q);

        ps.setInt(1, 20);

        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            return rs.getInt(1);
        } else { 
            return 0;
        }
    } catch (Exception e) {
        // TODO: handle exception
        return 0;
    }
}

if you want to regularly update the count, you can run a Timer, just be careful about threadsafety when updating.

How to show "count" MySQL query into JTextField?

3 Answers3