• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

inserting queries advance

 
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
reply
    Bookmark Topic Watch Topic
  • New Topic