How to connect PostgreSQL with JavaFX

Question

I want to connect my PostgreSQL database to JavaFX in a way that when the user enters the command in the text field, the label or the table view displays the query result. Here is my code for that which I receive a null connection error.

P.S. The picture of the UI which I have added may explain the situation better

public class ListController {

    @FXML
    public TextField sqlTextField;
    @FXML
    public Label textLabel;

    public void handleSaveButton(ActionEvent actionEvent) {
    }

    public void handleRunButton(ActionEvent actionEvent1) throws SQLException {

        ConnectionClass connectionClass = new ConnectionClass();
        Connection connection = connectionClass.getConnection();

        String sql = sqlTextField.getText() + ";";
        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            textLabel.setText(resultSet.getString(1));

        }
    }
}

enter image description here

Study a [Postgres JDBC tutorial](https://mkyong.com/jdbc/how-do-connect-to-postgresql-with-jdbc-driver-java/) and make sure you can get that work independent if the UI and JavaFX. — jewelsea, Oct 20 '21 at 21:49
Study the [*JDBC Database Access*](https://docs.oracle.com/javase/tutorial/jdbc/TOC.html) tutorial by Oracle, free of charge. — Basil Bourque, Oct 21 '21 at 00:48

Basil Bourque · Answer 1 · 2021-10-21T01:03:42.017

I have no idea what you meant by ConnectionClass.

In JDBC, use an implementation of DataSource to get connections to your database.

This JDBC driver for Postgres provides a couple of implementations. You likely will want org.postgresql.ds.PGSimpleDataSource.

PGSimpleDataSource dataSource = new PGSimpleDataSource();

String[] serverAddresses = { "your-server-address-goes-here" };
dataSource.setServerNames( serverAddresses );
int[] serverPortNumbers = { your-port-number-goes-here };
dataSource.setPortNumbers( serverPortNumbers );
dataSource.setSslCert( cert );
dataSource.setDatabaseName( "your-database-name" );
dataSource.setUser( "your-database-user-name" );
dataSource.setPassword( "your-password-goes-here" );

return dataSource ;  // Return as a `DataSource` type of object.

Your code elsewhere can ask for a connection. Use try-with-resources syntax to automatically close your connection (and other resources such as result set).

try (
    Connection conn = dataSource.getConnection() ;
)
{
    // … Use `conn` Connection object to access database.
}
catch ( SQLException e )
{
    e.printStackTrace();
}

See my Answer for more discussion.

Of course, embedding your database password within your JavaFX app is insecure. One alternative is using JNDI to access a service such as an LDAP server to obtain database credentials. But that is a whole other discussion.

Also insecure is blindly executing SQL statements typed by a user. That should never be done in a real system. Learn about SQL injection attacks. But if you are just experimenting, have fun.

How to connect PostgreSQL with JavaFX

1 Answers1