JAVA JDBC开发过程

前言

学习中的一点小总结,过程比较繁琐,怕忘。不喜勿喷

项目需求

假设现在有一家航空公司为了提高用户体验,希望做一个航班信息系统,用户
可以根据需求去对航班信息进行操作。组长把这个任务安排给了程序员赵丹,
赵丹发现这里需要通过 java 代码操作数据库,并且用户是可以在控制台做对
应的操作,JDBC 可以帮她解决这个问题。学习起来,试着把这个系统实现出
来。

  1. 显示航班信息系统主菜单
    在这里插入图片描述

  2. 列出所有的航班信息
    在这里插入图片描述

  3. 按起飞时间查询
    在这里插入图片描述

  4. 按目的地查询
    在这里插入图片描述

  5. 删除航班
    在这里插入图片描述

  6. 更新航班
    在这里插入图片描述

  7. 退出系统

项目编写

数据库创建

既然是数据库操作,当然得有数据库了

//创建一个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;
    }
  1. String sql="select * from message where flightNum=? ";定义好要执行的sql语句,使用?来占位,以便后续参数绑定
  2. ArrayList params = new ArrayList();params.add(num);用于参数绑定
  3. 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;
    }
    
  4. 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); }如果返回不为空,用实体类去承接查询的结果,并将其放到数组中。
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值