inserting queries

[duhit Choudhary]

package newXmlReader2;

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

public class ITunesNewManager {
	
	ITunesNewest obj = new ITunesNewest();
	@SuppressWarnings("unchecked")
	public ArrayList<ITunesNewest> getTracksFrOmXml(String path)
	{
		ArrayList<ITunesNewest> trackObj = new ArrayList<ITunesNewest>();
		
		try
	    {
		XMLConfiguration config = new XMLConfiguration("C:\\Users\\pbsl\\Downloads\\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);
			
			 obj.setAlbum(album);
			 obj.setArtist(artist);
			 obj.setFile_format(file_format);
			 obj.setGenre(genre);
			 obj.setLocation(location);
			 obj.setTitle(title);
			 
			 trackObj.add(obj);
		   }
	    }
		 catch (ConfigurationException e) 
		    {
			   e.printStackTrace();
		    }
		 return trackObj;
	}
	
	public void insertIntoDb(List<ITunesNewest> trackObj, String ConnectionString)
	{
		try
		{
			 String connectionURL = "jdbc:mysql://172.16.1.152:3306/mymovie";  //machine name, port number, database name
			 
			 Connection connection = null;
			 
			 Statement statement = null;
			 
			 ResultSet rs = null;
			 ResultSet rs2 = null, rs3 = null;
			 int affectedRows = 0;
			 
			 Class.forName("com.mysql.jdbc.Driver").newInstance();
			 connection = DriverManager.getConnection(connectionURL, "phpdev", "phpdev");
			 
			 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 "+trackObj.size());

				for (ITunesNewest data : trackObj)
				{
					if(data!=null)
					{
						QueryString = "INSERT INTO track(title, file_format, location) VALUES"
+ " ('"+ data.getTitle().replace("'","") +"', '"+ data.getFile_format().replace("'","") +"', '"+ data.getLocation().replace("'","") +"')";
//						System.out.println("QueryString "+QueryString);
//						affectedRows = statement.executeUpdate(QueryString);
						System.out.println("QueryString "+QueryString);
						affectedRows = statement.executeUpdate(QueryString,statement.RETURN_GENERATED_KEYS);
						rs = statement.getGeneratedKeys();
						if(rs.next())
						{
							String track_id = rs.getInt(1)+ "";
						}
						
						QueryString3 = "INSERT INTO track_subcategory(name, parent_cat) VALUES ('"
+ data.getArtist() +"' , (SELECT cat_id FROM track_category WHERE name = 'Artist')), ('"+ data.getGenre() +"' , (SELECT cat_id FROM track_category WHERE name = 'Genre')), ('"
+ data.getAlbum() +"' , (SELECT cat_id FROM track_category WHERE name = 'Album'))";
						System.out.println("QueryString3 "+QueryString3);
						affectedRows = statement.executeUpdate(QueryString3);
						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'),track_id), (1, (SELECT cat_id FROM track_category where name = 'Album'),track_id)";
								
							}
							
							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'),track_id), (1, (SELECT cat_id FROM track_category where name = 'Album'),track_id)";
								System.out.println("QueryString "+QueryString2);
								affectedRows = statement.executeUpdate(QueryString2);
								
							}
							
							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'),track_id), (1, (SELECT cat_id FROM track_category where name = 'Artist'),track_id)";
								System.out.println("QueryString "+QueryString3);
								affectedRows = statement.executeUpdate(QueryString3);
							}
							    
							if(obj.getArtist()!= null && obj.getAlbum()!= null && obj.getGenre()!= null) 
							{
							    QueryString3 = "INSERT INTO use_categorizes_track(user_id, cat_id, track_id)"
+ " VALUES (1, (SELECT cat_id FROM track_category where name = 'Genre'),track_id), (1, (SELECT cat_id FROM track_category where name = 'Artist')"
+ ",track_id), (1, (SELECT cat_id FROM track_category where name = 'Album'),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();
				statement.close();
				connection.close();
		}
		 catch (Exception ex)
	      {
		    ex.printStackTrace();
		    System.out.println("Unable to connect to batabase.");
          }
	}

	public static void main(String[] args)
	{
		ITunesNewManager iTunesObj = new ITunesNewManager();
		List<ITunesNewest> list = iTunesObj.getTracksFrOmXml("C:\\Users\\pbsl\\Downloads\\itunes.xml");
		iTunesObj.insertIntoDb( list, "172.16.1.152, phpdev, phpdev");
	}

}

when i tried to execute this code, it is giving me only one value throughout. but i want all the values given in the xml file... please tell me where am i wrong in this code and how to get different desired values.
thanks a lot...

[Paul Clapham]

You only have one object, and you keep adding it to the List. Create a new object each time when you get data from the XML instead of reusing the single object.

[duhit Choudhary]

thank you so much but can you tell me in which line?

[Paul Clapham]

You don't have an extremely large number of lines where you add an object to a list...

[duhit Choudhary]

i got that line by the way.... but thanks for your help. it was really very helpfull

[Paul Clapham]

That's a very common "gotcha", by the way, a lot of beginners get caught doing that.

[duhit Choudhary]

i'll try not to repeat these type of silly mistakes again...