第一步,根据数据库的表名和列名将属性进行封装(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);
}
}
都看到这了,想白嫖?一键三连打赏一波再走!!!