基于PostgreSQL和JavaFX的银行数据库设计与应用系统
选题背景：
随着银行业务的复杂性不断提高，对用户，账户和交易等核心信息数字化管理需求显著提升。本项目基于PostgreSQL数据库技术和JavaFX界面开发，模拟银行核心业务流程，构建一套实现基础功能的银行数据库管理系统。
需求分析：
项目通过对银行业务需求的分析，定义了以下核心功能模块：
- 用户管理：实现用户的增删改查和角色权限管理。
- 账户管理：实现账户创建、余额查询和删除功能。
- 交易管理：实现存款、取款等交易记录管理。
- 贷款与还款管理：支持贷款申请、利率管理和还款记录管理。
- 图形化交互：提供直观友好的用户界面，简化数据库操作流程。
数据库设计：
基于 PostgreSQL 数据库设计了一套规范化的表结构，具体包括：
- Users 表：存储用户基本信息。
- Account 表：存储用户账户信息，支持账户余额管理。
- Transaction 表：记录账户交易流水。
- Loan 表：存储贷款信息。
- Repayment 表：记录还款信息。
- InterestRate 表：支持利率管理。
数据库设计遵循规范化原则，并通过外键约束实现数据关联，使用 ON DELETE CASCADE 确保数据一致性。
ER图：
数据库实施：
利用pgAdmin工具实现数据库表结构的创建与调试，通过SQL脚本导入数据库的初始化数据。数据库支持在pgAdmin和基于JavaFX开发的图形化界面实时的增删改查。
数据库应用开发：
- 使用 JavaFX 开发用户界面，支持用户通过图形化界面直接与数据库交互，进行数据的实时增删改查。
- 使用 JDBC 连接 PostgreSQL 数据库，封装常见的增删改查功能。
项目功能：
- 用户管理：
- 支持用户注册、登录、信息管理（增删改查）。
- 用户角色分为管理员和普通用户，不同角色拥有不同操作权限。
- 账户管理：
- 实现账户的创建、余额查询和删除功能。
- 支持用户与账户的一对多关系管理。
- 交易管理：
- 记录每笔交易的金额、时间、类型和描述。
- 支持存款、取款和交易流水查询功能。
- 贷款与还款管理：
- 记录用户贷款信息，包括金额、利率、期限和状态。
- 支持还款操作，并维护还款记录。
- 图形化界面：
- 使用 JavaFX 开发登录界面、管理员界面和普通用户界面。
- 用户通过直观的操作界面完成复杂数据库操作，提升交互体验。
项目亮点：
- 前后端分离：前端基于JavaFX 使用IDE eclipse编写交互界面，后端使用 PostgreSQL 存储和管理数据，通过 JDBC 连接实现无缝交互。
- 友好的用户界面：使用 JavaFX 提供动态化的交互界面，支持不同用户角色的权限操作。
- 模块化设计：采用 MVC 分层架构，代码组织清晰，便于扩展和维护，也方便未来添加更多功能模块。
- 数据完整性：利用外键约束和级联删除规则，确保数据的一致性和完整性。
- 图形化操作：用户无需编写 SQL 查询，通过图形化界面完成实时的增删改查操作。
数据库应用系统运行和演示：
在vs code使用基于 Python 的 faker 库的脚本文件生成初始化数据：
在 pgAdmin 上建表，导入数据后，进行 pgAdmin 上的数据查询：
查询用户表：
查询账户表：
查询交易表：
基于 JavaFX 的界面设计工程文件：
在 Eclipse 上通过 JDBC 连接数据库对数据库进行界面化的增删改查操作：
运行 DatabaseConnection.java 连接数据库：
控制台输出信息：
运行 Main.java 进入图形化登录界面：
复制一个管理员账号登录，使用 JavaFX 的 PasswordField 控件对密码进行隐藏：
验证当账户 ID 或密码输错时候会有报错提醒：
进入管理员界面后，可以看到所有用户的信息，包括用户类别，用户每个账户下的余额：
尝试从界面化操作向数据添加用户信息：
可以看到界面化操作可以实时将用户添加进数据库：
再验证从 pgAdmin 添加用户，也同时可以在 Eclipse 界面上显示。这里添加用户小亮：
在项目开始后也可以从 pgAdmin 添加用户，并且可以在图形化界面显示：
删除操作，将用户小明删除，在 pgAdmin 上再次查询可以看到用户小明已经被删除：
进入新添加的用户小亮的账户，可以看到刚才添加时给他设置的初始余额 7478.75：
存款：
取款：
用户界面还可以看到记录实时时间的存取款记录：
再回到 pgAdmin 查询账户表可以看到小亮账户余额已经变成 6978.75：
再查询交易表，可以看到留下的两条存取款记录：
建立数据库
我用的是PostgreSQL 10
在pgAdmin上的数据库建表代码
bank_database_schema.sql
-- 文件名：bank_database_schema.sql
-- 描述：银行数据库设计表结构定义
-- 创建 Users 表
CREATE TABLE Users (
user_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
id_number VARCHAR(20) UNIQUE NOT NULL,
phone_number VARCHAR(15),
email VARCHAR(50),
password VARCHAR(255) NOT NULL,
user_role VARCHAR(20) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 创建 Account 表
CREATE TABLE Account (
account_id SERIAL PRIMARY KEY,
user_id INT NOT NULL REFERENCES Users(user_id) ON DELETE CASCADE,
balance DECIMAL(15, 2) DEFAULT 0.0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 创建 Transaction 表
CREATE TABLE Transaction (
transaction_id SERIAL PRIMARY KEY,
account_id INT NOT NULL REFERENCES Account(account_id) ON DELETE CASCADE,
transaction_type VARCHAR(20) NOT NULL,
amount DECIMAL(15, 2) NOT NULL,
transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
description TEXT
);
-- 创建 Loan 表
CREATE TABLE Loan (
loan_id SERIAL PRIMARY KEY,
user_id INT NOT NULL REFERENCES Users(user_id) ON DELETE CASCADE,
loan_amount DECIMAL(15, 2) NOT NULL,
interest_rate DECIMAL(5, 2) NOT NULL,
loan_term INT NOT NULL,
start_date DATE NOT NULL,
end_date DATE,
status VARCHAR(20) DEFAULT 'unpaid',
remaining_amount DECIMAL(15, 2) DEFAULT 0.0
);
-- 创建 Repayment 表
CREATE TABLE Repayment (
repayment_id SERIAL PRIMARY KEY,
loan_id INT NOT NULL REFERENCES Loan(loan_id) ON DELETE CASCADE,
repayment_amount DECIMAL(15, 2) NOT NULL,
repayment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
remaining_amount DECIMAL(15, 2) DEFAULT 0.0
);
-- 创建 InterestRate 表
CREATE TABLE InterestRate (
rate_id SERIAL PRIMARY KEY,
account_type VARCHAR(20) NOT NULL,
rate DECIMAL(5, 2) NOT NULL,
effective_date DATE NOT NULL
);
随机数据生成
使用vs code基于Python的faker库生成随机数据
makeData.py
import random
from faker import Faker
# 初始化 Faker
fake = Faker()
# 生成 SQL 文件
def generate_sql_file(num_users=50, num_accounts=100, num_transactions=200, file_name="output.sql"):
with open(file_name, "w", encoding="utf-8") as file:
# 写入 Users 表的 SQL 语句
admin_count = max(1, num_users // 10) # 管理员数量为总用户的 10%
for i in range(1, num_users + 1):
name = fake.name().replace("'", "''") # 规范化名字，处理单引号
id_number = str(fake.unique.random_number(digits=18)) # 唯一18位数字
phone_number = fake.unique.numerify("1##########") # 格式化电话号码
email = fake.email().replace("'", "''")
password = "123456" # 固定初始密码
user_role = "管理员" if i <= admin_count else "普通用户" # 前几位用户设为管理员
sql_user = (
f"INSERT INTO Users (name, id_number, phone_number, email, user_role, password) "
f"VALUES ('{name}', '{id_number}', '{phone_number}', '{email}', '{user_role}', '{password}');\n"
)
file.write(sql_user)
# 写入 Account 表的 SQL 语句
for account_id in range(1, num_accounts + 1):
user_id = random.randint(1, num_users) # 随机分配用户
balance = round(random.uniform(100, 10000), 2) # 随机余额
sql_account = (
f"INSERT INTO Account (user_id, balance) "
f"VALUES ({user_id}, {balance});\n"
)
file.write(sql_account)
# 写入 Transaction 表的 SQL 语句
for transaction_id in range(1, num_transactions + 1):
account_id = random.randint(1, num_accounts) # 随机选择账户
transaction_type = random.choice(["deposit", "withdrawal"]) # 随机选择交易类型
amount = round(random.uniform(10, 1000), 2) # 随机金额
description = fake.text(max_nb_chars=50).replace("'", "''") # 限制描述长度
sql_transaction = (
f"INSERT INTO Transaction (account_id, transaction_type, amount, description) "
f"VALUES ({account_id}, '{transaction_type}', {amount}, '{description}');\n"
)
file.write(sql_transaction)
print(f"SQL statements have been written to {file_name}")
# 主函数
if __name__ == "__main__":
# 根据需求生成数据
generate_sql_file(num_users=20, num_accounts=20, num_transactions=30, file_name="bank_data.sql")
将生成的随机数据设置为SQL语句，可以直接在数据库执行
bank_data.sql
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Shawn Mitchell', '111430174828023805', '13459927149', 'hamiltoncorey@example.org', '管理员', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Brooke Smith', '455972571286691791', '18747909747', 'brianlopez@example.com', '管理员', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Frederick Hamilton', '914647555966448596', '17042194928', 'vanessamcguire@example.org', '普通用户', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Stephanie Pope', '831027771234354527', '11545753205', 'calvinsantiago@example.net', '普通用户', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Scott Crosby', '473404209904942433', '15592781047', 'shannon87@example.com', '普通用户', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Heather Keller', '108996214639832114', '15693212305', 'yhampton@example.com', '普通用户', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Jose Cunningham', '617971393664048434', '17794131715', 'mary78@example.org', '普通用户', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('David Vasquez', '264962795655727623', '11680163925', 'thomasdarren@example.org', '普通用户', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Thomas Wilson', '983189128670863046', '11162324051', 'jasonperez@example.org', '普通用户', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Kerri Becker', '99807865366989124', '17746581397', 'marquezrobert@example.org', '普通用户', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Christina Lamb', '444113155786233533', '12487797951', 'kevin36@example.com', '普通用户', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Brandon Turner', '982908583417668194', '17719162393', 'patricia18@example.com', '普通用户', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Gabrielle Jones MD', '881248438264024715', '11351065372', 'amber97@example.com', '普通用户', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Karen Holland', '203863307236086405', '10031431444', 'fhendricks@example.org', '普通用户', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Susan Garcia', '782459584954157714', '14129137631', 'larrydecker@example.net', '普通用户', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Kevin Owen', '644831308777996033', '18794555905', 'kristin30@example.net', '普通用户', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Gina Lee', '699268531381211781', '17669202466', 'brandon12@example.net', '普通用户', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Carol Watson', '196354328120239422', '15148227678', 'marquezjames@example.net', '普通用户', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Mandy Erickson', '507729922452310392', '19345993878', 'tkemp@example.net', '普通用户', '123456');
INSERT INTO Users (name, id_number, phone_number, email, user_role, password) VALUES ('Charles Villarreal', '662489994660239838', '14198356977', 'elizabeth97@example.net', '普通用户', '123456');
INSERT INTO Account (user_id, balance) VALUES (8, 3460.41);
INSERT INTO Account (user_id, balance) VALUES (19, 753.87);
INSERT INTO Account (user_id, balance) VALUES (19, 6759.81);
INSERT INTO Account (user_id, balance) VALUES (3, 7418.76);
INSERT INTO Account (user_id, balance) VALUES (14, 5621.28);
INSERT INTO Account (user_id, balance) VALUES (20, 7019.86);
INSERT INTO Account (user_id, balance) VALUES (16, 1000.6);
INSERT INTO Account (user_id, balance) VALUES (17, 5142.28);
INSERT INTO Account (user_id, balance) VALUES (9, 2784.35);
INSERT INTO Account (user_id, balance) VALUES (8, 9460.47);
INSERT INTO Account (user_id, balance) VALUES (17, 6837.86);
INSERT INTO Account (user_id, balance) VALUES (14, 9723.85);
INSERT INTO Account (user_id, balance) VALUES (11, 5796.07);
INSERT INTO Account (user_id, balance) VALUES (19, 5455.54);
INSERT INTO Account (user_id, balance) VALUES (4, 6344.67);
INSERT INTO Account (user_id, balance) VALUES (12, 5831.94);
INSERT INTO Account (user_id, balance) VALUES (14, 9500.69);
INSERT INTO Account (user_id, balance) VALUES (17, 2126.03);
INSERT INTO Account (user_id, balance) VALUES (13, 9870.19);
INSERT INTO Account (user_id, balance) VALUES (17, 5651.8);
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (14, 'withdrawal', 163.55, 'Early run theory seek word general.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (17, 'deposit', 335.96, 'One visit bed bank range.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (19, 'deposit', 18.66, 'Dream indicate effort series play explain.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (18, 'deposit', 524.29, 'Position half meet check.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (14, 'deposit', 497.25, 'Office off soldier course true do people else.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (9, 'deposit', 332.17, 'This admit different five natural.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (1, 'withdrawal', 862.07, 'Indicate wonder specific left citizen piece.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (1, 'deposit', 880.06, 'Every professor similar while meet.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (14, 'withdrawal', 351.6, 'Cell kitchen suddenly style agree.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (6, 'deposit', 113.48, 'Person end five together view bit perhaps.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (10, 'withdrawal', 857.98, 'Behind indeed accept view choice against.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (9, 'deposit', 602.11, 'View turn maybe until. Future scene new growth.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (20, 'withdrawal', 848.04, 'Congress attorney camera a buy fly.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (19, 'withdrawal', 401.99, 'Choose view center few member everything.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (10, 'withdrawal', 626.44, 'With such very music also action.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (1, 'deposit', 259.35, 'Truth commercial economy various.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (17, 'deposit', 264.5, 'There also require building keep.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (11, 'withdrawal', 174.88, 'Century tell seek history.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (4, 'deposit', 530.13, 'Work technology industry general tax.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (15, 'deposit', 187.19, 'Pick training letter near a catch.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (10, 'withdrawal', 536.1, 'Book maybe worry determine.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (6, 'withdrawal', 481.64, 'Year do standard dog. New there best above.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (16, 'deposit', 514.0, 'Forward possible trouble glass often.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (8, 'withdrawal', 815.44, 'Remember economic voice interesting.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (15, 'withdrawal', 864.53, 'Data however why nothing.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (19, 'withdrawal', 784.2, 'Past though TV team involve seat professional.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (7, 'deposit', 421.08, 'Beyond drug back morning center.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (16, 'withdrawal', 762.2, 'True keep know partner discussion likely almost.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (5, 'deposit', 59.64, 'Old because indicate teach arrive personal.');
INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (8, 'withdrawal', 399.81, 'Ahead type enough matter.');
基于JavaFX实现的UI
项目IDE使用eclipse
需要去JavaFX官网下载JavaFX的一些.jar文件添加到Modulepath
添加.jar文件
在eclipse右键Java项目，选择Properties,在左侧找到Java Build Path,进去后在右侧选择Libraries,选择Modulepaht,点击Add External JARs,把JavaFX的.jar文件都添加进去即可
配置VM路径
右键项目，点击Run As,选择Run Configurations,选择当前项目的Main,点击Arguments,在VM arguments里面添加
--module-path "JavaFX的安装路径\to\javafx-sdk\lib" --add-modules javafx.controls,javafx.fxml
把路径指向JavaFX的lib即可，.jar文件都在里面
application.Main.java
package application;
import javafx.application.Application;
import javafx.fxml.FXMLLoader;
import javafx.scene.Scene;
import javafx.scene.layout.AnchorPane;
import javafx.stage.Stage;
public class Main extends Application {
@Override
public void start(Stage primaryStage) {
try {
// 加载登录界面的FXML文件
FXMLLoader loader = new FXMLLoader(getClass().getResource("/resources/login.fxml"));
AnchorPane root = loader.load();
// 创建一个场景并设置到主舞台
Scene scene = new Scene(root, 800, 600);
primaryStage.setTitle("银行数据库系统 - 登录界面");
primaryStage.setScene(scene);
primaryStage.show();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
launch(args); // 启动JavaFX应用
}
}
controllers.AdminController.java
package controllers;
import service.AccountService;
import service.UserService;
import javafx.fxml.FXML;
import javafx.scene.image.ImageView;
import javafx.scene.control.ListView;
import javafx.scene.control.Button;
import model.Account;
import model.User;
import javafx.scene.control.Alert;
import javafx.scene.control.PasswordField;
import javafx.scene.control.TextField;
import javafx.scene.control.ComboBox;
import java.util.Map; // 引入 Map 接口
import java.util.HashMap; // 引入 HashMap 类
import java.util.List; // 引入 List 接口
import java.util.ArrayList; // 引入 ArrayList 类
public class AdminController {
@FXML
private ImageView backgroundImage;
@FXML private ListView<String> userListView;
@FXML private Button addUserButton;
@FXML private Button deleteUserButton;
// 新增的输入字段
@FXML private TextField nameField;
@FXML private TextField idNumberField;
@FXML private TextField phoneField;
@FXML private TextField emailField;
@FXML private PasswordField passwordField;
@FXML private ComboBox<String> roleComboBox;
private UserService userService = new UserService();
private AccountService accountService = new AccountService();
private User currentUser;
/**
* 设置当前用户并加载所有用户信息
* @param user 登录的用户
*/
public void setUser(User user) {
this.currentUser = user;
loadAllUsers();
}
@FXML
public void initialize() {
loadAllUsers();
}
private void loadAllUsers() {
List<User> users = userService.getAllUsers();
Map<Integer, List<Account>> accountsMap = accountService.getAllAccounts();
userListView.getItems().clear();
for (User user : users) {
List<Account> accounts = accountsMap.getOrDefault(user.getUserId(), new ArrayList<>());
StringBuilder accountBalances = new StringBuilder();
if (!accounts.isEmpty()) {
for (Account account : accounts) {
accountBalances.append("账户 ID: ").append(account.getAccountId())
.append(", 余额: ").append(account.getBalance()).append("; ");
}
} else {
accountBalances.append("无账户信息");
}
userListView.getItems().add(
user.getUserId() + ": " + user.getName() + " (" + user.getUserRole() + ") - " + accountBalances
);
}
}
@FXML
public void addUser() {
// 获取管理员输入的数据
String name = nameField.getText().trim();
String idNumber = idNumberField.getText().trim();
String phone = phoneField.getText().trim();
String email = emailField.getText().trim();
String password = passwordField.getText().trim();
String role = roleComboBox.getValue();
// 简单的输入验证
if (name.isEmpty() || idNumber.isEmpty() || password.isEmpty() || role == null) {
showAlert("Error", "请完整填写所有信息");
return;
}
// 检查 ID Number 是否唯一
List<User> existingUsers = userService.getAllUsers();
for (User user : existingUsers) {
if (user.getIdNumber().equals(idNumber)) {
showAlert("Error", "ID号码已经存在");
return;
}
}
// 创建新用户对象
User newUser = new User();
newUser.setName(name);
newUser.setIdNumber(idNumber);
newUser.setPhoneNumber(phone);
newUser.setEmail(email);
newUser.setPassword(password);
newUser.setUserRole(role);
// 调用服务添加新用户
boolean success = userService.addUser(newUser);
if (success) {
System.out.println("AdminController: User added with ID: " + newUser.getUserId()); // 调试信息
// 如果是普通用户，则创建一个账户记录
if ("普通用户".equalsIgnoreCase(role)) {
boolean accountCreated = accountService.createAccountForUser(newUser.getUserId());
if (accountCreated) {
System.out.println("AdminController: Account created for user ID: " + newUser.getUserId()); // 调试信息
} else {
showAlert("Error", "成功创建用户但创建账户失败");
return;
}
}
showAlert("Success", "用户添加成功");
loadAllUsers();
clearInputFields();
} else {
showAlert("Error", "用户添加失败");
}
}
@FXML
public void deleteUser() {
String selected = userListView.getSelectionModel().getSelectedItem();
if (selected != null) {
int userId = Integer.parseInt(selected.split(":")[0]);
if (userId == currentUser.getUserId()) {
showAlert("Error", "不能删除现在正在登录的管理员");
return;
}
boolean success = userService.deleteUser(userId);
if (success) {
showAlert("Success", "用户成功删除");
loadAllUsers();
} else {
showAlert("Error", "用户删除失败");
}
} else {
showAlert("Error", "没有选择用户");
}
}
private void clearInputFields() {
nameField.clear();
idNumberField.clear();
phoneField.clear();
emailField.clear();
passwordField.clear();
roleComboBox.getSelectionModel().clearSelection();
}
private void showAlert(String title, String message) {
Alert alert = new Alert(title.equals("Error") ? Alert.AlertType.ERROR : Alert.AlertType.INFORMATION);
alert.setTitle(title);
alert.setHeaderText(null);
alert.setContentText(message);
alert.showAndWait();
}
}
controllers.LoginController.java
package controllers;
import javafx.fxml.FXML;
import javafx.scene.image.ImageView;
import javafx.fxml.FXMLLoader;
import javafx.scene.Scene;
import javafx.scene.control.Alert;
import javafx.scene.control.Button;
import javafx.scene.control.PasswordField;
import javafx.scene.control.TextField;
import javafx.scene.layout.AnchorPane;
import javafx.stage.Stage;
import service.UserService;
import model.User;
import java.io.IOException;
public class LoginController {
@FXML private TextField idNumberField;
@FXML private PasswordField passwordField;
@FXML private Button loginButton;
@FXML
private ImageView backgroundImage; // 添加此字段来与FXML绑定背景图
private UserService userService = new UserService();
@FXML
public void handleLogin() {
String idNumber = idNumberField.getText();
String password = passwordField.getText();
User user = userService.validateLogin(idNumber, password);
if (user != null) {
try {
FXMLLoader loader;
AnchorPane root;
System.out.println("agw"+user.getUserRole());
if ("管理员".equalsIgnoreCase(user.getUserRole())) {
// 加载管理员界面
loader = new FXMLLoader(getClass().getResource("/resources/admin.fxml"));
root = loader.load();
// 获取 AdminController 并设置用户信息
AdminController adminController = loader.getController();
adminController.setUser(user);
} else {
// 加载普通用户界面
loader = new FXMLLoader(getClass().getResource("/resources/user.fxml"));
root = loader.load();
// 获取 UserController 并设置用户信息
UserController userController = loader.getController();
userController.setUser(user);
}
// 设置新的场景
Stage stage = (Stage) loginButton.getScene().getWindow();
Scene scene = new Scene(root, 800, 800);
stage.setScene(scene);
stage.setTitle("银行系统 - " + (user.getUserRole().equalsIgnoreCase("管理员") ? "管理员" : "普通用户"));
stage.show();
} catch (IOException e) {
e.printStackTrace();
showAlert("错误", "加载下一个界面失败");
}
} else {
showAlert("Login Failed", "Invalid credentials!");
}
}
private boolean isAdmin(String idNumber) {
// Check user role in the database (this can be more complex based on your logic)
return idNumber.startsWith("A"); // Assuming 'A' prefix for admins
}
private void loadScene(String fxmlFile) {
try {
FXMLLoader loader = new FXMLLoader(getClass().getResource(fxmlFile));
AnchorPane root = loader.load();
Stage stage = (Stage) loginButton.getScene().getWindow();
Scene scene = new Scene(root);
stage.setScene(scene);
stage.show();
} catch (Exception e) {
e.printStackTrace();
}
}
private void showAlert(String title, String message) {
Alert alert = new Alert(Alert.AlertType.ERROR);
alert.setTitle(title);
alert.setHeaderText(null);
alert.setContentText(message);
alert.showAndWait();
}
}
controllers.UserController.java
package controllers;
import javafx.fxml.FXML;
import javafx.scene.image.ImageView;
import javafx.scene.control.Button;
import javafx.scene.control.ListView;
import javafx.scene.control.TextField;
import javafx.scene.control.Alert;
import model.User;
import service.AccountService;
import service.TransactionService;
import model.Account;
import model.Transaction;
import java.util.List;
public class UserController {
@FXML private ImageView backgroundImage;
@FXML private TextField accountInfoField;
@FXML private TextField depositAmountField;
@FXML private TextField withdrawAmountField;
@FXML private Button depositButton;
@FXML private Button withdrawButton;
@FXML private ListView<String> transactionListView;
private User currentUser;
private AccountService accountService = new AccountService();
private TransactionService transactionService = new TransactionService();
private int accountId;
/**
* 设置当前用户并加载账户信息和交易记录
* @param user 登录的用户
*/
@FXML
public void initialize() {
// 如果有需要在初始化时执行的逻辑，也可以放在这里
// 例如，设置背景图
// backgroundImage.setImage(new Image("path/to/image.png"));
System.out.println("UserController: Initialized");
}
public void setUser(User user) {
this.currentUser = user;
System.out.println("UserController: Setting user: " + user.getName()); // 调试信息
loadAccountInfo();
loadTransactionHistory();
}
/**
* 加载当前用户的账户信息
*/
private void loadAccountInfo() {
Account account = accountService.getAccountByUserId(currentUser.getUserId());
if (account != null) {
this.accountId = account.getAccountId();
accountInfoField.setText("账户 ID: " + account.getAccountId() + " \n余额: " + account.getBalance());
System.out.println("UserController: Account loaded: " + account.getAccountId()); // 调试信息
} else {
System.out.println("UserController: Account not found for user: " + currentUser.getUserId()); // 调试信息
showAlert("Error", "账户没有找到");
}
}
/**
* 加载当前用户的交易记录
*/
private void loadTransactionHistory() {
System.out.println("UserController: Loading transaction history for account ID: " + accountId); // 调试信息
List<Transaction> transactions = transactionService.getTransactionHistory(accountId);
transactionListView.getItems().clear();
for (Transaction txn : transactions) {
String txnInfo = txn.getTransactionDate() + " - " + txn.getTransactionType() + " - " + txn.getAmount() + " - " + txn.getDescription();
transactionListView.getItems().add(txnInfo);
}
}
@FXML
public void handleDeposit() {
try {
double amount = Double.parseDouble(depositAmountField.getText());
if (amount <= 0) {
showAlert("Error", "存款金额必须是整数");
return;
}
boolean success = accountService.deposit(accountId, amount);
if (success) {
transactionService.recordTransaction(accountId, "deposit", amount, "Deposit");
loadAccountInfo();
loadTransactionHistory();
showAlert("Success", "存款成功");
} else {
showAlert("Error", "存款失败");
}
} catch (NumberFormatException e) {
showAlert("Error", "Invalid deposit amount.");
}
}
@FXML
public void handleWithdraw() {
try {
double amount = Double.parseDouble(withdrawAmountField.getText());
if (amount <= 0) {
showAlert("Error", "取钱必须是正数");
return;
}
// 检查余额是否足够
Account account = accountService.getAccountByUserId(currentUser.getUserId());
if (account.getBalance() < amount) {
showAlert("Error", "无效金额");
return;
}
boolean success = accountService.withdraw(accountId, amount);
if (success) {
transactionService.recordTransaction(accountId, "withdrawal", amount, "Withdrawal");
loadAccountInfo();
loadTransactionHistory();
showAlert("Success", "取款成功");
} else {
showAlert("Error", "取款失败");
}
} catch (NumberFormatException e) {
showAlert("Error", "无效取款金额");
}
}
private void showAlert(String title, String message) {
Alert.AlertType type = title.equals("Error") ? Alert.AlertType.ERROR : Alert.AlertType.INFORMATION;
Alert alert = new Alert(type);
alert.setTitle(title);
alert.setHeaderText(null);
alert.setContentText(message);
alert.showAndWait();
}
}
database.DatabaseConnection.java
package database;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DatabaseConnection {
// 数据库连接信息
private static final String URL = "jdbc:postgresql://localhost:5432/BANKDB";
private static final String USER = "postgres";
private static final String PASSWORD = "123456";
/**
* 建立数据库连接的方法
* @return Connection 对象，如果连接失败，返回 null
*/
public static Connection getConnection() {
try {
// 加载 PostgreSQL JDBC 驱动（显式加载以防止模块化项目问题）
Class.forName("org.postgresql.Driver");
// 建立连接
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
System.out.println("成功连接数据库");
return conn;
} catch (ClassNotFoundException e) {
System.out.println("JDBC Driver not found: " + e.getMessage());
return null;
} catch (SQLException e) {
System.out.println("数据库连接错误: " + e.getMessage());
return null;
}
}
/**
* 测试数据库连接的主方法
*/
public static void main(String[] args) {
Connection conn = getConnection();
if (conn != null) {
System.out.println("连接已激活!");
} else {
System.out.println("连接建立失败.");
}
}
}
model.Account.java
package model;
public class Account {
private int accountId;
private int userId;
private double balance;
// Getters and setters
public int getAccountId() {
return accountId;
}
public void setAccountId(int accountId) {
this.accountId = accountId;
}
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public double getBalance() {
return balance;
}
public void setBalance(double balance) {
this.balance = balance;
}
}
model.Transaction.java
package model;
import java.sql.Timestamp;
public class Transaction {
private int transactionId;
private int accountId;
private String transactionType;
private double amount;
private Timestamp transactionDate;
private String description;
// Getters and setters
public int getTransactionId() {
return transactionId;
}
public void setTransactionId(int transactionId) {
this.transactionId = transactionId;
}
public int getAccountId() {
return accountId;
}
public void setAccountId(int accountId) {
this.accountId = accountId;
}
public String getTransactionType() {
return transactionType;
}
public void setTransactionType(String transactionType) {
this.transactionType = transactionType;
}
public double getAmount() {
return amount;
}
public void setAmount(double amount) {
this.amount = amount;
}
public Timestamp getTransactionDate() {
return transactionDate;
}
public void setTransactionDate(Timestamp transactionDate) {
this.transactionDate = transactionDate;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
}
model.User.java
package model;
public class User {
private int userId;
private String name;
private String idNumber;
private String phoneNumber;
private String email;
private String password;
private String userRole;
// Getters and setters
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getIdNumber() {
return idNumber;
}
public void setIdNumber(String idNumber) {
this.idNumber = idNumber;
}
public String getPhoneNumber() {
return phoneNumber;
}
public void setPhoneNumber(String phoneNumber) {
this.phoneNumber = phoneNumber;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getUserRole() {
return userRole;
}
public void setUserRole(String userRole) {
this.userRole = userRole;
}
}
resources.admin_style.css
.root {
-fx-background-color: #e0e0e0;
}
.button {
-fx-background-color: #FF5722;
-fx-text-fill: white;
}
.list-view {
-fx-border-color: #ccc;
}
resources.login_style.css
.root {
-fx-background-color: #f0f0f0;
}
.text-field, .password-field {
-fx-background-color: #ffffff;
-fx-border-color: #ccc;
-fx-font-size: 14px;
}
.button {
-fx-background-color: #4CAF50;
-fx-text-fill: white;
}
resources.user_style.css
.root {
-fx-background-color: #ffffff;
}
.button {
-fx-background-color: #2196F3;
-fx-text-fill: white;
}
.text-field {
-fx-background-color: #ffffff;
-fx-border-color: #ccc;
}
resources.admin.fxml
<?xml version="1.0" encoding="UTF-8"?>
<?import javafx.scene.layout.AnchorPane?>
<?import javafx.scene.control.Button?>
<?import javafx.scene.control.ListView?>
<?import javafx.scene.control.TextField?>
<?import javafx.scene.control.Label?>
<?import javafx.scene.control.PasswordField?>
<?import javafx.scene.control.ComboBox?>
<?import javafx.scene.image.ImageView?>
<?import javafx.scene.image.Image?>
<?import javafx.collections.FXCollections?>
<AnchorPane xmlns:fx="http://javafx.com/fxml" fx:controller="controllers.AdminController">
<children>
<!-- 现有控件 -->
<ImageView fx:id="backgroundImage" fitHeight="800.0" fitWidth="800.0" layoutX="0.0" layoutY="0.0">
<image>
<Image url="@admin_background.png"/>
</image>
</ImageView>
<ListView fx:id="userListView" layoutX="100.0" layoutY="100.0" prefHeight="400.0" prefWidth="600.0"/>
<Button fx:id="addUserButton" layoutX="150.0" layoutY="520.0" text="Add User" onAction="#addUser"/>
<Button fx:id="deleteUserButton" layoutX="300.0" layoutY="520.0" text="Delete User" onAction="#deleteUser"/>
<!-- 新增的输入字段 -->
<Label layoutX="100.0" layoutY="550.0" text="姓名:"/>
<TextField fx:id="nameField" layoutX="150.0" layoutY="545.0" promptText="请输入姓名" prefWidth="200.0"/>
<Label layoutX="400.0" layoutY="550.0" text="身份证号码："/>
<TextField fx:id="idNumberField" layoutX="480.0" layoutY="545.0" promptText="请输入身份证号码" prefWidth="200.0"/>
<Label layoutX="100.0" layoutY="580.0" text="电话号码:"/>
<TextField fx:id="phoneField" layoutX="150.0" layoutY="575.0" promptText="请输入电话号码" prefWidth="200.0"/>
<Label layoutX="400.0" layoutY="580.0" text="电子邮箱:"/>
<TextField fx:id="emailField" layoutX="480.0" layoutY="575.0" promptText="请输入电子邮箱" prefWidth="200.0"/>
<Label layoutX="100.0" layoutY="610.0" text="密码:"/>
<PasswordField fx:id="passwordField" layoutX="150.0" layoutY="605.0" promptText="请输入密码" prefWidth="200.0"/>
<Label layoutX="400.0" layoutY="610.0" text="用户类型:"/>
<ComboBox fx:id="roleComboBox" layoutX="480.0" layoutY="605.0" prefWidth="200.0">
<items>
<FXCollections fx:factory="observableArrayList">
<java.lang.String fx:value="普通用户"/>
<java.lang.String fx:value="管理员"/>
</FXCollections>
</items>
</ComboBox>
</children>
</AnchorPane>
resources.login.fxml
<?xml version="1.0" encoding="UTF-8"?>
<?import javafx.scene.layout.AnchorPane?>
<?import javafx.scene.control.Button?>
<?import javafx.scene.control.PasswordField?>
<?import javafx.scene.control.TextField?>
<?import javafx.scene.image.ImageView?>
<?import javafx.scene.image.Image?>
<AnchorPane xmlns:fx="http://javafx.com/fxml" fx:controller="controllers.LoginController">
<children>
<ImageView fx:id="backgroundImage" fitHeight="600.0" fitWidth="800.0" layoutX="0.0" layoutY="0.0">
<image>
<Image url="@login_background.png"/>
</image>
</ImageView>
<TextField fx:id="idNumberField" layoutX="300.0" layoutY="200.0" promptText="请输入ID（身份证号码）"/>
<PasswordField fx:id="passwordField" layoutX="300.0" layoutY="250.0" promptText="请输入密码"/>
<Button fx:id="loginButton" layoutX="350.0" layoutY="300.0" text="Login" onAction="#handleLogin"/>
</children>
</AnchorPane>
resources.user.fxml
<?xml version="1.0" encoding="UTF-8"?>
<?import javafx.scene.layout.AnchorPane?>
<?import javafx.scene.control.Button?>
<?import javafx.scene.control.ListView?>
<?import javafx.scene.control.TextField?>
<?import javafx.scene.image.ImageView?>
<?import javafx.scene.image.Image?>
<AnchorPane xmlns:fx="http://javafx.com/fxml" fx:controller="controllers.UserController">
<children>
<ImageView fx:id="backgroundImage" fitHeight="800.0" fitWidth="800.0" layoutX="0.0" layoutY="0.0">
<image>
<Image url="@user_background.png"/>
</image>
</ImageView>
<TextField fx:id="accountInfoField" layoutX="150.0" layoutY="100.0" prefWidth="500.0" editable="false"/>
<TextField fx:id="depositAmountField" layoutX="150.0" layoutY="200.0" promptText="请输入存款金额"/>
<TextField fx:id="withdrawAmountField" layoutX="150.0" layoutY="250.0" promptText="请输入取款金额"/>
<Button fx:id="depositButton" layoutX="350.0" layoutY="200.0" text="存款" onAction="#handleDeposit"/>
<Button fx:id="withdrawButton" layoutX="350.0" layoutY="250.0" text="取款" onAction="#handleWithdraw"/>
<ListView fx:id="transactionListView" layoutX="100.0" layoutY="400.0" prefHeight="150.0" prefWidth="600.0"/>
</children>
</AnchorPane>
service.AccountService.java
package service;
import database.DatabaseConnection;
import model.Account;
import java.util.Map; // 引入 Map 接口
import java.util.HashMap; // 引入 HashMap 类
import java.util.List; // 引入 List 接口
import java.util.ArrayList; // 引入 ArrayList 类
import java.sql.*;
public class AccountService {
// 获取用户账户信息
public Account getAccountInfo(int userId) {
String query = "SELECT * FROM Account WHERE user_id = ?";
try (Connection conn = DatabaseConnection.getConnection();
PreparedStatement stmt = conn.prepareStatement(query)) {
stmt.setInt(1, userId);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
Account account = new Account();
account.setAccountId(rs.getInt("account_id"));
account.setUserId(rs.getInt("user_id"));
account.setBalance(rs.getDouble("balance"));
return account;
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public boolean createAccountForUser(int userId) {
String query = "INSERT INTO Account (user_id, balance) VALUES (?, ?)";
try (Connection conn = DatabaseConnection.getConnection();
PreparedStatement stmt = conn.prepareStatement(query)) {
stmt.setInt(1, userId);
stmt.setDouble(2, 0.0); // 初始余额为0
int rowsAffected = stmt.executeUpdate();
if (rowsAffected > 0) {
System.out.println("AccountService: Account created for user ID: " + userId); // 调试信息
return true;
} else {
System.out.println("AccountService: Failed to create account for user ID: " + userId); // 调试信息
return false;
}
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
public Map<Integer, List<Account>> getAllAccounts() {
String query = "SELECT * FROM Account";
Map<Integer, List<Account>> accountsMap = new HashMap<>();
try (Connection conn = DatabaseConnection.getConnection();
PreparedStatement stmt = conn.prepareStatement(query);
ResultSet rs = stmt.executeQuery()) {
while (rs.next()) {
Account account = new Account();
account.setAccountId(rs.getInt("account_id"));
account.setUserId(rs.getInt("user_id"));
account.setBalance(rs.getDouble("balance"));
// 将账户信息添加到对应用户的列表中
accountsMap.computeIfAbsent(account.getUserId(), k -> new ArrayList<>()).add(account);
}
} catch (SQLException e) {
e.printStackTrace();
}
return accountsMap;
}
public Account getAccountByUserId(int userId) {
String query = "SELECT * FROM Account WHERE user_id = ?";
try (Connection conn = DatabaseConnection.getConnection();
PreparedStatement stmt = conn.prepareStatement(query)) {
stmt.setInt(1, userId);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
Account account = new Account();
account.setAccountId(rs.getInt("account_id"));
account.setUserId(rs.getInt("user_id"));
account.setBalance(rs.getDouble("balance"));
return account;
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public List<Account> getAccountsByUserId(int userId) {
String query = "SELECT * FROM Account WHERE user_id = ?";
List<Account> accounts = new ArrayList<>();
try (Connection conn = DatabaseConnection.getConnection();
PreparedStatement stmt = conn.prepareStatement(query)) {
stmt.setInt(1, userId);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
Account account = new Account();
account.setAccountId(rs.getInt("account_id"));
account.setUserId(rs.getInt("user_id"));
account.setBalance(rs.getDouble("balance"));
accounts.add(account);
}
} catch (SQLException e) {
e.printStackTrace();
}
return accounts;
}
public boolean updateBalance(int accountId, double amount) {
String query = "UPDATE Account SET balance = balance + ? WHERE account_id = ?";
try (Connection conn = DatabaseConnection.getConnection();
PreparedStatement stmt = conn.prepareStatement(query)) {
stmt.setDouble(1, amount);
stmt.setInt(2, accountId);
return stmt.executeUpdate() > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
// 存款
public boolean deposit(int accountId, double amount) {
String query = "UPDATE Account SET balance = balance + ? WHERE account_id = ?";
try (Connection conn = DatabaseConnection.getConnection();
PreparedStatement stmt = conn.prepareStatement(query)) {
stmt.setDouble(1, amount);
stmt.setInt(2, accountId);
return stmt.executeUpdate() > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
// 取款
public boolean withdraw(int accountId, double amount) {
String query = "UPDATE Account SET balance = balance - ? WHERE account_id = ?";
try (Connection conn = DatabaseConnection.getConnection();
PreparedStatement stmt = conn.prepareStatement(query)) {
stmt.setDouble(1, amount);
stmt.setInt(2, accountId);
return stmt.executeUpdate() > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
}
service.TransactionService.java
package service;
import database.DatabaseConnection;
import model.Transaction;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class TransactionService {
// 记录交易信息
public boolean recordTransaction(int accountId, String transactionType, double amount, String description) {
String query = "INSERT INTO Transaction (account_id, transaction_type, amount, description) VALUES (?, ?, ?, ?)";
try (Connection conn = DatabaseConnection.getConnection();
PreparedStatement stmt = conn.prepareStatement(query)) {
stmt.setInt(1, accountId);
stmt.setString(2, transactionType);
stmt.setDouble(3, amount);
stmt.setString(4, description);
return stmt.executeUpdate() > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
// 获取某个账户的所有交易记录
public List<Transaction> getTransactionHistory(int accountId) {
List<Transaction> transactions = new ArrayList<>();
String query = "SELECT * FROM Transaction WHERE account_id = ? ORDER BY transaction_date DESC";
try (Connection conn = DatabaseConnection.getConnection();
PreparedStatement stmt = conn.prepareStatement(query)) {
stmt.setInt(1, accountId);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
Transaction txn = new Transaction();
txn.setTransactionId(rs.getInt("transaction_id"));
txn.setAccountId(rs.getInt("account_id"));
txn.setTransactionType(rs.getString("transaction_type"));
txn.setAmount(rs.getDouble("amount"));
txn.setTransactionDate(rs.getTimestamp("transaction_date"));
txn.setDescription(rs.getString("description"));
transactions.add(txn);
}
} catch (SQLException e) {
e.printStackTrace();
}
return transactions;
}
}
service.UserService.java
package service;
import database.DatabaseConnection;
import model.User;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class UserService {
public boolean login(String idNumber, String password) {
String query = "SELECT * FROM Users WHERE id_number = ? AND password = ?";
try (Connection conn = DatabaseConnection.getConnection();
PreparedStatement stmt = conn.prepareStatement(query)) {
stmt.setString(1, idNumber);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
public User validateLogin(String idNumber, String password) {
String query = "SELECT * FROM Users WHERE id_number = ? AND password = ?";
try (Connection connection = DatabaseConnection.getConnection();
PreparedStatement statement = connection.prepareStatement(query)) {
statement.setString(1, idNumber);
statement.setString(2, password);
ResultSet resultSet = statement.executeQuery();
if (resultSet.next()) {
User user = new User();
user.setUserId(resultSet.getInt("user_id"));
user.setName(resultSet.getString("name"));
user.setIdNumber(resultSet.getString("id_number"));
user.setPhoneNumber(resultSet.getString("phone_number"));
user.setEmail(resultSet.getString("email"));
user.setUserRole(resultSet.getString("user_role"));
return user;
}
} catch (SQLException e) {
e.printStackTrace();
}
return null; // 验证失败
}
/**
* 获取所有用户信息
* @return 用户列表
*/
public List<User> getAllUsers() {
List<User> users = new ArrayList<>();
String query = "SELECT * FROM Users";
try (Connection conn = DatabaseConnection.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query)) {
while (rs.next()) {
User user = new User();
user.setUserId(rs.getInt("user_id"));
user.setName(rs.getString("name"));
user.setIdNumber(rs.getString("id_number"));
user.setPhoneNumber(rs.getString("phone_number"));
user.setEmail(rs.getString("email"));
user.setUserRole(rs.getString("user_role"));
users.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}
/**
* 添加用户
* @param user 要添加的用户
* @return 是否成功
*/
public boolean addUser(User user) {
String query = "INSERT INTO Users (name, id_number, phone_number, email, password, user_role) VALUES (?, ?, ?, ?, ?, ?)";
try (Connection conn = DatabaseConnection.getConnection();
PreparedStatement stmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS)) {
stmt.setString(1, user.getName());
stmt.setString(2, user.getIdNumber());
stmt.setString(3, user.getPhoneNumber());
stmt.setString(4, user.getEmail());
stmt.setString(5, user.getPassword());
stmt.setString(6, user.getUserRole());
int rowsAffected = stmt.executeUpdate();
if (rowsAffected > 0) {
// 获取生成的user_id
ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()) {
int userId = rs.getInt(1);
user.setUserId(userId); // 设置用户ID
System.out.println("UserService: User added with ID: " + userId); // 调试信息
}
return true;
}
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public boolean deleteUser(int userId) {
String query = "DELETE FROM Users WHERE user_id = ?";
try (Connection conn = DatabaseConnection.getConnection();
PreparedStatement stmt = conn.prepareStatement(query)) {
stmt.setInt(1, userId);
return stmt.executeUpdate() > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
}
module-info.java
module BANKDB {
requires javafx.controls;
requires javafx.fxml;
requires java.sql;
opens controllers to javafx.fxml;
exports controllers;
opens model to javafx.base;
exports model;
exports application;
exports database;
exports service;
}