目录
任务概述
现在有一家航空公司为了提高用户体验，希望做一个航班信息系统，用户 可以根据需求去对航班信息进行操作。组长把这个任务安排给了程序员赵丹， 赵丹发现这里需要通过 java 代码操作数据库，并且用户是可以在控制台做对 应的操作，JDBC 可以帮她解决这个问题。学习起来，试着把这个系统实现出来。
模块分析
数据库操作
代码实现
db.properties文件配置
driver=com.mysql.cj.jdbc.Driver
name=root
pass=123456
url=jdbc:mysql://localhost:3306/air?serverTimezone=UTC导入jar包
AirInfo类
package bean;
import java.util.Date;
/**
* 实体类
*/
public class AirInfo {
private int id; //编号
private String number; //航班号
private String destination; //目的地
private Date date; //起飞日期
public AirInfo() {
}
public AirInfo(int id, String number, String destination, Date date) {
this.id = id;
this.number = number;
this.destination = destination;
this.date = date;
}
@Override
public String toString() {
return id +" "+'\t'+'\t'+ number +" "+'\t'+ destination +" "+'\t'+'\t'+ date;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public String getDestination() {
return destination;
}
public void setDestination(String destination) {
this.destination = destination;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
}
AirInfoDao接口
package dao;
import bean.AirInfo;
import java.util.List;
public interface AirInfoDao {
//列出所有航班
public List<AirInfo> getAll();
//按起飞时间查询
public List<AirInfo> getByDate(String date);
//按目的地查询
public List<AirInfo> getByDestination(String destination);
//删除航班
public int delete(String number);
//更新航班
public int update(int num,String newDestination,String newDate);
}
AirInfoDaoImpl实现类
package dao;
import bean.AirInfo;
import util.BaseDao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* 实现类
*/
public class AirInfoDaoImpl extends BaseDao implements AirInfoDao {
/**
* 1.列出所有航班
* @return
*/
@Override
public List<AirInfo> getAll() {
ArrayList arrayList = new ArrayList();
try {
String sql = "select * from airinfo";
ResultSet resultSet = query(sql, null);
while (resultSet.next()) {
AirInfo airInfo = new AirInfo();
airInfo.setId(resultSet.getInt("id"));
airInfo.setNumber(resultSet.getString("number"));
airInfo.setDestination(resultSet.getString("destination"));
airInfo.setDate(resultSet.getDate("date"));
arrayList.add(airInfo);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll();
}
return arrayList;
}
/**
* 2.按起飞时间查询
* @param date
* @return
*/
@Override
public List<AirInfo> getByDate(String date) {
ArrayList list = new ArrayList();
try {
String sql = "select * from airinfo where date=?";
ArrayList arrayList = new ArrayList();
arrayList.add(date);
ResultSet resultSet = query(sql, arrayList);
if (resultSet.next() == false) {
return null;
}else {
do{
AirInfo airInfo = new AirInfo();
airInfo.setId(resultSet.getInt("id"));
airInfo.setNumber(resultSet.getString("number"));
airInfo.setDestination(resultSet.getString("destination"));
airInfo.setDate(resultSet.getDate("date"));
list.add(airInfo);
}while (resultSet.next());
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll();
}
return list;
}
/**
* 3.按目的地查询
* @param destination
* @return
*/
@Override
public List<AirInfo> getByDestination(String destination) {
ArrayList arrayList = new ArrayList();
try {
String sql = "select * from airinfo where destination like ?";
ArrayList list = new ArrayList();
list.add("%" + destination + "%");
ResultSet resultSet = query(sql, list);
if (resultSet.next() == false) {
return null;
}else {
do {
AirInfo airInfo = new AirInfo();
airInfo.setId(resultSet.getInt("id"));
airInfo.setNumber(resultSet.getString("number"));
airInfo.setDestination(resultSet.getString("destination"));
airInfo.setDate(resultSet.getDate("date"));
arrayList.add(airInfo);
}while (resultSet.next());
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll();
}
return arrayList;
}
/**
* 4.删除航班
* @param number
* @return
*/
@Override
public int delete(String number) {
int count = -1;
try {
String sql = "delete from airinfo where number=?";
ArrayList list = new ArrayList();
list.add(number);
count = update(sql,list);
} catch (Exception e) {
e.printStackTrace();
} finally {
closeAll();
}
return count;
}
/**
* 根据航班号查找航班
* @param number
* @return
*/
public List<AirInfo> getById(String number) {
ArrayList arrayList = new ArrayList();
try {
String sql = "select * from airinfo where number=?";
ArrayList list = new ArrayList();
list.add(number);
ResultSet resultSet = query(sql, list);
if (resultSet.next() == false) {//查询结果为空
return null;
} else {
do {
AirInfo airInfo = new AirInfo();
airInfo.setId(resultSet.getInt("id"));
airInfo.setNumber(resultSet.getString("number"));
airInfo.setDestination(resultSet.getString("destination"));
airInfo.setDate(resultSet.getDate("date"));
arrayList.add(airInfo);
}while (resultSet.next());
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll();
}
return arrayList;
}
/**
* 5.更新航班
* @param newDestination 新的地点
* @param newDate 新的起飞日期
* @return
*/
@Override
public int update(int num, String newDestination, String newDate) {
int count = -1;
try {
String sql = "update airinfo set destination=?,date=? where number=?";
ArrayList list = new ArrayList();
list.add(newDestination);
list.add(newDate);
list.add(num);
count = update(sql, list);
} catch (Exception e) {
e.printStackTrace();
} finally {
closeAll();
}
return count;
}
/**
* 根据航班号查找航班
* @param number
* @return
*/
public AirInfo getByNumber(int number) {
AirInfo airInfo = new AirInfo();
try {
String sql = "select * from airinfo where number=?";
ArrayList list = new ArrayList();
list.add(number);
ResultSet resultSet = query(sql, list);
if (resultSet.next() == false) {//查询结果为空
return null;
} else {
airInfo.setId(resultSet.getInt("id"));
airInfo.setNumber(resultSet.getString("number"));
airInfo.setDestination(resultSet.getString("destination"));
airInfo.setDate(resultSet.getDate("date"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll();
}
return airInfo;
}
}
Main类
package test;
import bean.AirInfo;
import dao.AirInfoDaoImpl;
import util.BaseDao;
import view.AirView;
import java.util.List;
import java.util.Scanner;
public class Main {
private static BaseDao b = new BaseDao();
private static AirView v = new AirView();
private static AirInfoDaoImpl dao = new AirInfoDaoImpl();
private static Scanner input = new Scanner(System.in);
public static void main(String[] args) {
v.welcome();
p: while (true) {
int m = v.menu();
switch (m){
case 1:
//1.列出所有航班
check();
break;
case 2:
//2.按起飞时间查询
findByAirDate();
break;
case 3:
//3.按目的地查询
findByAirDestination();
break;
case 4:
//4.删除航班
cut();
break;
case 5:
//5.更新航班
modify();
break;
case 6:
//6.离开系统
v.bye();
break p;
}
}
}
/**
* 1.列出所有航班
*/
private static void check() {
v.printAll(dao.getAll());
}
/**
* 2.按起飞时间查询
*/
private static void findByAirDate() {
String date = v.findByDate();//得到起飞时间
List<AirInfo> infoList = dao.getByDate(date);
if (infoList != null) {
v.printAll(infoList);
}else {
v.printNull();
}
}
/**
* 3.按目的地查询
*/
private static void findByAirDestination() {
String destination = v.findByDestination();
List<AirInfo> infoList = dao.getByDestination(destination);
if (infoList != null) {
v.printAll(infoList);
}else {
v.printNull();
}
}
/**
* 4.删除航班
*/
private static void cut() {
String id = v.getId();
List<AirInfo> airInfo = dao.getById(id);
if (airInfo != null) {
v.printAll(airInfo);
if (v.isDelete() == 1) {
dao.delete(id);
v.success();
}else {
v.success();
}
}else {
v.printNull();
}
}
/**
* 5.更新航班
*/
private static void modify() {
int num = v.getById();
AirInfo daoById = dao.getByNumber(num);
if (daoById != null) {
v.printAirInfo(daoById);
String newAddress = v.getAddress();
String newDate = v.getDate();
dao.update(num,newAddress,newDate);
v.success();
}else {
v.fail();
}
}
}
BaseDao工具类
package util;
import com.alibaba.druid.pool.DruidDataSource;
import java.sql.*;
import java.util.List;
import java.util.ResourceBundle;
/**
* 链接数据库，关闭数据库
*
* 工具类
* (把原来操作数据库的步骤进行独立的封装，方便后期的调用)
*/
public class BaseDao {
/**
* 1.定义变量
*/
private Connection connection;
private PreparedStatement pps;
private ResultSet resultSet;
private int count; //存储受影响行数
private static String userName;
private static String password;
private static String url;
private static String driverName;
//德鲁伊
private static DruidDataSource dataSource = new DruidDataSource();
/**
* 2.加载驱动
*/
static {
ResourceBundle bundle = ResourceBundle.getBundle("db");
driverName = bundle.getString("driver");
url = bundle.getString("url");
userName = bundle.getString("name");
password = bundle.getString("pass");
dataSource.setUsername(userName);
dataSource.setPassword(password);
dataSource.setUrl(url);
dataSource.setDriverClassName(driverName);
dataSource.setTestWhileIdle(false);
}
/**
* 3.获得链接
*/
protected Connection getConnection(){//受保护，能被子类调用(防止其他类调用)
try {
connection = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
/**
* 4.得到预状态通道
*/
protected PreparedStatement getPps(String sql){
try {
getConnection();
pps = getConnection().prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pps;
}
/**
* 5.绑定参数 (list保存的是给占位符所赋的值)
*/
protected void param(List list){
try {
if (list != null && list.size() > 0) { //集合里面要有数据
for (int i = 0; i < list.size(); i++) { //遍历
pps.setObject(i+1,list.get(i)); //赋值 (list.get(i)取出对应数据)
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 6.执行操作(增删改+查询)
*/
protected int update(String sql,List list){
try {
getPps(sql);//得到预状态通道
param(list);//绑定参数
count = pps.executeUpdate();//得到受影响行数
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
//查询
protected ResultSet query(String sql,List list){
try {
getPps(sql);//得到预状态通道
param(list);//绑定参数
resultSet = pps.executeQuery();
} catch (SQLException e) {
}
return resultSet;
}
/**
* 7.关闭资源
*/
protected void closeAll(){
try {
if (connection != null) {
connection.close();
}
if (pps != null) {
pps.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}AirView
package view;
import bean.AirInfo;
import dao.AirInfoDaoImpl;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class AirView {
/**
* 欢迎页面
*/
public void welcome(){
System.out.println(" * * * * * * 欢迎使用航班信息管理系统 * * * * * * ");
}
private List<AirInfo> airInfoList = new ArrayList<>();
private AirInfoDaoImpl airInfoDao = new AirInfoDaoImpl();
private Scanner input = new Scanner(System.in);
private int count = -1;
/**
* 主方法
* @return 1.列出所有航班,2.按起飞时间查询,3.按目的地查询,4.删除航班,5.更新航班6.离开系统
*/
public int menu() {
System.out.println("请选择操作:");
System.out.println("1.列出所有航班,2.按起飞时间查询,3.按目的地查询,4.删除航班,5.更新航班6.离开系统");
String text = input.next();
try {
count = Integer.parseInt(text);
} catch (NumberFormatException e) {
}
if (count < 1 || count > 6) {
System.out.println("亲,请按提示操作! 需要输入操作序号");
return menu();
}
return count;
}
/**
* 1.列出所有航班
* @param airInfoList
*/
public void printAll(List<AirInfo> airInfoList){
System.out.println("航班信息如下:");
System.out.println("编号 \t航班号 \t目的地\t\t起飞日期");
for (AirInfo a : airInfoList) {
System.out.println(a.toString());
}
}
/**
* 2.按起飞时间查询
*/
public String findByDate() {
System.out.println("请输入起飞时间:(yyyy-MM-dd)");
String text = input.next();
return text;
}
/**
* 3.按目的地查询
* @return
*/
public String findByDestination(){
System.out.println("请输入目的地:");
String text = input.next();
return text;
}
/**
* 4.删除航班(根据航班号)
* @return
*/
public int isDelete(){
System.out.println("是否确认删除?");
System.out.println("1.确认");
System.out.println("0.取消");
String text = input.next();
try {
count = Integer.parseInt(text);
} catch (NumberFormatException e) {
e.printStackTrace();
}
if (count < 0 || count > 1) {
System.out.println("亲,请按提示操作! 需要输入操作序号");
return isDelete();
}
return count;
}
/**
* 得到航班编号
* @return 航班编号
*/
public String getId(){
System.out.println("请输入需要操作的航班号:");
String id = input.next();
return id;
}
/**
* 5.更新航班
*/
public int getById(){
System.out.println("请输入要更新的航班号:");
String s = input.next();
count = Integer.parseInt(s);
return count;
}
public String getAddress(){
System.out.println("请输入新的目的地:");
String s = input.next();
return s;
}
public String getDate(){
System.out.println("请输入新的航班起飞时间:");
String s = input.next();
return s;
}
public void printAirInfo(AirInfo a) {
System.out.println("航班信息如下:");
System.out.println("编号 \t航班号 \t目的地\t\t起飞日期");
System.out.println(a.toString());
}
public void printNull(){
System.out.println("暂无该航班信息！");
}
public void success(){
System.out.println("操作成功！");
}
public void fail(){
System.out.println("操作失败！");
}
public void bye(){
System.out.println("欢迎下次使用!");
}
}
结果展示