JDBC——根据需求编写项目

 

 第一步，根据数据库的表名和列名将属性进行封装（Bean包）

表名=类名 属性名=列名

package yhp.bean;

import java.util.Date;

//表名=类名 属性名=列名
public class AirInfo {
    private Integer airId;
    private String number;
    private String address;
    private Date beginTime;

    public Integer getAirId() {
        return airId;
    }

    public void setAirId(Integer airId) {
        this.airId = airId;
    }

    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;
    }
}

第二步，将之前写好的JDBC的工具包复制到项目中

package yhp.utils;

import com.alibaba.druid.pool.DruidDataSource;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ResourceBundle;

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 driverName;

    //德鲁伊
    private static DruidDataSource dataSource = new DruidDataSource();
    //2.加载驱动
    static {
        /*try {
            *//*InputStream inputStream = DBUtils.class.getClassLoader()
                    .getResourceAsStream("db.properties");

            Properties properties = new Properties();
            properties.load(inputStream);

            driverName = properties.getProperty("driverclass");
            url = properties.getProperty("url");
            userName = properties.getProperty("uname");
            userPass = properties.getProperty("upass");*//*

            ResourceBundle bundle = ResourceBundle.getBundle("db");
            driverName = bundle.getString("driverclass");
            url = bundle.getString("url");
            userName = bundle.getString("uname");
            userPass = bundle.getString("upass");

            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }*//*catch (IOException e) {
            e.printStackTrace();
        }*/

        /*//DBCP
        ResourceBundle bundle = ResourceBundle.getBundle("db");
        driverName = bundle.getString("driverclass");
        url = bundle.getString("url");
        userName = bundle.getString("uname");
        userPass = bundle.getString("upass");

        basicDataSource.setUsername(userName);
        basicDataSource.setPassword(userPass);
        basicDataSource.setUrl(url);
        basicDataSource.setDriverClassName(driverName);
        basicDataSource.setInitialSize(20);*/
        //德鲁伊
        ResourceBundle bundle = ResourceBundle.getBundle("db");
        driverName = 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(driverName);
        //dataSource.setInitialSize(20);


    }

    //2.获得链接
    protected Connection getConnection(){
        try {
            connection=dataSource.getConnection();
            //connection = comboPooledDataSource.getConnection();
            //connection = basicDataSource.getConnection();
            //connection = DriverManager.getConnection(url, userName, userPass);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    //得到预状态通道
    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){
        getPps(sql);
        param(list);
        try {
            count = pps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
    //7.查询
    protected ResultSet quary(String sql,List list){
        getPps(sql);
        param(list);
        try {
            resultSet = pps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    //8.关闭资源
    protected void closeAll(){
        try {
            if (connection != null) {
                connection.close();
            }
            if (pps != null) {
                pps.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

第三步，写出接口，将需要实现的方法写入（Dao包）

package yhp.dao;

import yhp.bean.AirInfo;

import java.util.List;

public interface AirInfoDao {

    //1.查询所有航班信息
    public List<AirInfo> finAll();
    //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);
}

第四步，将接口中的方法一一实现，写一个方法测试一个方法（impl包）

package yhp.dao.impl;

import yhp.bean.AirInfo;
import yhp.dao.AirInfoDao;
import yhp.utils.DBUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AirInfoDaoImpl extends DBUtils implements AirInfoDao {
    @Override
    public List<AirInfo> finAll() {
        ArrayList arrayList = new ArrayList<>();
        String sql = "select * from airinfo";
        resultSet = quary(sql, null);
        try {
            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 e) {
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return arrayList;

    }

    @Override
    public List<AirInfo> findByTime(String time) {
        ArrayList arrayList = new ArrayList<>();//存结果集
        String sql="select * from airinfo where begintime=?";
        ArrayList params = new ArrayList<>();
        params.add(time);
        ResultSet resultSet = quary(sql, params);
        try {
            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 e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return arrayList;
    }

    @Override
    public List<AirInfo> findByAddress(String address) {
        ArrayList arrayList = new ArrayList<>();//存结果集
        String sql="select * from airinfo where address like ?";
        ArrayList params = new ArrayList<>();
        params.add("%"+address+"%");
        ResultSet resultSet = quary(sql, params);
        try {
            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 e) {
            e.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) {
        String sql = "select * from airinfo where number=?";
        ArrayList params = new ArrayList<>();
        params.add(number);//将参数传入集合内
        AirInfo airInfo = null;
        resultSet = quary(sql, params);
        try {
            while (resultSet.next()) {
                airInfo=new AirInfo();
                airInfo.setNumber(resultSet.getString("number"));
                airInfo.setAddress(resultSet.getString("address"));
                airInfo.setBeginTime(resultSet.getDate("begintime"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return airInfo;
    }
}

第五步，按照需求视图，写出测试类（test包）

package yhp.test;

import yhp.bean.AirInfo;
import yhp.dao.impl.AirInfoDaoImpl;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Scanner;
import java.util.logging.SimpleFormatter;

public class Demo {
    public static void main(String[] args) throws ParseException {
        int num=0;
        AirInfoDaoImpl infoDao = new AirInfoDaoImpl();
        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.finAll();
                    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:
                    System.out.println("谢谢使用！");
                    return;
                default:
                    System.out.println("请输入1-6之间的值");
            }
        } while (num!=6);


    }
}

都看到这了，想白嫖？一键三连打赏一波再走！！！