SQLExecption

[duhit Choudhary]

Hi all,
I am trying to use resultset method but i am getting an error like this:

java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:796)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6857)

and here is my code

package com.iremote.itunes;

import java.sql.*;
import java.util.*;
import org.apache.commons.configuration.ConfigurationException;
import org.apache.commons.configuration.HierarchicalConfiguration;
import org.apache.commons.configuration.XMLConfiguration;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

public class ITunesManager {
	
	ITunes obj = new ITunes();
	ArrayList<ITunes> trackObj = new ArrayList<ITunes>();
	ArrayList<ITunes> playlistObj = new ArrayList<ITunes>(); // gives the trackObj for play list
	@SuppressWarnings("unchecked")
	public ArrayList<ITunes> getTracksFromXml(String path)
	{	
		try
        {
		XMLConfiguration config = new XMLConfiguration("C:/Users/pbsl/Documents/itunes.xml");
		System.out.println("---------1---------");
		
		List<HierarchicalConfiguration> fields = config.configurationsAt("Tracks.Track");
		System.out.println(fields);
		 for(HierarchicalConfiguration sub : fields)
		   {
			 System.out.println("-------------2-------------");
			 
			 String title = sub.getString("Name");
			 System.out.println(title);
			 
			 String file_format = sub.getString("Kind");
			 System.out.println(file_format);
			 
			 String location = sub.getString("Location");
			 System.out.println(location);
			 
			 String artist = sub.getString("Artist");
			 System.out.println(artist);
			 
			 String genre = sub.getString("Genre");
			 System.out.println(genre);
			 
			 String album = sub.getString("Album");
			 System.out.println(album);
			 
			 int track_id = sub.getInt("Track-ID");
			 
			 ITunes trackListobject = new ITunes();
			 trackListobject.setAlbum(album);
			 trackListobject.setArtist(artist);
			 trackListobject.setFile_format(file_format);
			 trackListobject.setGenre(genre);
			 trackListobject.setLocation(location);
			 trackListobject.setTitle(title);
			 trackListobject.setTrack_id(track_id);
			 
         	 trackObj.add(trackListobject);
		   }
	    }
		 catch (ConfigurationException e) 
		    {
			   e.printStackTrace();
		    }
		 
        
		return trackObj;
	}
	
	
    @SuppressWarnings("unchecked")
	public ArrayList<ITunes> getPlaylistFromXml(String path)
	{
		try
		{
		XMLConfiguration config = new XMLConfiguration("C:/Users/pbsl/Documents/itunes.xml");
		System.out.println("---------1---------");
		
		List<HierarchicalConfiguration> playlistFields = config.configurationsAt("Playlists.Playlist");
		System.out.println(playlistFields);
		  for(HierarchicalConfiguration sub : playlistFields)
		  {
			 System.out.println("-------------2-------------");
			 
			 String Playlist_Persistent_id = sub.getString("Playlist-Persistent-ID");
			 System.out.println(Playlist_Persistent_id);
			 
			 String name = sub.getString("Name");
			 System.out.println(name);
			 
			 ITunes playListobject = new ITunes();
			 playListobject.setPlaylist_Persistent_id(Playlist_Persistent_id);
			 playListobject.setName(name);
			 
			 playlistObj.add(playListobject);
		  }
		}
		catch (ConfigurationException e) 
	    {
		   e.printStackTrace();
	    }
		
		playlistObj.addAll(trackObj);
		System.out.println(playlistObj);
	    
     	return playlistObj;
	}
	
	@SuppressWarnings("static-access")
	public void insertIntoDb(List<ITunes> playlistObj, String ConnectionString)
	{
		try
		{

			 String connectionURL = "jdbc:mysql://localhost:3306/mymovie?useUnicode=yes&characterEncoding=UTF-8";  //machine name, port number, database name
			 
			 Connection connection = null;
			 
			 Statement statement = null;
			 
			 ResultSet rs = null;
			 ResultSet rs2 = null, rs3 = null, rs4 = null;
			 int affectedRows = 0;
			 
			 Class.forName("com.mysql.jdbc.Driver").newInstance();
			 Logger log = Logger.getLogger(ITunesManager.class);
			 log.info("DUHIT#######################started");
			 connection = DriverManager.getConnection(connectionURL, "root", "duhit");
			 log.info("DUHIT#######################started");
			 
			 statement = connection.createStatement();
			 //System.out.println("INSERT INTO track(Location, Artist) VALUES ('"+locations+"','"+artists+"')")
			 String QueryString = null;
			 String QueryString2 = null,QueryString3 = null;
			 System.out.println("Size of list is "+playlistObj.size());

				for (ITunes data : playlistObj)
			    {
					if(data!=null)
					{
						System.out.println("-------------3-------------");
						String track_id="";
						QueryString = "INSERT INTO track(title, file_format, location, itunes_track_id) VALUES ('"+ (data.getTitle() != null?data.getTitle().replace("'",""): "") +"', '"+ (data.getFile_format() != null?data.getFile_format().replace("'",""):"") +"', '"+ (data.getLocation() != null?data.getLocation().replace("'",""):"") +"','" + (data.getTrack_id()) +"')";
						System.out.println("QueryString "+QueryString);
						affectedRows = statement.executeUpdate(QueryString,statement.RETURN_GENERATED_KEYS);
						rs = statement.getGeneratedKeys();
						if(rs.next())
						{
							track_id = rs.getInt(1)+ "";
							System.out.println(track_id);
						}
						System.out.println(data.getArtist() + data.getAlbum() + data.getGenre() + data.getName());
						
						if(statement.executeQuery("select * from track_subcategory where `name` = '"+ (data.getArtist() != null?data.getArtist().replace("'", ""): "")+"' " + " and parent_cat = (SELECT cat_id FROM track_category WHERE `name` = 'Artist')").next())
						{
							log.info("--------Already in Database--------");
						}
						else
						{
							QueryString3 = "INSERT INTO track_subcategory(name, parent_cat) VALUES ('"+ (data.getArtist() != null?data.getArtist().replace("'", ""): "")+"' , (SELECT cat_id FROM track_category WHERE name = 'Artist'))";
							System.out.println("QueryString3 "+QueryString3);
							affectedRows = statement.executeUpdate(QueryString3);
							System.out.println(affectedRows);
						}
						
						if(statement.executeQuery("select * from track_subcategory where `name` = '"+ (data.getGenre() != null?data.getGenre().replace("'", ""): "")+"' " + " and parent_cat = (SELECT cat_id FROM track_category WHERE `name` = 'Genre')").next())
						{
							log.info("--------Already in Database--------");
						}
						else
						{
							QueryString3 = "INSERT INTO track_subcategory(name, parent_cat) VALUES ('"+ (data.getGenre() != null?data.getGenre().replace("'", ""): "")+"' , (SELECT cat_id FROM track_category WHERE name = 'Genre'))";
							System.out.println("QueryString3 "+QueryString3);
							affectedRows = statement.executeUpdate(QueryString3);
							System.out.println(affectedRows);
						}
						
						if(statement.executeQuery("select * from track_subcategory where `name` = '"+ (data.getAlbum() != null?data.getAlbum().replace("'", ""): "")+"' " + " and parent_cat = (SELECT cat_id FROM track_category WHERE `name` = 'Album')").next())
						{
							log.info("--------Already in Database--------");
						}
						else
						{
							QueryString3 = "INSERT INTO track_subcategory(name, parent_cat) VALUES ('"+ (data.getAlbum() != null?data.getAlbum().replace("'", ""): "")+"' , (SELECT cat_id FROM track_category WHERE name = 'Album'))";
							System.out.println("QueryString3 "+QueryString3);
							affectedRows = statement.executeUpdate(QueryString3);
							System.out.println(affectedRows);
						}
						
						rs4 = statement.executeQuery("SELECT subcat_id FROM track_subcategory");
						
						while(rs4.next())
						{
							QueryString = "INSERT INTO user_subcategorizes_track(user_id, subcat_id, track_id) VALUE (1, "+ rs.getString(1)+ ", (SELECT track_id FROM track WHERE itunes_track_id =  " +data.getTrack_id() + "))";
							System.out.println("QueryString "+QueryString);
							affectedRows = statement.executeUpdate(QueryString);
							System.out.println(affectedRows);
						}
						
						if(obj.getArtist() == null || obj.getAlbum() == null || obj.getGenre() == null)
						{
							if(obj.getArtist() == null)
							{
								QueryString = "INSERT INTO use_categorizes_track(user_id, cat_id, track_id) VALUES (1, (SELECT cat_id FROM track_category where name = 'Genre'),"+Integer.parseInt(track_id)+"), (1, (SELECT cat_id FROM track_category where name = 'Album'),"+Integer.parseInt(track_id)+")";
								System.out.println("QueryString "+QueryString);
								affectedRows = statement.executeUpdate(QueryString);
							}
							
							else if(obj.getGenre() == null)
							{
								QueryString2 = "INSERT INTO use_categorizes_track(user_id, cat_id, track_id) VALUES (1, (SELECT cat_id FROM track_category where name = 'Artist'),"+Integer.parseInt(track_id)+"), (1, (SELECT cat_id FROM track_category where name = 'Album'),"+Integer.parseInt(track_id)+")";
								System.out.println("QueryString "+QueryString2);
								affectedRows = statement.executeUpdate(QueryString2);
								
							}
							
							else if(obj.getAlbum() == null)
							{
       							QueryString3 = "INSERT INTO use_categorizes_track(user_id, cat_id, track_id) VALUES (1, (SELECT cat_id FROM track_category where name = 'Genre'),"+Integer.parseInt(track_id)+"), (1, (SELECT cat_id FROM track_category where name = 'Artist'),"+Integer.parseInt(track_id)+")";
								System.out.println("QueryString "+QueryString3);
								affectedRows = statement.executeUpdate(QueryString3);
							}
						}
							    
						else 
							{
							    QueryString3 = "INSERT INTO use_categorizes_track(user_id, cat_id, track_id) VALUES (1, (SELECT cat_id FROM track_category where name = 'Genre'),"+Integer.parseInt(track_id)+"), (1, (SELECT cat_id FROM track_category where name = 'Artist'),"+Integer.parseInt(track_id)+"), (1, (SELECT cat_id FROM track_category where name = 'Album'),"+Integer.parseInt(track_id)+")";
							    System.out.println("QueryString "+QueryString3);
							    affectedRows = statement.executeUpdate(QueryString3);
							}
							
     						if (affectedRows != 0)
                            {
							System.out.println("affectedRows " + affectedRows );
     						}
						
						System.out.println("Data in database");
					}
				}
				
				QueryString = "SELECT * from track";
				rs = statement.executeQuery(QueryString);
				
				QueryString3 = "SELECT * from track_subcategory";
				rs3 = statement.executeQuery(QueryString3);
				
				QueryString2 = "SELECT * from use_categorizes_track";
				rs2 = statement.executeQuery(QueryString2);
				
				rs.close();
				rs3.close();
				rs2.close();
				rs4.close();
				statement.close();
				connection.close();
		}
		 catch (Exception ex)
	      {
		    ex.printStackTrace();
		    System.out.println("Unable to connect to database.");
          }
	}

	public static void main(String[] args)
	{
		ITunesManager iTunesObject = new ITunesManager();
		List<ITunes> list = iTunesObject.getTracksFromXml("C:\\Users\\pbsl\\Documents\\itunes.xml");
		iTunesObject.insertIntoDb( list, "localhost, root, duhit");
//		List<ITunesNewest> playlist = iTunesObject.getPlaylistFromXml("C:/Users/pbsl/Documents/itunes.xml");
//		iTunesObject.insertIntoDb( playlist, "localhost, root, duhit");
		
		PropertyConfigurator.configure("C:\\Users\\pbsl\\Desktop\\Log4J.properties");
		Logger log = Logger.getLogger(ITunesManager.class);
		log.info("started");
	}
}

[nir sharma]

can you paste your complete stacktrace.

[subhod lagade]

I have faced this problem many times.

The only solution i found is using different statement reference for insert queries as execute update close the resultset.

Please try this out if not please correct me.

[Wendy Gibbons]

I am think that the result set is connected to the staement. You then tell the statement to scrap it's old sql job and start a new one. So you have scrapped the old result set as well.

As duhit Choudhary use a new statements where you are still processing an old one.