Scribd Logo
Upload

Welcome to Scribd!

  • 58 views

    S517 Lab Database Operationlab5

    Uploaded by

    Nitin Ainani
    1) The document describes creating a Java web application to connect to a MySQL database and search for records. It provides code to connect to the database, display a search form, and output matching results. 2) doGet() displays a form to input an age and submit the search. doPost() gets the age from the request, executes a query to find matching ages, and outputs the results in an HTML table. 3) Improvements suggested are displaying a message if no results are found and properly closing all database connections.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd

    S517 Lab Database Operationlab5

    Uploaded by

    Nitin Ainani
    58 views7 pages
    1) The document describes creating a Java web application to connect to a MySQL database and search for records. It provides code to connect to the database, display a search form, and output matching results. 2) doGet() displays a form to input an age and submit the search. doPost() gets the age from the request, executes a query to find matching ages, and outputs the results in an HTML table. 3) Improvements suggested are displaying a message if no results are found and properly closing all database connections.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    1) The document describes creating a Java web application to connect to a MySQL database and search for records. It provides code to connect to the database, display a search form, and output matching results. 2) doGet() displays a form to input an age and submit the search. doPost() gets the age from the request, executes a query to find matching ages, and outputs the results in an HTML table. 3) Improvements suggested are displaying a message if no results are found and properly closing all database connections.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    58 views7 pages

    S517 Lab Database Operationlab5

    Uploaded by

    Nitin Ainani
    1) The document describes creating a Java web application to connect to a MySQL database and search for records. It provides code to connect to the database, display a search form, and output matching results. 2) doGet() displays a form to input an age and submit the search. doPost() gets the age from the request, executes a query to find matching ages, and outputs the results in an HTML table. 3) Improvements suggested are displaying a message if no results are found and properly closing all database connections.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    of 7

    S517 Lab Database Operation

    Task 1: Browse existing DB In this task, you will create a Java project (Dynamic Web Project) to connect MySql database. This project will provide an easy web interface to search in a database table and return the relevant results. First, create a Dynamic Web Project as pervious labs, and then create a java Servlet class named like DBsearch. Second, you need to import a complied java library for MySql database connection. You can download this Jar file at: mysql-connector-java5.0.7-bin.jar and save this file in a folder. Then, right click the project name in the Project Explorer, and choose Properties. As following diagram shows, click Java Build Path, select Libraries tab, and click Add External JARs button. In the browse window, select the downloaded Jar file and click OK to continue.

    Meanwhile, you will have to cope and paste this Jar file into the {project folder}/WebContent/WEB-INF/lib folder and this will be used by Java Servlet. After finish this, you can click the project and press F5 to refresh the project content.

    Third, you need to write some code to connect to a test database. In order to start, you need to import necessary classes as following:
    import import import import import java.sql.Connection; java.sql.DriverManager; java.sql.SQLException; java.sql.Statement; java.sql.ResultSet;

    Then, please past the following code to replace the class code:
    public class DBconnect extends HttpServlet { private static final long serialVersionUID = 1L; Connection connection; Statement statement = null; /** * @see HttpServlet#HttpServlet() */ public DBconnect() { super(); // TODO Auto-generated constructor stub } public void connect () throws Exception { Class.forName("org.gjt.mm.mysql.Driver"); String url = "jdbc:mysql://rdc04.uits.iu.edu:3264/S517DB"; String username = "S517"; String password = "S517"; connection = DriverManager.getConnection (url, username, password); statement = connection.createStatement(); } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub response.setContentType("text/html"); java.io.PrintWriter out = response.getWriter(); out.println("<html><head>"); //Start to print HTML page out.println("<title>DB connection</title></head><body>"); out.println("<h2>Please click this button and get the DB results</h2>"); //Create a HTML form with post method out.println("<form method=\"post\" action =\"" + request.getContextPath() + "/DBconnect\" >"); out.println("<table border=\"0\"><tr><td valign=\"top\">"); out.println("</td></tr><tr><td valign=\"top\">"); out.println("<input type=\"submit\" value=\"Show results\"></td></tr>"); out.println("</table></form>"); out.println("</body></html>"); } protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //Result page response.setContentType("text/html"); java.io.PrintWriter out = response.getWriter(); out.println("<html><head>"); out.println("<title>DB Result</title></head><body>"); out.println("<h2>DB results:</h2>"); out.println("ID\tFirstname\tLastname\tAge\tGender<br>");

    //Output try { connect(); ResultSet rs = statement.executeQuery("select * from user"); //Select all the records from user table while (rs.next()) { //Read each record int ID = rs.getInt("ID"); String firstname = rs.getString("Firstname"); String lastname = rs.getString("Lastname"); int age = rs.getInt("Age"); String gender = rs.getString("gender"); out.println(ID + "\t" + firstname + "\t"+ lastname + "\t" + age + "\t" + gender + "\t<br>"); } rs.close(); statement.close(); connection.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } out.println("</body></html>"); } }

    Please focusing on the global variables


    Connection connection; Statement statement = null;

    Which can be used for this class for DB connection and DB statement. There is another method you will use later as following:
    public void connect () throws Exception { Class.forName("org.gjt.mm.mysql.Driver"); String url = "jdbc:mysql://rdc04.uits.iu.edu:3264/S517DB"; String username = "S517"; String password = "S517"; connection = DriverManager.getConnection (url, username, password); statement = connection.createStatement(); }

    Which tells the DB connection information. The url variable declares the remote DB location as jdbc:mysql://rdc04.uits.iu.edu. 3264 is this string is the port number. S517DB is the database name. Meanwhile, username and password string can be found at the corresponding variables. Read doGet and doPost program carefully, and write down the functionality of these functions: doGet: _________
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); java.io.PrintWriter out = response.getWriter(); out.println("<html><head>");

    out.println("<title>DB connection</title></head><body>"); out.println("<h2>Please click this button and get the DB results</h2>"); out.println("<form method=\"post\" action =\"" + request.getContextPath() + "/firstdb\" >"); out.println("<table border=\"0\"><tr><td valign=\"top\">"); out.println("</td></tr><tr><td valign=\"top\">"); out.println("<input type=\"submit\" value=\"Show results\"></td></tr>"); out.println("</table></form>"); out.println("</body></html>");

    _______________________________________________________________________ _________ doPost:


    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub response.setContentType("text/html"); java.io.PrintWriter out = response.getWriter(); out.println("<html><head>"); out.println("<title>DB Result</title></head><body>"); out.println("<h2>DB results:</h2>"); out.println("ID\tFirstname\tLastname\tAge\tGender<br>"); try {

    connect(); ResultSet rs = statement.executeQuery("select * from user"); //Select all the records from user table while (rs.next()) { //Read each record int ID = rs.getInt("ID"); String firstname = rs.getString("Firstname"); String lastname = rs.getString("Lastname"); int age = rs.getInt("Age"); String gender = rs.getString("gender"); out.println(ID + "\t" + firstname + "\t"+ lastname + "\t" + age + "\t" + gender + "\t<br>"); } rs.close(); statement.close(); connection.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } out.println("</body></html>");

    }________________________________________________________________

    _______________________________________________________________________ _______________________________________________________________________ _______________________________________________________________________ _______________________________________________________________________ ________________________________________ Tips: in doPost method, ResultSet rs = statement.executeQuery("select * from user"); will open the following table and save all the records into the variable rs (rs saved all the records of user table) DBname: S517DB Table Firstname Lastname George Ryan Kenneth Armstrong Amy Archer Don Anderson Jenny Crawford Virginia Allen Billy Rice James Rostetter Paul Ropp Mary Drexler Jenny Cox name: user Age 23 52 18 25 26 19 62 26 73 32 45

    ID 1 2 3 4 5 6 7 8 9 10 11

    Gender M M F M F F M M M F F

    Task 2: search user age from DB In this task, you will create a Java Servlet class, and this class will provide a very basic interface. Allow user to input an age, and the system will return all the records match this input age. i.e. ResultSet rs = statement.executeQuery("select * from user where age = " + searchage); Where searchage is the variable read from user input interface. After implement this, think about how to improve this interface? For instance, when user cannot find any matched records, system will print a message like Sorry, no result found! Pay attention! You need to close the database connection, database statement, and database recordset.

    Paste your code here: doGet:


    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub response.setContentType("text/html"); java.io.PrintWriter out = response.getWriter(); out.println("<html><head>"); out.println("<title>DB connection</title></head><body>"); out.println("<h2>Please click this button and get the DB results</h2>"); out.println("<form method=\"post\" action =\"" + request.getContextPath() + "/testdb\" >"); out.println("<table border=\"0\"><tr><td valign=\"top\">"); out.println("Age: </td> <td valign=\"top\">"); out.println("<input type=\"text\" name=\"Age\" size=\"3\" value=\"0\">"); out.println("</td></tr><tr><td valign=\"top\">"); out.println("<input type=\"submit\" value=\"Show results\"></td></tr>"); out.println("</table></form>"); out.println("</body></html>");

    doPost:
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { int searchage=0; response.setContentType("text/html"); java.io.PrintWriter out = response.getWriter(); out.println("<html><head>"); out.println("<title>DB Result</title></head><body>"); out.println("<h2>DB results:</h2>");

    searchage = Integer.parseInt(request.getParameter("Age")); out.println("ID\tFirstname\tLastname\tAge\tGender<br>"); try {

    connect(); ResultSet rs = statement.executeQuery("select * from user where age="+searchage); //Select all the records from user table while (rs.next()) { //Read each record int ID = rs.getInt("ID"); String firstname = rs.getString("Firstname"); String lastname = rs.getString("Lastname"); int age = rs.getInt("Age"); String gender = rs.getString("gender"); out.println(ID + "\t" + firstname + "\t"+ lastname + "\t" + age + "\t" + gender + "\t<br>"); } rs.close(); statement.close(); connection.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } out.println("</body></html>");

    // TODO Auto-generated method stub }

    You might also like