Getting data from ResultSet

Managing the current line

We wrote the first program and it worked perfectly. We wrote a query, executed it, and as a result, the executeQuery() method returned an object to usresult setAn that contains all the results of the query. And now we will try to figure out how to get these results from it.

The query result can contain thousands of rows and hundreds of columns of various types, so this is not as trivial a task as you might think. For example, pictures can be stored in the database, then you can get a picture as a set of bytes or an InputStream to download it.

But we will start with the simplest - with the concept of “ current result line ”. Since the result usually has a lot of rows, the objectresult sethas a pointer to the current line inside. And sequentially switches the lines to read them using the next() method .

This approach is primarily done for optimization. The JDBC Driver may not load strings from the database until you sequentially get to read them. You too read FileInputStream sequentially from the beginning to the end. So this approach should be familiar and understandable to you.

However, if you really need it, then files can be read anywhere using the RandomAccessFile class .

The ResultSet class also allows something similar and allows you to move the current row along the result anywhere. To do this, it has the following methods:

The methods are quite simple, but two explanations need to be made. The results are, as it were, framed by empty lines on both sides. Therefore, initially the current line is before the first line of the result. And to get the first row, you need to call the next() method at least once .

If you called the next() method on the last row , then you moved to the line after the last one . You cannot read data from it, but no error will occur. Here the isAfterLast() method will declare true as the result.

Example:

Statement statement = connection.createStatement();
ResultSet results = statement.executeQuery("SELECT * FROM user");

System.out.println( results.getRow() );        	// 0
System.out.println( results.isBeforeFirst() );  // true
System.out.println( results.isFirst() );          	// false

results.next();

System.out.println( results.getRow() );        	// 1
System.out.println( results.isBeforeFirst() );  // false
System.out.println( results.isFirst() );          	// true

results.next();

System.out.println( results.getRow() );        	// 2
System.out.println( results.isBeforeFirst() );  // false
System.out.println( results.isFirst() );          	// false

Getting data from the current row

You have learned to masterfully manage the current line. Now let's figure out how to get data from it. For this, the objectresult setthere are special methods that can all be described by one template:

getType(numberColumns)

However, if the column has a name, then you can also get by the column name:

getType(nameColumns)

Example:

while (results.next()) {
        	Integer id = results.getInt(“id”);
        	String name = results.getString(“name”);
        	System.out.println(results.getRow() + ". " + id + "\t"+ name);
}

Below I will provide a table that will help you associate SQL data types and ResultSet methods:

The point, I think you get it.

Getting different data about ResultSet

We figured out how to read data from the current line: both by the column number and by its name. By the way, how can I find out the column name by its number? Or the number of columns in the result?

On the one hand, if you write a request, then you seem to have to know all this. On the other hand, we can write a program that displays the result of a query on the screen: the query is passed to us and we just want to display on the screen (in the console, web page) everything that the SQL server returned to us.

JDBC has a special object for this, the ResultSetMetaData interface . Getting an object of this type is quite simple:

Statement statement = connection.createStatement();
ResultSet results = statement.executeQuery("SELECT * FROM user");
ResultSetMetaData resultSetMetaData = results.getMetaData();

The ResultSetMetaData interface has some very interesting methods. Below are the most popular ones:

Let's use it to learn a little about our table:

ResultSetMetaData metaData = results.getMetaData();
int columnCount = metaData.getColumnCount();
for (int column = 1; column <= columnCount; column++)
{
        	String name = metaData.getColumnName(column);
        	String className = metaData.getColumnClassName(column);
        	String typeName = metaData.getColumnTypeName(column);
        	int type = metaData.getColumnType(column);

        	System.out.println(name + "\t" + className + "\t" + typeName + "\t" + type);
}

Important! Pay attention that columns are numbered from 1. Rows, by the way, too. How unusual is that, right?

And this is the result I got after running the program:

1

Task

Module 4. Working with databases, level 7, lesson 2

Locked

task0704

task0704

Open

1

Task

Module 4. Working with databases, level 7, lesson 2

Locked

task0705

task0705

Open

1

Task

Module 4. Working with databases, level 7, lesson 2

Locked

task0706

task0706

Open