JDBC練習:航班資訊管理系統
文章目錄
- JDBC練習:航班資訊管理系統
- 1. 練習題
- 2. 過程描述
- 3. 項目結構及代碼
- 3.1 整體代碼結構
- 3.2 建立資料庫及相關表格
- 3.3 db.properties
- 3.4 代碼
- 4. 結果
1. 練習題
現在有一家航空公司為了提高使用者體驗,希望做一個航班資訊系統,使用者可以根據需求去對航班資訊進行操
作。組長把這個任務安排給了程式員趙丹,趙丹發現這裡需要通過 java 代碼操作資料庫,并且使用者是可以在控制
台做對應的操作,JDBC 可以幫她解決這個問題。學習起來,試着把這個系統實作出來。
- 顯示航班資訊系統主菜單
- 列出所有的航班資訊
- 按起飛時間查詢
- 按目的地查詢
- 删除航班
- 更新航班
- 退出系統
2. 過程描述
1、建立資料庫表 airinfo,添加測試資料不少于 4 條。要求主鍵自增
2、建立實體類 AirInfo,根據業務提供需要的構造方法和 setter/getter 方法。
3、建立 BaseDao 類,實作資料庫連接配接和關閉功能。
4、建立 DAO 接口 AirInfoDao,定義查詢所有航班,按日期和目的地查詢航班,删除航班,更新航班的方法。
5、建立 DAO 實作類 AirInfoDaoImpl,繼承 BaseDao 類,實作 AirInfoDao 接口,使用 JDBC 完成相應數
據庫操作。
6、建立 Main 類,完成在控制台顯示留言資訊和使用者添加留言操作,啟動和運作系統。
3. 項目結構及代碼
3.1 整體代碼結構
按照上述過程描述建立如下項目結構和相關檔案,并導入資料庫相關jar包到lib目錄:
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiI0gTMx81dsQWZ4lmZf1GLlpXazVmcvwFciV2dsQXYtJ3bm9CX9s2RkBnVHFmb1clWvB3MaVnRtp1XlBXe0xCMy81dvRWYoNHLwEzX5xCMx8FesU2cfdGLwMzX0xiRGZkRGZ0Xy9GbvNGLpZTY1EmMZVDUSFTU4VFRR9Fd4VGdsYTMfVmepNHLrJXYtJXZ0F2dvwVZnFWbp1zczV2YvJHctM3cv1Ce-cmbw5yM5kDMxYmMyEWOxIjYxQ2YyYzXzMjN1QTMyEzLcdDMyIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjLyM3Lc9CX6MHc0RHaiojIsJye.png)
3.2 建立資料庫及相關表格
建立air庫、airinfo表,表結構如下,id設定為主鍵并自增:
暫輸入如下四行内容:
3.3 db.properties
driver=com.mysql.cj.jdbc.Driver
name=root
pass=12345678
url=jdbc:mysql://localhost:3306/air?serverTimezone=UTC
3.4 代碼
- AirInfo.java
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.java
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;
/**
* 連結資料庫,關閉資料庫
* <p>
* 工具類
* (把原來操作資料庫的步驟進行獨立的封裝,友善後期的調用)
*/
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("歡迎下次使用!");
}
}