飞机起飞系统
1.设计数据库
create table airport(
id int(10) primary key auto_increment,
portname varchar(20),
cityname varchar(20)
);
create Table airplane(
id int(10) primary key auto_increment,
airno varchar(20),
time int(5) comment '单位分钟',
price double,
takeid int(10) comment '起飞机场',
landid int(10) comment '降落机场'
);
insert into airport values(default,'代红1机场','代红1');
insert into airport values(default,'代红2机场','代红2');
insert into airport values(default,'代红3机场','代红3');
insert into airplane values(default,'红飞机1',111,100,1,3);
insert into airplane values(default,'红飞机2',133,100,3,2);
2.导入jar包
3.配置全局文件
<?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>
4.先对起飞机场进行创建实体类
在com.youdian.pojo中新建Airport.java实体类
生成set()和get()方法
public class Airport {
private int id;
private String portName;
private String cityName;
}
5.用注解写查询,返回多个Airport(起飞和降落机场)
com.youdian.mapper包下写AirportMapper.java:
用distinct去除重复
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();
}
6.service层
接口:
public interface AirportService {
/**
* 显示所有起飞机场
* @return
*/
List<Airport> showTakePort();
/**
* 显示所有降落机场
* @return
*/
List<Airport> showLandPort();
}
再实现类之前先新建过滤器的包及类和封装mybatis的工具类:
@WebFilter("/*")
public class OpenSessionInView implements Filter {
public void init(FilterConfig filterconfig) throws ServletException {
// TODO Auto-generated method stub
}
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();
}
}
public void destroy() {
}
}
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);
}
}
实现类:
取到接口对象即可,并返回给servlet
public class AirportServiceImpl implements AirportService{
public List<Airport> showTakePort() {
SqlSession session = MyBatisUtil.getSession();
AirportMapper airportMapper = session.getMapper(AirportMapper.class);
return airportMapper.selTakePort();
}
public List<Airport> showLandPort() {
SqlSession session = MyBatisUtil.getSession();
AirportMapper airportMapper = session.getMapper(AirportMapper.class);
return airportMapper.selLandPort();
}
}
7.servlet层
ShowTakeServlet.java类:
@WebServlet("/showtake")
public class ShowTakeServlet extends HttpServlet{
private AirportService airService = new AirportServiceImpl();
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setAttribute("takeport", airService.showTakePort());
req.getRequestDispatcher("showland").forward(req, resp);
}
}
ShowLandServlet.java类:
@WebServlet("/showland")
public class ShowLandServlet extends HttpServlet {
private AirportService airportService = new AirportServiceImpl();
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setAttribute("landport", airportService.showLandPort());
req.getRequestDispatcher("showairplane").forward(req, resp);
}
}
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="take">
<option value="${take.id }">${take.portName }</option>
</c:forEach>
</select>
<input type="submit" value="查询"/>
</body>
</html>
至此,查询机场的起飞和降落功能实现
8.实现表格查询飞机各自信息
- pojo下新建Airplane.java
public class Airplane {
private int id;
private String airNo;
private int time;
private double price;
private Airport takePort;
private Airport landPort;
}
- 用动态查询需要有xml文件:(mapper)
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.youdian.mapper.AirplaneMapper">
<resultMap type="airplane" id="mymap">
<id column="id" property="id" />
<result column="time" property="time" />
<result column="price" property="price" />
<result column="airno" property="airNo"/>
<association property="takePort" javaType="airport">
<id column="takeid" property="id"/>
<result column="takecityname" property="cityName"/>
<result column="takeportname" property="portName" />
</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="mymap">
select a.*,p.id takeid,p.portname takeportname,p.cityname takecityname,t.id landid,t.portname landportname,t.cityname landcityname
from airplane a LEFT JOIN airport p on a.takeid=p.id
LEFT JOIN airport t on t.id=a.landid
<where>
<if test="takeid>0">
and takeid=#{takeid}
</if>
<if test="landid>0">
and landid=#{landid}
</if>
</where>
</select>
</mapper>
- service层
接口:
public interface AirplaneService {
List<Airplane> show(int takeid,int landid);
}
实现类:
public class AirplaneServiceImpl implements AirplaneService {
@Override
public List<Airplane> show(int takeid, int landid) {
return MyBatisUtil.getSession().getMapper(AirplaneMapper.class).selByTakeidLandid(takeid, landid);
}
}
- servlet层
ShowAirplaneServlet.java类:
@WebServlet("/showairplane")
public class ShowAirplaneServlet extends HttpServlet {
private AirplaneService airplaneService = new AirplaneServiceImpl();
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("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);
}
}
index.jsp中添加查询的动态查询
<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>