mysql 8.0一个简易的航班管理系统
发布时间
阅读量：
阅读量
一 技能目标:
- 使用 JDBC 操作 MySql 数据库
- MySql 数据库的使用
- Sql 的使用
二 任务过程：
1、创建数据库表 airinfo
添加测试数据不少于 4 条。要求主键自增2 创建实体类 AirInfo
根据业务提供需要的构造方法和 setter/getter方法。3创建 DBUtils 类
实现数据库连接和关闭功能。4、创建 DAO 接口 AirInfoDao
定义查询所有航班，按日期和目的地查询航班，删除航班，更新航班的方法。5、创建DAO实现类AirInfoDaoImpl
继承BaseDao类，实现AirInfoDao接口，使用 JDBC 完成相应数据库操作。三 代码
1airinfo数据库
2 Airinfo类
public class AirInfo {
public Integer getAriId() {
return ariId;
}
public void setAriId(Integer ariId) {
this.ariId = ariId;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Date getBeginTime() {
return beginTime;
}
public void setBeginTime(Date beginTime) {
this.beginTime = beginTime;
}
private Integer ariId;
private String number;
private String address;
private Date beginTime;
}3创建 DBUtils 类
public class DBUtils {
//1定义变量
private Connection connection;
private PreparedStatement pps;
protected ResultSet resultSet;
private int count;
private static String userName;
private static String userPass;
private static String url;
private static String dirverName;
private static DruidAbstractDataSource dataSource = new DruidDataSource();
//2.加载驱动
static {
ResourceBundle bundle =ResourceBundle.getBundle("db");
dirverName = bundle.getString("driverclass");
url = bundle.getString("url");
userName = bundle.getString("uname");
userPass = bundle.getString("upass");
dataSource.setUsername(userName);
dataSource.setPassword(userPass);
dataSource.setUrl(url);
dataSource.setDriverClassName(dirverName);
//dataSource.setInitialSize(20);
}
//3.获得链接
protected Connection getConnection(){
try {
// connection = DriverManager.getConnection(url, userName, userPass);
connection=dataSource.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
//4得到预装态通道
protected PreparedStatement getPps(String sql){
try {
pps = getConnection().prepareStatement(sql);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return pps;
}
//5 绑定参数 占位符的值在List保存
protected void param(List list){
if(list!=null&&list.size()>0){
for (int i =0;i<list.size();i++){
try {
pps.setObject(i+1,list.get(i));
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
//6 执行操作(增删改查)
protected int update(String sql,List list){
getPps(sql);
param(list);
try {
count = pps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return count;
}
protected ResultSet query(String sql,List list){
getPps(sql);
param(list);
try {
resultSet=pps.executeQuery();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return resultSet;
}
protected void closeAll(){
if (connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (pps!=null){
try {
pps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (resultSet!=null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}说明:我用的Druid(德鲁伊)连接池,也可以根据喜好用自己的连接池
driverclass=com.mysql.cj.jdbc.Driver
uname=root
upass=你自己的密码
url=jdbc:mysql://localhost:3306/airinfo?serverTimezone=UTC这是一个db.properties 文件 记得设置
4 创建 DAO 接口 AirInfoDao
public interface AirInfoDao {
//1.查询所有航班
public List<AirInfo> findAll();
//2.根据航班时间进行查询
public List<AirInfo> findByTime(String time);
//3.根据目的地进行查询
public List<AirInfo> findByAddress(String address);
//4删除航班(根据编号)
public int delete(String number);
//5更新航班
public int update(AirInfo airInfo,String time);
//6查询航班是否存在
public AirInfo findByNumber(String number);
}5创建DAO实现类AirInfoDaoImpl
public class AirInfoIpml extends DBUtils implements AirInfoDao {
@Override
public List<AirInfo> findAll() {
ArrayList arrayList = new ArrayList<>();
try {
String sql="select * from airinfo";
resultSet= query(sql, null);
while (resultSet.next()){
AirInfo airInfo = new AirInfo();
airInfo.setNumber(resultSet.getString("number"));
airInfo.setAddress(resultSet.getString("address"));
airInfo.setBeginTime(resultSet.getDate("begintime"));
arrayList.add(airInfo);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
closeAll();
}
return arrayList;
}
@Override
public List<AirInfo> findByTime(String time) {
ArrayList arrayList = new ArrayList<>();
try {
String sql="select * from airinfo where begintime=?";
ArrayList params = new ArrayList();
params.add(time);
resultSet=query(sql,params);
while (resultSet.next()){
AirInfo airInfo = new AirInfo();
airInfo.setNumber(resultSet.getString("number"));
airInfo.setAddress(resultSet.getString("address"));
airInfo.setBeginTime(resultSet.getDate("begintime"));
arrayList.add(airInfo);
}
} 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 params = new ArrayList();
params.add("%"+address+"%");
resultSet=query(sql,params);
while (resultSet.next()){
AirInfo airInfo = new AirInfo();
airInfo.setNumber(resultSet.getString("number"));
airInfo.setAddress(resultSet.getString("address"));
airInfo.setBeginTime(resultSet.getDate("begintime"));
arrayList.add(airInfo);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
closeAll();
}
return arrayList;
}
@Override
public int delete(String number) {
int update =0;
try {
String sql ="delete from airinfo where number=?";
ArrayList params = new ArrayList();
params.add(number);
update=update(sql,params);
} catch (Exception e) {
e.printStackTrace();
} finally {
closeAll();
}
return update;
}
@Override
public int update(AirInfo airInfo,String time) {
int update =0;
try {
String sql ="update airinfo set address=?,begintime=? where number=?";
ArrayList params = new ArrayList();
params.add(airInfo.getAddress());
params.add(time);
params.add(airInfo.getNumber());
update=update(sql,params);
} catch (Exception e) {
e.printStackTrace();
} finally {
closeAll();
}
return update;
}
@Override
public AirInfo findByNumber(String number) {
AirInfo airInfo = null;
try {
String sql ="select *from airinfo where number=?";
ArrayList params = new ArrayList();
params.add(number);
resultSet =query(sql,params);
while (resultSet.next()){
airInfo=new AirInfo();
airInfo.setNumber(resultSet.getString("number"));
airInfo.setAddress(resultSet.getString("address"));
airInfo.setBeginTime(resultSet.getDate("begintime"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
closeAll();
}
return airInfo;
}
}6测试类
public class Demo {
public static void main(String[] args) throws ParseException {
int num=0;
AirInfoDao infoDao = new AirInfoIpml();
do {
//1.输出菜单
System.out.println("************欢迎使用航班信息管理系统*****************");
System.out.println("1.查询所有航班 2.按时间查询 3.按目的地查询 4.删除航班 5.更新航班 6.退出");
System.out.println("请输入选择");
Scanner scanner = new Scanner(System.in);
num=scanner.nextInt();
switch (num){
case 1:
List<AirInfo> infos = infoDao.findAll();
System.out.println("编号\t航班号\t目的地\t起飞时间");
for (int i=0;i<infos.size();i++){
AirInfo airInfo = infos.get(i);
System.out.println((i+1)+"\t"+airInfo.getNumber()+"\t"+airInfo.getAddress()+"\t"+airInfo.getBeginTime());
}
break;
case 2:
System.out.println("请输入起飞时间");
String time=scanner.next();
List<AirInfo> airInfos = infoDao.findByTime(time);
for (int i=0;i<airInfos.size();i++){
AirInfo airInfo = airInfos.get(i);
System.out.println((i+1)+"\t"+airInfo.getNumber()+"\t"+airInfo.getAddress()+"\t"+airInfo.getBeginTime());
}
break;
case 3:
System.out.println("请输入目的地");
String address=scanner.next();
List<AirInfo> airs = infoDao.findByAddress(address);
for (int i=0;i<airs.size();i++){
AirInfo airInfo = airs.get(i);
System.out.println((i+1)+"\t"+airInfo.getNumber()+"\t"+airInfo.getAddress()+"\t"+airInfo.getBeginTime());
}
break;
case 4:
System.out.println("请输入航班号");
String nums=scanner.next();
int delete = infoDao.delete(nums);
System.out.println(delete>0?"删除成功":"失败");
break;
case 5:
System.out.println("请输入航班号");
String nums2=scanner.next();
//验证航班号是否存在
AirInfo byNumber = infoDao.findByNumber(nums2);
if (byNumber ==null){
System.out.println("不存在这个航班号");
}else {
System.out.println("请输入新的航班地址");
String addr = scanner.next();
System.out.println("请输入新的起飞时间");
String time2 = scanner.next();
AirInfo airInfo = new AirInfo();
airInfo.setNumber(nums2);
airInfo.setAddress(addr);
int update = infoDao.update(airInfo,time2);
System.out.println(update>0?"修改成功":"失败");
}
break;
case 6:
break;
default:
System.out.println("请输入1-6之间的值");
}
}while (num !=6);
System.out.println("谢谢使用");
}
}四 代码演示
全部评论 (0)
还没有任何评论哟~