Problem Statement:
DBMS using connections(Client-application
server-Data sever, three tier) Oracle/MySQL (ODBC/JDBC), SQL Joints, prompt.
Required Softwares:
1. Apache Tomcat Server
2. Eclipse
PROGRAM:
Html page
asgb1.html
<body>
<head align=center>MUSIC LIBRARY</head>
<hr>
<form name="form1" action="q11.jsp" method="post">
1. Display all album names and corresponding artist names and artist ID.
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form2" action="q22.jsp" method="post">
2. Display details of all tracks in a particular album.
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form3" action="q33.jsp" method="post">
3. Show the longest and shortest track of the album.
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form4" action="q44.jsp" method="post">
4. Display the total number of tracks in the album by.
Artist= <input type="text" name="user">
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form4" action="q55.jsp" method="post">
5. List all Tracks and show when it was last played.If a track has never been played, still show those tracks.
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form4" action="q66.jsp" method="post">
6. List all Tracks and show when it was last played.If a track has never been played, DO NOT show those tracks.
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form4" action="q77.jsp" method="post">
7. Create index on Track_Name and find the Album Name for particular track.
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form4" action="q88.jsp" method="post">
8. Delete the index created in (7)
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form4" action="q99.jsp" method="post">
9. CREATE and DISPLAY a BRIEF VIEW of all the tracks.
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form4" action="q10.jsp" method="post">
10.DELETE the Track VIEW created in (9)
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
</body>
JSP Pages
q11.jsp
<%@ page import ="java.sql.*" %>
<%@ page import ="javax.sql.*" %>
<html>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/b1","root","root");
//Connection con = DriverManager.getConnection("jdbc:mysql://192.168.5.101:3306/TE3205db","TE3205","TE3205");
Statement st= con.createStatement();
ResultSet rs=st.executeQuery("select Artist_ID,Artist_Name,Album_Name from Artist natural join Album");
%>
<table border="1">
<tr>
<th>Artist ID</th>
<th>Artist Name</th>
<th>Album Name</th>
</tr>
<% while(rs.next()){ %>
<TR>
<TD> <%= rs.getInt(1) %></td>
<TD> <%= rs.getString(2) %></TD>
<TD> <%= rs.getString(3) %></TD>
</TR>
<% } %>
</table>
<%
rs.close();
st.close();
con.close();
%>
<body>
<form name="prev" action="asgb1.html" method="post">
<hr>
<INPUT TYPE=SUBMIT VALUE="PREVIOUS" />
</body>
</html>
1. Apache Tomcat Server
2. Eclipse
PROGRAM:
Html page
asgb1.html
<body>
<head align=center>MUSIC LIBRARY</head>
<hr>
<form name="form1" action="q11.jsp" method="post">
1. Display all album names and corresponding artist names and artist ID.
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form2" action="q22.jsp" method="post">
2. Display details of all tracks in a particular album.
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form3" action="q33.jsp" method="post">
3. Show the longest and shortest track of the album.
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form4" action="q44.jsp" method="post">
4. Display the total number of tracks in the album by.
Artist= <input type="text" name="user">
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form4" action="q55.jsp" method="post">
5. List all Tracks and show when it was last played.If a track has never been played, still show those tracks.
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form4" action="q66.jsp" method="post">
6. List all Tracks and show when it was last played.If a track has never been played, DO NOT show those tracks.
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form4" action="q77.jsp" method="post">
7. Create index on Track_Name and find the Album Name for particular track.
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form4" action="q88.jsp" method="post">
8. Delete the index created in (7)
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form4" action="q99.jsp" method="post">
9. CREATE and DISPLAY a BRIEF VIEW of all the tracks.
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
<form name="form4" action="q10.jsp" method="post">
10.DELETE the Track VIEW created in (9)
<INPUT TYPE=SUBMIT VALUE="submit" />
</form>
<hr>
</body>
JSP Pages
q11.jsp
<%@ page import ="java.sql.*" %>
<%@ page import ="javax.sql.*" %>
<html>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/b1","root","root");
//Connection con = DriverManager.getConnection("jdbc:mysql://192.168.5.101:3306/TE3205db","TE3205","TE3205");
Statement st= con.createStatement();
ResultSet rs=st.executeQuery("select Artist_ID,Artist_Name,Album_Name from Artist natural join Album");
%>
<table border="1">
<tr>
<th>Artist ID</th>
<th>Artist Name</th>
<th>Album Name</th>
</tr>
<% while(rs.next()){ %>
<TR>
<TD> <%= rs.getInt(1) %></td>
<TD> <%= rs.getString(2) %></TD>
<TD> <%= rs.getString(3) %></TD>
</TR>
<% } %>
</table>
<%
rs.close();
st.close();
con.close();
%>
<body>
<form name="prev" action="asgb1.html" method="post">
<hr>
<INPUT TYPE=SUBMIT VALUE="PREVIOUS" />
</body>
</html>
q22.jsp
<%@ page import ="java.sql.*" %>
<%@ page import ="javax.sql.*" %>
<html>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/b1","root","root");
//Connection con = DriverManager.getConnection("jdbc:mysql://192.168.5.101:3306/TE3205db","TE3205","TE3205");
Statement st= con.createStatement();
ResultSet rs=st.executeQuery("select Artist_ID,Artist_Name,Album_Name from Artist natural join Album");
%>
<table border="1">
<tr>
<th>Artist ID</th>
<th>Artist Name</th>
<th>Album Name</th>
</tr>
<% while(rs.next()){ %>
<TR>
<TD> <%= rs.getInt(1) %></td>
<TD> <%= rs.getString(2) %></TD>
<TD> <%= rs.getString(3) %></TD>
</TR>
<% } %>
</table>
<%
rs.close();
st.close();
con.close();
%>
<body>
<form name="prev" action="asgb1.html" method="post">
<hr>
<INPUT TYPE=SUBMIT VALUE="PREVIOUS" />
</body>
</html>
q33.jsp
<%@ page import ="java.sql.*" %>
<%@ page import ="javax.sql.*" %>
<html>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/b1","root","root");
Statement st= con.createStatement();
ResultSet rs=st.executeQuery("select Track_ID ,Track_Name,Duration from Track where Album_ID=1 AND Duration=(select MAX(Duration) FROM Track where Album_ID=1) OR Duration=(select MIN(Duration) FROM Track where Album_ID=1)");
%>
<table border="1">
<tr>
<th>Track ID</th>
<th>Track Name</th>
<th>Duration</th>
</tr>
<% while(rs.next()){ %>
<TR>
<TD> <%= rs.getInt(1) %></td>
<TD> <%= rs.getString(2) %></TD>
<TD> <%= rs.getString(3) %></TD>
</TR>
<% } %>
</table>
<%
rs.close();
st.close();
con.close();
%>
<body>
<form name="prev" action="asgb1.html" method="post">
<hr>
<INPUT TYPE=SUBMIT VALUE="PREVIOUS" />
</body>
</html>
q44.jsp
<%@ page import ="java.sql.*" %>
<%@ page import ="javax.sql.*" %>
<html>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/b1","root","root");
PreparedStatement pstmt = null;
Statement st= con.createStatement();
String artist=request.getParameter("user");
String sqlString ="Select Artist_Name,Album_Name,count(*) AS No_Of_Tracks from Artist inner join Album using(Artist_ID) inner join Track using(Artist_ID,Album_ID) where Artist_Name =?";
pstmt = con.prepareStatement(sqlString);
pstmt.setString(1,artist);
ResultSet rs ;
rs= pstmt.executeQuery();
%>
<table border="1">
<tr>
<th>Artist Name</th>
<th>Album Name</th>
<th>No. of tracks</th>
</tr>
<% while(rs.next()){ %>
<TR>
<TD> <%= rs.getString(1) %></TD>
<TD> <%= rs.getString(2) %></TD>
<TD> <%= rs.getInt(3) %></td>
</TR>
<% } %>
</table>
<%
rs.close();
st.close();
con.close();
%>
<body>
<form name="prev" action="asgb1.html" method="post">
<hr>
<INPUT TYPE=SUBMIT VALUE="PREVIOUS" />
</body>
</html>
q55.jsp
<%@ page import ="java.sql.*" %>
<%@ page import ="javax.sql.*" %>
<html>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/b1","root","root");
Statement st= con.createStatement();
ResultSet rs=st.executeQuery("select Track_Name , Last_Played from Track left join Played USING(Album_ID,Track_ID)ORDER BY Last_Played DESC");
%>
<table border="1">
<tr>
<th>Track Name</th>
<th>Last Played</th>
</tr>
<% while(rs.next()){ %>
<TR>
<TD> <%= rs.getString(1) %></TD>
<TD> <%= rs.getString(2) %></TD>
</TR>
<% } %>
</table>
<%
rs.close();
st.close();
con.close();
%>
<body>
<form name="prev" action="asgb1.html" method="post">
<hr>
<INPUT TYPE=SUBMIT VALUE="PREVIOUS" />
</body>
</html>
q66.jsp
<%@ page import ="java.sql.*" %>
<%@ page import ="javax.sql.*" %>
<html>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/b1","root","root");
Statement st= con.createStatement();
ResultSet rs=st.executeQuery("select Track_Name , Last_Played from Track right join Played USING(Album_ID,Track_ID)ORDER BY Last_Played DESC");
%>
<table border="1">
<tr>
<th>Track Name</th>
<th>Last Played</th>
</tr>
<% while(rs.next()){ %>
<TR>
<TD> <%= rs.getString(1) %></TD>
<TD> <%= rs.getString(2) %></TD>
</TR>
<% } %>
</table>
<%
rs.close();
st.close();
con.close();
%>
<body>
<form name="prev" action="asgb1.html" method="post">
<hr>
<INPUT TYPE=SUBMIT VALUE="PREVIOUS" />
</body>
</html>
q77.jsp
<%@ page import ="java.sql.*" %>
<%@ page import ="javax.sql.*" %>
<html>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/b1","root","root");
Statement st= con.createStatement();
st.executeUpdate("CREATE INDEX Name_Index on Track(Track_Name)");
ResultSet rs=st.executeQuery("select Album_Name,Duration from Album INNER JOIN Track USING(Album_ID)WHERE Track_Name='Hey Brother'");
%>
<table border="1">
<tr>
<th>Artist Name</th>
<th>Duration</th>
</tr>
<% while(rs.next()){ %>
<TR>
<TD> <%= rs.getString(1) %></TD>
<TD> <%= rs.getString(2) %></td>
</TR>
<% } %>
</table>
<%
rs.close();
st.close();
con.close();
%>
<body>
<form name="prev" action="asgb1.html" method="post">
<hr>
<INPUT TYPE=SUBMIT VALUE="PREVIOUS" />
</body>
</html>
q88.jsp
<%@ page import ="java.sql.*" %>
<%@ page import ="javax.sql.*" %>
<html>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/b1","root","root");
Statement st= con.createStatement();
st.executeUpdate("alter table Track drop index Name_Index");
out.println("<BR>");
out.println("<BR>");
out.println("INDEX on Name column DELETED");
st.close();
con.close();
%>
<body>
<form name="prev" action="asgb1.html" method="post">
<hr>
<INPUT TYPE=SUBMIT VALUE="PREVIOUS" />
</body>
</html>
q99.jsp
<%@ page import ="java.sql.*" %>
<%@ page import ="javax.sql.*" %>
<html>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/b1","root","root");
Statement st= con.createStatement();
st.executeUpdate("CREATE VIEW Track_View AS SELECT Track_Name,Album_ID,Artist_ID FROM Track ORDER BY Album_ID,Track_Name");
ResultSet rs=st.executeQuery("select * from Track_View");
%>
<table border="1">
<tr>
<th>Track Name</th>
<th>Album ID</th>
<th>Artist ID</th>
</tr>
<% while(rs.next()){ %>
<TR>
<TD> <%= rs.getString(1) %></TD>
<TD> <%= rs.getInt(2) %></TD>
<TD> <%= rs.getInt(3) %></td>
</TR>
<% } %>
</table>
<%
rs.close();
st.close();
con.close();
%>
<body>
<form name="prev" action="asgb1.html" method="post">
<hr>
<INPUT TYPE=SUBMIT VALUE="PREVIOUS" />
</body>
</html>
q10.jsp
<%@ page import ="java.sql.*" %>
<%@ page import ="javax.sql.*" %>
<html>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/b1","root","root");
Statement st= con.createStatement();
st.executeUpdate("drop view Track_View");
out.println("<BR>");
out.println("<BR>");
out.println("Track View has been DELETED");
st.close();
con.close();
%>
<body>
<form name="prev" action="asgb1.html" method="post">
<hr>
<INPUT TYPE=SUBMIT VALUE="PREVIOUS" />
</body>
</html>
No comments:
Post a Comment