任务概述
有一家航空公司为了提高用户体验,希望做一个航班信息系统,用户可以根据需求去对航班信息进行操作:
- 显示航班信息系统主菜单
- 查询所有的航班信息
- 按起飞时间查询
- 按目的地查询
- 删除航班
- 更新航班
- 退出系统
具体需求
通过java代码操作数据库,用户可以在控制台做对应的操作
涉及知识点
任务过程
1、创建数据库表 airinfo,添加测试数据,主键自增
2、新建对象包pojo,创建实体类 AirInfo,根据业务提供需要的构造方法和 setter/getter方法
3、新建工具包utils,创建 DBUtils 类,封装数据库操作方法,实现数据库连接和关闭功能
4、新建异常包exception,创建自定义异常类OutNumberBoundException
5、新建视图包view,创建AirInfoView类,用于提示用户输入及输出
6、新建数据操作包dao,创建 AirInfoDao接口,定义查询所有航班、按起飞时间和目的地查询航班、删除航班、更新航班的方法
7、在数据操作包dao中,创建、AirInfoDaoImpl类,继承DBUtils类、实现AirInfoDao接口,使用 JDBC 完成相应数据库操作
8、新建测试包test,创建Platform类,完成在控制台显示留言信息和用户添加留言操作,启动和运行系统
思路及代码实现
一、 工具包
创建一个utils包,新建类DBUtils,新建配置文件db.properties
properties配置文件
使用ResourceBundle访问本地资源,从里面读取我们需要的值
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT
username=root
password=123456
DBUtils
JDBC工具类,封装了操作sql的方法,因此方法都推荐静态static
-
变量定义
//定义需要的工具类对象(变量定义)
protected static Connection connection = null;
protected static PreparedStatement pps = null;//后续都是用预状态通道来实现
protected static ResultSet rs = null;//结果集
protected static int count = 0;//受影响的行数
//登录的用户名和密码
private static String username;
private static String password;
private static String url;
private static String driverName;
//Druid连接池
private static DruidDataSource druidDataSource = new DruidDataSource();
-
加载驱动
//加载驱动
static {
//Druid
ResourceBundle bundle = ResourceBundle.getBundle("db");//参数只写属性文件名,不需要写后缀
//加载属性文件
driverName = bundle.getString("driver");
url = bundle.getString("url");
username = bundle.getString("username");
password = bundle.getString("password");
druidDataSource.setUsername(username);
druidDataSource.setPassword(password);
druidDataSource.setUrl(url);
druidDataSource.setDriverClassName(driverName);
}
-
获得链接
protected static Connection getConnection() {
try {
connection = druidDataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
-
得到预状态通道并绑定参数
//得到预状态通道
protected static PreparedStatement getPps(String sql) {
try {
getConnection();
pps = connection.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pps;
}
//绑定参数,给占位符赋值,list中保存的是给占位符所赋的值
private static void setParams(List list) {
try {
if (list != null && list.size() > 0) {//集合中有内容
for (int i = 0; i < list.size(); i++) {
pps.setObject(i + 1, list.get(i));//赋值,位置从1开始所以为i+1
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
-
增删改
protected static int update(String sql, List list) {
try {
getPps(sql);//得到预状态通道
setParams(list);//绑定参数
count = pps.executeUpdate();//pps.executeUpdate()执行sql语句,返回受影响的行数
} catch (SQLException e) {
e.printStackTrace();
}
return count;//返回受影响的行数
}
-
数据查询
protected static ResultSet query(String sql, List list) {
try {
getPps(sql);//得到预状态通道
setParams(list);//绑定参数
rs = pps.executeQuery();//pps.executeUpdate()执行sql语句,返回结果集
return rs;//返回结果集
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
-
关闭资源
protected static void closeAll() {
try {
if (rs != null) {
rs.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
二、 自定义异常
创建一个exception包,新建类OutNumberBoundException
OutNumberBoundException
public class OutNumberBoundException extends Throwable {
public OutNumberBoundException(String s) {
super(s);
}
}
三、 对象
创建一个pojo包,新建类AirInfo
AirInfo类对应的数据表airinfo,因此
类属性名 = 表字段名
airinfo表如下:
AirInfo
-
定义属性并用setter和getter设置和获取值
private Integer number;
private String airid;
private String address;
private Date flydate;
public Integer getNumber() {
return number;
}
public void setNumber(Integer number) {
this.number = number;
}
public String getAirId() {
return airid;
}
public void setAirId(String airid) {
this.airid = airid;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Date getFlyDate() {
return flydate;
}
public void setFlyDate(Date flydate) {
this.flydate = flydate;
}
-
定义无参和全参构造方法
public AirInfo() {
}
public AirInfo(Integer number, String airid, String address, Date flydate) {
this.number = number;
this.airid = airid;
this.address = address;
this.flydate = flydate;
}
-
重写toString()方法将信息转换为字符串
@Override
public String toString() {
return number + " " + '\t' + airid + " " + '\t' + address + " " + '\t' + flydate;
}
四、 视图分析
主要负责数据输入输出模块
创建一个view包,新建类AirInfoView
AirInfoView
-
进入系统界面主菜单
public static int mainMenu() {
int mainNum = 0;
do {
System.out.println("----------欢迎使用航班信息管理系统----------");
System.out.println("请选择操作:");
System.out.println("1.查询所有航班信息");
System.out.println("2.按起飞时间查询航班");
System.out.println("3.按目的地查询航班");
System.out.println("4.删除航班");
System.out.println("5.更新航班");
System.out.println("0.退出系统");
String s = input.nextLine();
try {
mainNum = AirInfoDaoImpl.validNum(s, 0, 5);//判断输入是否有效
break;
} catch (NumberFormatException | OutNumberBoundException e) {
System.out.println(e.getMessage());
}
} while (true);
return mainNum;
}
-
查询所有航班信息
/**
* 查询所有的航班信息
*/
public static void printAllAir(List<AirInfo> airInfoList) {
System.out.println("航班信息如下:");
System.out.println("编号\t航班号 \t目的地\t\t起飞日期");
for (AirInfo airInfo : airInfoList) {
printAirInfo(airInfo);//打印单条航班信息
}
}
/**
* 打印单条航班信息
*/
public static void printAirInfo(AirInfo a) {
System.out.println(a.toString());
}
-
按起飞时间查询航班
public static String findByDate() {
System.out.print("请输入起飞时间(form:year-month-day):");
String s = input.nextLine();
return s;
}
-
按目的地查询航班
public static String findByAddress() {
System.out.print("请输入航班的目的地:");
String s = input.nextLine();
return s;
}
-
删除航班
/**
* (根据航班号)删除航班
*/
public static String deleteByAirId() {
System.out.print("请输入要删除航班的航班号:");
String s = input.nextLine();
return s;
}
/**
* 是否删除航班
*/
public static int isDelete() {
int num = 0;
do {
System.out.println("是否确认删除?");
System.out.println("1.确认删除");
System.out.println("0.取消操作");
String s = input.nextLine();
try {
num = AirInfoDaoImpl.validNum(s, 0, 1);
break;
} catch (NumberFormatException | OutNumberBoundException e) {
System.out.println(e.getMessage());
}
} while (true);
return num;
}
-
更新航班
/**
* (根据编号)更新航班
*/
public static int updateByNumber() {
System.out.print("请输入要更新航班的编号:");
String s = input.nextLine();
int num = Integer.parseInt(s);
return num;
}
/**
* 输入新的航班目的地
*/
public String updateAddress() {
System.out.print("请输入新的航班目的地:");
String s = input.nextLine();
return s;
}
/**
* 输入新的航班日期
*/
public String updateDate() {
System.out.print("请输入新的航班起飞时间:");
String s = input.nextLine();
return s;
}
-
进入系统与退出系统
/**
* 进入系统
*/
public static void welcome() {
System.out.println("欢迎进入航班信息管理系统!");
}
/**
* 退出系统
*/
public static void bye() {
System.out.println("感谢使用航班信息管理系统!");
}
五、 数据存取
主要负责数据处理的模块
创建一个dao包,新建 AirInfoDao接口,新建AirInfoDaoImpl类继承DBUtils类、实现AirInfoDao接口,使用 JDBC 完成相应数据库操作
AirInfoDao
定义查询所有航班、按起飞时间和目的地查询航班、删除航班、更新航班的
抽象方法
-
查询所有航班信息
List<AirInfo> getAirList();
-
按起飞时间查询航班
List<AirInfo> findByDate(String date);
-
按目的地查询航班
List<AirInfo> findByAddress(String address);
-
删除航班
int deleteByAirId(String airid);
-
更新航班
int updateAirInfo(int num, String newAddress, String newDate);
AirInfoDaoImpl
具体实现
接口中定义的抽象方法
-
判断输入是否为数字、是否在有效范围内
public static int validNum(String s, int begin, int end) throws NumberFormatException, OutNumberBoundException {
try {
int num = Integer.parseInt(s);
if (num < begin || num > end) {
throw new OutNumberBoundException("数字的范围必须在" + begin + "和" + end + "之间");
}
return num;
} catch (NumberFormatException e) {
throw new NumberFormatException("输入的必须是数字!");
}
}
-
查询所有航班信息
@Override
public List<AirInfo> getAirList() {
ArrayList arrayList = new ArrayList();
try {
String sql = "select* from airinfo ";
ResultSet resultSet = query(sql, null);
while (resultSet.next()) {
AirInfo info = new AirInfo();
info.setNumber(resultSet.getInt("number"));
info.setAirId(resultSet.getString("airid"));
info.setAddress(resultSet.getString("address"));
info.setFlyDate(resultSet.getDate("flydate"));
arrayList.add(info);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
closeAll();
}
return arrayList;
}
-
按起飞时间查询航班
@Override
public List<AirInfo> findByDate(String date) {
ArrayList arrayList = new ArrayList();
try {
String sql = "select* from airinfo where flydate = ?";
ArrayList param = new ArrayList();
param.add(date);
ResultSet resultSet = query(sql, param);
if (resultSet.next() == false) {//查询结果为空
return null;
} else {//查询结果非空
do {
AirInfo info = new AirInfo();
info.setNumber(resultSet.getInt("number"));
info.setAirId(resultSet.getString("airid"));
info.setAddress(resultSet.getString("address"));
info.setFlyDate(resultSet.getDate("flydate"));
arrayList.add(info);
} while (resultSet.next());
}//end if
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
closeAll();
}
return arrayList;
}
-
按目的地查询航班
@Override
public List<AirInfo> findByAddress(String address) {
ArrayList arrayList = new ArrayList();
try {
String sql = "select* from airinfo where address like ?";
ArrayList param = new ArrayList();
param.add("%" + address + "%");
ResultSet resultSet = query(sql, param);
if (resultSet.next() == false) {//查询结果为空
return null;
} else {//查询结果非空
do {
AirInfo info = new AirInfo();
info.setNumber(resultSet.getInt("number"));
info.setAirId(resultSet.getString("airid"));
info.setAddress(resultSet.getString("address"));
info.setFlyDate(resultSet.getDate("flydate"));
arrayList.add(info);
} while (resultSet.next());
}//end if
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
closeAll();
}
return arrayList;
}
-
删除航班
/**
* (根据航班号)删除航班
*/
@Override
public int deleteByAirId(String airid) {
int count = 0;
try {
String sql = "delete from airinfo where airid = ?";
ArrayList param = new ArrayList();
param.add(airid);
count = update(sql, param);
} finally {
closeAll();
}
return count;//返回受影响的行数
}
/**
* (根据航班号)查询航班是否存在
* 可能该航班存在多个时间点,因此用list存储查找到的信息
*/
public List<AirInfo> findById(String airid) {
ArrayList arrayList = new ArrayList();
try {
String sql = "select* from airinfo where airid = ?";
ArrayList param = new ArrayList();
param.add(airid);
ResultSet resultSet = query(sql, param);
if (resultSet.next() == false) {//查询结果为空
return null;
} else {//查询结果非空
do {
AirInfo info = new AirInfo();
info.setNumber(resultSet.getInt("number"));
info.setAirId(resultSet.getString("airid"));
info.setAddress(resultSet.getString("address"));
info.setFlyDate(resultSet.getDate("flydate"));
arrayList.add(info);
} while (resultSet.next());
}//end if
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
closeAll();
}
return arrayList;
}
-
更新航班
/**
* (根据编号)更新航班
*/
@Override
public int updateAirInfo(int num, String newAddress, String newDate) {
int count = 0;
try {
String sql = "update airinfo set address = ? ,flydate = ? where number = ?";
ArrayList param = new ArrayList();
param.add(newAddress);
param.add(newDate);
param.add(num);
count = update(sql, param);
} finally {
closeAll();
}
return count;//返回受影响的行数
}
/**
* 根据编号查找航班是否存在
*/
public AirInfo findByNumber(int number) {
AirInfo info = new AirInfo();
try {
String sql = "select* from airinfo where number = ?";
ArrayList param = new ArrayList();
param.add(number);
ResultSet resultSet = query(sql, param);
if (resultSet.next() == false) {//查询结果为空
return null;
} else {//查询结果非空
info.setNumber(resultSet.getInt("number"));
info.setAirId(resultSet.getString("airid"));
info.setAddress(resultSet.getString("address"));
info.setFlyDate(resultSet.getDate("flydate"));
}//end if
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
closeAll();
}
return info;
}
六、 主界面
创建一个test包,新建类Platform
Platform
-
使用系统主菜单进入相应功能界面
public static void main(String[] args) {
v.welcome();//进入界面
//调用主菜单进入相应的功能界面
m:
while (true) {
int mainNum = v.mainMenu();//调用主菜单
switch (mainNum) {
case 0://结束使用
break m;
case 1://查询所有的航班信息
printAllAir();
break;
case 2://按起飞时间查询
findByDate();
break;
case 3://按目的地查询
findByAddress();
break;
case 4://(根据航班号)删除航班
deleteByAirId();
break;
case 5://(根据航班号)更新航班
updateAirInfo();
break;
}
}// end while
v.bye();//退出系统
}//end main
-
查询所有航班信息
public static void printAllAir() {
v.printAllAir(dao.getAirList());
}
-
按起飞时间查询航班
public static void findByDate() {
String date = v.findByDate();
List<AirInfo> infos = dao.findByDate(date);
if (infos != null) {
v.printAllAir(infos);
} else {
System.out.println("该时间暂未航班信息!");
}
}
-
按目的地查询航班
public static void findByAddress() {
String address = v.findByAddress();
List<AirInfo> infos = dao.findByAddress(address);
if (infos != null) {
v.printAllAir(infos);
} else {
System.out.println("暂未到达该目的地的航班信息!");
}
}
-
(根据航班号)删除航班
public static void deleteByAirId() {
String airId = v.deleteByAirId();
List<AirInfo> infos = dao.findById(airId);
if (infos != null) {
v.printAllAir(infos);
if (v.isDelete() == 1) {
int count = dao.deleteByAirId(airId);
System.out.println(count + "行受影响,删除成功!");
} else {
System.out.println("取消成功!");
}
} else {
System.out.println("该航班不存在!");
}
}
-
(根据编号)更新航班
public static void updateAirInfo() {
int num = v.updateByNumber();//输入要更新的航班的编号
AirInfo info = dao.findByNumber(num);
if (info != null) {
System.out.println("要更新的航班信息如下:");
v.printAirInfo(info);//打印所有该航班号的航班信息
String newAddress = v.updateAddress();
String newDate = v.updateDate();
dao.updateAirInfo(num, newAddress, newDate);
System.out.println("更新成功!");
} else {
System.out.println("暂未此航班号的航班信息!");
}
}
完整代码如下
一、 工具包
properties配置文件
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT
username=root
password=123456
DBUtils
public class DBUtils {
//1.定义需要的工具类对象(变量定义)
protected static Connection connection = null;
protected static PreparedStatement pps = null;//后续都是用预状态通道来实现
protected static ResultSet rs = null;//结果集
protected static int count = 0;//受影响的行数
//登录的用户名和密码
private static String username;
private static String password;
private static String url;
private static String driverName;
//Druid
private static DruidDataSource druidDataSource = new DruidDataSource();
//2.加载驱动
static {
//Druid
ResourceBundle bundle = ResourceBundle.getBundle("db");//参数只写属性文件名,不需要写后缀
//加载属性文件
driverName = bundle.getString("driver");
url = bundle.getString("url");
username = bundle.getString("username");
password = bundle.getString("password");
druidDataSource.setUsername(username);
druidDataSource.setPassword(password);
druidDataSource.setUrl(url);
druidDataSource.setDriverClassName(driverName);
}
//3.获得连接
protected static Connection getConnection() {
try {
connection = druidDataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//4.得到预状态通道
protected static PreparedStatement getPps(String sql) {
try {
getConnection();//insert into users values(?,?,?,?,)
pps = connection.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pps;
}
//5.绑定参数,给占位符赋值,list中保存的是给占位符所赋的值
private static void setParams(List list) {
try {
if (list != null && list.size() > 0) {//集合中有内容
for (int i = 0; i < list.size(); i++) {
pps.setObject(i + 1, list.get(i));//赋值,位置从1开始所以为i+1
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//6.增删改
protected static int update(String sql, List list) {
try {
getPps(sql);//得到预状态通道
setParams(list);//绑定参数
count = pps.executeUpdate();//pps.executeUpdate()执行sql语句,返回受影响的行数
} catch (SQLException e) {
e.printStackTrace();
}
return count;//返回受影响的行数
}
//7.查询
protected static ResultSet query(String sql, List list) {
try {
getPps(sql);//得到预状态通道
setParams(list);//绑定参数
rs = pps.executeQuery();//pps.executeUpdate()执行sql语句,返回结果集
return rs;//返回结果集
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//8.关闭资源
protected static void closeAll() {
try {
if (rs != null) {
rs.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
二、 自定义异常
OutNumberBoundException
public class OutNumberBoundException extends Throwable {
public OutNumberBoundException(String s) {
super(s);
}
}
三、 对象
AirInfo
public class AirInfo {
private Integer number;
private String airid;
private String address;
private Date flydate;
public AirInfo() {
}
public AirInfo(Integer number, String airid, String address, Date flydate) {
this.number = number;
this.airid = airid;
this.address = address;
this.flydate = flydate;
}
public Integer getNumber() {
return number;
}
public void setNumber(Integer number) {
this.number = number;
}
public String getAirId() {
return airid;
}
public void setAirId(String airid) {
this.airid = airid;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Date getFlyDate() {
return flydate;
}
public void setFlyDate(Date flydate) {
this.flydate = flydate;
}
@Override
public String toString() {
return number + " " + '\t' + airid + " " + '\t' + address + " " + '\t' + flydate;
}
}
四、 视图分析
AirInfoView
public class AirInfoView {
public static Scanner input = new Scanner(System.in);
/**
* 进入系统
*/
public static void welcome() {
System.out.println("欢迎进入航班信息管理系统!");
}
/**
* 主菜单,系统界面
*/
public static int mainMenu() {
int mainNum = 0;
do {
System.out.println("----------欢迎使用航班信息管理系统----------");
System.out.println("请选择操作:");
System.out.println("1.查询所有航班信息");
System.out.println("2.按起飞时间查询航班");
System.out.println("3.按目的地查询航班");
System.out.println("4.删除航班");
System.out.println("5.更新航班");
System.out.println("0.退出系统");
String s = input.nextLine();
try {
mainNum = AirInfoDaoImpl.validNum(s, 0, 5);
break;
} catch (NumberFormatException | OutNumberBoundException e) {
System.out.println(e.getMessage());
}
} while (true);
return mainNum;
}
/**
* 1、查询所有的航班信息
*/
public static void printAllAir(List<AirInfo> airInfoList) {
System.out.println("航班信息如下:");
System.out.println("编号\t航班号 \t目的地\t\t起飞日期");
for (AirInfo airInfo : airInfoList) {
printAirInfo(airInfo);
}
}
/**
* 打印单条航班信息
*/
public static void printAirInfo(AirInfo a) {
System.out.println(a.toString());
}
/**
* 2、按起飞时间查询
*/
public static String findByDate() {
System.out.print("请输入起飞时间(form:year-month-day):");
String s = input.nextLine();
return s;
}
/**
* 3、按目的地查询
*/
public static String findByAddress() {
System.out.print("请输入航班的目的地:");
String s = input.nextLine();
return s;
}
/**
* 4、(根据航班号)删除航班
*/
public static String deleteByAirId() {
System.out.print("请输入要删除航班的航班号:");
String s = input.nextLine();
return s;
}
/**
* 是否删除航班
*/
public static int isDelete() {
int num = 0;
do {
System.out.println("是否确认删除?");
System.out.println("1.确认删除");
System.out.println("0.取消操作");
String s = input.nextLine();
try {
num = AirInfoDaoImpl.validNum(s, 0, 1);
break;
} catch (NumberFormatException | OutNumberBoundException e) {
System.out.println(e.getMessage());
}
} while (true);
return num;
}
/**
* 5、根据编号更新航班
*/
public static int updateByNumber() {
System.out.print("请输入要更新航班的编号:");
String s = input.nextLine();
int num = Integer.parseInt(s);
return num;
}
/**
* 输入新的航班目的地
*/
public String updateAddress() {
System.out.print("请输入新的航班目的地:");
String s = input.nextLine();
return s;
}
/**
* 输入新的航班日期
*/
public String updateDate() {
System.out.print("请输入新的航班起飞时间:");
String s = input.nextLine();
return s;
}
/**
* 退出系统
*/
public static void bye() {
System.out.println("感谢使用航班信息管理系统!");
}
}
五、 数据存取
AirInfoDao
/**
* 定义查询所有航班,按日期和目的地查询航班,删除航班,更新航班的方法
*/
public interface AirInfoDao {
/**
* 1、查询所有的航班信息
*/
List<AirInfo> getAirList();
/**
* 2、按起飞时间查询
*/
List<AirInfo> findByDate(String date);
/**
* 3、按目的地查询
*/
List<AirInfo> findByAddress(String address);
/**
* 4、(根据航班号)删除航班
*/
int deleteByAirId(String airid);
/**
* 5、更新航班
*/
int updateAirInfo(int num, String newAddress, String newDate);
}
AirInfoImpl
/**
* 使用 JDBC 完成相应数据库操作
*/
public class AirInfoDaoImpl extends DBUtils implements AirInfoDao {
/**
* 判断输入是否为数字、是否在有效范围内
*/
public static int validNum(String s, int begin, int end) throws NumberFormatException, OutNumberBoundException {
try {
int num = Integer.parseInt(s);
if (num < begin || num > end) {
throw new OutNumberBoundException("数字的范围必须在" + begin + "和" + end + "之间");
}
return num;
} catch (NumberFormatException e) {
throw new NumberFormatException("输入的必须是数字!");
}
}
/**
* 1、查询所有的航班信息
*/
@Override
public List<AirInfo> getAirList() {
ArrayList arrayList = new ArrayList();
try {
String sql = "select* from airinfo ";
ResultSet resultSet = query(sql, null);
while (resultSet.next()) {
AirInfo info = new AirInfo();
info.setNumber(resultSet.getInt("number"));
info.setAirId(resultSet.getString("airid"));
info.setAddress(resultSet.getString("address"));
info.setFlyDate(resultSet.getDate("flydate"));
arrayList.add(info);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
closeAll();
}
return arrayList;
}
/**
* 2、按起飞时间查询
*/
@Override
public List<AirInfo> findByDate(String date) {
ArrayList arrayList = new ArrayList();
try {
String sql = "select* from airinfo where flydate = ?";
ArrayList param = new ArrayList();
param.add(date);
ResultSet resultSet = query(sql, param);
if (resultSet.next() == false) {//查询结果为空
return null;
} else {//查询结果非空
do {
AirInfo info = new AirInfo();
info.setNumber(resultSet.getInt("number"));
info.setAirId(resultSet.getString("airid"));
info.setAddress(resultSet.getString("address"));
info.setFlyDate(resultSet.getDate("flydate"));
arrayList.add(info);
} while (resultSet.next());
}//end if
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
closeAll();
}
return arrayList;
}
/**
* 3、按目的地查询
*/
@Override
public List<AirInfo> findByAddress(String address) {
ArrayList arrayList = new ArrayList();
try {
String sql = "select* from airinfo where address like ?";
ArrayList param = new ArrayList();
param.add("%" + address + "%");
ResultSet resultSet = query(sql, param);
if (resultSet.next() == false) {//查询结果为空
return null;
} else {//查询结果非空
do {
AirInfo info = new AirInfo();
info.setNumber(resultSet.getInt("number"));
info.setAirId(resultSet.getString("airid"));
info.setAddress(resultSet.getString("address"));
info.setFlyDate(resultSet.getDate("flydate"));
arrayList.add(info);
} while (resultSet.next());
}//end if
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
closeAll();
}
return arrayList;
}
/**
* 4、(根据航班号)删除航班
*/
@Override
public int deleteByAirId(String airid) {
int count = 0;
try {
String sql = "delete from airinfo where airid = ?";
ArrayList param = new ArrayList();
param.add(airid);
count = update(sql, param);
} finally {
closeAll();
}
return count;//返回受影响的行数
}
/**
* (根据航班号)查询航班是否存在
* 可能该航班存在多个时间点,因此用list存储查找到的信息
*/
public List<AirInfo> findById(String airid) {
ArrayList arrayList = new ArrayList();
try {
String sql = "select* from airinfo where airid = ?";
ArrayList param = new ArrayList();
param.add(airid);
ResultSet resultSet = query(sql, param);
if (resultSet.next() == false) {//查询结果为空
return null;
} else {//查询结果非空
do {
AirInfo info = new AirInfo();
info.setNumber(resultSet.getInt("number"));
info.setAirId(resultSet.getString("airid"));
info.setAddress(resultSet.getString("address"));
info.setFlyDate(resultSet.getDate("flydate"));
arrayList.add(info);
} while (resultSet.next());
}//end if
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
closeAll();
}
return arrayList;
}
/**
* 5、(根据编号)更新航班
*/
@Override
public int updateAirInfo(int num, String newAddress, String newDate) {
int count = 0;
try {
String sql = "update airinfo set address = ? ,flydate = ? where number = ?";
ArrayList param = new ArrayList();
param.add(newAddress);
param.add(newDate);
param.add(num);
count = update(sql, param);
} finally {
closeAll();
}
return count;//返回受影响的行数
}
/**
* 根据编号查找航班是否存在
*/
public AirInfo findByNumber(int number) {
AirInfo info = new AirInfo();
try {
String sql = "select* from airinfo where number = ?";
ArrayList param = new ArrayList();
param.add(number);
ResultSet resultSet = query(sql, param);
if (resultSet.next() == false) {//查询结果为空
return null;
} else {//查询结果非空
info.setNumber(resultSet.getInt("number"));
info.setAirId(resultSet.getString("airid"));
info.setAddress(resultSet.getString("address"));
info.setFlyDate(resultSet.getDate("flydate"));
}//end if
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
closeAll();
}
return info;
}
}
六、 主界面
Platform
public class Platform {
public static AirInfoView v = new AirInfoView();
public static AirInfoDaoImpl dao = new AirInfoDaoImpl();
public static Scanner input = new Scanner(System.in);
public static void main(String[] args) {
v.welcome();
//调用主菜单进入相应的功能界面
m:
while (true) {
int mainNum = v.mainMenu();//调用主菜单
switch (mainNum) {
case 0://结束使用
break m;
case 1://查询所有的航班信息
printAllAir();
break;
case 2://按起飞时间查询
findByDate();
break;
case 3://按目的地查询
findByAddress();
break;
case 4://(根据航班号)删除航班
deleteByAirId();
break;
case 5://(根据航班号)更新航班
updateAirInfo();
break;
}
}// end while
v.bye();
}//end main
/**
* 1、查询所有航班信息
*/
public static void printAllAir() {
v.printAllAir(dao.getAirList());
}
/**
* 2、按起飞时间查询
*/
public static void findByDate() {
String date = v.findByDate();
List<AirInfo> infos = dao.findByDate(date);
if (infos != null) {
v.printAllAir(infos);
} else {
System.out.println("该时间暂未航班信息!");
}
}
/**
* 3、按目的地查询
*/
public static void findByAddress() {
String address = v.findByAddress();
List<AirInfo> infos = dao.findByAddress(address);
if (infos != null) {
v.printAllAir(infos);
} else {
System.out.println("暂未到达该目的地的航班信息!");
}
}
/**
* 4、(根据航班号)删除航班
*/
public static void deleteByAirId() {
String airId = v.deleteByAirId();
List<AirInfo> infos = dao.findById(airId);
if (infos != null) {
v.printAllAir(infos);
if (v.isDelete() == 1) {
int count = dao.deleteByAirId(airId);
System.out.println(count + "行受影响,删除成功!");
} else {
System.out.println("取消成功!");
}
} else {
System.out.println("该航班不存在!");
}
}
/**
* 5、更新航班(由于编号唯一,因此根据编号更新)
*/
public static void updateAirInfo() {
int num = v.updateByNumber();//输入要更新的航班的编号
AirInfo info = dao.findByNumber(num);
if (info != null) {
System.out.println("要更新的航班信息如下:");
v.printAirInfo(info);//打印所有该航班号的航班信息
String newAddress = v.updateAddress();
String newDate = v.updateDate();
dao.updateAirInfo(num, newAddress, newDate);
System.out.println("更新成功!");
} else {
System.out.println("暂未此航班号的航班信息!");
}
}
}//end class