We help IT Professionals succeed at work.

Cached ResultSet with MySQL in Java

mandriluy
mandriluy asked
on
My Lovers,

Im trying to close a MySQL connection and getting problems with the ResultSet , in Oracle i remember i used something like OracleCacheStatement to store the ResultSet and then close the database connection properly after executing a query.

Anyone knows if this is possible with MySQL and can give me an example of how to implement ?

Thanks!
Comment
Watch Question

Awarded 2011
Awarded 2011

Commented:
>Im trying to close a MySQL connection and getting problems with the ResultSet

what does this mean ?

If you do it in the regular way - iterating through resust set and then closing it - would you have problems?

Author

Commented:
yan, i have an abstract Database class that i use to inherit in my Models, so for example if i want to insert some user on the database i just use :

String sql = "some random SQL Selection";

ResultSet rs = executeQuery(sql);

That function will :

a)Connect to the database
b)Execute the SQL Query
c) Return the Resultset to the Service class so i can then iterate with a while through every record.

Problem im having is that.. if i close the connection before the return of ResultSet in executeQuery the ResultSet pointer will clear and then it wont iterate through it.

I don't know if i have properly explained myself.. my english is not that good :)  but here is the code.

http://pastebin.com/157wmwqA
Awarded 2011
Awarded 2011

Commented:
I acnnot open your link, but that probably does not matter

Perahps you can use CachedRowSet interface

http://docs.oracle.com/javase/1.5.0/docs/api/javax/sql/rowset/CachedRowSet.html

as it is part of JDBC driiver should in general implement it no matter if it
is Oracle or MySQL
Awarded 2011
Awarded 2011

Commented:
Awarded 2011
Awarded 2011
Commented:
Still I'd think that it is not common to use for a good reason - with connected ResultSet you can have ResultSet of basically unlimited size
With this stuff it may be memory consuming as I understand

Other than that, if JDBC driver conforms to specification it should provide implementation of
CachedRowSetImpl :

Author

Commented:
So what do you recommend ?

To return the resultset and close the connection from outside the class after iteration ? Because making the connection and never closing it seems like a bad idea to me... and yeah, the cache has a limited size and will load entirely on memory... that sounds bad also.

Author

Commented:
package persistence;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author Diego , Martin
 * 
 */

public abstract class Database {

	private Connection connection;
	
	private String mySqlDriver = "com.mysql.jdbc.Driver";
	private String dbHost = "jdbc:mysql://localhost/";
	private String dbName = "dental";
	private String dbUser = "root";
	private String dbPassword = "";
	
     private void connect() throws SQLException, ClassNotFoundException {
    	 
    	 if (connection != null)
    		 if (connection.isClosed() == false)
    			 return;
    	try {
    		Class.forName(mySqlDriver);
    		connection = DriverManager.getConnection(dbHost+dbName,dbUser,dbPassword);
    	}catch(ClassNotFoundException e) {
        	throw new ClassNotFoundException("No se encuentra el driver de conexion "+mySqlDriver);
        }
    }
    
    protected ResultSet executeQuery(String sql) throws ClassNotFoundException, SQLException 	{
        ResultSet rs = null;
        connect();
        Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        rs = statement.executeQuery(sql);
        return rs;
    }

    protected void closeConnection() throws SQLException {
       connection.close();
    }
    
}

Open in new window

Awarded 2011
Awarded 2011

Commented:
I could not see your code - but that is a potentialy a big problem - if possible - I'd iterarte right away extract what is necessary for the service and pass
it as array list or as a map
If servoce needs such huge data - there is no simple way you can resolve it
Awarded 2011
Awarded 2011

Commented:
Yes, I think to return to service ResultSet if possible should be acvoided

Author

Commented:
I don't know why u can't see the code.. can you see any image if i attach here ? Because is hard to explain why is that i need to return the ResultSet
Awarded 2011
Awarded 2011

Commented:
no, now you posted the code - I see it,
but this is a genral issue - no need really to see the code, you explained everything already
I understand - I think it is better  to retrieve what is necessary and pass over data which is discnnected form database.
If you are not passing too much data, then CachedresultSet would be an option

Author

Commented:
I figured out another solution to mantain my MVC arquitecture.

Now my Database class is on the helpers package and is not abstract any more, is public and all the method's are static, so im using it this way:

public void deleteDentist(Dentist dentist) throws ClassNotFoundException, SQLException, UnSelectedItemException {
		if(dentist == null)
			throw new UnSelectedItemException("No has seleccionado ningun item de la lista");
		
		String sql = "DELETE FROM dentists WHERE id="+dentist.getId();
		Database.connect();
		Database.executeUpdate(sql);
		Database.close();
		listOfDentists.remove(dentist);
	}

Open in new window


And for the select query i just connect, return RS, iterate and then close from the service class.
Awarded 2011
Awarded 2011

Commented:
If you can do it - that is great!

Author

Commented:
Yeah! genius i mark your comment as helpfull did you get the points ? or i have to mark some post as the solution ? ;-)
Awarded 2011
Awarded 2011

Commented:
No, this question is not closed, so no points were dspatched.