前言
学习中的一点小总结,过程比较繁琐,怕忘。不喜勿喷
项目需求
假设现在有一家航空公司为了提高用户体验,希望做一个航班信息系统,用户
可以根据需求去对航班信息进行操作。组长把这个任务安排给了程序员赵丹,
赵丹发现这里需要通过 java 代码操作数据库,并且用户是可以在控制台做对
应的操作,JDBC 可以帮她解决这个问题。学习起来,试着把这个系统实现出
来。
-
显示航班信息系统主菜单
-
列出所有的航班信息
-
按起飞时间查询
-
按目的地查询
-
删除航班
-
更新航班
-
退出系统
项目编写
数据库创建
既然是数据库操作,当然得有数据库了
//创建一个airinf的数据库
create database airinfo;
//创建一个主键自增的message表
create table message(serialNum int(100) primary key not null auto_increment,flightNum varchar(10),address varchar(20),flightData datetime);
//添加测试数据
insert into message(flightNum,address,flightData) values('001','beijing01','2021-9-29 10:10:10');
insert into message(flightNum,address,flightData) values('002','beijing02','2021-9-29 11:11:11');
insert into message(flightNum,address,flightData) values('003','beijing03','2021-9-29 12:12:12');
insert into message(flightNum,address,flightData) values('004','beijing04','2021-9-29 13:13:13');
insert into message(flightNum,address,flightData) values('005','beijing05','2021-9-29 14:14:14');
项目架构
message
与数据库表对应
//表名=类名,列名=属性
public class message {
//create table message(serialNum int(100) primary key not null auto_increment,flightNum varchar(10),
// address varchar(20),flightData datetime);
private int serialNum;
private String flightNum;
private String address;
private Date datetaime;
@Override
public String toString() {
return "message{" +
"serialNum=" + serialNum +
", flightNum='" + flightNum + '\'' +
", address='" + address + '\'' +
", datetaime=" + datetaime +
'}';
}
public int getSerialNum() {
return serialNum;
}
public void setSerialNum(int serialNum) {
this.serialNum = serialNum;
}
public String getFlightNum() {
return flightNum;
}
public void setFlightNum(String flightNum) {
this.flightNum = flightNum;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Date getDatetaime() {
return datetaime;
}
public void setDatetaime(Date datetaime) {
this.datetaime = datetaime;
}
}
airInfo
用来实现上述项目需求的接口
public interface airInfo {
//1.列出所有的航班信息
public List<message> getall();
//2.按起飞时间查询
public List<message> findByTime(String time);
//3.按目的地查询
public List<message> findByAddress(String address);
//4.删除航班
public List<message> deleteByNum(String num);
//5.更新航班
public int updateByNum(message airInfo,String time);
//6.是否存在某一航班
public message isExitByNum(String num);
}
test
通过用户输入不同的数字进行不同功能的匹配
package show;
import bean.message;
import dao.impl.airInfoImpl;
import javax.swing.text.DateFormatter;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Scanner;
/**
* @program: JDBCTest
* @description: [程序显示类]
* @author: iris
* @create: 2021-10-03 11:01
**/
public class test {
public static void main(String[] args) throws ParseException {
int usrSelect =0;
airInfoImpl infoimpl = new airInfoImpl();
do {
System.out.println("********欢迎使用航班管理信息系统********");
System.out.println("请选择操作(1.列出所有航班,2.按起飞时间查询,3.按目的地查询,4.删除航班,5更新航班,6.离开系统)");
System.out.println("请输入您的选择:");
Scanner scanner = new Scanner(System.in);
usrSelect = scanner.nextInt();
switch (usrSelect) {
case 1://列出所有的航班信息
List<message> airInfos = infoimpl.getall();
System.out.println("编号\t航班号\t目的地\t起飞时间");
for (int i=0;i<airInfos.size();i++){
message message = airInfos.get(i);
System.out.println(message.getSerialNum()+"\t"+message.getFlightNum()+"\t"+message.getAddress()+"\t"+message.getDatetaime());
}
break;
case 2://按起飞时间查询
System.out.println("请输入起飞时间:(YY-MM-DD)");
Scanner scanner_string = new Scanner(System.in);
String dateString = scanner_string.nextLine();
List<message> airInfosTime = infoimpl.findByTime(dateString);
System.out.println("编号\t航班号\t目的地\t起飞时间");
for (int i=0;i<airInfosTime.size();i++){
message message = airInfosTime.get(i);
System.out.println(message.getSerialNum()+"\t"+message.getFlightNum()+"\t"+message.getAddress()+"\t"+message.getDatetaime());
}
break;
case 3://按目的地查询
System.out.println("请输入航班目的地:");
Scanner scannerAddress = new Scanner(System.in);
String addressString = scannerAddress.nextLine();
List<message> airInfosAddress = infoimpl.findByAddress(addressString);
System.out.println("编号\t航班号\t目的地\t起飞时间");
for (int i=0;i<airInfosAddress.size();i++){
message message = airInfosAddress.get(i);
System.out.println(message.getSerialNum()+"\t"+message.getFlightNum()+"\t"+message.getAddress()+"\t"+message.getDatetaime());
}
break;
case 4://删除航班
System.out.println("请输入删除的航班编号:");
Scanner scannerdelete = new Scanner(System.in);
String deleteString = scannerdelete.nextLine();
List<message> delteteAirInfos = infoimpl.deleteByNum(deleteString);
System.out.println("编号\t航班号\t目的地\t起飞时间");
for (int i=0;i<delteteAirInfos.size();i++){
message message = delteteAirInfos.get(i);
System.out.println(message.getSerialNum()+"\t"+message.getFlightNum()+"\t"+message.getAddress()+"\t"+message.getDatetaime());
}
case 5://更新航班
Scanner updatescanner = new Scanner(System.in);
System.out.println("请输入需要更新的航班编号:");
String updateNum = updatescanner.nextLine();
message oldAirInfo = infoimpl.isExitByNum(updateNum);
if (oldAirInfo == null){
System.out.println("航班号不存在,请检查");
}else {
System.out.println("请输入需要新的目的地:");
String newAdress = updatescanner.nextLine();
System.out.println("请输入需要新的起飞日期:YY-MM-DD HH:mm:ss");
String newTime = updatescanner.nextLine();
message newAirInfo = new message();
newAirInfo.setFlightNum(updateNum);
newAirInfo.setAddress(newAdress);
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("YY-MM-DD HH:mm:ss");
newAirInfo.setDatetaime(simpleDateFormat.parse(newTime));
infoimpl.updateByNum(newAirInfo,newTime);
}
break;
case 6:
break;
default:
System.out.println("请按照1-6的选择输入。");
}
} while (usrSelect!=6);
System.out.println("谢谢使用");
}
}
baseDao
用来实现数据的连接、资源回收、CURD功能实现的主要类
package utils;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.sql.dialect.db2.visitor.DB2ASTVisitor;
import com.sun.source.tree.NewArrayTree;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.List;
import java.util.Properties;
/**
* @program: JDBCTest
* @description: [数据库连接和关闭功能]
* @author: iris
* @create: 2021-10-02 10:39
**/
public class baseDao {
//1.定义需要的工具类对象
protected Connection connection=null;
protected PreparedStatement pps=null;
protected ResultSet rs=null;
protected int k=0;//受影响的行数
private static String url="";
private static String username="";
private static String password="";
private static String driverName="";
private static DruidDataSource baseDataSource = new DruidDataSource();
//2.加载驱动
static{
//实例化配置对象
Properties properties=new Properties();
try {
//加载配置文件内容
properties.load(baseDao.class.getClassLoader().getResourceAsStream("dbcpconfig.properties"));
//设置驱动类全称
baseDataSource.setDriverClassName(properties.getProperty("driverClassName"));
//设置连接的数据库
baseDataSource.setUrl(properties.getProperty("url"));
//设置用户名
baseDataSource.setUsername(properties.getProperty("username"));
//设置密码
baseDataSource.setPassword(properties.getProperty("password"));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//3.获得连接
protected Connection getConnection(){
try {
System.out.println("connect getconnnect");
connection = baseDataSource.getConnection();
return connection;
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("connect 未连接成功");
e.printStackTrace();
}
return null;
}
//4.创建通道
protected PreparedStatement getPps(String sql){
try {
getConnection();//insert into users values(?,?,?,?,)
pps=connection.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pps;
}
//5.给占位符赋值 list中保存的是给占位符所赋的值
private 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));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//6.增删改调取的方法,返回受影响的行数
protected int update(String sql,List params){
try {
getPps(sql);
setParams(params);
k= pps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return k;
}
//7.查询的时候调取一个方法,返回查询的数据
protected ResultSet query(String sql,List list){
try {
getPps(sql);
setParams(list);
rs=pps.executeQuery();
return rs;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//8.关闭资源
protected void closeall(){
try {
if (rs != null) {
rs.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
airInfoImpl
package dao.impl;
import bean.message;
import dao.airInfo;
import utils.baseDao;
import java.sql.Array;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* @program: JDBCTest
* @description: [数据库接口实现类]
* @author: iris
* @create: 2021-10-02 10:36
**/
public class airInfoImpl extends baseDao implements airInfo {
@Override
public List<message> getall() {
ArrayList arrayList = new ArrayList();
try {
String sql = "select * from message";
rs = query(sql, null);
while (rs.next()){
message airinfo = new message();
airinfo.setSerialNum(rs.getInt("serialNum"));
airinfo.setFlightNum(rs.getString("flightNum"));
airinfo.setAddress(rs.getString("address"));
airinfo.setDatetaime(rs.getDate("flightData"));
arrayList.add(airinfo);
}
} catch (SQLException e){
e.printStackTrace();
}finally {
closeall();
}
return arrayList;
}
@Override
public List<message> findByTime(String time) {
ArrayList arrayList = new ArrayList();
try {
String sql = "select * from message where date(flightData)=?;";
ArrayList params = new ArrayList();
params.add(time);
rs = query(sql, params);
while (rs.next()){
message airinfo = new message();
airinfo.setSerialNum(rs.getInt("serialNum"));
airinfo.setFlightNum(rs.getString("flightNum"));
airinfo.setAddress(rs.getString("address"));
airinfo.setDatetaime(rs.getDate("flightData"));
arrayList.add(airinfo);
}
} catch (SQLException e){
e.printStackTrace();
}finally {
closeall();
}
return arrayList;
}
@Override
public List<message> findByAddress(String address) {
ArrayList arrayList = new ArrayList();
try {
String sql = "select * from message where address=?;";
ArrayList params = new ArrayList();
params.add(address);
rs = query(sql, params);
while (rs.next()){
message airinfo = new message();
airinfo.setSerialNum(rs.getInt("serialNum"));
airinfo.setFlightNum(rs.getString("flightNum"));
airinfo.setAddress(rs.getString("address"));
airinfo.setDatetaime(rs.getDate("flightData"));
arrayList.add(airinfo);
}
} catch (SQLException e){
e.printStackTrace();
}finally {
closeall();
}
return arrayList;
}
@Override
public List<message> deleteByNum(String num) {
ArrayList arrayList = new ArrayList();
try {
String sql = "select * from message where flightNum=?;";
ArrayList params = new ArrayList();
params.add(num);
rs = query(sql, params);
while (rs.next()){
message airinfo = new message();
airinfo.setSerialNum(rs.getInt("serialNum"));
airinfo.setFlightNum(rs.getString("flightNum"));
airinfo.setAddress(rs.getString("address"));
airinfo.setDatetaime(rs.getDate("flightData"));
arrayList.add(airinfo);
}
String deletesql = "delete from message where flightNum=?;";
ArrayList deleteparams = new ArrayList();
deleteparams.add(num);
int a = update(deletesql, deleteparams);
System.out.println(a>0?"删除成功":"删除失败请检查");
} catch (SQLException e){
e.printStackTrace();
}finally {
closeall();
}
return arrayList;
}
@Override
public int updateByNum(message airInfo,String time) {
int a =0;
try {
String deletesql = "update message set address=?,flightData=? where flightNum=?;";
ArrayList deleteparams = new ArrayList();
// System.out.println(airInfo.getAddress());
// System.out.println(airInfo.getDatetaime().toString());
// System.out.println(airInfo.getFlightNum());
deleteparams.add(airInfo.getAddress());
deleteparams.add(time);
deleteparams.add(airInfo.getFlightNum());
a = update(deletesql, deleteparams);
System.out.println(a>0?"更新成功":"更新失败请检查");
} finally {
closeall();
}
return a;
}
@Override
public message isExitByNum(String num) {
message airinfo =null;
String sql="select * from message where flightNum=? ";
ArrayList params = new ArrayList();
params.add(num);
try {
rs = query(sql, params);
while (rs.next()){
airinfo = new message();
airinfo.setSerialNum(rs.getInt("serialNum"));
airinfo.setFlightNum(rs.getString("flightNum"));
airinfo.setAddress(rs.getString("address"));
airinfo.setDatetaime(rs.getDate("flightData"));
}
} catch (SQLException e){
e.printStackTrace();
}finally {
closeall();
}
return airinfo;
}
}
重点
public message isExitByNum(String num) {
message airinfo =null;
String sql="select * from message where flightNum=? ";
ArrayList params = new ArrayList();
params.add(num);
try {
rs = query(sql, params);
while (rs.next()){
airinfo = new message();
airinfo.setSerialNum(rs.getInt("serialNum"));
airinfo.setFlightNum(rs.getString("flightNum"));
airinfo.setAddress(rs.getString("address"));
airinfo.setDatetaime(rs.getDate("flightData"));
}
} catch (SQLException e){
e.printStackTrace();
}finally {
closeall();
}
return airinfo;
}
String sql="select * from message where flightNum=? ";
定义好要执行的sql语句,使用?
来占位,以便后续参数绑定ArrayList params = new ArrayList();params.add(num);
用于参数绑定rs = query(sql, params);
protected ResultSet query(String sql,List list){ try { getPps(sql); //使用预编译,一定程度上提升安全性 setParams(list);//绑定所需要的参数 rs=pps.executeQuery();//执行SQL语句,返回结果集 return rs; } catch (SQLException e) { e.printStackTrace(); } return null; }
while (rs.next()){ airinfo = new message(); airinfo.setSerialNum(rs.getInt("serialNum")); airinfo.setFlightNum(rs.getString("flightNum")); airinfo.setAddress(rs.getString("address")); airinfo.setDatetaime(rs.getDate("flightData")); arrayList.add(airinfo); }
如果返回不为空,用实体类去承接查询的结果,并将其放到数组中。