用mybatis注解和多表联合查询完成小系统

最新推荐文章于 2023-06-21 09:22:36 发布

Re_view

最新推荐文章于 2023-06-21 09:22:36 发布

阅读量422

点赞数

分类专栏： MyBatis 文章标签：用mybatis注解和多表联合查询

本文链接：https://blog.csdn.net/Re_view/article/details/99837782

版权

MyBatis 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

飞机起飞系统

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>

Re_view

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
用mybatis注解和多表联合查询完成小系统

飞机起飞系统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(2...
复制链接

扫一扫