I got this servlet off of coreservlets.com i want to know how i can edit it so that it connects to my database to get catalogItem instead of using static html.

/** A catalog that lists the items available in inventory.
*
*  Taken from Core Servlets and JavaServer Pages 2nd Edition
*  from Prentice Hall and Sun Microsystems Press,
*  http://www.coreservlets.com/.
*  © 2003 Marty Hall; may be freely used or adapted.
*/


public class Catalog {
// This would come from a database in real life.
// We use a static table for ease of testing and deployment.
// See JDBC chapters for info on using databases in
// servlets and JSP pages.
private static CatalogItem[] items =
{
new CatalogItem
("hall002",
"<Core Web Programming, 2nd Edition " +
"by Marty Hall and Larry Brown",
"One stop shopping for the Web programmer. " +
"Topics include \n" +
"Thorough coverage of Java 2; " +
"including Threads, Networking, Swing, \n" +
"Java 2D, RMI, JDBC, and Collections\n" +
"A fast introduction to HTML 4.01, " +
"including frames, style sheets, and layers.\n" +
"A fast introduction to HTTP 1.1, " +
"servlets, and JavaServer Pages.\n" +
"A quick overview of JavaScript 1.2\n" +
"",
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)
};


public static CatalogItem getItem(String itemID) {
CatalogItem item;
if (itemID == null) {
return(null);
}
for(int i=0; i<items.length; i++) {
item = items;
if (itemID.equals(item.getItemID())) {
return(item);
}
}
return(null);
}
}</pre>

I have made this servlet below which connect to my database how is it possible to merge the two so a catalogItem is got from mysql tables?

<pre>
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;


// Connects to a database to retrieve music data


public class browser extends HttpServlet {
public void doGet(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();



// Database connection code starts here


Connection conn = null;


// loading jdbc driver for mysql (help in mysql.jar file in classpath)
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch(Exception e) {
System.out.println(e);
}


// connecting to database
try{
// connection string for demos database, username demos, password demo-pass
conn = DriverManager.getConnection
("jdbc:mysql:/mysql tables link goes here!!");
// System.out.println("Connection to database successful.");
}
catch(SQLException se) {
System.out.println(se);
}


// Create select statement and execute it
try{
// Get the category from the input form
String categoryString = request.getParameter("category");
// check if no category
if (categoryString == "") categoryString = "Action & Adventure";


// Build up the SQL statement from our data requirements
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> image here</center>\n" +
"<H1 ALIGN=\"CENTER\">" + title + "</H1>\n");


out.println("" +
"" +
"  Title\n" +
"  Director\n" +
"  Rating\n" +
"  Year Released\n" +
"  Price\n" +
"  Number in stock\n" +
"  image name"
);


// Retrieve the results
while(rs1.next()){
// getInt or getString or getFloat etc to get the appropriate column data
// wrap output in html for web
out.println("" +
"" + rs1.getString("title") + "" +
"" + rs1.getString("director") + "" +
"" + rs1.getString("rating") + "" +
"" + rs1.getDouble("year_released") + "" +
"" + rs1.getString("price") + "" +
"" + rs1.getString("stock_count") + "" +


"" + rs1.getString("image_name")  +"\n");
//"image here">"
}
// close the html
out.println("</BODY></HTML>");


// Close the stament and database connection
//      (must remember to always do this)
stmt.close();
conn.close();
} catch(SQLException se) {
System.out.println(se);
}
}



}
</pre>

Thank you for any help at all i'm really stuck on this so any help please lol :cheesy:

Edited 3 Years Ago by happygeek: fixed formatting

This article has been dead for over six months. Start a new discussion instead.