【数据库】停车场系统

阳光穿透心脏的1/2处 2022-11-25 11:47 183阅读 0赞

> ☞本项目主要练习MySQL数据库和java代码之间的连通☜  
> ☞后续会在本基础上，利用Spring实现WEB端☜  
> ☞梦想进大厂的一只程序猿☜  
> ☞期望毕业前力扣刷够400题☜  
> ☞正在复习数据结构和算法☜  
> ☞博客地址：[https://www.huangliangshuai.com/][https_www.huangliangshuai.com]☜

# 1. 项目架构图 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70]

# 2. 系统分析 #

## 2.1. 数据库 ##

### 2.1.1. 表parking ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70 1]

### 2.1.2 表record ###

![在这里插入图片描述][20200810192701335.png]

## 2.2 java端 ##

![在这里插入图片描述][20200808181027673.png]

### 2.2.1 JBDC ###

#### 2.2.1.1JDBC连接 ####

package com.situ.parking.util;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ResourceBundle;
    
    public class JDBCUtil {
        
    
    	private static ResourceBundle rb = ResourceBundle.getBundle("com.situ.parking.util.JDBC");
    	private static String cLassName = rb.getString("driver");
    	private static String url = rb.getString("url");
    	private static String user = rb.getString("user");
    	private static String password = rb.getString("pass");
    
    	// 1- 加载驱动
    	static {
        
    		try {
        
    			Class.forName(cLassName);
    		} catch (Exception e) {
        
    			e.getCause();
    		}
    	}
    
    	// 2- 获得链接
    	public static Connection getConnection() throws SQLException {
        
    		return DriverManager.getConnection(url, user, password);
    	}
    
    	public static void close(Connection conn, Statement st, ResultSet rs) {
        
    		try {
        
    			if (rs != null) {
        
    				rs.close();
    				rs = null;
    			}
    			if (st != null) {
        
    				st.close();
    				st = null;
    			}
    			if (conn != null) {
        
    				conn.close();
    				conn = null;
    			}
    		} catch (SQLException e) {
        
    			e.printStackTrace();
    		}
    	}
    
    	public static void close(Connection conn, Statement st) {
        
    		close(conn, st, null);
    	}
    
    	public static void main(String[] args) throws SQLException {
        
    		// 加载驱动 + 获得链接
    		Connection con = JDBCUtil.getConnection();
    		System.out.println(con);
    		JDBCUtil.close(con, null); // 关闭的是插入的
    	}
    
    	private JDBCUtil() {
        
    
    	}
    }

#### 2.2.1.1JDBC配置文件 ####

user=root
    pass=123456
    driver=com.mysql.cj.jdbc.Driver
    url=jdbc:mysql://127.0.0.1:3308/demo02?useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Hongkong&allowPublicKeyRetrieval=true

### 2.2.2 Parking包 ###

#### 2.2.2.1 Model ####

package com.situ.parking.parking.model;
    
    public class ParkingModel {
        
    	private String id; // 停车场的编号
    	private String isEmpty; // 有没有车
    	private String carName; // 车的号码
    	private String startDate; // 开始的时间
    
    	public String getId() {
        
    		return id;
    	}
    
    	public void setId(String id) {
        
    		this.id = id;
    	}
    
    	public String getIsEmpty() {
        
    		return isEmpty;
    	}
    
    	public void setIsEmpty(String isEmpty) {
        
    		this.isEmpty = isEmpty;
    	}
    
    	public String getCarName() {
        
    		return carName;
    	}
    
    	public void setCarName(String carName) {
        
    		this.carName = carName;
    	}
    
    	public String getStartDate() {
        
    		return startDate;
    	}
    
    	public void setStartDate(String startDate) {
        
    		this.startDate = startDate;
    	}
    
    	@Override
    	public String toString() {
        
    		return "ParkingModel [id=" + id + ", isEmpty=" + isEmpty + ", carName=" + carName + ", startDate=" + startDate
    				+ "]";
    	}
    }

#### 2.2.2.2 Dao ####

package com.situ.parking.parking.dao;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.util.ArrayList;
    import java.util.List;
    
    import com.situ.parking.parking.model.ParkingModel;
    import com.situ.parking.util.JDBCUtil;
    
    public class ParkingDao {
        
    
    	Connection connection = null;
    	PreparedStatement ps = null;
    
    	public String insert(ParkingModel model) {
        
    		String sql = "insert into parking(p_id, isempty, carName, StartDate) values(?,?,?,?)";
    		try {
        
    			connection = JDBCUtil.getConnection();
    			ps = connection.prepareStatement(sql);
    			ps.setString(1, model.getId());
    			ps.setString(2, model.getIsEmpty());
    			ps.setString(3, model.getCarName());
    			ps.setString(4, model.getStartDate());
    			int res = ps.executeUpdate();
    			return res + "";
    		} catch (Exception e) {
        
    			e.printStackTrace();
    		} finally {
        
    			JDBCUtil.close(connection, ps);
    		}
    		return -1 + "";
    	}
    
    	public String delete(ParkingModel model) {
        
    		String sql = "delete from parking where p_id = ?";
    		try {
        
    			connection = JDBCUtil.getConnection();
    			ps = connection.prepareStatement(sql);
    			ps.setString(1, model.getId());
    			int res = ps.executeUpdate();
    			return res + "";
    		} catch (Exception e) {
        
    			e.printStackTrace();
    		} finally {
        
    			JDBCUtil.close(connection, ps);
    		}
    		return -1 + "";
    	}
    
    	public String update(ParkingModel model) {
        
    		String sql = "update parking set isempty = ?, carname = ?, startdate = ? where p_id = ?";
    		try {
        
    			connection = JDBCUtil.getConnection();
    			ps = connection.prepareStatement(sql);
    			ps.setString(1, model.getIsEmpty());
    			ps.setString(2, model.getCarName());
    			ps.setString(3, model.getStartDate());
    			ps.setString(4, model.getId());
    			int res = ps.executeUpdate();
    			return res + "";
    		} catch (Exception e) {
        
    			e.printStackTrace();
    		} finally {
        
    			JDBCUtil.close(connection, ps);
    		}
    		return -1 + "";
    	}
    
    	public List<ParkingModel> select(ParkingModel model) {
        
    		StringBuffer sql = new StringBuffer("select p_id, isempty, carName, startDate from parking where 1 = 1");
    		List<Object> list = new ArrayList<Object>();
    		list = where(model, sql);
    		System.out.println(sql);
    		List<ParkingModel> list2 = new ArrayList<ParkingModel>();
    		ResultSet set;
    		try {
        
    			connection = JDBCUtil.getConnection();
    			ps = connection.prepareStatement(sql.toString());
    			for (int i = 0; i < list.size(); i++) {
        
    				ps.setObject(i + 1, list.get(i));
    			}
    			System.out.println("1");
    			set = ps.executeQuery();
    			System.out.println("1");
    			while (set.next()) {
        
    				ParkingModel model2 = new ParkingModel();
    				model2.setId(set.getString("p_id"));
    				model2.setIsEmpty(set.getString("isempty"));
    				model2.setCarName(set.getString("carname"));
    				model2.setStartDate(set.getString("startdate"));
    				list2.add(model2);
    			}
    		} catch (Exception e) {
        
    			e.printStackTrace();
    			return null;
    		} finally {
        
    			JDBCUtil.close(connection, ps);
    		}
    		return list2;
    	}
    
    	public List<Object> where(ParkingModel model, StringBuffer sql) {
        
    		List<Object> list = new ArrayList<Object>();
    		String id = model.getId();
    		if (id != null && !id.trim().isEmpty()) {
        
    			sql.append(" and p_id = ?");
    			list.add(id);
    		}
    		String isempty = model.getIsEmpty();
    		if (isempty != null && !isempty.trim().isEmpty()) {
        
    			sql.append(" and isempty = ?");
    			list.add(isempty);
    		}
    		String carName = model.getCarName();
    		if (carName != null && !carName.trim().isEmpty()) {
        
    			sql.append(" and carName = ?");
    			list.add(carName);
    		}
    		String startDate = model.getStartDate();
    		if (startDate != null && !startDate.trim().isEmpty()) {
        
    			sql.append(" and startDate = ?");
    			list.add(startDate);
    		}
    		return list;
    	}
    
    }

#### 2.2.2.3 Service ####

package com.situ.parking.parking.service;
    
    import java.util.ArrayList;
    import java.util.List;
    
    import com.situ.parking.parking.dao.ParkingDao;
    import com.situ.parking.parking.model.ParkingModel;
    
    public class ParkingService {
        
    	ParkingDao dao = new ParkingDao();
    
    	public String insert(ParkingModel model) {
        
    		ParkingModel model2 = new ParkingModel();
    		model2.setId(model.getId());
    		List<ParkingModel> list = new ArrayList<ParkingModel>();
    		list = dao.select(model2);
    		if (list == null || list.isEmpty()) {
        
    			return dao.insert(model);
    		}
    		return null;
    	}
    
    	public String delete(ParkingModel model) {
        
    		return dao.delete(model);
    	}
    
    	public String update(ParkingModel model) {
        
    		return dao.update(model);
    	}
    
    	public List<ParkingModel> selectList(ParkingModel model) {
        
    		return dao.select(model);
    	}
    
    	public ParkingModel selectModel(ParkingModel model) {
        
    		List<ParkingModel> list = new ArrayList<ParkingModel>();
    		list = dao.select(model);
    		if (list == null || list.isEmpty()) {
        
    			return null;
    		}
    		return list.get(0);
    	}
    }

### 2.2.3 record包 ###

#### 2.2.3.1 Model ####

package com.situ.parking.record.model;
    
    public class RecordModel {
        
    	private int id; // 记录的主键
    	private String carId; // 停车位的编号
    	private String carName; // 车的车牌
    	private String startDate; // 开始停放时间
    	private String endDate; // 结束停放时间
    	private String price; // 价格
    
    	public int getId() {
        
    		return id;
    	}
    
    	public void setId(int id) {
        
    		this.id = id;
    	}
    
    	public String getCarId() {
        
    		return carId;
    	}
    
    	public void setCarId(String carId) {
        
    		this.carId = carId;
    	}
    
    	public String getCarName() {
        
    		return carName;
    	}
    
    	public void setCarName(String carName) {
        
    		this.carName = carName;
    	}
    
    	public String getStartDate() {
        
    		return startDate;
    	}
    
    	public void setStartDate(String startDate) {
        
    		this.startDate = startDate;
    	}
    
    	public String getEndDate() {
        
    		return endDate;
    	}
    
    	public void setEndDate(String endDate) {
        
    		this.endDate = endDate;
    	}
    
    	public String getPrice() {
        
    		return price;
    	}
    
    	public void setPrice(String price) {
        
    		this.price = price;
    	}
    
    	@Override
    	public String toString() {
        
    		return "RecordModel [id=" + id + ", carId=" + carId + ", carName=" + carName + ", startDate=" + startDate
    				+ ", endDate=" + endDate + ", price=" + price + "]";
    	}
    
    }

#### 2.2.3.2 Dao ####

package com.situ.parking.record.dao;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.util.ArrayList;
    import java.util.List;
    
    import com.situ.parking.record.model.RecordModel;
    import com.situ.parking.util.JDBCUtil;
    
    public class RecordDao {
        
    	Connection connection = null;
    	PreparedStatement ps = null;
    
    	public String insert(RecordModel model) {
        
    		String sql = "insert into record(carid, carname, startdate, enddate, price) values(?,?,?,?,?)";
    		try {
        
    			connection = JDBCUtil.getConnection();
    			ps = connection.prepareStatement(sql);
    			ps.setString(1, model.getCarId());
    			ps.setString(2, model.getCarName());
    			ps.setString(3, model.getStartDate());
    			ps.setString(4, model.getEndDate());
    			ps.setString(5, model.getPrice());
    			int res = ps.executeUpdate();
    			return res + "";
    		} catch (Exception e) {
        
    			e.printStackTrace();
    		} finally {
        
    			JDBCUtil.close(connection, ps);
    		}
    		return -1 + "";
    	}
    
    	public String delete(RecordModel model) {
        
    		String sql = "delete from record where id = ?";
    		try {
        
    			connection = JDBCUtil.getConnection();
    			ps = connection.prepareStatement(sql);
    			ps.setInt(1, model.getId());
    			int res = ps.executeUpdate();
    			return res + "";
    		} catch (Exception e) {
        
    			e.printStackTrace();
    		} finally {
        
    			JDBCUtil.close(connection, ps);
    		}
    		return -1 + "";
    	}
    
    	public String update(RecordModel model) {
        
    		String sql = "update record set carid = ?, carname = ?, startdate = ?, enddate = ?, price = ? where id = ?";
    		try {
        
    			connection = JDBCUtil.getConnection();
    			ps = connection.prepareStatement(sql);
    			ps.setString(1, model.getCarName());
    			ps.setString(2, model.getStartDate());
    			ps.setString(3, model.getEndDate());
    			ps.setString(4, model.getPrice());
    			ps.setInt(5, model.getId());
    			int res = ps.executeUpdate();
    			return res + "";
    		} catch (Exception e) {
        
    			e.printStackTrace();
    		} finally {
        
    			JDBCUtil.close(connection, ps);
    		}
    		return -1 + "";
    	}
    
    	public List<RecordModel> select(RecordModel model) {
        
    		StringBuffer sql = new StringBuffer(
    				"select id, carid, carname, startdate, enddate, price from record where 1=1");
    		List<Object> list = new ArrayList<Object>();
    		list = where(model, sql);
    		List<RecordModel> list2 = new ArrayList<RecordModel>();
    		ResultSet set;
    		try {
        
    			connection = JDBCUtil.getConnection();
    			ps = connection.prepareStatement(sql.toString());
    			for (int i = 0; i < list.size(); i++) {
        
    				ps.setObject(i + 1, list.get(i));
    			}
    			set = ps.executeQuery();
    			while (set.next()) {
        
    				RecordModel model2 = new RecordModel();
    				model2.setId(set.getInt("id"));
    				model2.setCarId(set.getString("carid"));
    				model2.setCarName(set.getString("carname"));
    				model2.setStartDate(set.getString("startdate"));
    				model2.setEndDate(set.getString("enddate"));
    				model2.setPrice(set.getString("price"));
    				list2.add(model2);
    			}
    		} catch (Exception e) {
        
    			e.printStackTrace();
    		} finally {
        
    			JDBCUtil.close(connection, ps);
    		}
    		return list2;
    	}
    
    	public List<Object> where(RecordModel model, StringBuffer sql) {
        
    		List<Object> list = new ArrayList<Object>();
    		int id = model.getId();
    		if (id != 0) {
        
    			sql.append("and id = ?");
    			list.add(id);
    		}
    		String carId = model.getCarId();
    		if (carId != null && !carId.trim().isEmpty()) {
        
    			sql.append("and carid = ?");
    			list.add(carId);
    		}
    		String carname = model.getCarName();
    		if (carname != null && !carname.trim().isEmpty()) {
        
    			sql.append("and carname = ?");
    			list.add(carname);
    		}
    		String startdate = model.getStartDate();
    		if (startdate != null && !startdate.trim().isEmpty()) {
        
    			sql.append("and startdate = ?");
    			list.add(startdate);
    		}
    		String enddate = model.getEndDate();
    		if (enddate != null && !enddate.trim().isEmpty()) {
        
    			sql.append("and enddate = ?");
    			list.add(enddate);
    		}
    		String price = model.getPrice();
    		if (price != null && !price.trim().isEmpty()) {
        
    			sql.append("and price = ?");
    			list.add(price);
    		}
    		return list;
    	}
    }

#### 2.2.3.3 Service ####

package com.situ.parking.record.service;
    
    import java.util.List;
    
    import com.situ.parking.record.dao.RecordDao;
    import com.situ.parking.record.model.RecordModel;
    
    public class RecordService {
        
    	RecordDao dao = new RecordDao();
    
    	public String insert(RecordModel model) {
        
    		return dao.insert(model);
    	}
    
    	public String delete(RecordModel model) {
        
    		return dao.delete(model);
    	}
    
    	public String update(RecordModel model) {
        
    		return dao.update(model);
    	}
    
    	public List<RecordModel> selectList(RecordModel model) {
        
    		return dao.select(model);
    	}
    
    	public void selectModel(RecordModel model) {
        
    		
    	}
    }

### 2.2.3 Test ###

package com.situ.parking.test;
    
    import java.time.LocalDateTime;
    import java.time.format.DateTimeFormatter;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Scanner;
    
    import com.situ.parking.parking.model.ParkingModel;
    import com.situ.parking.parking.service.ParkingService;
    import com.situ.parking.record.model.RecordModel;
    import com.situ.parking.record.service.RecordService;
    
    public class Test {
        
    	Scanner scanner = new Scanner(System.in);
    	DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    	boolean f = true;
    	ParkingService parkingService = new ParkingService();
    	RecordService recordService = new RecordService();
    
    	public static void main(String[] args) {
        
    		Test parkingManagement = new Test();
    		parkingManagement.start();
    	}
    
    	public void updateModel(String id) {
        
    		ParkingModel model = new ParkingModel();
    		model.setId(id);
    		model.setIsEmpty("true");
    		model.setCarName("无");
    		model.setStartDate("无");
    		parkingService.update(model);
    	}
    
    	public void start() {
        
    		while (f) {
        
    			System.out.println("1.停车");
    			System.out.println("2.取车");
    			System.out.println("3.查询全部停车位的状态");
    			System.out.println("4.查询全部的消费记录");
    			System.out.println("5.退出");
    			System.out.println("请输入你要执行的操作：");
    			String string = scanner.nextLine();
    			start_1(string);
    		}
    	}
    
    	public void start_1(String string) {
        
    		if (string.equals("1")) {
        
    			parkingCar();
    		} else if (string.equals("2")) {
        
    			getCar();
    		} else if (string.equals("3")) {
        
    			queryParking();
    		} else if (string.equals("4")) {
        
    			queryRecord();
    		} else if (string.equals("5")) {
        
    			f = false;
    			System.out.println("已为你退出系统！");
    		} else {
        
    			System.out.println("你的输入有误！");
    		}
    	}
    
    	public void parkingCar() {
        
    		System.out.println("请输入车辆的信息：");
    		System.out.println("车牌：");
    		String carName = scanner.nextLine();
    		System.out.println("高度：");
    		String height = scanner.nextLine();
    		double height_ = Double.parseDouble(height);
    		if (height_ > 3.0) {
        
    			System.out.println("对不起，当前车辆高度大于3米，不允许进入。");
    			start();
    		}
    		System.out.println("请输入你想停放的停车位：");
    		String id_ = scanner.nextLine();
    		ParkingModel model = new ParkingModel();
    		model.setId(id_);
    		if (parkingService.selectModel(model).getIsEmpty().equals("false")) {
        
    			System.out.println("对不起，当前位置已经有车！");
    		} else {
        
    			model.setCarName(carName);
    			model.setIsEmpty("false");
    			LocalDateTime startDate_ = LocalDateTime.now();
    			String startDate = df.format(startDate_);
    			model.setStartDate(startDate);
    			parkingService.update(model);
    		}
    	}
    
    	public void getCar() {
        
    		System.out.println("1.按车牌取车");
    		System.out.println("2.按停车位取车");
    		System.out.println("请选择你的操作：");
    		String string = scanner.nextLine();
    		if (string.equals("1")) {
        
    			getCarName();
    		} else if (string.equals("2")) {
        
    			getCarParking();
    		} else {
        
    			System.out.println("你的输入有误。");
    			start();
    		}
    	}
    
    	public void getCarName() {
        
    		System.out.println("请输入你的车牌号：");
    		String carName = scanner.nextLine(); // 车牌号
    		ParkingModel model = new ParkingModel();
    		model.setCarName(carName);
    		ParkingModel model1 = parkingService.selectModel(model); // 利用车名称找到记录
    		if (model1 != null) {
        
    			String id = model1.getId(); // 获取这是第几个停车位
    			updateModel(id);
    			String startdate_ = model1.getStartDate();
    			LocalDateTime startdate = LocalDateTime.parse(startdate_, df);
    			LocalDateTime enddate = LocalDateTime.now();
    			int price = (enddate.getSecond() - startdate.getSecond()) / 10 * 2; // 按照10s2元的价格 便于查看
    			RecordModel model2 = new RecordModel();
    			model2.setCarId(id); // 输入停车位
    			model2.setCarName(carName);
    			model2.setStartDate(startdate_);
    			DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    			model2.setEndDate(fmt.format(enddate));
    			model2.setPrice(String.valueOf(price));
    			recordService.insert(model2);
    		} else {
        
    			System.out.println("没有查到！");
    		}
    
    	}
    
    	public void getCarParking() {
        
    		System.out.println("请输入你的停车位：");
    		String id = scanner.nextLine();
    		ParkingModel model = new ParkingModel();
    		model.setId(id);
    		ParkingModel model1 = parkingService.selectModel(model); // 利用停车场id找到记录
    		if (model1 != null) {
        
    			updateModel(id);
    			String carName = model1.getCarName();// 获取这个停车位的车牌号
    			String startdate_ = model1.getStartDate();
    			LocalDateTime startdate = LocalDateTime.parse(startdate_, df); // 转成时间类型
    			LocalDateTime enddate = LocalDateTime.now();
    			int price = (enddate.getSecond() - startdate.getSecond()) / 10 * 2; // 按照10s2元的价格 便于查看
    			RecordModel model2 = new RecordModel();
    			model2.setCarId(id); // 输入停车位
    			model2.setCarName(carName);
    			model2.setStartDate(startdate_);
    			DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    			model2.setEndDate(fmt.format(enddate));
    			model2.setPrice(String.valueOf(price));
    			recordService.insert(model2);
    		} else {
        
    			System.out.println("没有查到！");
    		}
    	}
    
    	public void queryParking() {
        
    		List<ParkingModel> list = new ArrayList<ParkingModel>();
    		ParkingModel model = new ParkingModel();
    		list = parkingService.selectList(model);
    		for (ParkingModel parkingModel : list) {
        
    			System.out.println(parkingModel);
    		}
    	}
    
    	public void queryRecord() {
        
    		List<RecordModel> list = new ArrayList<RecordModel>();
    		RecordModel model = new RecordModel();
    		list = recordService.selectList(model);
    		for (RecordModel recordModel : list) {
        
    			System.out.println(recordModel);
    		}
    	}
    
    }

[https_www.huangliangshuai.com]: https://www.huangliangshuai.com/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70]: /images/20221124/b650e7727aa6459f9179dee0353045fd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70 1]: /images/20221124/b38ce314eefc42e9a16a23306f90dc71.png
[20200810192701335.png]: /images/20221124/108d31047e744e59bdd3dcae78041572.png
[20200808181027673.png]: /images/20221124/748d6391b1264120bff3cb33c61ec163.png