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...