When using java+mysql I came up with two methods to traverse through the datasets
Using cursor and stored procedure in sql and then execute it in java to find whether the user exist or not.
Using "resultSet" in java to find the user exist or not.
Can anyone tell me which is efficient to use and why?
"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.
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.
WHEREclause and let the server figure it out, something done almost instantly if you have anINDEXin place.cursors. Again, inside a stored proc.