ex02.md

Exercise 2

Milestone 7: Create database prj301 with student table

• Use the following script to create the student table

CREATE DATABASE prj301;
GO
USE prj301;
GO
CREATE TABLE student (
    id INT PRIMARY KEY,
    firstname VARCHAR(100),
    lastname VARCHAR(100),
    age INT
);
GO
INSERT INTO student (id, firstname, lastname, age) VALUES
(1, 'John', 'Doe', 20),
(2, 'John', 'Smith', 22),
(3, 'Alex', 'Johnson', 21),
(4, 'Emily', 'Williams', 19),
(5, 'Michael', 'Brown', 23);
GO

Milestone 8: Pull source code of prj301-demo2

• Pull the prj301-demo2.

cd e:/Projects/prj302-demo-teacher
git pull

• Copy e:/Projects/prj302-demo-teacher/prj301-demo2 to your development source
• Add prj301-demo2 to NetBean, change database connection in DBUtils

Milestone 9: Develop StudentListServlet to show table of all Students with id, firstname, lastname, age

• YOUR STEPS

  • Write SQL to select all students with id, firstname, lastname, age
  • Write code to execute the query (get connection from DBUtils)
  • Present the result set in table with html tags

• SUBMIT SCREENSHOTS:
  9.1 List of student in Browser
  9.2 Source code of StudentListServlet.java

Milestone 10: Develop function Search student by first name and last name

• YOUR STEPS

  • In StudentListServlet, add a form at the top of page to submit keyword for searching

        <form action=''>
            <input name=keyword type=text>
            <input type=submit value=Search>
        </form>
    

  • Catching the value of the parameter keyword
  • Add WHERE condition to SQL with firstname like ? or lastname like ?

• SUBMIT SCREENSHOTS:
  10.1 Searching student with name John. Screenshot the result in Browser
  10.2 Source code of StudentListServlet.java

Milestone 11: (Optional) Develop function Sort student by first name and last name

• YOUR STEPS

  • Create a link for header of Lastname with a href ?sortCol=lastname
  • Catching the parameter sortCol to add to ORDER BY of the SQL

• SUBMIT SCREENSHOTS:
  11.2 Source code of StudentListServlet.java