aspose file tools*
The moose likes JDBC and the fly likes inserting queries advance Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "inserting queries advance" Watch "inserting queries advance" New topic
Author

inserting queries advance

duhit Choudhary
Ranch Hand

Joined: Apr 01, 2012
Posts: 64
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
 
Don't get me started about those stupid light bulbs.
 
subject: inserting queries advance