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>
