dcsimg
How do i connect to mysql database with this servlet?? i need help somebody help me lol
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   david_halewood
Posted On:   Thursday, December 28, 2006 02:13 PM

package coreservlets; /** 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 ("hall00   More>>
			package coreservlets;
			


/** 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",
"
"by Marty Hall and Larry Brown",
"One stop shopping for the Web programmer. " +
"Topics include
" +
"Thorough coverage of Java 2; " +
"including Threads, Networking, Swing,
" +
"Java 2D, RMI, JDBC, and Collections
" +
"A fast introduction to HTML 4.01, " +
"including frames, style sheets, and layers.
" +
"A fast introduction to HTTP 1.1, " +
"servlets, and JavaServer Pages.
" +
"A quick overview of JavaScript 1.2
" +
"",
49.99),
new CatalogItem
("lewis001",
" The Chronicles of Narnia by C.S. Lewis",
"The classic children's adventure pitting " +
"Aslan the Great Lion and his followers
" +
"against the White Witch and the forces " +
"of evil. Dragons, magicians, quests,
" +
"and talking animals wound around a deep " +
"spiritual allegory. Series includes
" +
" The Magician's Nephew ,
" +
" The Lion, the Witch and the Wardrobe ,
" +
" The Horse and His Boy ,
" +
" Prince Caspian ,
" +
" The Voyage of the Dawn Treader ,
" +
" The Silver Chair , and
" +
" The Last Battle .",
19.95),
new CatalogItem
("alexander001",
" The Prydain Series by Lloyd Alexander",
"Humble pig-keeper Taran joins mighty " +
"Lord Gwydion in his battle against
" +
"Arawn the Lord of Annuvin. Joined by " +
"his loyal friends the beautiful princess
" +
"Eilonwy, wannabe bard Fflewddur Fflam," +
"and furry half-man Gurgi, Taran discovers " +
"courage, nobility, and other values along
" +
"the way. Series includes
" +
" The Book of Three ,
" +
" The Black Cauldron ,
" +
" The Castle of Llyr ,
" +
" Taran Wanderer , and
" +
" The High King .",
19.95),
new CatalogItem
("rowling001",
" The Harry Potter Series by J.K. Rowling",
"The first five of the popular stories " +
"about wizard-in-training Harry Potter
" +
"topped both the adult and children's " +
"best-seller lists. Series includes
" +
" Harry Potter and the Sorcerer's Stone ,
" +
" Harry Potter and the Chamber of Secrets ,
" +
" Harry Potter and the " +
"Prisoner of Azkaban
,
" +
" Harry Potter and the Goblet of Fire , and
" +
" Harry Potter and the "+
"Order of the Phoenix
.
",
59.95)
};

public static CatalogItem getItem(String itemID) {
CatalogItem item;
if (itemID == null) {
return(null);
}
for(int i=0; i
item = items[i];
if (itemID.equals(item.getItemID())) {
return(item);
}
}
return(null);
}
}




I got this code from www.coreservlets.com, how would i edit it so it would connect to mysql tables? i have the servlet below which connects to mysql tables. How would i merge them together so that a catalogItem comes from the database and not from me just typing them in each time??

			
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) +
"
" +
" image here
" +
"

" + title + "


");

out.println("" +
"" +
" Title
" +
" Director
" +
" Rating
" +
" Year Released
" +
" Price
" +
" Number in stock
" +
" 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") +"
");
//"image here">"
}
// close the html
out.println(" ");

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


}


Some of the second servlet may look strange but its because it outputs a html table and images it kept bringing errors when i tried to submit this post, the 2nd servlet works perfect.    <<Less

Re: How do i connect to mysql database with this servlet?? i need help somebody help me lol

Posted By:   Joost_Schouten  
Posted On:   Saturday, December 30, 2006 12:40 AM

Have your servlet execute a sql query resulting in a ResultSet containing the rows of your database table you queries. Loop though them and print them to your page.


I strongly urge you to keep your presentation and logic seperate though. So populate your CatalogItem bean with the values from your database in the servlet, better even in a backing bean, better EVEN with hibernate.org or a package alike. The resulting array of CatalogItem beans should be passed (through the request or session object) to a JSP page which will loop though them and print them to html.


There are plenty of tutorials online. Sorry I can't give you your immediate solution ;-)


Good luck,

Joost

About | Sitemap | Contact