MyBatis -- 小练习

   目标：加深对mybatis框架的学习。

   具体实现以下功能

总体思路：搭建好mybatis环境后，通过封装好的MybatisUtil类进行获取SqlSession,并对mapper.xml绑定接口，通过PageInfo类封装前端页面所需要的所有信息，在service的实现类中实现所有的关于数据库查询操作，并将所有信息封装到PageInfo实现类中，返回到servlet中，在servlet中将PageInfo放入请求头信息中，并进行请求转发，在前端页面通过el来获取，以及Jquery实现相应的效果。

步骤：

1. 搭建mybatis框架

    mybatis.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>
  		
  		<settings>
  			<setting name="logImpl" value="LOG4J"/>
  		</settings>
  		<typeAliases>
  			<package name="com.yyl.pojo"/>
  		</typeAliases>
  		<environments default="default">
  			<environment id="default">
  				<transactionManager type="JDBC"></transactionManager>
  				<dataSource type="POOLED">
  					<property name="driver" value="com.mysql.jdbc.Driver"/>
  					<property name="url" value="jdbc:mysql://localhost:3306/db_study"/>
  					<property name="username" value=" "/>
  					<property name="password" value=" "/>
  				</dataSource>
  			</environment>
  		
  		</environments>
	  	<mappers>
	  		<package name="com.yyl.mapper"/>
	  	</mappers>
  </configuration>

mapper.xml

//StudenMapper.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="com.yyl.mapper.StudentMapper">
	<select id="selByPage" resultType="student" parameterType="PageInfo">
		select * from student
		<where>
			<if test="sname!=null and sname!='' ">
				<bind name="sname" value="'%'+sname+'%'" />
				and name like #{sname}
			</if>
			<if test="tname!=null and tname!='' ">
				<bind name="tname" value="'%'+tname+'%'" />
				and tid in(select id from teacher where name like #{tname})
			</if>
		</where>
		limit #{pageStart},#{pageSize}
	</select>

	<select id="selCount" resultType="long" parameterType="PageInfo">
		select count(*) from student
		<where>
			<if test="sname!=null and sname!='' ">
				<bind name="sname" value="'%'+sname+'%'" />
				and name like #{sname}
			</if>
			<if test="tname!=null and tname!=''">
				<bind name="tname" value="'%'+tname+'%'" />
				and tid in(select id from teacher where name like #{tname})
			</if>
		</where>

	</select>

</mapper>

//TeacherMapper.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="com.yyl.mapper.TeacherMapper">
  	<select id="selById" resultType="teacher" parameterType="int">
  		select * from teacher where id=#{0}
  	</select>
  </mapper>

2.建立相应实体类

//student类
package com.yyl.pojo;

public class Student {
	@Override
	public String toString() {
		return "Student [sid=" + getId() + ", name=" + name + ", age=" + age + ", tid=" + tid + ", teacher=" + teacher
				+ "]";
	}

	private int id;
	private String name;
	private int age;
	private int tid;
	private Teacher teacher;


	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public int getTid() {
		return tid;
	}

	public void setTid(int tid) {
		this.tid = tid;
	}

	public Teacher getTeacher() {
		return teacher;
	}

	public void setTeacher(Teacher teacher) {
		this.teacher = teacher;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

}

//teacher类
package com.yyl.pojo;

public class Teacher {
	private int tid;
	private String name;

	public int getTid() {
		return tid;
	}

	public void setTid(int tid) {
		this.tid = tid;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

}

package com.yyl.pojo;

import java.util.List;

public class PageInfo {
	private int pageSize;    //每页记录数
	private int pageNum;    //当前页
	private int pageStart;    // 开始的记录条数
	private long total;    //  公共多少页
	private String sname;    // 学生姓名
	private String tname;    // 教师姓名
	private List<?> list;    // 存储当前页的学生信息
	public int getPageSize() {    
		return pageSize;
	}
	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}
	public int getPageNum() {
		return pageNum;
	}
	public void setPageNum(int pageNum) {
		this.pageNum = pageNum;
	}
	public int getPageStart() {
		return pageStart;
	}
	public void setPageStart(int pageStart) {
		this.pageStart = pageStart;
	}
	public long getTotal() {
		return total;
	}
	public void setTotal(long total) {
		this.total = total;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	public String getTname() {
		return tname;
	}
	public void setTname(String tname) {
		this.tname = tname;
	}
	public List<?> getList() {
		return list;
	}
	public void setList(List<?> list) {
		this.list = list;
	}

}

3.创建service类

package com.yyl.service.impl;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.yyl.mapper.StudentMapper;
import com.yyl.mapper.TeacherMapper;
import com.yyl.pojo.PageInfo;
import com.yyl.pojo.Student;
import com.yyl.service.StudentService;
import com.yyl.utils.MybatisUtil;
/**
* 实现对数据库的查询，查询学生信息，以及总的记录数，用于分页，将所有信息存储在PageInfo的实体类
* 中，并将此类返回
*
*/
public class StudentServiceImpl implements StudentService {

	@Override
	public PageInfo showPage(String sname, String tname, String pageSizeStr, String pageNumStr) {
		int pageSize = 2;
		if (pageSizeStr != null && !pageSizeStr.equals("")) {
			pageSize = Integer.parseInt(pageSizeStr);
		}
		int pageNum = 1;
		if (pageNumStr != null && !pageNumStr.equals("")) {
			pageNum = Integer.parseInt(pageNumStr);
		}
		
		SqlSession session = MybatisUtil.getSession();
		
		StudentMapper studentMapper = session.getMapper(StudentMapper.class);
		
		
		PageInfo pi = new PageInfo();
		pi.setPageNum(pageNum);
		pi.setPageSize(pageSize);
		pi.setSname(sname);
		pi.setTname(tname);
		pi.setPageStart((pageNum-1)*pageSize);
		
		List<Student> list = studentMapper.selByPage(pi);
		System.out.println(studentMapper.selByPage(pi));
		TeacherMapper teacherMapper = session.getMapper(TeacherMapper.class);
		for (Student student : list) {
			student.setTeacher(teacherMapper.selById(student.getTid()));
		}
		pi.setList(list);
		long count = studentMapper.selCount(pi);
		pi.setTotal(count%pageSize==0? count/pageSize:count/pageSize+1);
	
		return pi;
	}

}

4.创建servlet

package com.yyl.servlet;

import java.io.IOException;
import java.util.List;

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

import com.yyl.pojo.PageInfo;
import com.yyl.pojo.Student;
import com.yyl.service.StudentService;
import com.yyl.service.impl.StudentServiceImpl;

/**
 * 将页面收集到的信息传给service，接受service返回的PageInfo类，并存储在request请求头内，然后
 * 请求转发给index.jsp页面
 */
@WebServlet("/ss")
public class StudentServlet extends HttpServlet {
	private StudentService sse = new StudentServiceImpl();
	@Override
	protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		req.setCharacterEncoding("utf-8");
		resp.setContentType("text/html;charset=utf-8");
		String sname = req.getParameter("sname");
		String tname = req.getParameter("tname");
		String pageSize = req.getParameter("pageSize");
		String pageNum = req.getParameter("pageNum");
		
		PageInfo pi = sse.showPage(sname, tname, pageSize, pageNum);
		
		req.setAttribute("PageInfo", pi);
	
		//System.out.println(pi);
		req.getRequestDispatcher("index.jsp").forward(req, resp);
	}

}

5.index.jsp页面进行显示

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!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>Insert title here</title>
<script type="text/javascript" src="/Student/js/jquery-1.7.2.js"></script>
<script type="text/javascript">
	$(function(){
		//得到关于PageInfo的相关信息
		var pageSize = "${PageInfo.pageSize}";
		var pageNum = "${PageInfo.pageNum}";
		var total="${PageInfo.total}";
		var tname = "${PageInfo.tname}";
		var sname="${PageInfo.sname}";
		
		// 将所选的页数设为 checked(默认)
		$.each($(":radio"),function(i,n){
			if($(n).val()==pageSize){
				$(n).attr("checked","checked");
			}
		});
		//设置输入框的值，防止在进行选择每页记录数时，进行刷新页面导致 值 丢失
		$(":text[name='sname']").val(sname);
		$(":text[name='tname']").val(tname);
	
		//查询按钮点击
		$(":button").click(function(){
			location.href="ss?"+"pageSize="+pageSize+"&pageNum=1"
					+"&tname="+$(":text[name='tname']").val()
					+"&sname="+$(":text[name='sname']").val();
			
		})
		//单选按钮点击
		$(":radio").click(function(){
			pageSize=$(this).val();
			location.href="ss?"+"pageSize="+pageSize+"&pageNum=1"
			+"&tname="+$(":text[name='tname']").val()
			+"&sname="+$(":text[name='sname']").val();
		})
		//分页
			//上一页
			$(".page_a:eq(0)").click(function(){
				pageNum = parseInt(pageNum)-1;
				if(pageNum>=1){
					location.href="ss?"+"pageSize="+pageSize+"&pageNum="+pageNum
					+"&tname="+$(":text[name='tname']").val()
					+"&sname="+$(":text[name='sname']").val();
				}else{
					pageNum=1;
				}
				return false;
			})
			//下一页
			$(".page_a:eq(1)").click(function(){
				pageNum = parseInt(pageNum)+1;
				if(pageNum<=total){
					location.href="ss?"+"pageSize="+pageSize+"&pageNum="+pageNum
					+"&tname="+$(":text[name='tname']").val()
					+"&sname="+$(":text[name='sname']").val();
				}else{
					pageNum=total;
				}
				return false;
			})	
	})
</script>
</head>
<body>
	<input type="radio" value="2" name="pageSize"/>2
	<input type="radio" value="3" name="pageSize"/>3
	<input type="radio" value="4" name="pageSize"/>4
	<br>
	学生姓名：<input type="text" name="sname">
	老师姓名：<input type="text" name="tname">
	 <button>查询</button>
	 <br>	 <br>	 <br>
	 
	<table border="1px">
		<tr>
			<td>学生编号</td>
			<td>姓名</td>
			<td>年龄</td>
			<td>老师姓名</td>
		</tr>
		<c:forEach items="${PageInfo.list}" var="stu">
			<tr>
				<td>${stu.id }</td>
				<td>${stu.name }</td>
				<td>${stu.age }</td>
				<td>${stu.teacher.name }</td>
			</tr>		
		</c:forEach>
	</table>
	<br>	 <br>	 <br>
	<a href="" class="page_a">上一页 </a> &nbsp &nbsp
	<a href="" class="page_a">下一页 </a>
</body>
</html>