Help connecting to mysql tables

Discussion in 'JSP' started by ajrobson, Dec 28, 2006.

  1. ajrobson

    ajrobson New Member

    Joined:
    Dec 28, 2006
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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
     
    Last edited by a moderator: Dec 29, 2006

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice