Vaadin: How do I display data from a MySQL database?

Question

I'm developing a Vaadin Flow (version 14.1) app and I have this issue, I don't get it to connect directly with MySQL database.

I have set up the JDBC connection with maven, I've also created a singleton class I call Datasource where I store my values and methods. However right now it only has one as I'm testing this, this is what I want to do:

• Click on a button on the app and update a label

Here's the button click listener:

button.addClickListener(click -> {
        label.setText(Datasource.getInstance().getUsername());
    });

Here's the Datasource class method:

public String getUsername() {
    String username = "QUERY-FAILED";

    try {
        start();
        statement = conn.createStatement();
        ResultSet rs = statement.executeQuery("select * from names");

        rs.next();
        username = rs.getString(1);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        close();
    }

    return username;
}

But the label doesn't update, if I comment the try block it updates to QUERY-FAILED which is the string I put to test if it failed, but if it isn't commented the label just stays the same.

I also tried to add a main method to the Datasource class and run it as a Java application, and the method works fine, it does return a string with the username. So I'm guessing I'm stuck somewhere in between the connection with the vaadin app. Also, If I try to get the username String in my vaadin app when I'm starting the app (and not with a click listener) I got an long stack of errors with the Datasource indicating a nullpointerexception here:

statement = conn.createStatement();

Thanks in advance!

Answer 1

You should check if the ResultSet rs actually has results. When calling rs.next(), look if it returns false. How to check if ResultSet is empty

public String getUsername() {
    String username = "QUERY-FAILED";

    try {
        start();
        statement = conn.createStatement();
        ResultSet rs = statement.executeQuery("select userName from names");

        if(rs.next() != false){
            username = rs.getString(1);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        close();
    }

    return username;
}

Another thing I noticed is that from the query select * from names, one cannot be sure that the userName attribute is the first column, which you are reading with rs.getString(1). Make sure to write your query more precise to avoid hard-to-find bugs: select userName from names; Do this even if the table only has one column, because what if somebody prepended another column? it could break your application.

Answer 2

I cannot spot any problem with your code. But I can provide an entire working example app for you to compare

My example app goes along the lines laid out in your Question's code. A Vaadin Button performs a database query using a DataSource object from a table of user names. The value from the first row found is displayed in a Vaadin Label widget on the web page.

This app was built and run with Vaadin 14.1.5 using a "Plain Java Servlet" flavor of a starter project provided by the Vaadin.com site. Running on macOS Mojave with the bundled Jetty web container.

My only changes to their Maven POM file was to change to Java version 13, and to add a dependency for H2 Database Engine to make this a self-contained example using an in-memory database.

    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>1.4.200</version>
    </dependency>

I used the hook for Vaadin starting, to establish my DataSource object and initialize the database. Following the manual, nested in the resources folder, I created folders META-INF > services. Per the Java SPI facility, there I created a file named com.vaadin.flow.server.VaadinServiceInitListener containing a single line to specify the name of my class that implements the interface named in the name of this file:

work.basil.example.ApplicationServiceInitListener

That is, my ApplicationServiceInitListener class implement the Vaadin interface VaadinServiceInitListener. My class will be automatically instantiated and its method invoked via that Java SPI facility when my Vaadin web app launches.

My ApplicationServiceInitListener class:

package work.basil.example;

import com.vaadin.flow.server.ServiceInitEvent;
import com.vaadin.flow.server.VaadinServiceInitListener;
import org.h2.jdbcx.JdbcDataSource;

public class ApplicationServiceInitListener implements VaadinServiceInitListener
{
    @Override
    public void serviceInit ( ServiceInitEvent serviceInitEvent )
    {
        System.out.println( "DEBUG Running `serviceInit` of " + this.getClass().getCanonicalName() );

        // Database work.
        prepareDataSource();
        App.INSTANCE.provideDatabase().initializeDatabase();
    }

    private void prepareDataSource ( )
    {
        JdbcDataSource ds = new JdbcDataSource();
        ds.setURL( "jdbc:h2:mem:demo;DB_CLOSE_DELAY=-1" );
        ds.setUser( "scott" );
        ds.setPassword( "tiger" );
        App.INSTANCE.rememberDataSource( ds );
    }
}

That class calls my App class which acts as a sort of service locator. Designed as a singleton via enum.

package work.basil.example;

import javax.sql.DataSource;
import java.util.Objects;

public enum App
{
    INSTANCE;

    // -------|  DataSource  |---------------------------------
    private DataSource dataSource;

    public DataSource provideDataSource ( )
    {
        return this.dataSource;
    }

    public void rememberDataSource ( DataSource dataSource )
    {
        this.dataSource = Objects.requireNonNull( dataSource );
    }


    // -------|  Database  |---------------------------------
    private Database database;

    public Database provideDatabase ( )
    {
        return new Database();
    }
}

That class calls my Database class. In real work, Database would be an interface with various concrete implementations for testing versus deployment. I ignored that here for demonstration purposes.

package work.basil.example;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Database
{
    public String getFirstUserName ( )
    {
        String userName = "QUERY-FAILED";

        String newline = "\n";
        StringBuilder sql = new StringBuilder();
        sql.append( "SELECT name_ from  user_ ; " ).append( newline );
        System.out.println( "sql = " + sql );
        try (
                Connection conn = App.INSTANCE.provideDataSource().getConnection() ;
                Statement statement = conn.createStatement() ;
                ResultSet resultSet = statement.executeQuery( sql.toString() ) ;
        )
        {
            while ( resultSet.next() )
            {
                userName = resultSet.getString( "name_" );
                break; // Go no further. We need only the first row found.
            }
        }
        catch ( SQLException e )
        {
            e.printStackTrace();
        }

        return userName;
    }

    public void initializeDatabase ( )
    {
        System.out.println( "DEBUG Running `initializeDatabase` of " + this.getClass().getCanonicalName() );
        String newline = "\n";

        // Create table.
        StringBuilder sql = new StringBuilder();
        sql.append( "CREATE TABLE user_ ( " ).append( newline );
        sql.append( "pkey_ IDENTITY NOT NULL PRIMARY KEY , " ).append( newline );  // `identity` = auto-incrementing long integer.
        sql.append( "name_ VARCHAR NOT NULL " ).append( newline );
        sql.append( ") " ).append( newline );
        sql.append( ";" ).append( newline );
        System.out.println( "sql = " + sql );
        try (
                Connection conn = App.INSTANCE.provideDataSource().getConnection() ;
                Statement statement = conn.createStatement() ;
        )
        {
            statement.executeUpdate( sql.toString() );
        }
        catch ( SQLException e )
        {
            e.printStackTrace();
        }
        System.out.println("DEBUG Finished `CREATE TABLE` statement.");

        // Populate table.
        sql = new StringBuilder();
        sql.append( "INSERT INTO user_ ( name_ ) " ).append( newline );
        sql.append( "VALUES " ).append( newline );
        sql.append( "( 'Alice' ) , " ).append( newline );
        sql.append( "( 'Bob' ) , " ).append( newline );
        sql.append( "( 'Carol' ) " ).append( newline );
        sql.append( ";" ).append( newline );
        System.out.println( "sql = " + sql );
        try (
                Connection conn = App.INSTANCE.provideDataSource().getConnection() ;
                Statement statement = conn.createStatement() ;
        )
        {
            int rowsAffected = statement.executeUpdate( sql.toString() );
            System.out.println( "DEBUG Inserted rows into name_ table: " + rowsAffected );
        }
        catch ( SQLException e )
        {
            e.printStackTrace();
        }
        System.out.println("DEBUG Finished `INSERT` statement.");
    }
}

And lastly, the MainView class. I disable the @PWA annotation as we are not using that feature for progressive web apps.

package work.basil.example;

import com.vaadin.flow.component.button.Button;
import com.vaadin.flow.component.html.Label;
import com.vaadin.flow.component.notification.Notification;
import com.vaadin.flow.component.orderedlayout.VerticalLayout;
import com.vaadin.flow.router.Route;

/**
 * The main view contains a button and a click listener.
 */
@Route ( "" )
//@PWA ( name = "Project Base for Vaadin", shortName = "Project Base" )
public class MainView extends VerticalLayout
{
    private Label label;
    private Button button;

    public MainView ( )
    {
        // Widgets
        this.label = new Label( "User: ?" );
        this.button = new Button(
                "Get user" ,
                event -> {
                    Notification.show( "Getting user." );
                    String userName = App.INSTANCE.provideDatabase().getFirstUserName();
                    this.label.setText( "User: " + userName );
                }
        );
        add( button );

        // Arrange
        this.add( label , button );
    }
}