欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

航班查询管理系统

程序员文章站 2022-03-11 17:42:53
...

项目类型：java Web项目
实现语言：Java
环境要求：eclipse，JDK1.8.0_31，tomcat-8.5.31，MySQL8.0.18，jquery-3.4.1
功能要求：
1.建立数据表，创建主键，设置主键自增，添加测试数据
2.使用开发工具创建Web项目 Flight
3.为工程添加 lombok、DBUtils、 C3P0等支持
4.引入jar包
5.创建实体类
6.创建DAO组件，完成相关数据库操作，参考方法包括：
A.查询所有城市信息
B.根据起飞城市ID和到达城市ID查询航班信息，并根据起飞时间升序
7.创建业务层的 Service接口及其实现类。在实现类中注入DAO组件，并在类中实现业务逻辑和错误处理
8.创建Servlet类，为其注入业务组件，实现功能，并正确配置相关配置文件
9.创建首页面
A.完成页面设计，正确使用city表数据填充下拉列表，选项的value为城市ID，选项的文本为城市名称
B.为表单添加jQuery验证
C.通过Servlet查询符合条件的航班信息，结果按起飞时间升序排列，设置标题栏样式和隔行变色，查询条件会显在页面（在下拉列表中选中）；如果没有符合条件的数据对用户进行提示。显示航班信息时，起飞城市和到达城市必须显示城市名，不可直接显示外键字段的值。
代码实现：

项目结构：
航班查询管理系统
航班查询管理系统
数据库

/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 8.0.18 : Database - flight
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @aaa@qq.com@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @aaa@qq.com@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @aaa@qq.com@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @aaa@qq.com@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`flight` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `flight`;

/*Table structure for table `city` */

DROP TABLE IF EXISTS `city`;

CREATE TABLE `city` (
  `cityid` int(10) NOT NULL AUTO_INCREMENT,
  `cityname` varchar(20) NOT NULL,
  PRIMARY KEY (`cityid`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

/*Data for the table `city` */

insert  into `city`(`cityid`,`cityname`) values (1,'北京'),(2,'天津'),(3,'石家庄'),(4,'太原'),(5,'广州'),(6,'济南'),(7,'兰州'),(8,'成都'),(9,'沈阳'),(10,'长春'),(11,'哈尔滨');

/*Table structure for table `flight` */

DROP TABLE IF EXISTS `flight`;

CREATE TABLE `flight` (
  `flightid` int(10) NOT NULL AUTO_INCREMENT,
  `flightno` varchar(10) NOT NULL,
  `departurecity` int(10) NOT NULL,
  `departuretime` datetime NOT NULL,
  `arrivalcity` int(10) NOT NULL,
  `arrivaltime` datetime NOT NULL,
  PRIMARY KEY (`flightid`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

/*Data for the table `flight` */

insert  into `flight`(`flightid`,`flightno`,`departurecity`,`departuretime`,`arrivalcity`,`arrivaltime`) values (1,'A1234',1,'2020-01-27 13:39:32',2,'2020-01-27 14:39:49'),(2,'A1235',1,'2020-01-02 13:40:34',2,'2020-01-02 15:40:48'),(3,'A1236',1,'2020-01-28 15:41:19',2,'2020-02-01 17:41:27'),(4,'B1001',4,'2020-01-01 13:42:10',5,'2020-01-01 15:42:15'),(5,'C1001',6,'2020-01-30 13:42:40',7,'2020-01-29 16:42:45'),(6,'C1002',6,'2020-01-30 13:43:16',8,'2020-01-30 15:43:06'),(7,'D1001',9,'2020-01-06 14:43:44',10,'2020-01-06 17:43:51'),(8,'D1002',9,'2020-01-31 13:44:26',7,'2020-01-31 16:44:32');

/*!40101 SET aaa@qq.com_SQL_MODE */;
/*!40014 SET aaa@qq.com_FOREIGN_KEY_CHECKS */;
/*!40014 SET aaa@qq.com_UNIQUE_CHECKS */;
/*!40111 SET aaa@qq.com_SQL_NOTES */;

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans>
   <bean id="fservice" value="cn.kgc.tangcco.chenxiangjian.service.imp.FlightServiceImp"></bean>
   <bean id="fdao" value="cn.kgc.tangcco.chenxiangjian.dao.imp.FlightDaoImp"></bean>
</beans>

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<!-- 默认配置，当使用ComboPooledDataSource无参构造器时，使用的就是这个配置 -->
	<default-config>
		<!-- 基本配置 -->
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/flight?useSSL=false&amp;serverTimezone=UTC
		</property>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="user">root</property>
		<property name="password">******</property>
		<!-- 每次增量，当需要创建Connection对象时，一次创建几个 -->
		<property name="acquireIncrement">5</property>
		<!-- 当创建池对象后，池中应该有几个Connection对象 -->
		<property name="initialPoolSize">10</property>
		<!-- 池中最少Connection个数，如果少于这个值，就会创建Connection -->
		<property name="minPoolSize">2</property>
		<!-- 池中最大连接个数 -->
		<property name="maxPoolSize">50</property>
	</default-config>
	<!-- 命名配置，new ComboPooledDataSource("tangcco")时，使用的就是这个配置 -->
	<named-config name="tangcco">
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/mysql3</property>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="user">root</property>
		<property name="password">******</property>
		<property name="acquireIncrement">3</property>
		<property name="initialPoolSize">10</property>
		<property name="minPoolSize">2</property>
		<property name="maxPoolSize">10</property>
	</named-config>
</c3p0-config>

java代码

package cn.kgc.tangcco.chenxiangjian.bean;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@AllArgsConstructor
@NoArgsConstructor
@Data
public class City {
	private int cityid;// 城市ID
	private String cityname;// 城市名
}

package cn.kgc.tangcco.chenxiangjian.bean;

import java.util.Date;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@AllArgsConstructor
@NoArgsConstructor
@Data
public class Flight implements Comparable<Flight> {
	private int flightid;// 航班ID
	private String flightno;// 航班编号
	private int departurecity;// 起飞城市ID
	private String dCity;// 起飞城市名称
	private Date departuretime;// 起飞时间
	private int arrivalcity;// 到达城市ID
	private String aCity;// 到达城市名称
	private String arrivaltime;// 到达时间

	@Override
	// 创建比较器,起飞时间升序
	public int compareTo(Flight o) {
		int s = this.getDeparturetime().compareTo(o.getDeparturetime());
		return s;
	}
}


package cn.kgc.tangcco.chenxiangjian.dao.imp;

import java.util.List;

import org.junit.Test;

import cn.kgc.tangcco.chenxiangjian.bean.City;
import cn.kgc.tangcco.chenxiangjian.bean.Flight;
import cn.kgc.tangcco.chenxiangjian.dao.FlightDao;
import cn.kgc.tangcco.chenxiangjian.util.Dbutil;

public class FlightDaoImp extends Dbutil implements FlightDao {
	// 查询所有城市信息
	@Override
	public List<City> querycity() {
		String sql = "select * from city";
		List<City> list = query(City.class, sql);
		return list;
	}

	// 查询所有航班信息
	@Override
	public List<Flight> queryflight() {
		String sql = "SELECT f.flightno,c.cityname dCity,f.departureTime,d.cityname aCity,f.arrivalTime "
				+ "FROM flight f LEFT JOIN city c ON c.`cityid`=f.departureCity "
				+ "LEFT JOIN city d ON d.cityid=f.arrivalCity ORDER BY departureTime";
		List<Flight> list = query(Flight.class, sql);
		return list;
	}

	// 查询指定航班信息
	@Override
	public List<Flight> selectflight(String departurecity, String arrivalcity) {
		String sql = "SELECT f.flightid,f.flightno,c.cityname dCity,f.departureTime,d.cityname aCity,f.arrivalTime "
				+ "FROM flight f LEFT JOIN city c ON c.`cityid`=f.departureCity "
				+ "LEFT JOIN city d ON d.cityid=f.arrivalCity WHERE c.cityid=? AND d.cityid=? ORDER BY departureTime";
		List<Flight> list = query(Flight.class, sql, departurecity, arrivalcity);
		return list;
	}

}


package cn.kgc.tangcco.chenxiangjian.dao;

import java.util.List;

import cn.kgc.tangcco.chenxiangjian.bean.City;
import cn.kgc.tangcco.chenxiangjian.bean.Flight;

public interface FlightDao {

	// 查询所有城市信息
	List<City> querycity();

	// 查询所有航班信息
	List<Flight> queryflight();

	// 查询指定航班信息
	List<Flight> selectflight(String departurecity, String arrivalcity);

}

package cn.kgc.tangcco.chenxiangjian.service.imp;

import java.util.List;

import org.junit.Test;

import cn.kgc.tangcco.chenxiangjian.bean.City;
import cn.kgc.tangcco.chenxiangjian.bean.Flight;
import cn.kgc.tangcco.chenxiangjian.dao.FlightDao;
import cn.kgc.tangcco.chenxiangjian.dao.imp.FlightDaoImp;
import cn.kgc.tangcco.chenxiangjian.service.FlightService;
import cn.kgc.tangcco.chenxiangjian.util.Constant;
import cn.kgc.tangcco.chenxiangjian.util.ObjectFactory;

public class FlightServiceImp implements FlightService {
	private FlightDao flightDao = ObjectFactory.getInstance(Constant.BD);

	// 查询所有城市信息
	@Override
	public List<City> querycity() {
		return flightDao.querycity();
	}

	// 查询所有航班信息
	@Override
	public List<Flight> queryflight() {
		return flightDao.queryflight();
	}

	// 查询指定航班信息
	@Override
	public List<Flight> selectflight(String departurecity, String arrivalcity) {
		return flightDao.selectflight(departurecity, arrivalcity);
	}
}


package cn.kgc.tangcco.chenxiangjian.service;

import java.util.List;

import cn.kgc.tangcco.chenxiangjian.bean.City;
import cn.kgc.tangcco.chenxiangjian.bean.Flight;

public interface FlightService {

	// 查询所有城市信息
	List<City> querycity();

	// 查询所有航班信息
	List<Flight> queryflight();

	// 查询指定航班信息
	List<Flight> selectflight(String departurecity, String arrivalcity);

}

package cn.kgc.tangcco.chenxiangjian.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 cn.kgc.tangcco.chenxiangjian.bean.City;
import cn.kgc.tangcco.chenxiangjian.bean.Flight;
import cn.kgc.tangcco.chenxiangjian.service.FlightService;
import cn.kgc.tangcco.chenxiangjian.service.imp.FlightServiceImp;
import cn.kgc.tangcco.chenxiangjian.util.Constant;
import cn.kgc.tangcco.chenxiangjian.util.ObjectFactory;

@WebServlet("/queryflight.do") // 注解地址
public class QueryFlightService extends HttpServlet {
	// 实例化FlightService
	private FlightService flightService = ObjectFactory.getInstance(Constant.BS);

	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// 获得属性值（departurecity，arrivalcity，dCity，aCity）
		String departurecity = request.getParameter("departurecity");
		String arrivalcity = request.getParameter("arrivalcity");
		String dCity = request.getParameter("dCity");
		String aCity = request.getParameter("aCity");
		// 调用selectflight(departurecity, arrivalcity)，querycity()方法
		List<Flight> flightlist = flightService.selectflight(departurecity, arrivalcity);
		List<City> citylist = flightService.querycity();
		// 给request添加属性
		request.setAttribute("x", flightlist);
		request.setAttribute("d", departurecity);
		request.setAttribute("a", arrivalcity);
		request.setAttribute("y", citylist);
		// 自动转发到jsp页面---等同于自动超连接
		request.getRequestDispatcher("/queryflight.jsp").forward(request, response);
	}

}


package cn.kgc.tangcco.chenxiangjian.util;

import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

public class Constant {
	public static final String BS = "fservice";
	public static final String BD = "fdao";

	private static Map<String, String> map = new HashMap<String, String>();
	// 解析"/applicationContext.xml"文件，其key值和value值存入map集合
	static {
		try {
			SAXReader sax = new SAXReader();
			// 通过Constant.class文件的getResourceAsStream（）方法获得"/applicationContext.xml"目录的输入流
			InputStream in = Constant.class.getResourceAsStream("/applicationContext.xml");
			Document doc = sax.read(in);
			Element root = doc.getRootElement();
			List<Element> beans = root.elements();
			for (Element element : beans) {
				String key = element.attributeValue("id");
				String value = element.attributeValue("value");
				map.put(key, value);
			}
		} catch (DocumentException e) {
			e.printStackTrace();
		}

	}

	// 通过map的key值调用value值
	public static String getUrl(String key) {
		return map.get(key);
	}
}


package cn.kgc.tangcco.chenxiangjian.util;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.impl.NewPooledConnection;
import com.sun.jndi.url.corbaname.corbanameURLContextFactory;

public class Dbutil<T> {
	// 创建数据源
	private static ComboPooledDataSource ds = new ComboPooledDataSource();
	private static QueryRunner qr = new QueryRunner(ds);

	// 更新语句的方法
	public int modify(String sql, Object... objects) {
		int i = -1;
		try {
			i = qr.update(sql, objects);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}

	// 查询语句的方法
	public List<T> query(Class clazz, String sql, Object... objects) {
		List<T> arraylist = new ArrayList<T>();
		try {
			arraylist = qr.query(sql, new BeanListHandler<T>(clazz), objects);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return arraylist;
	}

}


package cn.kgc.tangcco.chenxiangjian.util;

import java.util.HashMap;
import java.util.Map;

import sun.security.jca.GetInstance;

public class ObjectFactory {

	private static Map<String, Object> map = new HashMap<String, Object>();

	public static <T> T getInstance(String key) {
		T obj = null;
		// 判断map的键名中是否包含参数key，如果包含通过参数key获取map中对应的键值,就是该对象
		if (map.containsKey(key)) {
			obj = (T) map.get(key);
		} else {
			Class clazz;
			try {
				clazz = Class.forName(Constant.getUrl(key));
				// 用反射实例类的对象
				obj = (T) clazz.newInstance();
				// 把参数key和对象存放给map
				map.put(key, obj);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return obj;
	}

}


package cn.kgc.tangcco.chenxiangjian.util;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class Utils {
	private static SimpleDateFormat fm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	// 时间转字符串
	public static String datetoString(Date date) {
		String value = "";
		value = fm.format(date);
		return value;
	}

	// 字符串转时间
	public static Date stringtoDate(String value) {
		Date date = null;
		try {
			date = fm.parse(value);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return date;
	}
}

jsp代码

<%@page import="cn.kgc.tangcco.chenxiangjian.bean.Flight"%>
<%@page import="org.apache.jasper.tagplugins.jstl.core.ForEach"%>
<%@page import="cn.kgc.tangcco.chenxiangjian.bean.City"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>航班查询</title>
<script type="text/javascript" src="jquery-3.4.1.js"></script>
<style type="text/css">
td {
	text-align: center;
}
</style>
</head>
<body>
	<center>
		<h2>航班查询页面</h2>
		<form action="queryflight.do">
			起飞城市：<select name="departurecity">
				<option value="0">请选择起飞城市</option>
				<%
					String departurecity = (String) request.getAttribute("d");
					List<City> cityList = (List<City>) request.getAttribute("y");
					for (City city : cityList) {
				%>
				<option value="<%=city.getCityid()%>"
					<%=(city.getCityid() + "").equals(departurecity) ? "selected" : ""%>><%=city.getCityname()%></option>
				<%
					}
				%>
			</select> ----到达城市：<select name="arrivalcity">
				<option value="0">请选择到达城市</option>
				<%
					String arrivalcity = (String) request.getAttribute("a");
					List<City> cityList2 = (List<City>) request.getAttribute("y");
					for (City city2 : cityList2) {
				%>
				<option value="<%=city2.getCityid()%>"
					<%=(city2.getCityid() + "").equals(arrivalcity) ? "selected" : ""%>><%=city2.getCityname()%></option>
				<%
					}
				%>
			</select> <input type="submit" value="查询" onclick="return test()">
		</form>
		<script type="text/javascript">
			function test() {
				var vd = $("[name=departurecity]").val();
				var va = $("[name=arrivalcity]").val();
				if (vd == 0) {
					alert('请选择起飞城市');
					return false;
				}
				if (va == 0) {
					alert('请选择到达城市');
					return false;
				}
				if (vd == va) {
					alert('起飞城市和到达城市不能相同');
					return false;
				} else {
					return true;
				}
			}
		</script>
		<hr>
		<%
			List<Flight> flights = (List<Flight>) request.getAttribute("x");
			if (flights != null & flights.size() != 0) {
		%>
		<h3>帮您找到以下航班信息</h3>
		<table border="0" width="80%" height="200px">
			<tr>
				<th>航班编号</th>
				<th>起飞城市</th>
				<th>起飞时间</th>
				<th>到达城市</th>
				<th>到达时间</th>
			</tr>

			<%
				for (Flight flight : flights) {
			%>
			<tr>
				<td><%=flight.getFlightno()%></td>
				<td><%=flight.getDCity()%></td>
				<td><%=flight.getDeparturetime()%></td>
				<td><%=flight.getFlightno()%></td>
				<td><%=flight.getACity()%></td>
			</tr>
			<%
				}
			%>
		</table>
		<%
			} else {
				out.print("<h2>没有找到符合条件的航班，请改变查询条件重试</h2>");
			}
		%>
		<script type="text/javascript">
			$(function() {
				$("table tr:odd").css("background-color", "gray");
			})
		</script>
	</center>
</body>
</html>
相关标签： 学习记录

上一篇： css中背景图怎么设置平铺方式

下一篇： css input大小怎么设置

推荐阅读