I have a local SQL-Server running MariaDB and need to query Data from a database using Java and JDBC. I can connect to the Database and also write data, but a simple SELECT does not work.

I already tried to use different versions of the mysql-java-connector and checked that the SQL-Server is up to date.

Connecting to Database:

//Check wether connection already exists

if(connection != null && !connection.isClosed()){

    return;

}

//Create new connection

connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/statdb", "root", "");

System.out.println("Connected to Database");

//Update guilds from Database

updateGuildsInDatabase();

The method called at the end looks like this:

private void updateGuildsInDatabase() throws SQLException {

    //Check for not existing connection

    if(connection == null || connection.isClosed()){

        init();

        return;

    }

    ArrayList<String> localInDb = new ArrayList<>();

    Statement qGStmt = connection.createStatement();

    //Execute Query

    ResultSet guilds = qGStmt.executeQuery("SELECT * FROM guilds;");

    guilds.first();

    //Adding results to List

    while(guilds.next()){

        localInDb.add(guilds.getString("guild_uid").toLowerCase());

    }

    System.out.println("Queried: " + localInDb.size());

    this.guildsInDb = localInDb;

}

The table "guilds" in the database "statdb" has two columns named "guild_uid" (primary key, varchar) and "display_name" (varchar). I've added one entry with the values "guild_1" and "Test Guild".

As I have one entry in the table 'guilds' I would expect the console to look like this:

Connected to Database

Queried: 1

But the actual output looks like this:

Connected to Database

Queried: 0

The ResultSet#first() method itself moves the cursor to the first row of the result set. The ResultSet#next() method then moves the cursor forward by one, and reads whatever record is there, if a record is there.

Therefore, using your pattern will always result in skipping the first record from the result set. If the result set happens to have only a single record, then the record count will appear to be zero.

You should probably remove the call to ResultSet#first(). Use this instead:

ResultSet guilds = qGStmt.executeQuery("SELECT * FROM guilds;");

while(guilds.next()) {

    localInDb.add(guilds.getString("guild_uid").toLowerCase());

}

System.out.println("Queried: " + localInDb.size());

this.guildsInDb = localInDb;

The reason why calling ResultSet#next() on a fresh result set logically works, is that by default a JDBC result set actually does not start pointing to the first record. Rather, we typically advance it to the first record with the very first call to ResultSet#next().

I don't think you need guilds.first(). Try removing it.

According to the ResultSet JavaDoc:

A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.

I believe what is happening here is, guilds.first() set the cursor from before first row to first row. And guilds.next() in the while loop move the cursor to further one step ahead. Therefore, you are missing the first row.

The other two Answers are correct, you are skipping the first row when retrieving from the result set. No need for your line guilds.first(); as they explained, the result set automatically points at first row by default.

Example code, retrieving from ResultSet

Here is a complete example using the H2 Database Engine. This shows creating a new database (in-memory, not persisted to disk), adding a table, populating that table with a few rows, and then retrieving all those rows through a ResultSet.

The key line is while ( rs.next() ) {.

package work.basil.example;



import java.sql.*;

import java.util.*;



import org.h2.jdbcx.JdbcDataSource;



import javax.sql.DataSource;



public class GuildDemo {



    public static void main ( String args ) {

        GuildDemo app = new GuildDemo();

        app.doIt();

    }



    private void doIt ( ) {



        DataSource dataSource = null;

        final String catalogName = "guild_demo_";

        final String tableName = "guild_";



        // Verify JDBC driver.

        try {

            Class.forName( "org.h2.Driver" );

            JdbcDataSource ds = new JdbcDataSource(); // The `javax.sql.DataSource` interface implemented by `org.h2.jdbcx.JdbcDataSource`.

            ds.setURL( "jdbc:h2:mem:" + catalogName + ";DB_CLOSE_DELAY=-1" );  // Set delay to -1 to keep in-memory database even after last connection closed.

            ds.setUser( "scott" );

            ds.setPassword( "tiger" );

            ds.setDescription( "Dummy database for demo showing how to retrieve rows from a ResultSet." );

            dataSource = ds;  // Generalize from the concrete class to the interface.

        } catch ( ClassNotFoundException e ) {

            e.printStackTrace();

            return;

        }



        // Connect, and create database.

        try (

                Connection conn = dataSource.getConnection() ;

        ) {

            String sql = null;



            // Create table.

            try ( Statement stmt = conn.createStatement() ; ) {

                sql = "CREATE TABLE " + "guild_" + " ( n" +

                        " pkey_ UUID DEFAULT random_uuid() PRIMARY KEY , n" +

                        " name_ VARCHAR NOT NULL n" +

                        ") ; n";

                System.out.println( "TRACE - SQL:n" + sql );

                stmt.execute( sql );

            }



            // Add rows.

            sql = "INSERT INTO guild_ ( name_ ) n" +

                    "VALUES ( ? ) " +

                    "; ";



            List < String > names = List.of( "Alpha" , "Beta" , "Gamma" , "Delta" ); // Insert a row for each of these names.

            System.out.println( "Inserting list of names: " + names );

            try (

                    PreparedStatement ps = conn.prepareStatement( sql ) ;

            ) {

                for ( String name : names ) {

                    ps.setString( 1 , name );

                    ps.executeUpdate();

                }

            }



            // Retrieve rows from a `ResultSet`.

            sql = "SELECT * FROM " + "guild_" + " ;";

            try (

                    Statement stmt = conn.createStatement() ;

                    ResultSet rs = stmt.executeQuery( sql ) ;

            ) {

                while ( rs.next() ) {

                    UUID pkey = rs.getObject( "pkey_" , UUID.class );

                    String name = rs.getString( "name_" );

                    System.out.println( "Row pkey_: " + pkey + " name_: " + name );

                }

            }





        } catch ( SQLException e ) {

            e.printStackTrace();

        }

    }



}

When run.

Inserting list of names: [Alpha, Beta, Gamma, Delta]

Row pkey_: 69908390-5fa6-4eee-8e12-40106db8d60d name_: Alpha

Row pkey_: 3116acb9-fcce-427f-b222-99c78c6e752a name_: Beta

Row pkey_: b3fd0930-a2e7-461a-be70-f05124fc58de name_: Gamma

Row pkey_: dddb423a-5eb2-4e5e-be16-7bb0c27c0033 name_: Delta