CODEWITHSUNDEEP
javamysql
Punith R Kashi
Punith R Kashi

Reputation: 159

Difference between resultset in java and cursor in mysql

When using java+mysql I came up with two methods to traverse through the datasets

  1. Using cursor and stored procedure in sql and then execute it in java to find whether the user exist or not.

  2. Using "resultSet" in java to find the user exist or not.

Can anyone tell me which is efficient to use and why?

Upvotes: 4

Views: 3674

Answers (2)

Lew Bloch
Lew Bloch

Reputation: 3433

"Cursor" is a SQL concept. ResultSet is a programming type in Java. "Cursor" uses database semantics, and is accessed via SQL. ResultSet uses type/object semantics and is accessed via Java. "Cursor" directly controls how much data is fetched by a SQL query. ResultSet generally does not, but does control how much data is exposed to the Java program. There may or may not be more data fetched by the SQL query than shown in a ResultSet.

SQL structures like "cursor" should only be used in the data context. It is part of the database. It will not stop you from needing a ResultSet when you use JDBC to query the data. ResultSet is how the Java code interacts with the query results.

"Cursor" is used for data operations. It is something that only data manipulation cares about. ResultSet is more evanescent, needed by Java code in the moment of retrieving query data. It is something only your client code cares about. The database has no awareness of ResultSet. Under nearly all circumstances, your Java code has no awareness of cursors.

Upvotes: 4

Nicolas Filotto
Nicolas Filotto

Reputation: 44965

You should avoid the best you can stored procedures as they are much more complex to maintain than a Java application and they are specific to the target database. You should only use stored procedures for complex use cases that cannot be managed with queries executed thanks to a Statement/PreparedStatement.

In other words 99.9 % of the time, your best choice will be #2.

Upvotes: 1

Related Questions