How do i write to a database by taking user input from a GUI and storing that user input into a database in java?

Question

I've recently been trying to write to a Database by taking user input from a GUI and I would like to store that user input from the GUI in a derby database on netbeans. At the moment, I have got some code but it does not seem to be functional as when i run the GUI and start inputting the data that is supposed to be stored in the back end of my program in the database, it does not store that data which is being inputted in the GUI. It does not load any data onto the database!

Here is the code for writing to the Database:

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
 *
 * @author Hammad
 */
public class WritePlayerDB 
{
    private final DBConnection dbc;
    private final Connection conn;
    private Statement stmt;
    String insertQuery;
    
    public WritePlayerDB() 
    {
        dbc = new DBConnection();
        conn = dbc.getConnection();
    }
    
    public void writeToPlayerDB()
    {
        try {
            int playerID = 0;
            insertQuery = "INSERT INTO 'PLAYERINFO' ('PLAYER_ID', 'PLAYER_NAME', 'PRIZE_WON', 'CORRECT_ANSWERS')" + "VALUES(1,2,3,4)";
            this.stmt = conn.createStatement();
            this.checkExistedTable("PLAYERS");
            PreparedStatement ps = conn.prepareStatement(insertQuery);
            
            CorrectAnswers.correctAnswers[MillionaireGui.moneyCounter] = PrizeMoney.prizeLadder[MillionaireGui.moneyCounter];
            
            ps.setInt(1, playerID++);
            ps.setString(2, MillionaireGui.nameField.getText());
            ps.setInt(3, PrizeMoney.prizeLadder[MillionaireGui.moneyCounter]);
            ps.setInt(4, CorrectAnswers.correctAnswers[MillionaireGui.moneyCounter]);
            ps.executeUpdate();
            
            ps.close();
        } 
        catch (SQLException ex) 
        {
            System.out.println(ex.getMessage());
        }
    }
    
    public void checkExistedTable(String name) 
    {
        try {
            DatabaseMetaData dbmd = this.conn.getMetaData();
            String[] types = {"TABLE"};
            stmt = this.conn.createStatement();
            ResultSet rs = dbmd.getTables(null, null, null, types);

            while (rs.next()) {
                String table_name = rs.getString("TABLE_NAME");
                System.out.println(table_name);
                if (table_name.equalsIgnoreCase(name)) {
                    stmt.executeUpdate("Drop table " + name);
                    System.out.println("Table " + name + " has been deleted.");
                    break;
                }
            }
            rs.close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
}

and I am calling the writeToPlayerDB() method in the actionListener for my GUI's JTextField as shown below:

nameField.addActionListener(new ActionListener()
        {
            public void actionPerformed(ActionEvent e)
            {
                if (nameField.getText().equals(""))
                {
                    blankName.setVisible(true);
                    blankName.setText("PLEASE DON'T LEAVE NAME BLANK");
                }
                else
                {
                    playerName.setPlayerName(nameField.getText().toUpperCase());
                    WritePlayerDB wdb = new WritePlayerDB();
                    wdb.writeToPlayerDB();
                    blankNamePanel.setVisible(false);
                    introRuleScreen();
                }
            }
        });

Would appreciate if i could get a hand with this as I have been stuck on it for a long time!

Answer 1

There's not enough information to go by to truely understand what your core problem is, how ever...

static is not a good idea, especially when you're dealing with UI elements, as you might have more then one instance of the UI and then you can no longer determine which instance of the element you're actually referencing. Better to make use of dependency injection and pass the information to the method directly.

+ "VALUES(1,2,3,4)" s not how you use PreparedStatement, see [Using Prepared Statements](Using Prepared Statements) for more details

Also...

int playerID = 0;
//...
ps.setInt(1, playerID++);

means that the player id is ALWAYS 0 (the ++ is a post operation). In fact, you should avoid at all costs, manually creating record ids like this and the database should be making use of "auto incrementing" values internally. See Autogenerated keys for more details.

For simplicty, I use the H2 Database Engine for the following example. It should otherwise work with Derby, but you'll want to remove the createTables method

import java.awt.EventQueue;
import java.awt.GridBagConstraints;
import java.awt.GridBagLayout;
import java.awt.Insets;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
import java.util.StringJoiner;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JSpinner;
import javax.swing.JTextField;
import javax.swing.SpinnerNumberModel;
import javax.swing.border.EmptyBorder;

public class Main {
    public static void main(String[] args) {
        new Main();
    }

    public Main() {
        EventQueue.invokeLater(new Runnable() {
            @Override
            public void run() {
                try {
                    DatabaseManager db = new DatabaseManager();
                    db.open();
                    JFrame frame = new JFrame();
                    frame.add(new MainPane(db));
                    frame.pack();
                    frame.setLocationRelativeTo(null);
                    frame.setVisible(true);
                } catch (SQLException ex) {
                    Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        });
    }

    public class MainPane extends JPanel {

        private JTextField nameField;
        private JSpinner moneyCounter;

        public MainPane(DatabaseManager database) {
            setBorder(new EmptyBorder(32, 32, 32, 32));
            setLayout(new GridBagLayout());
            GridBagConstraints gbc = new GridBagConstraints();

            gbc.gridx = 0;
            gbc.gridy = 0;
            gbc.anchor = GridBagConstraints.LINE_END;
            gbc.insets = new Insets(4, 4, 4, 4);

            add(new JLabel("Name: "), gbc);
            gbc.gridy++;
            add(new JLabel("Counter: "), gbc);

            gbc.gridx = 1;
            gbc.gridy = 0;
            gbc.anchor = GridBagConstraints.LINE_START;

            nameField = new JTextField(10);
            moneyCounter = new JSpinner(new SpinnerNumberModel(0, 0, 100, 10));

            add(nameField, gbc);
            gbc.gridy++;
            add(moneyCounter, gbc);

            JButton btnSave = new JButton("Save");
            btnSave.addActionListener(new ActionListener() {
                @Override
                public void actionPerformed(ActionEvent e) {
                    String name = nameField.getText();
                    if (name.isBlank()) {
                        JOptionPane.showMessageDialog(MainPane.this, "Name can not be blank", "Error", JOptionPane.ERROR_MESSAGE);
                        return;
                    }
                    try {
                        int playerId = database.savePlayer(name, (int) moneyCounter.getValue());
                        List<String> results = database.getPlayerById(playerId);
                        StringJoiner sj = new StringJoiner("<br>", "<html>", "</html>");
                        for (String text : results) {
                            sj.add(text);
                        }
                        JOptionPane.showMessageDialog(MainPane.this, sj.toString(), "Success", JOptionPane.PLAIN_MESSAGE);
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                        JOptionPane.showMessageDialog(MainPane.this, "Failed to create player record", "Error", JOptionPane.ERROR_MESSAGE);
                    }
                }
            });

            gbc.gridy++;
            gbc.gridx = 0;
            gbc.gridwidth = GridBagConstraints.REMAINDER;
            gbc.anchor = GridBagConstraints.CENTER;

            add(btnSave, gbc);
        }

    }

    public class DatabaseManager {

        private Connection connection;

        protected void createTables() throws SQLException {
            String cmd = "create table IF NOT EXISTS PLAYERINFO ("
                    + "PLAYER_ID bigint GENERATED BY DEFAULT AS IDENTITY(START WITH 0) PRIMARY KEY,"
                    + "PLAYER_NAME varchar(255) not null,"
                    + "PRIZE_WON int not null,"
                    + "CORRECT_ANSWERS int not null"
                    + ")";
            try (Statement stmt = connection.createStatement()) {
                stmt.execute(cmd);
            }
        }

        public void open() throws SQLException {
            if (connection != null && !connection.isClosed()) {
                try {
                    connection.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Main.class.getName()).log(Level.WARNING, null, ex);
                }
            }
            connection = DriverManager.getConnection("jdbc:h2:~/test");
            createTables();
        }

        public void close() throws SQLException {
            if (connection == null) {
                return;
            }
            try {
                if (connection.isClosed()) {
                    connection = null;
                }
                connection.close();
            } finally {
                connection = null;
            }
        }

        public int savePlayer(String name, int counter) throws SQLException {
            String cmd = "insert into PLAYERINFO (PLAYER_NAME, PRIZE_WON, CORRECT_ANSWERS) values (?, ?, ?)";
            try (PreparedStatement stmt = connection.prepareStatement(cmd, Statement.RETURN_GENERATED_KEYS)) {
                Random rnd = new Random();
                stmt.setString(1, name);
                stmt.setInt(2, rnd.nextInt(100) + 1);
                stmt.setInt(3, counter);
                if (stmt.executeUpdate() != 1) {
                    throw new SQLException("Unexpectedly modified more then one row");
                }
                try (ResultSet rs = stmt.getGeneratedKeys()) {
                    if (rs.next()) {
                        return rs.getInt(1);
                    }
                }
            }

            throw new SQLException("Failed to create player record");
        }

        public List<String> getPlayerById(int id) throws SQLException {
            String cmd = "select * from playerinfo where PLAYER_ID = ?";
            List<String> results = new ArrayList<>(8);
            try (PreparedStatement stmt = connection.prepareStatement(cmd)) {
                stmt.setInt(1, id);
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        String name = rs.getString("PLAYER_NAME");
                        int prizeWon = rs.getInt("PRIZE_WON");
                        int correctAnswer = rs.getInt("CORRECT_ANSWERS");

                        results.add(name + " won " + prizeWon + " with " + correctAnswer + " correct answers");
                    }
                }
            }
            return results;
        }

    }
}