/* * AlbumDatabase.java * * Created on October 30, 2002, 9:12 PM */ /** * * @author Nathan Balon * IS 375 * Assignment 5 * @version */ import java.sql.*; public class AlbumDatabase { public static void main(String args[]){ try{ String username = "guest"; String password = "guest"; String url = "jdbc:odbc:technical_library"; String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; // SQL to create the table language String createLang = "CREATE TABLE Language (Album_Number int, " + "Song_Number int, lang char(20), " + "PRIMARY KEY (Album_Number, Song_Number), " + "FOREIGN KEY (Album_Number) REFERENCES Album, "+ "FOREIGN KEY (Song_number) REFERENCES Song)"; // SQL to insert records into language table String [] insertLang = { "INSERT INTO Language VALUES(1, 1, 'English')", "INSERT INTO Language VALUES(2, 13, 'Spanish')", "INSERT INTO Language VALUES(3, 23, 'English')", "INSERT INTO Language VALUES(4, 34, 'English')", "INSERT INTO Language VALUES(5, 44, 'English')", "INSERT INTO Language VALUES(6, 52, 'English')", }; // connect to database Class.forName(driver); Connection connection = DriverManager.getConnection(url); Statement statement = connection.createStatement(); // create the language table statement.executeUpdate(createLang); System.out.println(createLang); // insert records in the language table for (int i = 0; i < insertLang.length; i ++){ statement.executeUpdate(insertLang[i]); System.out.println(insertLang[i]); }//for // SQL query to select album name song name and the language String qryLang = "SELECT Album_Name, Song_Name, lang " + "FROM Language, Song, Album " + "WHERE Language.Album_Number = Album.Album_number " + "AND Language.Song_Number = Song.Song_Number "; // Excute select language query ResultSet rs = statement.executeQuery(qryLang); System.out.println(); System.out.println(qryLang); System.out.println(); System.out.println("Result of Language Query"); // Display the result of all songs that have a language while(rs.next()){ String aName = rs.getString(1); String sName = rs.getString(2); String lan = rs.getString(3); System.out.println("Album: " + aName + " Song: " + sName + " Language: " + lan); }//while }//try catch (ClassNotFoundException cnfe){ System.err.println(cnfe); } catch (SQLException sqle){ System.err.println(sqle); } }//main }