目的:使用servlet+mybatis+Mysql完成对学生信息表的增删改查操作,并结合jsp展示效果
项目结构
准备工作>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1.建表
附上建表代码:
附上SQL代码:
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` bigint(11) NOT NULL AUTO_INCREMENT,
`name` varchar(150) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`phone` varchar(11) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
2.所需jar包
引入mybatis
1.将mybatis-3.4.1.jar,mysql-connector-java-5.1.38.jar放到lib目录下,并buildPath。
2.鼠标右键点击项目名,新建一个名为resource的资源文件,注意是这种图标
资源文件放到这里才能被识别。在resource文件夹下新建一个db.properties文件
放连接数据库信息
db.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///student?characterEncoding=UTF8&useSSL=true&serverTimezone=UTC
username=root
password=root
再在该目录下建一个名为Mybatis-config.xml的xml配置文件
Mybatis-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="db.properties"></properties>
<!-- <typeAliases>
<typeAlias alias="" type="" />
</typeAliases> 这里是配置别名-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="${driverClassName}" />
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="" />
</mappers>
</configuration>
src下创建domain包存放实体类,domain下新建一个名为Student的java类
Student.java
/**
* 学生实体类
* @author 红烛
*
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student implements Serializable{
private static final long serialVersionUID = 1L;
private Long id;
private String name;
private Integer age;
private Date birthday;
private String address;
private String phone;
private String email;
}
src下创建mapper包,新建StudentMapper接口和StudentMapper.xml文件
StudentMapper接口中什么都不用写,我们把接口写在dao层
StudentMapper.java
/**
*
* @author 红烛
*
*/
public interface StudentMapper {
}
StudentMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.wolfcode.mapper.StudentMapper">
<sql id="Base_Column_List">
id,name,age,birthday,address,phone,email
</sql>
<!-- 添加查询 -->
<insert id="insert">
insert into student(id,name,age,birthday,address,phone,email)values(#{id},#{name},#{age},#{birthday},#{address},#{phone},#{email})
</insert>
<!-- 删除查询 -->
<delete id="delete">
delete from student where id=#{id}
</delete>
<!-- 修改查询 -->
<update id="update" parameterType="cn.wolfcode.domain.Student">
update student
<set>
<if test="name!=null">name=#{name},</if>
<if test="age!=null">age=#{age},</if>
<if test="birthday">birthday=#{birthday},</if>
<if test="address!=null">address=#{address},</if>
<if test="phone!=null">phone=#{phone},</if>
<if test="email!=null">email=#{email},</if>
</set>
where id=#{id}
</update>
<!-- 查询某个学生 -->
<select id="select" resultType="cn.wolfcode.domain.Student">
select
<include refid="Base_Column_List"></include>
from student where id=#{id}
</select>
<!-- 查询所有学生 -->
<select id="selectAll" resultType="cn.wolfcode.domain.Student">
select
<include refid="Base_Column_List"></include>
from student
</select>
</mapper>
StudentMapper.xml文件写完后注意,在主配置文件mybatis-config.xml中配置资源扫描
在src下创建dao.impl包,在dao包下创建IStudentDAO接口,在dao.impl下创建StudentDAOImpl实现类。
IStudentDAO.java
/**
*
* @author 红烛
*
*/
public interface IStudentDAO {
/**
* 添加学生信息
* @param stu
*/
void insert(Student stu);
/**
* 删除学生信息
* @param id
*/
void delete(Long id);
/**
* 修改学生信息
* @param stu
*/
void update(Student stu);
/**
* 查询某个学生信息
* @param id
* @return
*/
Student select(Long id);
/**
* 查询所有学生信息
* @return
*/
List<Student> selectAll();
}
StudentDAOImpl.java
/**
*
* @author 红烛
*
*/
public class StudentDAOImpl implements IStudentDAO {
@Override
public void insert(Student stu) {
SqlSession session=MybatisUtil.getSession();
session.insert("cn.wolfcode.mapper.StudentMapper.insert",stu);
session.commit();
session.close();
}
@Override
public void delete(Long id) {
SqlSession session=MybatisUtil.getSession();
session.delete("cn.wolfcode.mapper.StudentMapper.delete",id);
session.commit();
session.close();
}
@Override
public void update(Student stu) {
SqlSession session=MybatisUtil.getSession();
session.update("cn.wolfcode.mapper.StudentMapper.update",stu);
session.commit();
session.close();
}
@Override
public Student select(Long id) {
SqlSession session=MybatisUtil.getSession();
Student stu=session.selectOne("cn.wolfcode.mapper.StudentMapper.select",id);
session.close();
return stu;
}
@Override
public List<Student> selectAll() {
SqlSession session=MybatisUtil.getSession();
List<Student> list=session.selectList("cn.wolfcode.mapper.StudentMapper.selectAll");
session.close();
return list;
}
}
这里面的MybatisUtil是一个工具类,用于获取数据库连接,在src下新建一个名为util包,用于存放工具类。
MybatisUtil.java
/**
*
- @author 红烛
*/
public class MybatisUtil {
private static SqlSessionFactory factory;
private MybatisUtil(){};
static{
try {
factory=new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("Mybatis-config.xml"));
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSession(){
SqlSession session=factory.openSession();
return session;
}
}
src下创建创建service.impl包。在service包下新建IStudentService.java接口。在impl包下创建StudentServiceImplement.java类
IStudentService
/**
* 这部分和dao包下接口代码一样
* @author 红烛
*
*/
public interface IStudentService {
/**
* 添加学生信息
* @param stu
*/
void insert(Student stu);
/**
* 删除学生信息
* @param id
*/
void delete(Long id);
/**
* 修改学生信息
* @param stu
*/
void update(Student stu);
/**
* 查询某个学生信息
* @param id
* @return
*/
Student select(Long id);
/**
* 查询所有学生信息
* @return
*/
List<Student> selectAll();
}
StudentServiceImplement
/**
* 实现接口
* @author 红烛
*
*/
public class StudentServiceImpl implements IStudentService {
//采用面向接口编程方式
private IStudentDAO dao=new StudentDAOImpl();
@Override
public void insert(Student stu) {
dao.insert(stu);
}
@Override
public void delete(Long id) {
dao.delete(id);
}
@Override
public void update(Student stu) {
dao.update(stu);
}
@Override
public Student select(Long id) {
Student stu=dao.select(id);
return stu;
}
@Override
public List<Student> selectAll() {
List<Student> list=dao.selectAll();
return list;
}
}
准备阶段完成:在src下快速创建StudentServiceImplement.java的测试类,测试对数据库student表的增删改查是否正确。
servlet增删改查
添加功能
这是通过查找所有学生信息在jsp上进行页面展示
思路:在servlet的service方法中,
1.通过调用service的selectAll()方法拿到所有的学生信息
2.使用request.setAttribute();方法将数据放到request请求域中
3.转发跳转到student.jsp页面
4.student.jsp页面通el表达式从request请求域中取得数据
servlet相关部分代码
protected void findAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//获取数据
List<Student> list=studentService.selectAll();
//将数据放到request请求域中
request.setAttribute("list", list);
//跳转到student.jsp页面
request.getRequestDispatcher("/WEB-INF/view/student.jsp").forward(request, response);
}
student.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>学生信息页</title>
</head>
<body>
<div>
<a href="/student?cmd=insert">添加</a>
<table border="1" cellspacing="0" cellpadding="0">
<thead>
<tr>
<td>编号</td>
<td>姓名</td>
<td>年龄</td>
<td>生日</td>
<td>地址</td>
<td>电话</td>
<td>邮箱</td>
<td>操作</td>
</tr>
</thead>
<tbody>
<c:forEach var="stu" items="${list}">
<tr>
//获取数据
<td>${stu.id}</td>
<td>${stu.name}</td>
<td>${stu.age}</td>
<%-- <!-- type="date"或是 type="both" 都可以实现日期格式化--> --%>
<td><fmt:formatDate pattern="yyyy-MM-dd" value="${stu.birthday}" type="date"/></td>
<td>${stu.address}</td>
<td>${stu.phone}</td>
<td>${stu.email}</td>
<td>
<a href="/student?cmd=update&id=${stu.id}">修改</a>
<a href="/student?cmd=delete&id=${stu.id}">删除</a>
</td>
</tr>
</c:forEach>
</tbody>
</table>
</div>
</body>
</html>
删除功能
点击删除,跳转到删除这一模块。那么问题来了,servlet怎么识别你传过来的地址是要执行什么功能呢?解决方案是在链接中传递一个参数,在servlet中接收参数,如果传过来的参数cmd=delete,就调用delete方法,如果传过来的参数cmd=update,则跳转到update方法,这样就解决了。代码如下:
@Override
protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//解决乱码问题
request.setCharacterEncoding("utf-8");
//接收地址中的参数值
String cmd=request.getParameter("cmd");
if("insertOrUpdate".equals(cmd)){
insertOrUpdate(request,response);
}else if("insert".equals(cmd)){
insert(request,response);
}else if("delete".equals(cmd)){
delete(request,response);
}else if("update".equals(cmd)){
update(request,response);
}else{
findAll(request, response);
}
}
实现删除的思路:点击删除
1.删除
我们需要在路径中将要删除的这一条数据的id传递过去,
2.在servlet中接收id的值
3.调用service中delete方法,通过id将这条数据删除
4.重定向到student.jsp页面 (我们需要看到是否数据被删除)
protected void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//接收传过来的id值
String id=request.getParameter("id");
//StringUtil是判断字符串是否为空的工具类,代码下面贴出
if(StringUtil.hasLength(id)){
studentService.delete(Long.valueOf(id));
}
//重定向到显示页面
response.sendRedirect("/student");
}
StringUtil.java
/***
*
* @author 红烛
*
*/
public class StringUtil {
private StringUtil(){};
public static boolean hasLength(String val){
return val!=null&&!"".equals(val.trim());
}
}
增加,修改操作
增加和修改操作在这四个功能中是相对比较难的部分
增加修改所用的jsp页面
edit.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>${empty stu?'添加':'修改' }页面</title>
</head>
<body>
<div>
<form action="/student?cmd=insertOrUpdate" method="post">
<table>
<tr>
<td><input type="hidden" name="id" value="${stu.id}" ></td>
</tr>
<tr>
<td>名字:<input type="text" name="name" value="${stu.name}" ></td>
</tr>
<tr>
<td>年龄:<input type="text" name="age" value="${stu.age}" ></td>
</tr>
<tr>
<td>生日:<input type="date" name="birthday" value="<fmt:formatDate pattern="yyyy-MM-dd" value="${stu.birthday}" type="date"/>"></td>
</tr>
<tr>
<td>地址:<input type="text" name="address" value="${stu.address}" ></td>
</tr>
<tr>
<td>电话:<input type="tel" name="phone" value="${stu.phone}" ></td>
</tr>
<tr>
<td>邮箱:<input type="email" name="email" value="${stu.email}" ></td>
</tr>
<tr>
<td><input type="submit" value="${empty stu?'添加':'修改' }"/></td>
<tr>
</table>
</form>
</div>
</body>
</html>
实现思路:
-
1.点击student.jsp中的添加按钮 添加进入servlet的insert()方法模块。
2.在insert()方法中跳转到edit.jsp页面
3.填好数据后点击提交 进入到servlet的insertOrUpdate模块,对用户提交的数据进行处理
4.使用request.getParameter();方法将数据一一接收
5.将接受的数据进行封装 Student stu=new Student(); stu.set… 调用service的insert()实现数据插入数据库
6.重定向到student.jsp页面 -
增加和修改操作基本一致,主要区别在于,增加操作从edit页面点击提交到servlet,id值是没有的,因为id是设置的自增长,添加时不需要填写id项。而修改操作需要通过id进行修改,点击修改时,需要将id值传到servlet,servlet接收到后就可以通过调用service的select方法查询出这个id的student的所有信息。然后通过将得到的student对象信息放到请求域(request)中,再跳转到edit页面,edit页面通过el表达式${}给输入框赋值实现数据回显。修改完成也是提交到servlet的insertOrUpdate模块,而这时id是有数据的;所以可以通过id值是否为空判断是添加还是修改操作,从而调用相应的方法。
protected void insertOrUpdate(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { Student stu=new Student(); String id=request.getParameter("id"); if(StringUtil.hasLength(id)){ stu.setId(Long.valueOf(id)); p2Stu(request,response,stu); studentService.update(stu); }else{ p2Stu(request,response,stu); studentService.insert(stu); } response.sendRedirect("/student"); } //点击修改 实现数据回显,并跳转到edit页面 protected void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String id=request.getParameter("id"); Student stu=studentService.select(Long.valueOf(id)); request.setAttribute("stu", stu); request.getRequestDispatcher("/WEB-INF/view/edit.jsp").forward(request, response); } //接收参数并封装 protected void insert(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.getRequestDispatcher("/WEB-INF/view/edit.jsp").forward(request, response); } private static void p2Stu(HttpServletRequest request, HttpServletResponse response,Student stu)throws ServletException, IOException{ String name=request.getParameter("name"); String birthday=request.getParameter("birthday"); String age=request.getParameter("age"); String address=request.getParameter("address"); String email=request.getParameter("email"); String phone=request.getParameter("phone"); stu.setAddress(address); if(StringUtil.hasLength(age)){ stu.setAge(Integer.valueOf(age)); } SimpleDateFormat sft=new SimpleDateFormat("yyyy-MM-dd"); Date date; try { date = sft.parse(birthday); stu.setBirthday(date); } catch (ParseException e) { e.printStackTrace(); } stu.setEmail(email); stu.setName(name); stu.setPhone(phone);
}
servlet代码总览
/**
*
* @author 红烛
*
*/
@WebServlet("/student")
public class StudentServlet extends HttpServlet{
private IStudentService studentService=new StudentServiceImpl();
private static final long serialVersionUID = 1L;
@Override
protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String cmd=request.getParameter("cmd");
if("insertOrUpdate".equals(cmd)){
insertOrUpdate(request,response);
}else if("insert".equals(cmd)){
insert(request,response);
}else if("delete".equals(cmd)){
delete(request,response);
}else if("update".equals(cmd)){
update(request,response);
}else{
findAll(request, response);
}
}
protected void insertOrUpdate(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Student stu=new Student();
String id=request.getParameter("id");
if(StringUtil.hasLength(id)){
stu.setId(Long.valueOf(id));
p2Stu(request,response,stu);
studentService.update(stu);
}else{
p2Stu(request,response,stu);
studentService.insert(stu);
}
response.sendRedirect("/student");
}
protected void insert(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.getRequestDispatcher("/WEB-INF/view/edit.jsp").forward(request, response);
}
protected void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String id=request.getParameter("id");
if(StringUtil.hasLength(id)){
studentService.delete(Long.valueOf(id));
}
response.sendRedirect("/student");
}
protected void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String id=request.getParameter("id");
Student stu=studentService.select(Long.valueOf(id));
request.setAttribute("stu", stu);
request.getRequestDispatcher("/WEB-INF/view/edit.jsp").forward(request, response);
}
protected void findAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
List<Student> list=studentService.selectAll();
request.setAttribute("list", list);
request.getRequestDispatcher("/WEB-INF/view/student.jsp").forward(request, response);
}
private static void p2Stu(HttpServletRequest request, HttpServletResponse response,Student stu)throws ServletException, IOException{
String name=request.getParameter("name");
String birthday=request.getParameter("birthday");
String age=request.getParameter("age");
String address=request.getParameter("address");
String email=request.getParameter("email");
String phone=request.getParameter("phone");
stu.setAddress(address);
if(StringUtil.hasLength(age)){
stu.setAge(Integer.valueOf(age));
}
SimpleDateFormat sft=new SimpleDateFormat("yyyy-MM-dd");
Date date;
try {
date = sft.parse(birthday);
stu.setBirthday(date);
} catch (ParseException e) {
e.printStackTrace();
}
stu.setEmail(email);
stu.setName(name);
stu.setPhone(phone);
}
}