MyBatis能力提升——航班机场练习

需求：

实体类：

package com.tao.pojo;
/**
 * 飞机类
 *
 */
public class AirPlane {
	private int id;		
	private String airNo;	//飞机编号
	private int time;		//航行时间
	private double price;	//航班价格
	private Airport takePort;	//起飞机场对象
	private Airport landPort;	//降落机场对象
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getAirNo() {
		return airNo;
	}
	public void setAirNo(String airNo) {
		this.airNo = airNo;
	}
	public int getTime() {
		return time;
	}
	public void setTime(int time) {
		this.time = time;
	}
	public double getPrice() {
		return price;
	}
	public void setPrice(double price) {
		this.price = price;
	}
	public Airport getTakePort() {
		return takePort;
	}
	public void setTakePort(Airport takePort) {
		this.takePort = takePort;
	}
	public Airport getLandPort() {
		return landPort;
	}
	public void setLandPort(Airport landPort) {
		this.landPort = landPort;
	}
}

package com.tao.pojo;
/**
 * 机场信息类
 *
 */
public class Airport {
	private int id;
	private String portName;	//机场名称
	private String cityName;	//机场城市名称
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getPortName() {
		return portName;
	}
	public void setPortName(String portName) {
		this.portName = portName;
	}
	public String getCityName() {
		return cityName;
	}
	public void setCityName(String cityName) {
		this.cityName = cityName;
	}
}

Mapper接口：

package com.tao.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Select;

import com.tao.pojo.Airport;

public interface AirportMapper {
	
	/*
	 * 起飞机场信息查询
	 */
	@Select("select * from airport where id in (select distinct takeid from airplane)")
	List<Airport> selTakePort();
	
	/*
	 * 降落机场信息查询
	 */
	@Select("select * from airport where id in (select distinct landid from airplane)")
	List<Airport> selLandPort();

}

package com.tao.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.tao.pojo.AirPlane;

public interface AirPlaneMapper {
	
	List<AirPlane> selByTakeidLandid(@Param("takeid")int takeid,@Param("landid")int landid);

}

映射文件:

<?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.tao.mapper.AirPlaneMapper">
	<resultMap type="AirPlane" id="airPlaneMap">
		<id column="id" property="id" />
		<result column="airno" property="airNo"/>
		<result column="time" property="time" />
		<result column="price" property="price" />
		<association property="takePort" javaType="Airport" >
			<id column="takeid" property="id"/>
			<result column="takeportname" property="portName"/>
			<result column="takecityname" property="cityName"/>
		</association>
		<association property="landPort" javaType="Airport" >
			<id column="landid" property="id"/>
			<result column="landcityname" property="cityName"/>
			<result column="landportname" property="portName"/>
		</association>
	</resultMap>

	<select id="selByTakeidLandid" resultMap="airPlaneMap">
		select a.*,b.id takeid,b.portname takeportname,b.cityname takecityname,c.id landid,c.cityname landcityname,c.portName landportname
	    from airplane a 
		left join airport b on a.takeid = b.id 
		left join airport c on a.landid = c.id
		<where>
			<if test="takeid>0">
				and takeid =#{takeid}
			</if>
			<if test="landid>0">
				and landid =#{landid}
			</if>
		</where>
	</select>
</mapper>

业务层:

package com.tao.service;

import java.util.List;

import com.tao.pojo.Airport;

public interface AirportService {
	
	/**
	 * 显示所有起飞机场
	 * @return
	 */
	List<Airport> showTakePort();
	/**
	 * 显示所有降落机场
	 * @return
	 */
	List<Airport> showLandPort();
}

package com.tao.service;

import java.util.List;

import com.tao.pojo.AirPlane;

public interface AirplaneService {
	/*
	 * 航班信息
	 */
	List<AirPlane> show(int takeid,int landid);
}

业务实现类：

package com.tao.service.impl;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.tao.mapper.AirPlaneMapper;
import com.tao.pojo.AirPlane;
import com.tao.service.AirplaneService;
import com.tao.util.MyBatisUtil;

public class AirPlaneServiceImpl implements AirplaneService{

	@Override
	public List<AirPlane> show(int takeid, int landid) {
		SqlSession session = MyBatisUtil.getSqlSession();
		AirPlaneMapper airPlaneMapper = session.getMapper(AirPlaneMapper.class);
		List<AirPlane> list = airPlaneMapper.selByTakeidLandid(takeid, landid);
		return list;
	}

}

package com.tao.service.impl;

import java.util.List;

import com.tao.mapper.AirportMapper;
import com.tao.pojo.Airport;
import com.tao.service.AirportService;
import com.tao.util.MyBatisUtil;

public class AirportServiceImpl implements AirportService{

	@Override
	public List<Airport> showTakePort() {
		return MyBatisUtil.getSqlSession().getMapper(AirportMapper.class).selTakePort();
	}

	@Override
	public List<Airport> showLandPort() {
		return MyBatisUtil.getSqlSession().getMapper(AirportMapper.class).selLandPort();
	}

}

控制层：

package com.tao.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.tao.pojo.Airport;
import com.tao.service.AirportService;
import com.tao.service.impl.AirportServiceImpl;

@WebServlet("/showtake")
public class ShowTakeServlet extends HttpServlet{

	private static final long serialVersionUID = 1L;

	private AirportService airportService = new AirportServiceImpl();
	
	@Override
	protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		List<Airport> takePort = airportService.showTakePort();
		req.setAttribute("takePort", takePort);
		req.getRequestDispatcher("/showland").forward(req, resp);
		
	}
	
}

package com.tao.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.tao.pojo.Airport;
import com.tao.service.AirportService;
import com.tao.service.impl.AirportServiceImpl;
@WebServlet("/showland")
public class ShowLandService extends HttpServlet{

	private static final long serialVersionUID = 1L;

	private AirportService airportService = new AirportServiceImpl();
	
	@Override
	protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		List<Airport> landPort = airportService.showLandPort();
		req.setAttribute("landPort", landPort);
		req.getRequestDispatcher("/showairplane").forward(req, resp);
		
	}
	
}

package com.tao.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.tao.service.AirplaneService;
import com.tao.service.impl.AirPlaneServiceImpl;

@WebServlet("/showairplane")
public class ShowAirplaneServlet extends HttpServlet{

	private static final long serialVersionUID = 1L;
	
	AirplaneService airplaneService = new AirPlaneServiceImpl();
	
	@Override
	protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		req.setCharacterEncoding("utf-8");
		resp.setContentType("text/html;charset=UTF-8");
		int takeid = 0;
		String takeIdStr = req.getParameter("takeid");
		if(takeIdStr!=null&&!takeIdStr.equals("")){
			takeid=Integer.parseInt(takeIdStr);
		}
		int landid = 0;
		String landIdStr = req.getParameter("landid");
		if(landIdStr!=null&&!"".equals(landIdStr)){
			landid = Integer.parseInt(landIdStr);
		}
		
		req.setAttribute("list", airplaneService.show(takeid, landid));
		req.getRequestDispatcher("/index.jsp").forward(req, resp);
	}

}

MyBatis简化工具类及过滤器配置：

package com.tao.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 {
	
	private static SqlSessionFactory factory;
	private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<>();
	
	static{
		try{
			InputStream is = Resources.getResourceAsStream("mybatis.xml");
			factory = new SqlSessionFactoryBuilder().build(is);
		}catch(IOException e){
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取SqlSession的方法
	 */
	public static SqlSession getSqlSession(){
		SqlSession session = threadLocal.get();
		if(session==null){
			threadLocal.set(factory.openSession());
		}
		return threadLocal.get();
	}
	
	/**
	 * 关闭Session的方法
	 */
	public static void closeSession(){
		SqlSession session = threadLocal.get();
		if(session!=null){
			session.close();
		}
		threadLocal.set(null);
	}
}

package com.tao.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.tao.util.MyBatisUtil;
/**
 * 最开始是由Spring框架提出的.整合Hibernate框架是使用的是OpenSessionInView
 * @author zhangtao
 *
 */
@WebFilter("/*")
public class openSessionViewFilter implements Filter{

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

	@Override
	public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain)
			throws IOException, ServletException {
		SqlSession session = MyBatisUtil.getSqlSession();
		try {
			filterChain.doFilter(servletRequest, servletResponse);
			session.commit();
		} catch (Exception e) {
			session.rollback();
			e.printStackTrace();
		}finally {
			MyBatisUtil.closeSession();
		}
	}

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

}

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>
	
	<!-- mybatis 开启log4j支持功能 -->
	<settings>
		<setting name="logImpl" value="LOG4J"/>
	</settings>
	
	<!-- 给某个包下所有类起别名，别名为类名, 不区分大小写 -->
	<typeAliases>
		<package name="com.tao.pojo"/>
	</typeAliases>
	
	<environments default="dev">
		<environment id="dev">
			<!-- JDBC 原生事务管理方式 -->
			<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.tao.mapper"/>
	</mappers>

</configuration>

前台：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" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/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>Insert title here</title>
</head>
<body>
<form action="showtake" method="post">
起飞机场：<select name="takeid">
			<option value="0">请选择</option>
			<c:forEach items="${takePort}" var="take">
				<option value="${take.id }">${take.portName }</option>
			</c:forEach>
	   </select>

降落机场：<select name="landid">
			<option value="0">请选择</option>
			<c:forEach items="${landPort }" var="land">
				<option value="${land.id }">${land.portName }</option>
			</c:forEach>
       </select>
<input type="submit" value="查询" />
</form>
<table border="1">
	<tr>
		<td>飞机编号</td>
		<td>起飞机场</td>
		<td>起飞城市</td>
		<td>降落机场</td>
		<td>降落城市</td>
		<td>航行时间</td>
		<td>票价(元)</td>
	</tr>
	
	<c:forEach items="${list }" var="plane">
		<tr>
		<td>${plane.airNo }</td>
		<td>${plane.takePort.portName }</td>
			<td>${plane.takePort.cityName }</td>
			<td>${plane.landPort.portName }</td>
			<td>${plane.landPort.cityName }</td>
			<td>
				<c:if test="${plane.time/60>0}">
					<fmt:formatNumber value="${plane.time/60 }" pattern="0"></fmt:formatNumber>小时
				</c:if>
				<c:if test="${ plane.time%60>0}">
					${ plane.time%60}分钟
				</c:if>
			</td>
			<td>${plane.price }</td>
	</tr>
	</c:forEach>
</table>

</body>
</html>