Besides the excellent points in the Answer by rzwitserloot, I can provide a few more instructions and give example code.
JDBC driver loads automatically
Using Class.forName to load a JDBC driver is old-school, from the earliest versions of Java.
In modern Java, there is no need for that. The Service Provider Interfaces (SPI) facility in Java (Wikipedia) is used by JDBC to automatically locate and load available JDBC drivers.
DataSource
Use of a DataSource is the preferred means of getting a connection to a database. See Tutorial by Oracle. The DataSource object contains your logon info, and the settings you want for your new connection. Using a DataSource enables you to externalize this information at deployment, rather than hard-coding this info within your codebase.
Your JDBC driver likely provides an implementation of DataSource for direct connections. You may also use other implementations, such as those for connection-pooling.
private DataSource configureDataSource ( )
{
System.out.println( "INFO - `configureDataSource` method. " + Instant.now() );
com.mysql.cj.jdbc.MysqlDataSource dataSource = Objects.requireNonNull( new com.mysql.cj.jdbc.MysqlDataSource() ); // Implementation of `DataSource` for this specific database engine.
dataSource.setServerName( "your_server_address" );
dataSource.setPortNumber( some_port_number );
dataSource.setDatabaseName( "your_database_name" );
dataSource.setUser( "scott" );
dataSource.setPassword( "tiger" );
return dataSource;
}
Instantiate a DataSource object, and keep it around. Pass it to your various database methods as needed.
DataSource dataSource = this.configureDataSource();
Drop table
First, while experimenting, you may want to drop any existing table and then rebuild it.
Here in this example we expect a table named event_.
Notice how we use the try-with-resources syntax, as suggested in the other Answer. The resources listed within the parentheses are automatically closed when flow-of-control leaves the try block. This closing happens whether leaving the block successfully as well as when leaving because of a thrown exception (or error). Using try-with-resources makes your code neater and easier to follow, with less boilerplate.
For the SQL strings, we use the text blocks feature new in Java 15. The triple QUOTATION MARK characters mark each block of text. Very handy for formatting snippets of SQL, XML, JSON, etc.
Notice that we use Statement rather than PreparedStatement because our SQL has no untrusted text. If using text within your SQL that came from users or other untrusted sources, always use PreparedStatement.
private void dropTable ( DataSource dataSource )
{
System.out.println( "INFO - `dropTable` method. " + Instant.now() );
try ( Connection conn = dataSource.getConnection() )
{
String sql = """
DROP TABLE IF EXISTS event_
;
""";
System.out.println( "sql: \n" + sql );
try ( Statement stmt = conn.createStatement() )
{
stmt.execute( sql );
}
}
catch ( SQLException e )
{
e.printStackTrace();
}
}
Create table
Let's create the table used for this example.
private void createTable ( DataSource dataSource )
{
System.out.println( "INFO - `createTable` method. " + Instant.now() );
try ( Connection conn = dataSource.getConnection() )
{
String sql = """
CREATE TABLE IF NOT EXISTS event_
(
id_ INT NOT NULL AUTO_INCREMENT PRIMARY KEY , -- ⬅ `identity` = auto-incrementing integer number.
title_ VARCHAR ( 30 ) NOT NULL ,
date_ DATE NOT NULL
)
;
""";
System.out.println( "sql: \n" + sql );
try ( Statement stmt = conn.createStatement() ; )
{
stmt.execute( sql );
}
}
catch ( SQLException e )
{
e.printStackTrace();
}
}
Notice how we use two try-with-resources, one nested within the other. If we moved that SQL string creation code higher up, we could declare and instantiate both the conn and stmt resources is a single try-with-resources.
Otherwise, this code is quite similar to code above. We trust the SQL text, so we use Statement.
private void createTable ( DataSource dataSource )
{
System.out.println( "INFO - `createTable` method. " + Instant.now() );
String sql = """
CREATE TABLE IF NOT EXISTS event_
(
id_ INT NOT NULL AUTO_INCREMENT PRIMARY KEY , -- ⬅ `identity` = auto-incrementing integer number.
title_ VARCHAR ( 30 ) NOT NULL ,
date_ DATE NOT NULL
)
;
""";
System.out.println( "sql: \n" + sql );
try (
Connection conn = dataSource.getConnection() ;
Statement stmt = conn.createStatement() ;
)
{
stmt.execute( sql );
}
catch ( SQLException e )
{
e.printStackTrace();
}
}
Insert rows
Next we insert some rows into our table.
In a real app, we are likely using data entered by users or obtained from another untrusted source. So here we use PreparedStatement. We happen to be using hard-coded strings such as "Dog Show", but these values would likely be variables in a real app.
The java.time types are supported in JDBC 4.2 and later. However, no type-specific methods such as setLocalDate were added (inexplicably). So we use setObject/getObject.
This code adds three rows to the table.
private void insertRows ( DataSource dataSource )
{
System.out.println( "INFO - `insertRows` method. " + Instant.now() );
String sql = """
INSERT INTO event_ ( title_ , date_ )
VALUES ( ? , ? )
;
""";
try (
Connection conn = dataSource.getConnection() ;
PreparedStatement pstmt = conn.prepareStatement( sql ) ;
)
{
pstmt.setString( 1 , "Dog Show" );
pstmt.setObject( 2 , LocalDate.of( 2022 , Month.JANUARY , 21 ) );
pstmt.executeUpdate();
pstmt.setString( 1 , "Cat Show" );
pstmt.setObject( 2 , LocalDate.of( 2022 , Month.FEBRUARY , 22 ) );
pstmt.executeUpdate();
pstmt.setString( 1 , "Bird Show" );
pstmt.setObject( 2 , LocalDate.of( 2022 , Month.MARCH , 23 ) );
pstmt.executeUpdate();
}
catch ( SQLException e )
{
e.printStackTrace();
}
}
Dump table
To verify our rows were successfully inserted, let's dump the contents of this table to the console.
Now we have a ResultSet to examine.
The problem is your Question's code is that you were trying to access a ResultSet outside of the JDBC code. But the JDBC code needs to close its resources. Among these resources is any ResultSet. You need to access your ResultSet object within your JDBC code block. Here we simply grab the values from each row of the result set, then write that value to System.out.
private void dumpTable ( DataSource dataSource )
{
System.out.println( "INFO - `dumpTable` method. " + Instant.now() );
String sql = "SELECT * FROM event_ ;";
try (
Connection conn = dataSource.getConnection() ;
Statement stmt = conn.createStatement() ;
ResultSet rs = stmt.executeQuery( sql ) ;
)
{
System.out.println( "-------| event_ table |--------------------" );
while ( rs.next() )
{
//Retrieve by column name
int id = rs.getInt( "id_" );
String title = rs.getString( "title_" );
LocalDate date = rs.getObject( "date_" , LocalDate.class );
System.out.println( "id_=" + id + " | title_=" + title + " | date_=" + date );
}
}
catch ( SQLException e )
{
e.printStackTrace();
}
}
When run, we get the following.
INFO - `dumpTable` method. 2021-03-11T02:31:22.769442Z
-------| event_ table |--------------------
id_=1 | title_=Dog Show | date_=2022-01-21
id_=2 | title_=Cat Show | date_=2022-02-22
id_=3 | title_=Bird Show | date_=2022-03-23
Looping the result set
The main part of your Question is asking how to loop a result set. The code above does just that.
First, we obtained a ResultSet as the value returned by call to Statement#executeQuery. Then we process that result set. A result set can only deal with one row at a time. So a “cursor” keeps track of which row is currently in hand.
The key part to processing the result set is calling next on your ResultSet object.
The next method does three things:
- Moves the cursor to the first row, on first usage.
- Moves the cursor to the next row, on successive usages.
- Returns
true if a row is queued, ready for access. Returns false if the cursor has moved past the last row in the result set.
Because the method returns a boolean, we can loop successively until encountering a false.
Sharing data
Of course, in a real app we do more than dump database values to the console. We want to share that data with other parts of the app.
As mentioned above and in the other Answer, you cannot share data with other parts of the app by sharing the ResultSet object itself. Instead you must copy the data out of the ResultSet and into other objects. As the other Answer suggests, you have a choice of frameworks to assist with this chore. Or, you may copy the data yourself.
In Java 16, the obvious way to transparently and immutably share such data is to the use the new records feature. A record is a brief way to declare a class. You simply declare the type and name of each member field within a pair of parentheses. The compiler implicitly creates the constructor, getters, equals & hashCode, and toString.
public record Event(Integer id , String title , LocalDate happening) {}
The following code is similar to the dumpTable method. This code passes the data fetched from database to the constructor of Event record to instantiate a Event object. Each Event object is added to a collection. The collection is returned to the calling method.
Generally it is best to return immutable data from a method that is not aware of its callers. As a record, each field on the Event is read-only (final), so is shallowly immutable. All three field types (Integer, String, and LocalDate) are designed to be immutable, so each Event is deeply immutable. The only mutable part of our fetch rows is their container, an ArrayList. So we make an unmodifiable list from that list by passing to List.copyOf. Now we are returning entirely immutable data.
private List < Event > fetchEvents ( DataSource dataSource )
{
System.out.println( "INFO - `fetchEvents` method. " + Instant.now() );
List < Event > events = new ArrayList <>();
String sql = "SELECT * FROM event_ ;";
try (
Connection conn = dataSource.getConnection() ;
Statement stmt = conn.createStatement() ;
ResultSet rs = stmt.executeQuery( sql ) ;
)
{
while ( rs.next() )
{
//Retrieve by column name
int id = rs.getInt( "id_" );
String title = rs.getString( "title_" );
LocalDate date = rs.getObject( "date_" , LocalDate.class );
Event event = new Event( id , title , date ); // Instantiate a record, an `Event` object.
events.add( event );
System.out.println( event );
}
}
catch ( SQLException e )
{
e.printStackTrace();
}
return List.copyOf( events ); // Return an unmodifiable list produced by `List.copyOf`.
}
The result is a list of Event objects. The calling method can do whatever it wants with those objects.
Run example
You can run those methods like this:
DataSource dataSource = this.configureDataSource();
this.dropTable( dataSource );
this.createTable( dataSource );
this.insertRows( dataSource );
this.dumpTable( dataSource );
List < Event > events = this.fetchEvents(dataSource);
System.out.println( "events = " + events );
When run:
events = [Event[id=1, title=Dog Show, happening=2022-01-21], Event[id=2, title=Cat Show, happening=2022-02-22], Event[id=3, title=Bird Show, happening=2022-03-23]]
Technical details
The code above was run from within IntelliJ 2021.1 beta, running on Java 16 early access (release candidate), on macOS Mojave, connecting to MySQL 8 as a managed database service hosted by DigitalOcean.com, connecting with this JDBC driver for MySQL via Maven POM dependency:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version>
</dependency>
RowSet
Another way to share data from your database is through the RowSet interfaces and implementations. See tutorial by Oracle.
I do not see these mentioned much, though I have never understood why. The seem to be quite the “sleeper” feature of JDBC.
The RowSet interface extends the ResultSet interface. A RowSet is designed to be used outside the JDBC code, unlike ResultSet. A RowSet can reconnect to the database to retrieve fresh data or to write changes.
A JdbcRowSet maintains a connection to the database.
A CachedRowSet can be used without maintaining a connection to the database.
