根据package com
.parking
.dao;
import com
.parking
.bean
.ParkingRecord;
import com
.parking
.util
.DBUtil;
import java
.sql
.*;
import java
.util
.ArrayList;
import java
.util
.List;
/**
* 停车记录数据访问对象
* 负责与数据库的交互操作
*/
public class ParkingRecordDao {
// 数据库连接对象
private Connection connection;
public ParkingRecordDao() {
this
.connection
= DBUtil
.getConnection
();
}
/**
* 添加停车记录
* @param record 停车记录对象
* @return 添加成功返回记录ID，失败返回-1
*/
public int addParkingRecord(ParkingRecord record) {
String sql
= "INSERT INTO parking_records (user_id, car_number, start_time, parking_spot, vehicle_type) " +
"VALUES (?, ?, ?, ?, ?)";
try (PreparedStatement
ps = connection
.prepareStatement(sql, Statement
.RETURN_GENERATED_KEYS)) {
ps.setInt(1, record
.getUserId());
ps.setString(2, record
.getCarNumber());
ps.setTimestamp(3, new Timestamp(record
.getStartTime()
.getTime()));
ps.setString(4, record
.getParkingSpot());
ps.setString(5, record
.getVehicleType());
int affectedRows
= ps.executeUpdate
();
if (affectedRows
== 0) {
throw new SQLException("创建停车记录失败");
}
// 获取自增ID
try (
ResultSet rs = ps.getGeneratedKeys()) {
if (
rs.next()) {
int id
= rs.getInt(1);
record
.setId(id);
return id;
} else {
throw new SQLException("获取停车记录ID失败");
}
}
} catch (SQLException e) {
e
.printStackTrace
();
return -1;
}
}
/**
* 更新停车记录
* @param record 停车记录对象
* @return 更新成功返回true，否则false
*/
public boolean updateParkingRecord(ParkingRecord record) {
String sql
= "UPDATE parking_records SET end_time
= ?, fee
= ?, status
= ?, " +
"payment_status
= ?, duration
= ? WHERE id
= ?";
try (PreparedStatement
ps = connection
.prepareStatement(sql)) {
if (record
.getEndTime() !
= null) {
ps.setTimestamp(1, new Timestamp(record
.getEndTime()
.getTime()));
} else {
ps.setNull(1, Types
.TIMESTAMP);
}
ps.setBigDecimal(2, record
.getFee());
ps.setString(3, record
.getStatus());
ps.setString(4, record
.getPaymentStatus());
ps.setInt(5, record
.getDuration());
ps.setInt(6, record
.getId());
int affectedRows
= ps.executeUpdate
();
return affectedRows > 0;
} catch (SQLException e) {
e
.printStackTrace
();
return false;
}
}
/**
* 根据ID获取停车记录
* @param id 记录ID
* @return 停车记录对象，不存在返回null
*/
public ParkingRecord getRecordById(int id) {
String sql
= "SELECT * FROM parking_records WHERE id
= ?";
try (PreparedStatement
ps = connection
.prepareStatement(sql)) {
ps.setInt(1, id);
try (
ResultSet rs = ps.executeQuery()) {
if (
rs.next()) {
return map
ResultSetToRecord(
rs);
}
}
} catch (SQLException e) {
e
.printStackTrace
();
}
return null;
}
/**
* 结束停车并计算费用
* @param recordId 记录ID
* @return 更新成功返回true，否则false
*/
public boolean endParking(int recordId) {
// 先获取记录
ParkingRecord record
= getRecordById(recordId);
if (record
== null || !"parking"
.equals(record
.getStatus())) {
return false;
}
// 结束停车
record
.endParking
();
// 更新数据库
return updateParkingRecord(record);
}
/**
* 标记记录为已支付
* @param recordId 记录ID
* @return 更新成功返回true，否则false
*/
public boolean markAsPaid(int recordId) {
String sql
= "UPDATE parking_records SET payment_status
= 'paid' " +
"WHERE id
= ? AND status
= 'completed' AND payment_status
= 'unpaid'";
try (PreparedStatement
ps = connection
.prepareStatement(sql)) {
ps.setInt(1, recordId);
int affectedRows
= ps.executeUpdate
();
return affectedRows > 0;
} catch (SQLException e) {
e
.printStackTrace
();
return false;
}
}
/**
* 获取用户当前停车记录
* @param userId 用户ID
* @return 当前停车记录，没有返回null
*/
public ParkingRecord getCurrentParking(int userId) {
String sql
= "SELECT * FROM parking_records " +
"WHERE user_id
= ? AND status
= 'parking' " +
"ORDER BY start_time DESC LIMIT 1";
try (PreparedStatement
ps = connection
.prepareStatement(sql)) {
ps.setInt(1, userId);
try (
ResultSet rs = ps.executeQuery()) {
if (
rs.next()) {
return map
ResultSetToRecord(
rs);
}
}
} catch (SQLException e) {
e
.printStackTrace
();
}
return null;
}
/**
* 获取用户历史停车记录（分页）
* @param userId 用户ID
* @param page 页码
* @param pageSize 每页数量
* @return 停车记录列表
*/
public List<ParkingRecord> getParkingHistory(int userId, int page, int pageSize) {
List<ParkingRecord> records
= new ArrayList<>
();
String sql
= "SELECT * FROM parking_records " +
"WHERE user_id
= ? AND status
= 'completed' " +
"ORDER BY start_time DESC " +
"LIMIT ? OFFSET ?";
try (PreparedStatement
ps = connection
.prepareStatement(sql)) {
ps.setInt(1, userId);
ps.setInt(2, pageSize);
ps.setInt(3, (page - 1) * pageSize);
try (
ResultSet rs = ps.executeQuery()) {
while (
rs.next()) {
records
.add(map
ResultSetToRecord(
rs));
}
}
} catch (SQLException e) {
e
.printStackTrace
();
}
return records;
}
/**
* 获取用户未支付记录
* @param userId 用户ID
* @return 未支付记录列表
*/
public List<ParkingRecord> getUnpaidRecords(int userId) {
List<ParkingRecord> records
= new ArrayList<>
();
String sql
= "SELECT * FROM parking_records " +
"WHERE user_id
= ? AND status
= 'completed' AND payment_status
= 'unpaid'";
try (PreparedStatement
ps = connection
.prepareStatement(sql)) {
ps.setInt(1, userId);
try (
ResultSet rs = ps.executeQuery()) {
while (
rs.next()) {
records
.add(map
ResultSetToRecord(
rs));
}
}
} catch (SQLException e) {
e
.printStackTrace
();
}
return records;
}
/**
* 获取用户总停车次数
* @param userId 用户ID
* @return 停车次数
*/
public int getTotalParkingCount(int userId) {
String sql
= "SELECT COUNT(*) FROM parking_records WHERE user_id
= ?";
try (PreparedStatement
ps = connection
.prepareStatement(sql)) {
ps.setInt(1, userId);
try (
ResultSet rs = ps.executeQuery()) {
if (
rs.next()) {
return
rs.getInt(1);
}
}
} catch (SQLException e) {
e
.printStackTrace
();
}
return 0;
}
/**
* 获取用户总消费金额
* @param userId 用户ID
* @return 总消费金额
*/
public BigDecimal getTotalSpent(int userId) {
String sql
= "SELECT SUM(fee) FROM parking_records " +
"WHERE user_id
= ? AND status
= 'completed' AND payment_status
= 'paid'";
try (PreparedStatement
ps = connection
.prepareStatement(sql)) {
ps.setInt(1, userId);
try (
ResultSet rs = ps.executeQuery()) {
if (
rs.next()) {
BigDecimal total
= rs.getBigDecimal(1);
return total !
= null ? total : BigDecimal
.ZERO;
}
}
} catch (SQLException e) {
e
.printStackTrace
();
}
return BigDecimal
.ZERO;
}
/**
* 将
ResultSet映射为ParkingRecord对象
* @param
rs 结果集
* @return ParkingRecord对象
* @throws SQLException
*/
private ParkingRecord map
ResultSetToRecord(
ResultSet rs) throws SQLException {
ParkingRecord record
= new ParkingRecord
();
record
.setId(
rs.getInt("id"));
record
.setUserId(
rs.getInt("user_id"));
record
.setCarNumber(
rs.getString("car_number"));
record
.setStartTime(
rs.getTimestamp("start_time"));
record
.setEndTime(
rs.getTimestamp("end_time"));
record
.setFee(
rs.getBigDecimal("fee"));
record
.setStatus(
rs.getString("status"));
record
.setPaymentStatus(
rs.getString("payment_status"));
record
.setParkingSpot(
rs.getString("parking_spot"));
record
.setVehicleType(
rs.getString("vehicle_type"));
record
.setDuration(
rs.getInt("duration"));
return record;
}
/**
* 获取用户平均停车时间
* @param userId 用户ID
* @return 平均停车时间（分钟）
*/
public int getAverageParkingTime(int userId) {
String sql
= "SELECT AVG(duration) FROM parking_records " +
"WHERE user_id
= ? AND status
= 'completed'";
try (PreparedStatement
ps = connection
.prepareStatement(sql)) {
ps.setInt(1, userId);
try (
ResultSet rs = ps.executeQuery()) {
if (
rs.next()) {
return
rs.getInt(1);
}
}
} catch (SQLException e) {
e
.printStackTrace
();
}
return 0;
}
/**
* 获取最近停车记录
* @param userId 用户ID
* @param limit 记录条数
* @return 最近停车记录列表
*/
public List<ParkingRecord> getRecentRecords(int userId, int limit) {
List<ParkingRecord> records
= new ArrayList<>
();
String sql
= "SELECT * FROM parking_records " +
"WHERE user_id
= ? " +
"ORDER BY start_time DESC LIMIT ?";
try (PreparedStatement
ps = connection
.prepareStatement(sql)) {
ps.setInt(1, userId);
ps.setInt(2, limit);
try (
ResultSet rs = ps.executeQuery()) {
while (
rs.next()) {
records
.add(map
ResultSetToRecord(
rs));
}
}
} catch (SQLException e) {
e
.printStackTrace
();
}
return records;
}
}
上面生成的该代码中 DBUtil
.getConnection，BigDecimal报错，帮我修改
本文针对实现登录注册功能时遇到的问题进行了详细解析。一是关于ResultSet中元素为null而非ResultSet本身为null的情况；二是PreparedStatement中设置参数时下标从1开始而非0开始的常见误区。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
