MyBatis学习笔记六之动态SQL能力提升

最新推荐文章于 2024-07-15 19:59:25 发布

cute。

最新推荐文章于 2024-07-15 19:59:25 发布

阅读量201

点赞数

分类专栏： Mybatis

本文链接：https://blog.csdn.net/weixin_41637380/article/details/91553910

版权

Mybatis 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

本文档详细介绍了使用MyBatis进行动态SQL操作的需求背景，包括数据库表的创建、测试数据的插入，以及后端项目的具体实现步骤。涵盖了从新建web项目、配置MyBatis全局文件，到pojo、mapper、service、util、filter、impl、servlet等各层代码的编写，最后还涉及到页面的构建和js库的引入。

摘要由CSDN通过智能技术生成

一.需求

url为：localhost：8080/student/show

二.数据库

一.创建表

二.插入测试数据

老师表：

学生表：

三.后端代码具体实现

一.新建student的web项目、构建框架

1.在lib下导入jar包

2.写mybatis全局配置文件

（使用接口绑定方式）

<mappers>
<package name="com.bjsxt.mapper"/>
</mappers>

完整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.bjsxt.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/ssm"/>
				<property name="username" value="root"/>
				<property name="password" value="root"/>
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<package name="com.bjsxt.mapper"/>
	</mappers>
</configuration>

二.pojo

1.Teacher.java（1）

package com.bjsxt.pojo;

public class Teacher {
	private int id;
	private String name;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
}

2.Student.java（2）

package com.bjsxt.pojo;

public class Student {
	private int id;
	private String name;
	private int age;
	private int tid;

	private Teacher teacher;//为了让一个类携带页面所有需要的数据，则把；另外的对象加上

	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	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;
	}
	
}

3.PageInfo.java（3）

package com.bjsxt.pojo;

import java.util.List;

public class PageInfo {
	private int pageSize;
	private int pageNumber;
	private long total;
	private List<?> list;
	//学生姓名
	private String sname;
	//老师姓名	
	private String tname;
	//起始行
	private int pageStart;
	public int getPageStart() {
		return pageStart;
	}
	public void setPageStart(int pageStart) {
		this.pageStart = pageStart;
	}
	public int getPageSize() {
		return pageSize;
	}
	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}
	public int getPageNumber() {
		return pageNumber;
	}
	public void setPageNumber(int pageNumber) {
		this.pageNumber = pageNumber;
	}
	public long getTotal() {
		return total;
	}
	public void setTotal(long total) {
		this.total = total;
	}
	public List<?> getList() {
		return list;
	}
	public void setList(List<?> list) {
		this.list = list;
	}
	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;
	}
	@Override
	public String toString() {
		return "PageInfo [pageSize=" + pageSize + ", pageNumber=" + pageNumber + ", total=" + total + ", list=" + list
				+ ", sname=" + sname + ", tname=" + tname + ", pageStart=" + pageStart + "]";
	}
}

三.mapper

1.StudentMapper.java（4）

package com.bjsxt.mapper;

import java.util.List;

import com.bjsxt.pojo.PageInfo;
import com.bjsxt.pojo.Student;

public interface StudentMapper {
	List<Student> selByPage(PageInfo pi);
	
	long selCountByPageInfo(PageInfo pi);//总条数
}

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

3.TeacherMapper.java

package com.bjsxt.mapper;

import com.bjsxt.pojo.Teacher;

public interface TeacherMapper {
	Teacher selById(int id);
}

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

四.service

StudentService.java

package com.bjsxt.service;

import com.bjsxt.pojo.PageInfo;

public interface StudentService {
	PageInfo showPage(String sname,String tname,String pageSize,String pageNumber);
}

五.util

package com.bjsxt.util;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisUtil {
	//factory实例化的过程是一个比较耗费性能的过程.
	//保证有且只有一个factory
	private static SqlSessionFactory factory;
	private static ThreadLocal<SqlSession> tl = new ThreadLocal<>();
	static{
		try {
			InputStream is = Resources.getResourceAsStream("mybatis.xml");
			factory = new SqlSessionFactoryBuilder().build(is);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 获取SqlSession的方法
	 */
	public static SqlSession getSession(){
		SqlSession session = tl.get();
		if(session==null){
			tl.set(factory.openSession());
		}
		return tl.get();
	}
	
	public static void closeSession(){
		SqlSession session = tl.get();
		if(session!=null){
			session.close();
		}
		tl.set(null);
	}
}

六.filter

OpenSessionInView.java

package com.bjsxt.filter;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.annotation.WebFilter;

import org.apache.ibatis.session.SqlSession;

import com.bjsxt.util.MyBatisUtil;

@WebFilter("/*")
public class OpenSessionInView implements Filter {

	@Override
	public void init(FilterConfig filterconfig) throws ServletException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void doFilter(ServletRequest servletrequest, ServletResponse servletresponse, FilterChain filterchain)
			throws IOException, ServletException {
		SqlSession session = MyBatisUtil.getSession();
		try {
			filterchain.doFilter(servletrequest, servletresponse);
			session.commit();
		} catch (Exception e) {
			session.rollback();
			e.printStackTrace();
		}finally{
			MyBatisUtil.closeSession();
		}
	}

	@Override
	public void destroy() {
		// TODO Auto-generated method stub
		
	}

}

七. impl

StudentServiceImpl.java

package com.bjsxt.service.impl;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.bjsxt.mapper.StudentMapper;
import com.bjsxt.mapper.TeacherMapper;
import com.bjsxt.pojo.PageInfo;
import com.bjsxt.pojo.Student;
import com.bjsxt.service.StudentService;
import com.bjsxt.util.MyBatisUtil;

public class StudentServiceImpl implements  StudentService{

	@Override
	public PageInfo showPage(String sname, String tname, String pageSizeStr, String pageNumberStr) {
		int pageSize = 2;
		if(pageSizeStr!=null&&!pageSizeStr.equals("")){
			pageSize = Integer.parseInt(pageSizeStr);
		}
		int pageNumber = 1;
		if(pageNumberStr!=null&&!pageNumberStr.equals("")){
			pageNumber = Integer.parseInt(pageNumberStr);
		}
		SqlSession session = MyBatisUtil.getSession();
		StudentMapper studentMapper = session.getMapper(StudentMapper.class);
		
		PageInfo pi = new PageInfo();
		pi.setPageNumber(pageNumber);
		pi.setPageSize(pageSize);
		pi.setPageStart((pageNumber-1)*pageSize);
		pi.setTname(tname);
		pi.setSname(sname);
		
		List<Student> list = 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.selCountByPageInfo(pi);
		
		pi.setTotal(count%pageSize==0?count/pageSize:count/pageSize+1);
		
		return pi;
	}

}

八.servlet

ShowServlet.java

package com.bjsxt.servlet;

import java.io.IOException;

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.bjsxt.pojo.PageInfo;
import com.bjsxt.service.StudentService;
import com.bjsxt.service.impl.StudentServiceImpl;

@WebServlet("/show")
public class ShowServlet extends HttpServlet {
	private StudentService stuService = new StudentServiceImpl();

	@Override
	protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		String sname = req.getParameter("sname");
		if (sname != null && !sname.equals(""))

			sname = new String(sname.getBytes("iso-8859-1"), "utf-8");

		String tname = req.getParameter("tname");
		if (tname != null && !tname.equals(""))
			tname = new String(tname.getBytes("iso-8859-1"), "utf-8");
		String pageSize = req.getParameter("pageSize");
		String pageNumber = req.getParameter("pageNumber");
		PageInfo pi = stuService.showPage(sname, tname, pageSize, pageNumber);
		System.out.println(pi);
		req.setAttribute("pageinfo", pi);
		req.getRequestDispatcher("index.jsp").forward(req, resp);
	}
}

九.页面

1.在webContent下新建js文件夹，将jquery库粘贴进去

2.index.jsp

（引进jquery库）

<script type="text/javascript" src="/student/js/jquery-1.7.2.js"></script>

<%@ 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(){
	var pageSize = "${pageinfo.pageSize}";
	var pageNumber = "${pageinfo.pageNumber}";
	var tname = "${pageinfo.tname}";
	var sname = "${pageinfo.sname}";
	var total = ${pageinfo.total};


//第一个功能的实现，选择按钮
	//i表示循环脚标   n表示迭代变量  n=数组[i]  n是dom对象（就是js对象）

	//dom对象转换成jquery对象：  $(dom对象)
	//把jquery对象转换成dom对象  jquery对象[0]  或者  jquery对象.get(0)

	$.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="show?pageSize="+pageSize+"&pageNumber=1&tname="+$(":text[name='tname']").val()+"&sname="+$(":text[name='sname']").val();
	});


//第四个功能的实现，单选按钮点击事件
	$(":radio").click(function(){
		pageSize = $(this).val();
		location.href="show?pageSize="+pageSize+"&pageNumber=1&tname="+$(":text[name='tname']").val()+"&sname="+$(":text[name='sname']").val();
	})

//第五个功能的实现，上下翻页
    //点击上一页
	$(".page_a:eq(0)").click(function(){
		pageNumber=parseInt(pageNumber)-1;
		if(pageNumber>=1){
			location.href="show?pageSize="+pageSize+"&pageNumber="+pageNumber+"&tname="+$(":text[name='tname']").val()+"&sname="+$(":text[name='sname']").val();
		}else{
			pageNumber = 1;
		}
		return false;//取消超链接默认行为
	});
    //点击下一页
	$(".page_a:eq(1)").click(function(){
		pageNumber=parseInt(pageNumber)+1;
		if(pageNumber<=total){
			location.href="show?pageSize="+pageSize+"&pageNumber="+pageNumber+"&tname="+$(":text[name='tname']").val()+"&sname="+$(":text[name='sname']").val();
		}else{
			pageNumber = 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/>

//表格
<table border="1">
	<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>

<a href="" class="page_a">上一页</a><a href="" class="page_a">下一页</a>

</body>
</html>