Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

error on statement

 
Tom Joiner
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This code generates are error, having something to do with using the "Statement" twice in a row. If I utilized "Statement2" it has no error and works correctly. Do I need to close out the recordset or the statement to utilize the statement again in another query?

The error is posted at the bottom.




 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Reusing/Closing a Statement will cause any resultset associated with to be released, so you can not use ResultSet once you close the statement or you execute any other query on same statement object.

you are getting the error because you are using resultset (roleSet), after you have executed another query on statement.

Shailesh
 
Nidhi Singhal
Ranch Hand
Posts: 89
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The error is coming because of using executeQuery() on the same statement object two times.

try if this works..

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/database01", "SMART", "password");

String str1 = "SELECT * FROM account WHERE username = '" + userName + "'";

String str2 = "select * from role";

Statement statement1 = connection.createStatement();
ResultSet roleSet = statement1.executeQuery(str2);

Statement statement2 = connection.createStatement();
ResultSet rs = statement2.executeQuery(str1);
 
Tom Joiner
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So you are supposed to create a new statement for every SQL execution? That seems a bit out of control.. is there a way to release it and use it again?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34095
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes. You can reuse a prepared statement. However the SQL needs to be the same, so it wouldn't help you here.

String sql = "SELECT * FROM account WHERE username = ?";
PreparedStatement statement1 = connection.prepareStatement(sql);
statement1.setString(1, userName)l
ResultSet roleSet = statement1.executeQuery();
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic