我是写了一个航班信息系统
首先我是自己写了一个工具包,
实现加载驱动获取连接,以及对数据库一系列CRUD的操作
package JDBC_Air.util;
import java.sql.*;
import java.util.List;
import java.util.ResourceBundle;
public class util {
//1.定义变量;
private Connection connection;
private PreparedStatement pps;
private ResultSet resultSet;
private int count;
private static String url;
private static String username;
private static String userpass;
private static String driverclass;
//2.加载驱动
static {
try {
ResourceBundle bundle = ResourceBundle.getBundle("mypps");
driverclass = bundle.getString("driverClassName");
url = bundle.getString("url");
username = bundle.getString("username");
userpass = bundle.getString("password");
Class.forName(driverclass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//3.获取连接
protected Connection getConnection() {
try {
connection = DriverManager.getConnection(url, username, userpass);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//4.加载预状态通道
protected PreparedStatement getPps(String sql) {
try {
pps = getConnection().prepareStatement(sql);
} catch (SQLException e) {
e.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 e) {
e.printStackTrace();
}
}
}
}
//6.增删改
protected int update(String sql, List list) {
pps = getPps(sql);
param(list);
try {
count = pps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
//7.查询
protected ResultSet select(String sql,List list){
pps = getPps(sql);
param(list);
try {
resultSet = pps.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return resultSet;
}
//8.关闭资源
protected void getClose(){
try {
if (connection != null) {
connection.close();
}
if (pps != null) {
pps.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
实体类
package JDBC_Air.Bean;
import java.util.Date;
/**
* 实体类
*/
public class airinfo {
private int id;
private String number;
private Date date;
private String address;
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 getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
}
方法接口
package JDBC_Air.Dao;
import JDBC_Air.Bean.airinfo;
/**
* 方法接口
*/
import java.util.List;
public interface AirInfoDao {
//获取所有航班信息
List<airinfo> getAllAirInfo();
//根据起飞时间查询
List<airinfo> getAllAirInfoByDate(String time);
//根据目的地查询
List<airinfo> getAllAirInfoByAddress(String address);
//删除航班,传入航班号
int delete(int id);
//更新航班
int update(airinfo info);
}
实现方法
package JDBC_Air.Dao;
import JDBC_Air.Bean.airinfo;
import JDBC_Air.util.util;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* 实现类,同时继承工具包
*/
public class impl_AirInfoDao extends util implements AirInfoDao {
@Override
public List<airinfo> getAllAirInfo() {
String sql = "select * from airinfo";
List<airinfo> arr = new ArrayList<>();
try {
ResultSet resultSet = select(sql,null);
while (resultSet.next()) {
airinfo info = new airinfo();
info.setId(resultSet.getInt("id"));
info.setNumber(resultSet.getString("number"));
info.setDate(resultSet.getDate("date"));
info.setAddress(resultSet.getString("address"));
arr.add(info);
}
return arr;
} catch (SQLException e) {
e.printStackTrace();
} finally {
getClose();
}
return null;
}
@Override
public List<airinfo> getAllAirInfoByDate(String time) {
String sql = "select * from airinfo where date=?";
List str=new ArrayList();
str.add(time);
try {
ResultSet resultSet = select(sql,str);
List<airinfo> arr = new ArrayList<>();
while (resultSet.next()) {
airinfo info = new airinfo();
info.setId(resultSet.getInt("id"));
info.setNumber(resultSet.getString("number"));
info.setDate(resultSet.getDate("date"));
info.setAddress(resultSet.getString("address"));
arr.add(info);
}
return arr;
} catch (SQLException e) {
e.printStackTrace();
} finally {
getClose();
}
return null;
}
@Override
public List<airinfo> getAllAirInfoByAddress(String address) {
String sql = "select * from airinfo where address=?";
List str=new ArrayList();
str.add(address);
try {
ResultSet resultSet = select(sql,str);
List<airinfo> arr = new ArrayList<>();
while (resultSet.next()) {
airinfo info = new airinfo();
info.setId(resultSet.getInt("id"));
info.setNumber(resultSet.getString("number"));
info.setDate(resultSet.getDate("date"));
info.setAddress(resultSet.getString("address"));
arr.add(info);
}
return arr;
} catch (SQLException e) {
e.printStackTrace();
} finally {
getClose();
}
return null;
}
@Override
public int delete(int id) {
String sql = "DELETE FROM airinfo where id=?";
List str=new ArrayList();
str.add(id);
int update = update(sql, str);
getClose();
return update;
}
@Override
public int update(airinfo info) {
String sql = "UPDATE airinfo SET number=?,date=?,address=? WHERE id=?";
List str=new ArrayList();
str.add(info.getNumber());
str.add(info.getDate());
str.add(info.getAddress());
str.add(info.getId());
int update = update(sql, str);
getClose();
return update;
}
}
视图类
package JDBC_Air.View;
import JDBC_Air.Bean.airinfo;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Scanner;
/**
* 视图类
*/
public class View {
private Scanner input = new Scanner(System.in);
//欢迎
public void welcome() {
System.out.println("*******************欢迎使用航班管理系统*******************");
System.out.println();
}
//选择开始界面
public int chooseStart() {
System.out.println("1.查询所有航班 2.按起飞时间查询 3.按目的地查询 4.删除航班 5.修改航班 0.离开系统");
int i = input.nextInt();
return i;
}
//输入查询时间
public String getTime() {
System.out.println("请输入你想要查询的起飞时间:");
String time = input.next();
return time;
}
//输入查询地址
public String getAddress() {
System.out.println("请输入你想要查询的目的地:");
String address = input.next();
return address;
}
//删除航班
public int deleteAirId() {
System.out.println("请输入你想要删除的编号:");
int id = input.nextInt();
return id;
}
//修改航班
public airinfo updateAir(){
airinfo info=new airinfo();
System.out.println("请输入你想要修改的编号:");
info.setId(input.nextInt());
System.out.println("请输入新的航空编号:");
info.setNumber(input.next());
System.out.println("请输入新的起飞时间:");
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
try {
info.setDate(sdf.parse(input.next()));
} catch (ParseException e) {
e.printStackTrace();
}
System.out.println("请输入新的目的地:");
info.setAddress(input.next());
return info;
}
public void bey(){
System.out.println("谢谢使用,欢迎下次再来");
}
public void defaultInput(){
System.out.println("输入有误请重新输入");
}
}
测试类
package JDBC_Air.Main;
import JDBC_Air.Bean.airinfo;
import JDBC_Air.Dao.impl_AirInfoDao;
import JDBC_Air.View.View;
import java.util.List;
public class Demo {
private static View v = new View();
private static impl_AirInfoDao ia = new impl_AirInfoDao();
public static void main(String[] args) {
boolean flag = true;
v.welcome();
while (flag) {
int i = v.chooseStart();
switch (i) {
case 1:
//获取所有航班信息
getAll();
break;
case 2:
//获取航班信息(通过起飞时间)
getAllByDate();
break;
case 3:
//获取航班信息(通过目的地)
getAllByAddress();
break;
case 4:
//删除航班(通过编号)
delete();
break;
case 5:
//更新航班
update();
break;
case 6:
//退出系统
v.bey();
break;
default:
v.defaultInput();
}
}
}
private static void update() {
airinfo airinfo = v.updateAir();
int update = ia.update(airinfo);
if (update!=0){
System.out.println("修改成功");
}else {
System.out.println("该航班信息不存在");
}
}
private static void delete() {
int id = v.deleteAirId();
int delete = ia.delete(id);
if (delete!=0){
System.out.println("删除成功");
}else {
System.out.println("该航班信息不存在");
}
}
private static void getAllByAddress() {
String address = v.getAddress();
List<airinfo> aai = ia.getAllAirInfoByAddress(address);
if (!judgeList(aai)){
System.out.println("没有该航班信息");
}
}
private static void getAllByDate() {
String time = v.getTime();
List<airinfo> aai = ia.getAllAirInfoByDate(time);
if (!judgeList(aai)){
System.out.println("没有该航班信息");
}
}
private static void getAll() {
List<airinfo> aai = ia.getAllAirInfo();
if (!judgeList(aai)){
System.out.println("没有该航班信息");
}
}
private static boolean judgeList(List<airinfo> list){
if (list != null || list.size() > 0) {
for (airinfo a : list) {
System.out.println("航班编号:" + a.getId() + " 航班编号:" + a.getNumber() + " 起飞时间:" + a.getDate() + " 目的地:" + a.getAddress());
}
return true;
}
return false;
}
}
运行截图