Sample Java MVC Web Application - Part III

In this part, we add JDBC and MySQL to our sample project. Hence, we divide the src package to four parts: entity, jdbc, servlet and test.

Take a look at the project structure -

Execution Results -

Create data in MySQL -

create database testdb;

use testdb;

create table person (
id int not null auto_increment primary key,
firstname varchar(20),
lastname varchar(20)
);

insert into person (firstname, lastname) values ("James","Bond");
insert into person (firstname, lastname) values ("James","White");

DBConnection.java -

package com.mqin.example.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnection {

    public static Connection getConnection() {
        Connection conn = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost/testdb?" + "user=root&password=root");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return conn;
    }
}

PersonOperation.java - 

package com.mqin.example.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mqin.example.entity.PersonModel;

public class PersonOperation {

    private Connection conn = null;

    public PersonOperation() {
        conn = DBConnection.getConnection();
    }

    public void insertPerson(PersonModel p) {
        String sql = "insert into person (firstname, lastname) valus (?,?)";

        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, p.getFirstName());
            ps.setString(2, p.getLastName());
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public PersonModel getPerson(int id) {
        PersonModel p = null;
        String sql = "select * from person where id = ?";

        try {
            PreparedStatement ps = conn.prepareCall(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                p = new PersonModel();
                p.setId(rs.getInt("id"));
                p.setFirstName(rs.getString("firstname"));
                p.setLastName(rs.getString("lastname"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return p;
    }

    public ArrayList<PersonModel> getPerson(String firstname) {
        ArrayList<PersonModel> parray = new ArrayList<PersonModel>();
        String sql = "select * from person where firstname = ?";

        try {
            PreparedStatement ps = conn.prepareCall(sql);
            ps.setString(1, firstname);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                PersonModel p = new PersonModel();
                p.setId(rs.getInt("id"));
                p.setFirstName(rs.getString("firstname"));
                p.setLastName(rs.getString("lastname"));
                parray.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return parray;
    }

}

PersonTest.java -

package com.mqin.example.test;

import java.util.ArrayList;
import java.util.Iterator;

import com.mqin.example.entity.PersonModel;
import com.mqin.example.jdbc.PersonOperation;

public class PersonTest {

    private PersonOperation pop = null;

    public PersonTest() {
        pop = new PersonOperation();
    }

    public boolean testGetById(int id) {
        PersonModel p = pop.getPerson(id);

        if (p == null) {
            return false;
        }

        log(p.getFirstName() + " " + p.getLastName());
        return true;
    }

    public boolean testGetByFirstName(String firstname) {
        ArrayList<PersonModel> parray = new ArrayList<PersonModel>();
        PersonModel p = null;

        parray = pop.getPerson(firstname);

        if (parray.isEmpty()) {
            return false;
        }

        Iterator<PersonModel> iterator = parray.iterator();

        while (iterator.hasNext()) {
            p = iterator.next();
            log(p.getFirstName() + " " + p.getLastName());
        }

        return true;
    }

    private void log(String string) {
        System.out.println(string);
    }

    public static void main(String[] args) {
        PersonTest pt = new PersonTest();

        int id = 2;
        String firstname = "James";

        if (!pt.testGetById(id)) {
            pt.log("Cannot find person with id = " + id);
        }

        if (!pt.testGetByFirstName(firstname)) {
            pt.log("Cannot find person with firstname = " + firstname);
        }
    }

}

SearchByID.java -

package com.mqin.example.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mqin.example.entity.PersonModel;
import com.mqin.example.jdbc.PersonOperation;

public class SearchByID extends HttpServlet {

    private static final long serialVersionUID = 1L;

    public SearchByID() {
        super();
    }

    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException,
            IOException {

        if (request.getParameter("id") == null || request.getParameter("id") == "") {
            getServletContext().getRequestDispatcher("/index.jsp").forward(request, response);
            return;
        }

        int id = (new Integer(request.getParameter("id"))).intValue();
        PersonOperation pop = new PersonOperation();
        PersonModel person = pop.getPerson(id);

        request.setAttribute("person", person);

        getServletContext().getRequestDispatcher("/searchidoutput.jsp").forward(request, response);
    }

}

SearchByFirstName.java -

package com.mqin.example.servlet;

import java.io.IOException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mqin.example.entity.PersonModel;
import com.mqin.example.jdbc.PersonOperation;

public class SearchByFirstName extends HttpServlet {

    private static final long serialVersionUID = 1L;

    public SearchByFirstName() {
        super();
    }

    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException,
            IOException {

        if (request.getParameter("firstname") == null || request.getParameter("firstname") == "") {
            getServletContext().getRequestDispatcher("/index.jsp").forward(request, response);
            return;
        }

        String firstName = request.getParameter("firstname");
        PersonOperation pop = new PersonOperation();
        ArrayList<PersonModel> parray = new ArrayList<PersonModel>();
        parray = pop.getPerson(firstName);

        request.setAttribute("parray", parray);

        getServletContext().getRequestDispatcher("/searchnameoutput.jsp").forward(request, response);
    }

}

index.jsp -

<?xml version="1.0" encoding="ISO-8859-1" ?>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Insert title here</title>
</head>
<body>
    <form action="" method="post">
        <table border="0">
            <tr>
                <td>ID:</td>
                <td><input type="text" name="id" /></td>
            </tr>
            <tr>
                <td>First Name:</td>
                <td><input type="text" name="firstname" /></td>
            </tr>
            <tr>
                <td>Last Name:</td>
                <td><input type="text" name="lastname" /></td>
            </tr>
            <tr>
                <td><input type="submit" value="SearchByID"
                    onclick="form.action='searchbyid';" /></td>
                <td><input type="submit" value="SearchByFirstName"
                    onclick="form.action='searchbyfirstname';" /></td>
            </tr>
        </table>
    </form>
</body>
</html>

searchidoutput.jsp -

<?xml version="1.0" encoding="ISO-8859-1" ?>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<jsp:useBean id="person" scope="request" class="com.mqin.example.entity.PersonModel" />
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Insert title here</title>
</head>
<body>
    <table>
        <tr>
            <td>First Name:</td>
            <td><jsp:getProperty name="person" property="firstName" /></td>
        </tr>
        <tr>
            <td>Last Name:</td>
            <td><jsp:getProperty name="person" property="lastName" /></td>
        </tr>
    </table>
</body>
</html>

searchnameoutput.jsp -

<?xml version="1.0" encoding="ISO-8859-1" ?>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ page import="java.util.ArrayList"%>
<%@ page import="java.util.Iterator"%>
<%@ page import="com.mqin.example.entity.PersonModel"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Insert title here</title>
</head>
<body>
    <table>
        <%
            ArrayList<PersonModel> parray = (ArrayList<PersonModel>) request.getAttribute("parray");
            PersonModel p = null;
            Iterator<PersonModel> iterator = parray.iterator();
            while (iterator.hasNext()) {
                p = iterator.next();
        %>
        <tr>
            <td>Person ID:</td>
            <td><%=p.getId()%></td>
        </tr>
        <tr>
            <td>First Name:</td>
            <td><%=p.getFirstName()%></td>
        </tr>
        <tr>
            <td>Last Name:</td>
            <td><%=p.getLastName()%><%}%></td>
        </tr>
    </table>
</body>
</html>