动态SQL的完整练习
- 数据库设计:老师表和学生表
- 学生姓名和老师姓名进行模糊查询，都可以不输出
- 实现动态SQL
- 每次点击"查询"按钮后显示当前查询结果第一页数据
- 默认进入页面显示全部信息的第一页数据
- 点击修改每页显示的个数,跳转到当前查询条件的第一页,且显示个数为radio对应个数
- 每次查询完成后,跳转回页面后需要保留查询条件和显示的个数
- 第一次进入页面时2的单选按钮被选中
- 不能使用ajax完成
具体实践
- 数据库表的设计
teacher是主键表，student是外键表
create table teacher(
id int(10) primary key auto_increment,
name varchar(20)
);
create table student(
id int(10) primary key auto_increment,
name varchar(20),
age int(3),
tid int(10),
constraint fk_teacher foreign key (tid)
references teacher(id)
);
测试数据录入
INSERT into teacher VALUES(default,'老师1');
INSERT into teacher VALUES(default,'老师2');
INSERT into student VALUES(default,'学生1',1,1);
INSERT into student VALUES(default,'学生2',2,1);
INSERT into student VALUES(default,'学生3',3,1);
INSERT into student VALUES(default,'学生4',4,1);
INSERT into student VALUES(default,'学生5',5,1);
INSERT into student VALUES(default,'学生6',6,1);
INSERT into student VALUES(default,'学生7',7,1);
INSERT into student VALUES(default,'学生8',8,1);
INSERT into student VALUES(default,'学生9',9,2);
INSERT into student VALUES(default,'学生10',10,2);
INSERT into student VALUES(default,'学生11',11,2);
- 导入相关的jar包
- 拿出mybatis的全局配置文件，这次用接口绑定的方式
<?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.youdian.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/sum"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.youdian.mapper"/>
</mappers>
</configuration>
- pojo层
学生和老师的实体类：
加入get()和set()方法
public class Teacher {
private int id;
private String name;
}
public class Student {
private int id;
private String name;
private int age;
private int tid;
private Teacher teacher;
}
- mapper层
因为参数个数是四个，所有把参数封装成对象进行传递更为方便
新建的PageInfo放到pojo层下：
加入get()和set()方法
public class PageInfo {
private int pageSize;
private int pageNumber;
private long total;
private List<?> list;
//学生姓名
private String sname;
//老师姓名
private String tname;
private int pageStart;
}
接口StudentMapper：
public interface StudentMapper {
List<Student> selByPage(PageInfo pi);
long selCountByPageInfo(PageInfo pi);
}
写出配置文件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.youdian.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>
接口TeacherMapper：
public interface TeacherMapper {
Teacher selById(int id);
}
写出配置文件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.youdian.mapper.TeacherMapper">
<select id="selById" parameterType="int" resultType="teacher">
select * from teacher where id=#{0}
</select>
</mapper>
- service层
接口：
public interface StudentService {
PageInfo showPage(String sname,String tname,String pageSize,String pageNumber);
}
实现类：
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;
}
}
工具类和过滤器：
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);
}
}
@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() {
}
}
- servlet层
@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);
}
}
index.jsp
用jquery-1.7.2库
必须保证写在jsp文件中，如果写在js文件中，由于js文件服务不会进行任何解析操作直接发送给客户端浏览器，所以如果写在js文件中认为$()是字符串
<%@ 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" src="/student/js/demo.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对象
//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>
扫一扫
447
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
