Go4Expert

Go4Expert (http://www.go4expert.com/)
-   JSP (http://www.go4expert.com/forums/jsp/)
-   -   Help connecting to mysql tables (http://www.go4expert.com/forums/help-connecting-mysql-tables-t2371/)

ajrobson 29Dec2006 02:02

Help connecting to mysql tables
 
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;
Code:

/** 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?
Code:

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. Again any help would be helpful this is very stessing :mad: lol


All times are GMT +5.5. The time now is 02:44.