connecting to database help
First let me say sorry as i have already started this post in another thread, but know one seems to be replying to it any more. the other thread can be found out: http://forum.java.sun.com/thread.jspa?messageID=9413573
package coreservlets;
publicclass Catalog{
privatestatic CatalogItem[] items =
{new CatalogItem
("hall002",
"<I>Core Web Programming, 2nd Edition</I> " +
"by Marty Hall and Larry Brown",
"One stop shopping for the Web programmer. " +
"Topics include \n" +
"<UL><LI>Thorough coverage of Java 2; " +
"including Threads, Networking, Swing, \n" +
"Java 2D, RMI, JDBC, and Collections\n" +
"<LI>A fast introduction to HTML 4.01, " +
"including frames, style sheets, and layers.\n" +
"<LI>A fast introduction to HTTP 1.1, " +
"servlets, and JavaServer Pages.\n" +
"<LI>A quick overview of JavaScript 1.2\n" +
"</UL>",
49.99),
new CatalogItem
("lewis001",
"<I>The Chronicles of Narnia</I> by C.S. Lewis",
"The classic children's adventure pitting " +
"Aslan the Great Lion and his followers\n" +
"against the White Witch and the forces " +
"of evil. Dragons, magicians, quests, \n" +
"and talking animals wound around a deep " +
"spiritual allegory. Series includes\n" +
"<I>The Magician's Nephew</I>,\n" +
"<I>The Lion, the Witch and the Wardrobe</I>,\n" +
"<I>The Horse and His Boy</I>,\n" +
"<I>Prince Caspian</I>,\n" +
"<I>The Voyage of the Dawn Treader</I>,\n" +
"<I>The Silver Chair</I>, and \n" +
"<I>The Last Battle</I>.",
19.95),
new CatalogItem
("alexander001",
"<I>The Prydain Series</I> by Lloyd Alexander",
"Humble pig-keeper Taran joins mighty " +
"Lord Gwydion in his battle against\n" +
"Arawn the Lord of Annuvin. Joined by " +
"his loyal friends the beautiful princess\n" +
"Eilonwy, wannabe bard Fflewddur Fflam," +
"and furry half-man Gurgi, Taran discovers " +
"courage, nobility, and other values along\n" +
"the way. Series includes\n" +
"<I>The Book of Three</I>,\n" +
"<I>The Black Cauldron</I>,\n" +
"<I>The Castle of Llyr</I>,\n" +
"<I>Taran Wanderer</I>, and\n" +
"<I>The High King</I>.",
19.95),
new CatalogItem
("rowling001",
"<I>The Harry Potter Series</I> by J.K. Rowling",
"The first five of the popular stories " +
"about wizard-in-training Harry Potter\n" +
"topped both the adult and children's " +
"best-seller lists. Series includes\n" +
"<I>Harry Potter and the Sorcerer's Stone</I>,\n" +
"<I>Harry Potter and the Chamber of Secrets</I>,\n" +
"<I>Harry Potter and the " +
"Prisoner of Azkaban</I>,\n" +
"<I>Harry Potter and the Goblet of Fire</I>, and\n" +
"<I>Harry Potter and the "+
"Order of the Phoenix</I>.\n",
59.95)
};
publicstatic CatalogItem getItem(String itemID){
CatalogItem item;
if (itemID ==null){
return(null);
}
for(int i=0; i<items.length; i++){
item = items[i];
if (itemID.equals(item.getItemID())){
return(item);
}
}
return(null);
}
}
Using the above servlet and this one:
package coreservlets
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
publicclass browserextends HttpServlet{
publicvoid doGet(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException{
response.setContentType("text/html");
PrintWriter out = response.getWriter();
Connection conn =null;
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
}catch(Exception e){
System.out.println(e);
}
// connecting to database
try{
conn = DriverManager.getConnection
("jdbc:mysql://MY WEB ADDRESS FOR THE SQL TABLES GOES HERE");
// System.out.println("Connection to database successful.");
}
catch(SQLException se){
System.out.println(se);
}
try{
// Get the category from the input form
String categoryString = request.getParameter("category");
// check if no category
if (categoryString =="") categoryString ="Action & Adventure";
String selectSQL ="select title, director, rating, year_released, price, stock_count, image_name "+
"from video_recordings "+
"where category = '" + categoryString +"'";
Statement stmt = conn.createStatement();
ResultSet rs1 = stmt.executeQuery(selectSQL);
// output html headers
String title ="Films in the " + categoryString +" genre" ;
out.println(ServletUtilities.headWithTitle(title) +
"><BODY BGCOLOR=\"#a00e0e\">\n" +
"<center><img src=\"http://localhost:8080/examples/pic.jpg\" width=350 height=200/></center>\n" +
"<H1 ALIGN=\"CENTER\">" + title +"</H1>\n");
out.println("<TABLE BORDER=1 ALIGN=\"CENTER\">\n" +
"<TR BGCOLOR=\"#FFAD00\">\n" +
" <TH>Title\n" +
" <TH>Director\n" +
" <TH>Rating\n" +
" <TH>Year Released\n" +
" <TH>Price\n" +
" <TH>Number in stock\n" +
" <TH>image name"
);
// Retrieve the results
while(rs1.next()){
out.println("<TR>" +
"<TD>" + rs1.getString("title") +"</TD>" +
"<TD>" + rs1.getString("director") +"</TD>" +
"<TD>" + rs1.getString("rating") +"</TD>" +
"<TD>" + rs1.getDouble("year_released") +"</TD>" +
"<TD>" + rs1.getString("price") +"</TD>" +
"<TD>" + rs1.getString("stock_count") +"</TD>" +
"<TD>" + rs1.getString("image_name") +"</TD>\n");
"<TD> <IMG SRC=\"../images/music/" + image_name +"\">"
}
// close the html
out.println("</TABLE></BODY></HTML>");
// Close the stament and database connection
stmt.close();
conn.close();
}catch(SQLException se){
System.out.println(se);
}
}
}
How do i merge these two together so that a catalogItem is created by connecting to the database and uses the select statement? If any one can help me here i would be really grateful as i am having real trouble with this.
