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();
ArrayList<ITunesNewest> trackObj = new ArrayList<ITunesNewest>();
ArrayList<ITunesNewest> playlistObj = new ArrayList<ITunesNewest>(); // gives the trackObj for play list
@SuppressWarnings("unchecked")
public ArrayList<ITunesNewest> 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);
ITunesNewest trackListobject = new ITunesNewest();
trackListobject.setAlbum(album);
trackListobject.setArtist(artist);
trackListobject.setFile_format(file_format);
trackListobject.setGenre(genre);
trackListobject.setLocation(location);
trackListobject.setTitle(title);
trackObj.add(trackListobject);
}
}
catch (ConfigurationException e)
{
e.printStackTrace();
}
return trackObj;
}
@SuppressWarnings("unchecked")
public ArrayList<ITunesNewest> 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);
ITunesNewest playListobject = new ITunesNewest();
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<ITunesNewest> playlistObj, 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 "+playlistObj.size());
for (ITunesNewest data : playlistObj)
{
if(data!=null)
{
System.out.println("-------------3-------------");
String track_id="";
QueryString = "INSERT INTO track(title, file_format, location) VALUES ('"+ (data.getTitle() != null?data.getTitle().replace("'",""): "") +"', '"+ (data.getFile_format() != null?data.getFile_format().replace("'",""):"") +"', '"+ (data.getLocation() != null?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())
{
track_id = rs.getInt(1)+ "";
System.out.println(track_id);
}
System.out.println(data.getArtist() + data.getAlbum() + data.getGenre() + data.getName());
QueryString3 = "INSERT INTO track_subcategory(name, parent_cat) VALUES ('"+ (data.getArtist() != null?data.getArtist().replace("'", ""): "")+"' , (SELECT cat_id FROM track_category WHERE name = 'Artist')), ('"+ (data.getGenre() != null?data.getGenre().replace("'", ""): "")+"' , (SELECT cat_id FROM track_category WHERE name = 'Genre')), ('"+ (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);
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);
}
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);
}
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);
}
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'),"+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();
statement.close();
connection.close();
}
catch (Exception ex)
{
ex.printStackTrace();
System.out.println("Unable to connect to database.");
}
}
public static void main(String[] args)
{
ITunesNewManager iTunesObject = new ITunesNewManager();
List<ITunesNewest> list = iTunesObject.getTracksFromXml("C:\\Users\\pbsl\\Documents\\itunes.xml");
iTunesObject.insertIntoDb( list, "172.16.1.152, phpdev, phpdev");
List<ITunesNewest> playlist = iTunesObject.getPlaylistFromXml("C:/Users/pbsl/Documents/itunes.xml");
iTunesObject.insertIntoDb( playlist, "172.16.1.152, phpdev, phpdev");
}
}
i extended my above program in which i tried to parse the xml having two different tags. i have successfully done that but i am not able to make the logic about how can i program further in order to insert another subcategory which is having a category Playlist. please tell me how to proceed further.
thanks