/* * DatabaseQuery.java * * Created on November 3, 2002, 11:53 PM */ /** * * @author Nathan Balon * SN# 797737 * IS 375 * Program 6 * Database Query * @version */ import java.sql.*; import javax.swing.*; public class DatabaseQuery { /** * @param args the command line arguments */ public static void main (String args[]) { try{ int choice; String ch; String username = "guest"; String password = "guest"; String url = "jdbc:odbc:technical_library"; String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; // connect to database Class.forName(driver); Connection connection = DriverManager.getConnection(url); Statement statement = connection.createStatement(); // show menu ch = JOptionPane.showInputDialog( "Enter Selection\n" + "1 to find artist numbers 20 albums\n" + "2 to find albums that have the letter B in their title\n" + "3 to find albums recorded by Bon Jovi \n" + "4 to find songs longer than 275 \n" + "5 to find the label that recorded Allen Town \n" + "6 to find all records from a specific table \n" + "0 to Exit"); choice = Integer.parseInt(ch); // switch statement to select query to run while (choice != 0){ switch (choice){ // Albums from artist 20 case 1: System.out.println("Artist 20's Albums"); String albumArtist20 = "SELECT artist_number, album_name " + "FROM album " + "WHERE artist_number=20;"; statement.executeQuery(albumArtist20); showResultSet(statement); break; // Albums that have B as the first letter of a word case 2: System.out.println("Albums with B for the first letter of a word"); String bAlbums = "SELECT album_name FROM album "+ "WHERE album_name Like 'B%' " + "Or album_name Like '% B%';"; statement.executeQuery(bAlbums); showResultSet(statement); break; // Bon Jovi albums case 3: System.out.println("Bon Jovi Albums"); String bonjoviAlbums = "SELECT artist_name, album_name " + "FROM album, artist " + "WHERE album.artist_number=artist.artist_number " + "And artist_name='Bon Jovi';"; statement.executeQuery(bonjoviAlbums); showResultSet(statement); break; // Songs whos length is greater than 275 case 4: System.out.println("Songs Greater than 275"); String songLength = "SELECT song_name, length " + "FROM song WHERE Length >275 " + "ORDER BY length;"; statement.executeQuery(songLength); showResultSet(statement); break; // label for albums called allen town case 5: System.out.println("Label for the Song Allen Town"); String allenTown = "SELECT DISTINCT artist.artist_name, " + "song.song_name, record_company.record_co_name " + "FROM artist, song, album, record_company " + "WHERE record_company.record_co_number = album.record_co_number " + "AND album.album_number = song.album_number " + "AND album.artist_number = artist.artist_number " + "AND song_name='AllenTown';"; statement.executeQuery(allenTown); showResultSet(statement); break; // select a table to query case 6: String tab = JOptionPane.showInputDialog( "Enter the tab table you want to Query"); String table = "Select * From " + tab; System.out.println(table); statement.executeQuery(table); showResultSet(statement); default: System.out.println("Make another selection"); break; }//switch // menu ch = JOptionPane.showInputDialog( "Enter Selection\n" + "1 to find artist numbers 20 albums\n" + "2 to find albums that have the letter B in their title\n" + "3 to find albums recorded by Bon Jovi \n" + "4 to find songs longer than 275 \n" + "5 to find the label that recorded Allen Town \n" + "6 to find all records from a specific table \n" + "0 to Exit"); choice = Integer.parseInt(ch); }//while connection.close(); statement.close(); }//try catch (ClassNotFoundException cnfe){ System.err.println(cnfe); } catch (SQLException sqle){ System.err.println(sqle); } }// main /** Prints the results of of a query */ public static void showResultSet(Statement stat) throws SQLException{ ResultSet result = stat.getResultSet(); ResultSetMetaData metaData = result.getMetaData(); int columnCount = metaData.getColumnCount(); for (int i =1; i <= columnCount; i++){ if (i > 1) System.out.print(", "); System.out.print(metaData.getColumnLabel(i)); }// for System.out.println(); while (result.next()){ for (int i = 1; i <= columnCount; i++){ if (i > 1) System.out.print(", "); System.out.print(result.getString(i)); }// for System.out.println(); }// while System.out.println(); result.close(); }// showResultSet }//Class DatabaseQuery