CODEWITHSUNDEEP
javasqlsql-server-2005jdbc
lili
lili

Reputation: 1906

How does PreparedStatement.executeQuery work?

How PreparedStatement.executeQuery works? Does it fetch results from database and I loop through them? Or it fetches the first result, and on record.next continues with sql query to the next row?

ResultSet record = statement.executeQuery("select * from users");
    while (record.next()) {
    //do something
}

thank you

Upvotes: 3

Views: 3374

Answers (2)

Francisco Spaeth
Francisco Spaeth

Reputation: 23893

From the documentation:

Statement

public ResultSet executeQuery(String sql) throws SQLException

Executes the given SQL statement, which returns a single ResultSet object.

ResultSet

A table of data representing a database result set, which is usually generated by executing a statement that queries the database. 

public boolean next() throws SQLException

Moves the cursor down one row from its current position. 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.

If an input stream is open for the current row, a call to the method next will implicitly close it. A ResultSet object's warning chain is cleared when a new row is read.

As a representation this means the statement is executed once and when iterated is iterated over the result of that execution.

But how the result from database is handled depends really on the implementation. To make a contrast I will refer two databases MSSQL, MYSQL.

MSSQL

The documentation of MSSQL driver that comments exactly how the results are handled you can find here:

There are two types of result sets: client-side and server-side.

Client-side result sets are used when the results can fit in the client process memory. These results provide the fastest performance and are read by the Microsoft JDBC Driver for SQL Server in their entirety from the database. These result sets do not impose additional load on the database by incurring the overhead of creating server-side cursors. However, these types of result sets are not updatable.

Server-side result sets can be used when the results do not fit in the client process memory or when the result set is to be updatable. With this type of result set, the JDBC driver creates a server-side cursor and fetches rows of the result set transparently as the user scrolls through it.

MySQL

MySql implementation of JDBC interface that you can read here:

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and cannot allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

Upvotes: 9

Nick Rolando
Nick Rolando

Reputation: 26157

You are doing it correctly. It fetches the entire results from your query, and you loop through them.
The internal pointer, or cursor, initially starts before the first row, so calling record.next() will move it to the first row.

http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/ResultSet.html#next%28%29

Upvotes: 2

Related Questions