Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Java > Java and JDK > Servlets
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Servlets section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 28th, 2006, 04:24 PM
Registered User
 
Join Date: Dec 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help connecting to mysql database catalog

I got this ervlet off of coreservlets.com but want to change it so instead of each catalogItem being typed in it gets the infomation from mysql database:

package coreservlets;

/** A catalog that lists the items available in inventory.
 * <P>
 * Taken from Core Servlets and JavaServer Pages 2nd Edition
 * from Prentice Hall and Sun Microsystems Press,
 * http://www.coreservlets.com/.
 * &copy; 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",
         "<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)
        };

  public static 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);
  }
}

I have made this code below which gets data from mysql tables, at the moment i use it as a simple search but is there a way to merge the two together so the above code gets it catalogItems by using the select statement like in the code below?

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 url 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><img src=\"http://localhost:8080/examples/LOGO.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()){
            // getInt or getString or getFloat etc to get the appropriate column data
            // wrap output in html for web
            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
// (must remember to always do this)
        stmt.close();
        conn.close();
    } catch(SQLException se) {
        System.out.println(se);
    }
    }


  }
Any help at all would be really helpful as i am really finding this hard, if you go to http://volume1.coreservlets.com/archive/Chapter9.html you can see the other files for how the catalog works.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Connecting to MySQL Database from VB2005 Tank Pro VB Databases 0 January 23rd, 2006 04:46 PM
Need Help Connecting to mySQL Database! jehchoi1 PHP Databases 6 April 21st, 2005 09:28 AM
database connecting with java &mysql rakeshkumar MySQL 1 February 4th, 2005 11:59 AM
Connecting to mySQL WIx ASP.NET 1.0 and 1.1 Basics 3 October 17th, 2004 04:13 PM
Connecting Access2000 to MySQL Database Rood67 Access 4 May 20th, 2004 12:49 PM



All times are GMT -4. The time now is 02:00 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.