一、实践目的
熟练掌握Java、JavaFX、数据库基础知识,并应用IntelliJ IDEA 2021.2、JavaFX Scene Builder 2.0、SQLyog Community 技术开发实现项目“东软工资管理系统”。
二、实验环境
开发技术:Java基础知识和环境配置、Java集合、异常处理、异常机制(异常的结构、异常处理的机制)、开发流程(MVC)、Java数据库连接技术(jdbc)、Java FX Scene builder
使用
开发环境:
Java编程语言
IDEA(java编程语言的集成开发环境)
SQLyog(图形化管理MYSQL数据库的工具)
MySQL5.1.15
Java FX Scene builder
三、实验内容
第一阶段(基础信息维护):
部门相关界面的绘制
第一步:绘制部门界面内容
其中用了tableview控件,SplitePane用于把页面划分出上下两部分,VBox用于把下半部分划分成垂直的两部分,HBox水平摆放控件。
其中表格中的表头信息有:部门编号、部门名称、人员编制数、备注
第二步:显示部门页面
在MainController中传入ImportInfoView对象,用于切换主页面中的布局内容
在MainApp中获取MainController,把ImportInfoView传递过去
完成显示部门页面的逻辑
部门表格中的内容
第一步:创建数据库及部门表
创建数据库的sql语句
创建表格
查看所有表格
插入三条数据
查询表中的数据
创建实体类Dept
该类的对象用于存储表中的记录数据,一条记录对应该类的一个对象
编写Dao及其实现类
该接口声明操作表dept的增删改查的方法
实现DeptController的逻辑
部门信息维护
- 新建部门
第一步:对话框界面绘制
DeptDialogView.fxml,该视图既有新建部门的功能,又有更新部门的功能,如果是新建部门的话,输入框为空;如果是更新部门的话输入框回显部门信息。
第二步:完成DeptDialogController的编写
该对话框承担了要完成新建部门和更新部门信息的任务
第三步:表写Dao层
- 更新部门
第一步 : 准备好dialog
第二步: 实现onUpdateDept方法
第三步:修改实体类Dept,实现数据的双向绑定
- 删除部门
第一步:实现deleteById方法
第二步:在控制类中将选中的部门信息从列表中移除,并调用Dao实现中的deleteById方法
- 查询部门
第一步:实现querByName方法
第二步:在控制类中获取TextField获得的关键词进行判断给予相应结果查询
职位信息维护
- 数据库表
创建数据库及职位表
创建数据库的sql语句
创建表格
查看所有表格
插入三条数据
查询表中的数据
绘制界面
第一步:绘制职位界面内容
其中用了tableview控件,SplitePane用于把页面划分出上下两部分,VBox用于把下半部分划分成垂直的两部分,HBox水平摆放控件。
其中表格中的表头信息有:职位编号、职位名称、备注
第二步:显示职位页面
显示界面(在MainController中添加一下方法,用于显示职位信息界面)
在MainController中传入mainView对象,用于切换主页面中的布局内容
在MainApp中获取MainController,把mainView传递过去
完成显示职位页面的逻辑
编写控制器
JobController控制器
JobEditDialogController控制器
实体类Job
该类的对象用于存储表中的记录数据,一条记录对应该类的一个对象
- 数据访问层Dao
该接口声明操作表job的增删改查的方法
实现JobController的逻辑
实现类
完成操作表job的增删改查的方法
- 新建职位
第一步:对话框界面绘制
JobEditDialogView.fxml,该视图既有新建部门的功能,又有更新部门的功能,如果是新建部门的话,输入框为空;如果是更新部门的话输入框回显部门信息。
第二步:完成JobEditDialogController的编写
该对话框承担了要完成新建部门和更新部门信息的任务
第三步:表写Dao层
- 更新部门
第一步 : 准备好dialog
第二步: 实现onUpdateJob方法
第三步:修改实体类Job,实现数据的双向绑定
- 删除部门
第一步:实现deleteById方法
第二步:在控制类中将选中的部门信息从列表中移除,并调用Dao实现中的deleteById方法
- 查询部门
第一步:实现querByName方法
第二步:在控制类中获取TextField获得的关键词进行判断给予相应结果查询
员工信息维护
- 绘制界面
第一步:绘制员工界面内容
其中用了tableview控件,SplitePane用于把页面划分出上下两部分,VBox用于把下半部分划分成垂直的两部分,HBox水平摆放控件。
其中表格中的表头信息有:员工编号、员工姓名、所属部门、职位、邮箱
第二步:显示员工页面
显示界面(在MainController中添加一下方法,用于显示职位信息界面)
在MainController中传入mainView对象,用于切换主页面中的布局内容
在MainApp中获取MainController,把mainView传递过去
完成显示员工页面的逻辑
- 数据库表
创建数据库及职位表
创建数据库的sql语句
创建表格
查看所有表格
插入三条数据
查询表中的数据
- 编写控制器
EmpController控制器
EmpEditDialogController控制器
实体类Emp
该类的对象用于存储表中的记录数据,一条记录对应该类的一个对象
- 数据访问层Dao
该接口声明操作表Emp的增删改查的方法
实现EmpController的逻辑
- 实现类
完成操作表emp的增删改查的方法
- 新建员工
第一步:对话框界面绘制
EmpEditDialogView.fxml,该视图既有新建部门的功能,又有更新部门的功能,如果是新建部门的话,输入框为空;如果是更新部门的话输入框回显部门信息。
第二步:完成EmpEditDialogController的编写
该对话框承担了要完成新建部门和更新部门信息的任务
第三步:表写Dao层
- 更新部门
第一步 : 准备好dialog
第二步: 实现onUpdateEmp方法
第三步:修改实体类Emp,实现数据的双向绑定
- 删除部门
第一步:实现deleteById方法
第二步:在控制类中将选中的部门信息从列表中移除,并调用Dao实现中的deleteById方法
- 查询部门
第一步:实现querByName方法
第二步:在控制类中获取TextField获得的关键词进行判断给予相应结果查询
第二阶段(工资数据维护):
固定工资相关界面的绘制
- 第一步:绘制员工工资统计报表界面内容
首先用SplitePane用于把页面划分出上下比例0.8的两部分,其中上半部分使用了TableView控件,其中包含5列TableColumn列表(员工编号、员工姓名、基本工资、餐饮补贴、交通补贴);下半部分采用HBox水平摆放控件,其中包含4个Label、3个ChoiceBox、1个TextField、2个button。
- 第二步:显示员工工资统计报表页面
在MainController中传入showFixedSalaryVIew对象,用于切换主页面中的布局内容
在MainApp中获取MainController,把showFixedSalaryVIew传递过去完成显示部门页面的逻辑
固定工资表格中的内容
在数据库中进行多表联查并导出
创建实体类FixedSalary
该类的对象用于存储表中的记录数据,一条记录对应该类的一个对象
编写DeptDao及其实现类
该接口声明操作表dept的更新查询的方法
实现FixedSalaryController的逻辑
部门信息维护
- 更新工资项
第一步 : 准备好dialog
第二步: 实现onUpdateDept方法
第三步:修改实体类FixedSalary,实现数据的双向绑定
- 查询部门
第一步:实现querByName方法
第二步:在控制类中获取TextField获得的关键词进行判断给予相应结果查询
导入数据维护
- 数据库表
在数据库中进行多表联查并导出
绘制界面
第一步:绘制职位界面内容
首先用SplitePane用于把页面划分出上下比例0.8的两部分,HBox水平摆放控件。其中用了tableview控件,其中上半部分使用了TableView控件,下半部分采用HBox水平摆放控件,其中包含3个button、3个Label、3个ChoiceBox。
其中表格中的表头信息有:员工编号、员工姓名、病假天数、事假天数、迟到天数、早退天数、加班天数、年份、月份
第二步:显示职位页面
显示界面(在MainController中添加一下方法,用于显示导入信息信息界面)
在ImportInfoController中传入ImportInfoView对象,用于切换主页面中的布局内容
在MainApp中获取ImportInfoController,把ImportInfoView传递过去
完成显示职位页面的逻辑
- 编写控制器
ImportInfoController控制器
ImpotInfoEditDialogController控制器
实体类ImportInfo
该类的对象用于存储表中的记录数据,一条记录对应该类的一个对象
- 数据访问层Dao
该接口声明操作表ImportInfoDao的增删改查的方法
实现ImportInfoController的逻辑
- 实现类
完成操作表ImportInfo的增删改查的方法
- 数据编辑
第一步 : 准备好dialog
第二步: 实现onUpdateJob方法
第三步:修改实体类Job,实现数据的双向绑定
工资计算
- 数据库表
在数据库中进行多表联查并导出
绘制界面
第一步:绘制职位界面内容
其中用了tableview控件,SplitePane用于把页面划分出上下两部分,HBox水平摆放控件。
其中表格中的表头信息有:员工编号、员工姓名、病假天数、事假天数、迟到天数、早退天数、加班天数、年份、月份
第二步:显示职位页面
显示界面(在MainController中添加一下方法,用于显示导入信息界面)
在MainController中传入ImportInfoView对象,用于切换主页面中的布局内容
在MainApp中获取MainController,把ImportInfoView传递过去
完成显示职位页面的逻辑
- 编写控制器
SalaryDealController控制器
- 实体类ImportInfo
该类的对象用于存储表中的记录数据,一条记录对应该类的一个对象
- 数据访问层Dao
该接口声明操作表RealSalaryDao的计算工资的方法
实现SalaryDealController的逻辑
- 实现类
完成操作表RealSalary的增删改查的方法
第三阶段(报表管理):
部门年度/月度统计报表
- 数据库表
在数据库中进行多表联查并导出
绘制界面
第一步:绘制职位界面内容
其中用了tableview控件,SplitePane用于把页面划分出上下两部分,HBox水平摆放控件。
其中表格中的表头信息有:部门名称、员工编号、员工姓名、实发工资
第二步:显示员工工资统计报表
显示界面(在MainController中添加一下方法,用于显示导入信息信息界面)
在MainController中传入ExportEmpFormView对象,用于切换主页面中的布局内容
在MainApp中获取MainController,把ExportEmpFormView传递过去
完成显示职位页面的逻辑
- 编写控制器
ExportEmpFormController控制器
实体类EmpForm
员工报表对应的实体类,声明对象,并双向绑定,调用getset、toString方法。
- 数据访问层EmpFormDao
员工报表访问接口,查询所有的员工工资信息,根据员工姓名去查找员工报表。
- 实现类EmpFormDaoImpl
实现EMPFormDao里面的方法。
- 通过员工姓名查询该员工信息报表
第一步:数据显示
在ExportEmpFormControlle中,方法initialize()绑定属性和列,查询数据加载报表数据。
第二步: 通过员工姓名查询该员工信息报表
在ExportEmpFormControlle中,方法find(),如果输入为空的姓名,则会弹出警告;如果输入的员工姓名不存在,也会弹出相应的警告。输入正确,则查询出员工的信息报表。
第三步:将数据dataList导出在excel文件中
在ExportEmpFormControlle中,方法onexportForm(),dataList通过POI导出到excel中。首先判断dataList是否为空,不为空,①创建一个与excel文件对应的对象XXSFWorkbook ②创建一个表格XSSFSheet③创建首行XXSFRow(headerRow)④创建首行单元格,用数组把表头写进去,再用循环把dataList里面的数据放进去,这里有三个循环。⑤保存文档,先定义文档路径,它会自动生成excel,OutputStream IO流,给workbook存入write ,最后关闭os
四、实验步骤(图文方式叙述)
第一阶段(基础信息维护):
部门相关界面的绘制
第一步:绘制部门界面内容
其中用了tableview控件,SplitePane用于把页面划分出上下两部分,VBox用于把下半部分划分成垂直的两部分,HBox水平摆放控件。
![](https://i-blog.csdnimg.cn/blog_migrate/51d6d6cba6b37ad735f9a8f92a9f4ccf.png)
其中表格中的表头信息有:部门编号、部门名称、人员编制数、备注
第二步:显示部门页面
在MainController中传入mainView对象,用于切换主页面中的布局内容
private BorderPane mainView;
public void setMainView(BorderPane mainView) { this.mainView = mainView; } |
在MainApp中获取MainController,把mainView传递过去
![](https://i-blog.csdnimg.cn/blog_migrate/0e2a44baca2255cfb1ddd633f385dd10.png)
完成显示部门页面的逻辑
/** * 当用户点击部门信息管理菜单的时候调用该方法 * @param event */ @FXML void onClickMenuDept(ActionEvent event) { try { FXMLLoader fxmlLoader = new FXMLLoader(); fxmlLoader.setLocation(MainController.class.getResource("/com/neuedu/neuedusystem/DeptView.fxml")); AnchorPane deptPane = fxmlLoader.load(); mainView.setCenter(deptPane); } catch (IOException e){ e.printStackTrace(); } } |
效果图如下:
![](https://i-blog.csdnimg.cn/blog_migrate/a035f06876aa61b75cc829434284084b.png)
部门表格中的内容
第一步:创建数据库及部门表
# 创建数据库的sql语句: create database neuedu_system default character set utf8; # 选择数据库 USE neuedu_system; # 创建表格 CREATE TABLE dept( dept_id VARCHAR(10) NOT NULL, detp_name VARCHAR(4) DEFAULT NULL, dept_number INT(10) DEFAULT NULL, dept_comm VARCHAR(255) DEFAULT NULL ); # 查看所有表格 SHOW TABLES; # 查看表结构 DESC dept; # 插入一条数据 INSERT INTO dept VALUES('001', '研发部', 100, '该部门主要负责产品的技术研发工作'); # 查询表中的数据 SELECT * FROM dept; |
创建实体类Dept
/** * 该类的对象用于存储表中的记录数据,一条记录对应该类的一个对象 */ public class Dept {
private String deptId; private String deptName; private Integer deptNumber; private String deptComm; //备注
public Dept() { }
public Dept(String deptId, String deptName, Integer deptNumber, String deptComm) { this.deptId = deptId; this.deptName = deptName; this.deptNumber = deptNumber; this.deptComm = deptComm; }
public String getDeptId() { return deptId; }
public void setDeptId(String deptId) { this.deptId = deptId; }
public String getDeptName() { return deptName; }
public void setDeptName(String deptName) { this.deptName = deptName; }
public Integer getDeptNumber() { return deptNumber; }
public void setDeptNumber(Integer deptNumber) { this.deptNumber = deptNumber; }
public String getDeptComm() { return deptComm; }
public void setDeptComm(String deptComm) { this.deptComm = deptComm; }
@Override public String toString() { return "Dept{" + "deptId='" + deptId + '\'' + ", deptName='" + deptName + '\'' + ", deptNumber=" + deptNumber + ", deptComm='" + deptComm + '\'' + '}'; } } |
编写Dao及其实现类
/** * 该接口声明操作表dept的增删改查的方法 */ public interface DeptDao {
/** * 查询表中所有的数据 * @return */ List<Dept> queryList() throws SQLException;
/** * 根据部门ID查询部门信息 * @param deptId * @return */ Dept queryById(String deptId);
/** * 添加部门 * @param dept * @return 表示添加部门是否成功 */ boolean addDept(Dept dept);
/** * 根据部门id删除部门信息 * @param deptId * @return 删除部门是否成功 */ boolean deleteById(String deptId);
/** * 更新部门信息 * @param dept * @return 更新是否成功 */ boolean updateById(Dept dept); } |
public class DeptDaoImpl implements DeptDao {
@Override public List<Dept> queryList() throws SQLException { String sql = "select * from dept"; Connection connection = JdbcUtil.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); //获取sql执行的结果集
//把数据从结果集中取出,存入到Dept类的某个集合中去,然后返回 List<Dept> deptList = new ArrayList<>();
//resultSet.next(); //判断是否有下一条数据, 如果有数据就返回true while(resultSet.next()){ String dept_id = resultSet.getString("dept_id"); String dept_name = resultSet.getString("dept_name"); int dept_number = resultSet.getInt("dept_number"); String dept_comm = resultSet.getString("dept_comm");
Dept dept = new Dept(); dept.setDeptId(dept_id); dept.setDeptName(dept_name); dept.setDeptNumber(dept_number); dept.setDeptComm(dept_comm); deptList.add(dept); } //关闭连接 JdbcUtil.close(connection, statement, resultSet); return deptList; }
@Override public Dept queryById(String deptId) { return null; }
@Override public boolean addDept(Dept dept) { return false; }
@Override public boolean deleteById(String deptId) { return false; }
@Override public boolean updateById(Dept dept) { return false; } } |
第四步:实现DeptController的逻辑
public class DeptController {
@FXML private TableView<Dept> mTableView; @FXML private TableColumn<Dept, String> colDeptId; @FXML private TableColumn<Dept, String> colDeptName; @FXML private TableColumn<Dept, Integer> colDeptNumber; @FXML private TableColumn<Dept, String> colDeptComm;
//特殊的集合 private ObservableList datas = FXCollections.observableArrayList();
private DeptDao deptDao = new DeptDaoImpl();
/** * 用于初始化controller */ @FXML protected void initialize() { //告诉4个TableColumn 分别显示Dept对象中的哪个字段 colDeptId.setCellValueFactory(new PropertyValueFactory<>("deptId")); colDeptName.setCellValueFactory(new PropertyValueFactory<>("deptName")); colDeptNumber.setCellValueFactory(new PropertyValueFactory<>("deptNumber")); colDeptComm.setCellValueFactory(new PropertyValueFactory<>("deptComm"));
try { List<Dept> deptList = deptDao.queryList(); datas.addAll(deptList); mTableView.setItems(datas); } catch (SQLException e){ e.printStackTrace(); } } } |
基础信息维护
主要完成针对于部门信息的管理功能:新建部门、更新部门、删除部门、查询部门
部门信息维护
新建部门
第一步:对话框界面绘制
DeptDialogView.fxml,该视图既有新建部门的功能,又有更新部门的功能,如果是新建部门的话,输入框为空;如果是更新部门的话输入框回显部门信息。
![](https://i-blog.csdnimg.cn/blog_migrate/065024b794f9fa587bf9487c8d3de37d.png)
第二步:完成DeptDialogController的编写
/** * 该对话框承担了要完成新建部门和更新部门信息的任务 */ public class DeptDialogController {
@FXML private TextField deptNumTF; @FXML private TextField deptNameTF; @FXML private TextField deptCommTF; @FXML private TextField deptIdTF;
private Dept dept; //如果该对象为空,说明操作的是新建部门;否则操作的是更新部门 private ObservableList dataList; private Stage stage;
//dao DeptDao deptDao = new DeptDaoImpl();
public void setDept(Dept dept) { this.dept = dept;
//数据的回显,当点击更新部门的时候,对话框中要显示更新部门的信息(选中的) deptIdTF.setText(dept.getDeptId()); deptNameTF.setText(dept.getDeptName()); deptNumTF.setText(String.valueOf(dept.getDeptNumber())); deptCommTF.setText(dept.getDeptComm()); }
public void setDataList(ObservableList dataList) { this.dataList = dataList; }
public void setStage(Stage stage) { this.stage = stage; }
@FXML void onSubmit(ActionEvent event) { //点击提交有两种可能,一种是新建,一种更新 if(dept == null){ //新建部门 dept = new Dept(); //该对象用于存储新建部门的信息 dept.setDeptId(deptIdTF.getText()); dept.setDeptName(deptNameTF.getText()); dept.setDeptNumber(Integer.parseInt(deptNumTF.getText())); dept.setDeptComm(deptCommTF.getText());
//要把新建信息的对象dept放入到那个特殊的集合中去 this.dataList.add(dept); //把新的部门信息存入到数据库中 deptDao.addDept(dept); } else { //更新部门
}
//关闭对话框 stage.close(); }
@FXML void onCancel(ActionEvent event) { stage.close(); } } |
第三步:表写Dao层
@Override public boolean addDept(Dept dept) { try { String sql = "insert into dept values(?,?,?,?)"; Connection connection = JdbcUtil.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, dept.getDeptId()); preparedStatement.setString(2, dept.getDeptName()); preparedStatement.setInt(3, dept.getDeptNumber()); preparedStatement.setString(4, dept.getDeptComm()); boolean execute = preparedStatement.execute(); return execute; } catch (SQLException e){ e.printStackTrace(); } catch (NumberFormatException e){ e.printStackTrace(); } return false; } |
更新部门
第一步 : 准备好dialog
![](https://i-blog.csdnimg.cn/blog_migrate/bc9f957f52178e6e03003194bee49a48.png)
第二步: 实现onUpdateDept方法
@FXML public void onUpdateDept(){ //更新部门 Dept dept = mTableView.getSelectionModel().getSelectedItem(); //获取选中的部门数据 //做非空判断 if (dept == null) return; FXMLLoader fxmlLoader = new FXMLLoader(); fxmlLoader.setLocation(DeptController.class.getResource("/com/neuedu/neuedusystem/DeptDialogView.fxml")); try { AnchorPane dialogPane = fxmlLoader.load(); Scene scene = new Scene(dialogPane);
Stage stage = new Stage(); //子 stage.setScene(scene); stage.setTitle("更新部门"); stage.initOwner(primaryStage); //primaryStage 父 stage.initModality(Modality.WINDOW_MODAL);
//设置dialog的controller对象 DeptDialogController dialogController = fxmlLoader.getController(); dialogController.setDept(dept); dialogController.setStage(stage);
stage.showAndWait(); } catch (IOException e){ e.printStackTrace(); } } |
第三步:修改实体类Dept,实现数据的双向绑定
public class Dept {
private StringProperty deptId; //双向绑定 private StringProperty deptName; private IntegerProperty deptNumber; private StringProperty deptComm;
public Dept() { //初始化 deptId = new SimpleStringProperty(""); deptName = new SimpleStringProperty(""); deptNumber = new SimpleIntegerProperty(0); deptComm = new SimpleStringProperty("");
}
public StringProperty deptIdProperty(){ return deptId; }
public StringProperty deptNameProperty(){ return deptName; }
private IntegerProperty deptNumberProperty(){ return deptNumber; }
public StringProperty deptCommProperty(){ return deptComm; }
public String getDeptId() { return deptId.get(); }
public void setDeptId(String deptId) { this.deptId.set(deptId); }
public String getDeptName() { return deptName.get(); }
public void setDeptName(String deptName) { this.deptName.set(deptName); }
public Integer getDeptNumber() { return deptNumber.get(); }
public void setDeptNumber(Integer deptNumber) { this.deptNumber.set(deptNumber); }
public String getDeptComm() { return deptComm.get(); }
public void setDeptComm(String deptComm) { this.deptComm.set(deptComm); }
@Override public String toString() { return "Dept{" + "deptId='" + deptId + '\'' + ", deptName='" + deptName + '\'' + ", deptNumber=" + deptNumber + ", deptComm='" + deptComm + '\'' + '}'; } } |
删除部门
第一步:实现deleteById方法
@Override public boolean deleteById(String deptId) { String sql = "delete from dept where dept_id = "+deptId; try{ Connection connection = JdbcUtil.getConnection(); Statement statement = connection.createStatement(); boolean execute = statement.execute(sql); JdbcUtil.close(connection,statement,null); return execute; }catch (SQLException e){ e.printStackTrace(); } return false; } |
第二步:在控制类中将选中的部门信息从列表中移除,并调用Dao实现中的deleteById方法
@FXML public void onDelDept() { Dept dept = mTableView.getSelectionModel().getSelectedItem(); //获取选中的部门数据 if (dept == null) return;//做非空判断 datas.removeAll(dept); deptDao.deleteById(dept.getDeptId()); } |
查询部门
第一步:实现querByName方法
@Override public List<Dept> queryByName(String deptName) { String sql = "select * from dept where dept_name like '%"+deptName+"%'"; List<Dept> deptList = new ArrayList<>(); try { Connection connection = JdbcUtil.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(sql); ResultSet resultSet = preparedStatement.executeQuery(); while (resultSet.next()){ Dept dept = new Dept(); dept.setDeptId(resultSet.getString("dept_id")); dept.setDeptName(resultSet.getString("dept_name")); dept.setDeptNumber(resultSet.getInt("dept_number")); dept.setDeptComm(resultSet.getString("dept_comm")); deptList.add(dept); } if(deptList.size()!=0){ return deptList; } JdbcUtil.close(connection, preparedStatement, resultSet);
}catch (SQLException e){ e.printStackTrace(); }; return null; } |
第二步:在控制类中获取TextField获得的关键词进行判断给予相应结果查询
@FXML void onFindDept(ActionEvent event) { //获取搜索的关键词 String keyword = deptNameTF.getText(); if (keyword != null && !keyword.equals("")) { List<Dept> deptList = deptDao.queryByName(keyword); if (deptList != null) { datas.clear(); //清空数据集 datas.addAll(deptList); } else { //假设查出来 DialogUtil.showWarnDialog(this.primaryStage, "查询结果", "请重新输入搜索ID"); } } else { //提示 DialogUtil.showWarnDialog(this.primaryStage, "缺少关键字", "请在输入框中输入要查询的关键词"); } } |
职位信息维护
数据库表
# 职位表 CREATE TABLE job( job_id VARCHAR(10) PRIMARY KEY, job_title VARCHAR(40), job_comm VARCHAR(255), dept_id VARCHAR(10), FOREIGN KEY(dept_id) REFERENCES dept(dept_id) # 外键约束 ); |
绘制界面
![](https://i-blog.csdnimg.cn/blog_migrate/b09314a01ccd4999041749c89a17c219.png)
显示界面(在MainController中添加一下方法,用于显示职位信息界面)
@FXML void showJobInfo(ActionEvent event) { try { FXMLLoader fxmlLoader = new FXMLLoader(); fxmlLoader.setLocation(MainController.class.getResource("/com/neuedu/neuedusystem/JobView.fxml")); AnchorPane deptPane = fxmlLoader.load(); mainView.setCenter(deptPane);
JobController jobController = fxmlLoader.getController();
} catch (IOException e){ e.printStackTrace(); } } |
编写控制器
JobController控制器
/** * 关于职位的控制器 */ public class JobController {
@FXML private TableColumn<Job, String> jobNameColumn; @FXML private TextField jobTF; @FXML private TableView<Job> jobTableView; @FXML private TableColumn<Job, String> jobIdColumn; @FXML private TableColumn<Job, String> jobCommColumn;
private JobDao jobDao = new JobDaoImpl(); private ObservableList dataList = FXCollections.observableArrayList(); private Stage primaryStage;
public void setPrimaryStage(Stage primaryStage) { this.primaryStage = primaryStage; }
@FXML void initialize(){ //TableColumn与Job对象中的属性进行绑定 jobIdColumn.setCellValueFactory(new PropertyValueFactory<>("jobId")); jobNameColumn.setCellValueFactory(new PropertyValueFactory<>("jobTitle")); jobCommColumn.setCellValueFactory(new PropertyValueFactory<>("jobComm"));
//查询数据并添加到dataList中去 List<Job> jobList = jobDao.queryAll(); dataList.addAll(jobList); //把dataList交给TableView去显示数据 jobTableView.setItems(dataList); }
/** * 创建新的职位 * @param event */ @FXML void onNewJob(ActionEvent event) { FXMLLoader fxmlLoader = new FXMLLoader(); fxmlLoader.setLocation(JobController.class.getResource("/com/neuedu/neuedusystem/JobEditDialogView.fxml")); try { AnchorPane anchorPane = fxmlLoader.load(); //创建场景 Scene scene = new Scene(anchorPane); //创建舞台 Stage stage = new Stage(); stage.setScene(scene); stage.setTitle("新建职位"); stage.initOwner(this.primaryStage); stage.initModality(Modality.WINDOW_MODAL);
JobEditDialogController controller = fxmlLoader.getController(); controller.setJobDao(jobDao); controller.setDataList(dataList); controller.setStage(stage);
stage.showAndWait(); } catch (IOException e){ e.printStackTrace(); } }
/** * 更新职位 * @param event */ @FXML void onUpdateJob(ActionEvent event) { //首先要选择更新的职位信息 Job selectJob = jobTableView.getSelectionModel().getSelectedItem(); if (selectJob != null){ FXMLLoader fxmlLoader = new FXMLLoader(); fxmlLoader.setLocation(JobController.class.getResource("/com/neuedu/neuedusystem/JobEditDialogView.fxml")); try { AnchorPane pane = fxmlLoader.load(); //创建场景对象 Scene scene = new Scene(pane); Stage stage = new Stage(); stage.setScene(scene); stage.initOwner(this.primaryStage); stage.setTitle("更新职位"); stage.initModality(Modality.WINDOW_MODAL);
JobEditDialogController controller = fxmlLoader.getController(); controller.setJob(selectJob); controller.setStage(stage); controller.setJobDao(jobDao);
stage.showAndWait();
} catch (IOException e){ e.printStackTrace(); }
} else { DialogUtil.showWarnDialog(primaryStage, "数据错误", "请选择要更新的职位"); } }
/** * 删除职位 * @param event */ @FXML void onDelJob(ActionEvent event) { DialogUtil.showWarnDialog(this.primaryStage, "危险操作", "确定要删除吗?"); //获取选择的职位信息 Job selectedJob = jobTableView.getSelectionModel().getSelectedItem(); if (selectedJob != null){ //TableView删除操作 dataList.remove(selectedJob);
//数据库删除 jobDao.delJobById(selectedJob.getJobId());
DialogUtil.showWarnDialog(this.primaryStage, "操作提示", "删除成功!"); } else { DialogUtil.showWarnDialog(this.primaryStage, "数据错误", "请选择要删除职位"); } }
/** * 查询职位 * @param event */ @FXML void onFindJob(ActionEvent event) { //获取搜索的关键词 String keyword = jobTF.getText(); if (keyword != null && !keyword.equals("")){ Job job = jobDao.queryByName(keyword); if (job != null){ dataList.clear(); //清空数据集 dataList.add(job); //添加搜索的结果 } else { //没查出来 DialogUtil.showWarnDialog(this.primaryStage, "查询结果", "请重新输入搜索词"); } } else { //提示 DialogUtil.showWarnDialog(this.primaryStage, "缺少关键词", "请在输入框中输入要搜索的关键词"); } } } |
JobEditDialogController控制器
public class JobEditDialogController {
@FXML private TextField jobCommTF; @FXML private TextField jobTitleTF; @FXML private TextField jobIdTF;
private JobDao jobDao; private ObservableList dataList; private Stage stage; private Job job;
public void setJob(Job job) { this.job = job; //回显数据 jobIdTF.setText(job.getJobId()); jobTitleTF.setText(job.getJobTitle()); jobCommTF.setText(job.getJobComm());
jobIdTF.setEditable(false); //设置职位编号不可修改
}
public void setStage(Stage stage) { this.stage = stage; }
public void setDataList(ObservableList dataList) { this.dataList = dataList; }
public void setJobDao(JobDao jobDao) { this.jobDao = jobDao; }
@FXML void onSubmit(ActionEvent event) { if (this.job == null) { //新建职位功能 //获取输入中用户输入的数据 String jobId = jobIdTF.getText(); String jobName = jobTitleTF.getText(); String jobComm = jobCommTF.getText(); Job job = new Job(); job.setJobId(jobId); job.setJobTitle(jobName); job.setJobComm(jobComm); job.setDeptId("003");
//调用Dao jobDao.saveJob(job); //修改数据库 dataList.add(job); //修改了数据集 } else { //更新职位 //获取到用户修改后的信息 this.job.setJobTitle(jobTitleTF.getText()); this.job.setJobComm(jobCommTF.getText());
//修改数据库中数据 jobDao.updateJob(this.job);
}
//关闭对话框 stage.close(); }
@FXML void onCancel(ActionEvent event) { stage.close(); } } |
实体类Job
public class Job {
private StringProperty jobId; //职位编号 private StringProperty jobTitle; //职位名称 private StringProperty jobComm; //备注 private StringProperty deptId; //部门编号
public Job(){ jobId = new SimpleStringProperty(""); jobTitle = new SimpleStringProperty(""); jobComm = new SimpleStringProperty(""); deptId = new SimpleStringProperty(""); }
public StringProperty jobIdProperty(){ return jobId; }
public StringProperty jobTitleProperty(){ return jobTitle; }
public StringProperty jobCommProperty(){ return jobComm; }
public StringProperty deptIdProperty(){ return deptId; }
public String getJobId() { return jobId.get(); }
public void setJobId(String jobId) { this.jobId.set(jobId); }
public String getJobTitle() { return jobTitle.get(); }
public void setJobTitle(String jobTitle) { this.jobTitle.set(jobTitle); }
public String getJobComm() { return jobComm.get(); }
public void setJobComm(String jobComm) { this.jobComm.set(jobComm); }
public String getDeptId() { return deptId.get(); }
public void setDeptId(String deptId) { this.deptId.set(deptId); }
@Override public String toString() { return "Job{" + "jobId=" + jobId + ", jobTitle=" + jobTitle + ", jobComm=" + jobComm + ", deptId=" + deptId + '}'; } } |
数据访问层Dao
接口
public class JobDaoImpl implements JobDao { @Override public List<Job> queryAll() { String sql = "select * from job"; List<Job> jobList = new ArrayList<>(); try { Connection connection = JdbcUtil.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); while(resultSet.next()){ Job job = new Job(); job.setJobId(resultSet.getString("job_id")); job.setJobTitle(resultSet.getString("job_title")); job.setJobComm(resultSet.getString("job_comm")); job.setDeptId(resultSet.getString("dept_id")); jobList.add(job); } //关闭资源 JdbcUtil.close(connection, statement, resultSet); return jobList; } catch (SQLException e){ e.printStackTrace(); } return null; }
@Override public Job queryByName(String name) { //String sql = "select * from job where job_title=?"; String sql = "select * from job where job_title like '%"+name+"%'"; //%keyword% xxxjavaxxx javaxxx xxxjava //_(下划线) __java_ xxjavax try { Connection connection = JdbcUtil.getConnection(); PreparedStatement statement = connection.prepareStatement(sql); ResultSet resultSet = statement.executeQuery(); if (resultSet.next()){ Job job = new Job(); job.setJobId(resultSet.getString("job_id")); job.setJobTitle(resultSet.getString("job_title")); job.setJobComm(resultSet.getString("job_comm")); job.setDeptId(resultSet.getString("dept_id")); JdbcUtil.close(connection, statement, resultSet); return job; } JdbcUtil.close(connection, statement, resultSet); } catch (SQLException e){ e.printStackTrace(); } return null; }
@Override public boolean updateJob(Job job) { String sql = "update job set job_title=?, job_comm=? where job_id=?"; try { Connection connection = JdbcUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1, job.getJobTitle()); ps.setString(2, job.getJobComm()); ps.setString(3, job.getJobId()); boolean execute = ps.execute(); JdbcUtil.close(connection, ps, null); return execute; } catch (SQLException e){ e.printStackTrace(); } return false; }
@Override public boolean delJobById(String jobId) { String sql = "delete from job where job_id=?"; try { Connection connection = JdbcUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1, jobId); boolean execute = ps.execute(); JdbcUtil.close(connection, ps,null); return execute; } catch (SQLException e){ e.printStackTrace(); } return false; }
@Override public boolean saveJob(Job job) { String sql = "insert into job values(?,?,?,?)"; try { Connection connection = JdbcUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1, job.getJobId()); ps.setString(2, job.getJobTitle()); ps.setString(3, job.getJobComm()); ps.setString(4, job.getDeptId()); boolean execute = ps.execute(); JdbcUtil.close(connection, ps, null); return execute; }catch (SQLException e){ e.printStackTrace(); } return false; } } |
实现类
public class JobDaoImpl implements JobDao { @Override public List<Job> queryAll() { String sql = "select * from job"; List<Job> jobList = new ArrayList<>(); try { Connection connection = JdbcUtil.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); while(resultSet.next()){ Job job = new Job(); job.setJobId(resultSet.getString("job_id")); job.setJobTitle(resultSet.getString("job_title")); job.setJobComm(resultSet.getString("job_comm")); job.setDeptId(resultSet.getString("dept_id")); jobList.add(job); } //关闭资源 JdbcUtil.close(connection, statement, resultSet); return jobList; } catch (SQLException e){ e.printStackTrace(); } return null; }
@Override public Job queryByName(String name) { //String sql = "select * from job where job_title=?"; String sql = "select * from job where job_title like '%"+name+"%'"; //%keyword% xxxjavaxxx javaxxx xxxjava //_(下划线) __java_ xxjavax try { Connection connection = JdbcUtil.getConnection(); PreparedStatement statement = connection.prepareStatement(sql); ResultSet resultSet = statement.executeQuery(); if (resultSet.next()){ Job job = new Job(); job.setJobId(resultSet.getString("job_id")); job.setJobTitle(resultSet.getString("job_title")); job.setJobComm(resultSet.getString("job_comm")); job.setDeptId(resultSet.getString("dept_id")); JdbcUtil.close(connection, statement, resultSet); return job; } JdbcUtil.close(connection, statement, resultSet); } catch (SQLException e){ e.printStackTrace(); } return null; }
@Override public boolean updateJob(Job job) { return false; }
@Override public boolean delJobById(String jobId) { return false; } } |
实现新建职位功能
第一步:绘制界面并实现
![](https://i-blog.csdnimg.cn/blog_migrate/250b83a24adde04be096975db23f6af6.png)
显示对话框
@FXML void onNewJob(ActionEvent event) { FXMLLoader fxmlLoader = new FXMLLoader(); fxmlLoader.setLocation(JobController.class.getResource("/com/neuedu/neuedusystem/JobEditDialogView.fxml")); try { AnchorPane anchorPane = fxmlLoader.load(); //创建场景 Scene scene = new Scene(anchorPane); //创建舞台 Stage stage = new Stage(); stage.setScene(scene); stage.setTitle("新建职位"); stage.initOwner(this.primaryStage); stage.initModality(Modality.WINDOW_MODAL); stage.showAndWait(); } catch (IOException e){ e.printStackTrace(); } } |
第二步:逻辑实现(控制器、Dao)
控制器
public class JobEditDialogController {
@FXML private TextField jobCommTF; @FXML private TextField jobTitleTF; @FXML private TextField jobIdTF;
private JobDao jobDao; private ObservableList dataList; private Stage stage;
public void setStage(Stage stage) { this.stage = stage; }
public void setDataList(ObservableList dataList) { this.dataList = dataList; }
public void setJobDao(JobDao jobDao) { this.jobDao = jobDao; }
@FXML void onSubmit(ActionEvent event) { //获取输入中用户输入的数据 String jobId = jobIdTF.getText(); String jobName = jobTitleTF.getText(); String jobComm = jobCommTF.getText(); Job job = new Job(); job.setJobId(jobId); job.setJobTitle(jobName); job.setJobComm(jobComm); job.setDeptId("003");
//调用Dao jobDao.saveJob(job); //修改数据库 dataList.add(job); //修改了数据集
//关闭对话框 stage.close(); }
@FXML void onCancel(ActionEvent event) { stage.close(); } } |
Dao
@Override public boolean saveJob(Job job) { String sql = "insert into job values(?,?,?,?)"; try { Connection connection = JdbcUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1, job.getJobId()); ps.setString(2, job.getJobTitle()); ps.setString(3, job.getJobComm()); ps.setString(4, job.getDeptId()); boolean execute = ps.execute(); JdbcUtil.close(connection, ps, null); return execute; }catch (SQLException e){ e.printStackTrace(); } return false; } |
员工信息维护
绘制界面
![](https://i-blog.csdnimg.cn/blog_migrate/73a4969a63e27b20e48b1d521f4d37e5.png)
显示页面
@FXML void showEmpInfo(ActionEvent event) { try { FXMLLoader fxmlLoader = new FXMLLoader(); fxmlLoader.setLocation(MainController.class.getResource("/com/neuedu/neuedusystem/EmpView.fxml")); AnchorPane deptPane = fxmlLoader.load(); mainView.setCenter(deptPane);
EmpController controller = fxmlLoader.getController();
} catch (IOException e){ e.printStackTrace(); } } |
数据库表
#员工表 CREATE TABLE emp( emp_id VARCHAR(10) PRIMARY KEY, emp_name VARCHAR(20), dept_id VARCHAR(10), job_id VARCHAR(10), email VARCHAR(60), FOREIGN KEY (dept_id) REFERENCES dept(dept_id), FOREIGN KEY (job_id) REFERENCES job(job_id) ); |
编写控制器
EmpController控制器
public class EmpController { @FXML private TableColumn<Emp,String> empIdColumn; @FXML private TableColumn<Emp,String> deptColumn; @FXML private TableColumn<Emp,String> empNameColumn; @FXML private ChoiceBox<String> conditionCB; @FXML private TableColumn<Emp,String> jobColumn; @FXML private HBox keywordTF; @FXML private TableView<Emp> empTableView; @FXML private TableColumn<Emp,String> emailColumn; private ObservableList dataList= FXCollections.observableArrayList(); private EmpDao empDao=new EmpDaoImpl(); private Stage primaryStage; public void setPrimaryStage(Stage primaryStage) { this.primaryStage = primaryStage; } @FXML void initialize(){ //绑定 empIdColumn.setCellValueFactory(new PropertyValueFactory<>("empId")); empNameColumn.setCellValueFactory(new PropertyValueFactory<>("empName")); emailColumn.setCellValueFactory(new PropertyValueFactory<>("email")); //准备要显示的数据 List<Emp> empList=empDao.queryAll(); dataList.addAll(empList); empTableView.setItems(dataList); } @FXML void onNewEmp(ActionEvent event) { FXMLLoader fxmlLoader=new FXMLLoader(); fxmlLoader.setLocation(EmpController.class.getResource("/com/neuedu/neuedusystem/EmpEditDialogView.fxml")); try { AnchorPane pane = fxmlLoader.load(); //创建场景 Scene scene=new Scene(pane); //创建舞台 Stage stage=new Stage(); stage.setScene(scene); stage.setTitle("员工登录"); stage.initOwner(this.primaryStage); stage.initModality(Modality.WINDOW_MODAL); EmpEditDialogController controller = fxmlLoader.getController(); controller.setDataList(dataList); controller.setStage(stage); stage.showAndWait(); }catch (IOException e){ e.printStackTrace(); } } @FXML void onDelEmp(ActionEvent event) { DialogUtil.showWarnDialog(primaryStage,"删除提示","你确定要删除吗?"); Emp selectedEmp = empTableView.getSelectionModel().getSelectedItem(); if(selectedEmp!=null){ empDao.deleteEmp(selectedEmp.getEmpId()); dataList.remove(selectedEmp); }else { DialogUtil.showWarnDialog(primaryStage,"数据错误","请选择要删除的数据"); } } @FXML void onUpdateEmp(ActionEvent event) { //更新员工 Emp selectedEmp = empTableView.getSelectionModel().getSelectedItem(); if(selectedEmp!=null) { FXMLLoader fxmlLoader = new FXMLLoader(); fxmlLoader.setLocation(EmpController.class.getResource("/com/neuedu/neuedusystem/EmpEditDialogView.fxml")); try { AnchorPane pane = fxmlLoader.load(); //创建场景 Scene scene = new Scene(pane); //创建舞台 Stage stage = new Stage(); stage.setScene(scene); stage.setTitle("更新员工"); stage.initOwner(this.primaryStage); stage.initModality(Modality.WINDOW_MODAL); EmpEditDialogController controller = fxmlLoader.getController(); controller.setStage(stage); controller.setEmp(selectedEmp); stage.showAndWait(); } catch (IOException e) { e.printStackTrace(); } }else { DialogUtil.showWarnDialog(primaryStage,"数据错误","请选择要更新的员工信息"); } } @FXML void onFindEmp(ActionEvent event) { //获取搜索的关键词 String keyword = empTF.getText(); if(keyword!=null &&!keyword.equals("")){//当它不是空字符串的时候 Emp emp = EmpDao.queryByName(keyword); if(emp!=null){//找得到 dataList.clear();//清空数据集 dataList.add(emp);//添加搜索的结果 }else{//找不到,查询为空 DialogUtil.showWarnDialog(this.primaryStage,"查询结果","请重新输入搜索词"); } }else { //提示 // Alert alert=new Alert(Alert.AlertType.WARNING); // alert.initOwner(this.primaryStage); // alert.setTitle("缺少关键词"); // alert.setContentText("请再输入框中输入要搜索的关键词"); // alert.showAndWait(); DialogUtil.showWarnDialog(this.primaryStage,"缺少关键词","请再输入框中输入要搜索的关键词"); } } |
实体类Emp
//员工信息 public class Emp { private StringProperty empId; private StringProperty empName; private StringProperty deptId; private StringProperty jobId; private StringProperty email; public Emp(){ empId=new SimpleStringProperty(""); empName=new SimpleStringProperty(""); deptId=new SimpleStringProperty(""); jobId=new SimpleStringProperty(""); email=new SimpleStringProperty(""); } public StringProperty empIdProperty(){ return empId; } public StringProperty empNameProperty(){ return empName; } public StringProperty deptIdProperty(){ return deptId; } public StringProperty jobIdProperty(){ return jobId; } public StringProperty emailProperty(){ return email; } public String getEmpId() { return empId.get(); } public void setEmpId(String empId) { this.empId.set(empId); } public String getEmpName() { return empName.get(); } public void setEmpName(String empName) { this.empName.set(empName); } public String getDeptId() { return deptId.get(); } public void setDeptId(String deptId) { this.deptId.set(deptId); } public String getJobId() { return jobId.get(); } public void setJobId(String jobId) { this.jobId.set(jobId); } public String getEmail() { return email.get(); } public void setEmail(String email) { this.email.set(email); } @Override public String toString() { return "Emp{" + "empId=" + empId + ", empName=" + empName + ", deptId=" + deptId + ", jobId=" + jobId + ", email=" + email + '}'; } } |
数据访问层Dao
接口
//员工表的增删改查 public interface EmpDao { /** * 根据员工Id查询员工姓名 * @param empId * @return */ String getEmpName(String empId); /** * 用于查询所有员工的信息 * @return */ List<Emp> queryAll(); /** * 根据员工编号查询员工信息 * @param empId * @return */ Emp queryByName(String empName); /** * 保存员工信息 * @param emp * @return */ boolean saveEmp(Emp emp); /** * 更新员工信息 * @param emp * @return */ boolean updateEmp(Emp emp); /** * 根据员工的编号 删除员工信息 * @param empId * @return */ boolean deleteEmp(String empId); } |
实现类
public class EmpDaoImpl implements EmpDao { @Override public String getEmpName(String empId) { String sql="select emp_name from emp where emp_id="+empId; try{ Connection connection = JdbcUtil.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); resultSet.next();//指向数据所在的行 String empName=resultSet.getString("emp_name"); return empName; }catch (SQLException e){ e.printStackTrace(); } return null; } @Override public List<Emp> queryAll() { String sql="select*from emp"; try { Connection connection = JdbcUtil.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet=statement.executeQuery(sql); List<Emp> empList=new ArrayList<>(); while(resultSet.next()){ Emp emp=new Emp(); emp.setEmpId(resultSet.getString("emp_id")); emp.setEmpName(resultSet.getString("emp_name")); emp.setEmail(resultSet.getString("email")); empList.add(emp); } JdbcUtil.close(connection,statement,resultSet); return empList; }catch (SQLException e){ e.printStackTrace(); } return null; } @Override public Emp queryByName(String empName) { //String sql="select*from job where job_title=?"; //改成一种模糊查询(大概) String sql="select*from emp where emp_name like '%"+name+"%'";//like可以添加%(没数量限制)和_(一个代表一个数) //System.out.println(sql); //System.out.println(sql); try{ Connection connection = JdbcUtil.getConnection(); PreparedStatement statement = connection.prepareStatement(sql); //statement.setString(1,name); ResultSet resultSet = statement.executeQuery(); if(resultSet.next()){ Emp emp=new Emp(); Emp.setJobId(resultSet.getString("emp_id")); Emp.setJobTitle(resultSet.getString("emp_name")); Emp.setJobComm(resultSet.getString("email")); Emp.setDeptId(resultSet.getString("dept_id")); Emp.setDeptId(resultSet.getString("job_id")); //关闭资源 JdbcUtil.close(connection,statement,resultSet);//如果有数据就在这关闭数据,如果没数据就在下面关闭数据 return Emp; return null; } @Override public boolean saveEmp(Emp emp) { String sql="insert into emp(emp_id,emp_name,email) values(?,?,?)"; try { Connection connection = JdbcUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1,emp.getEmpId()); ps.setString(2,emp.getEmpName()); ps.setString(3,emp.getEmail()); boolean execute = ps.execute(); JdbcUtil.close(connection,ps,null); return execute; }catch (SQLException e){ e.printStackTrace(); } return false; } @Override public boolean updateEmp(Emp emp) { String sql="update emp set emp_name=?,email=? where emp_id=?"; try { Connection connection = JdbcUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1,emp.getEmpName()); ps.setString(2,emp.getEmail()); ps.setString(3,emp.getEmpId()); boolean execute = ps.execute(); JdbcUtil.close(connection,ps,null); return execute; }catch (SQLException e){ e.printStackTrace(); } return false; } @Override public boolean deleteEmp(String empId) { String sql="delete from emp where emp_id=?"; try { Connection connection = JdbcUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1,empId); boolean execute = ps.execute(); JdbcUtil.close(connection,ps,null); return execute; }catch (SQLException e){ e.printStackTrace(); } return false; } } |
|
实现新建职位功能
第一步:绘制界面并实现
显示对话框
![](https://i-blog.csdnimg.cn/blog_migrate/59ecb5b807d6acdec0359c09de6981b5.png)
第二步:逻辑实现(控制器、Dao)
EmpEditDialogController控制器
public class EmpEditDialogController { @FXML private TextField emailTF; @FXML private TextField empIdTF; @FXML private TextField empNameTF; private EmpDao empDao=new EmpDaoImpl(); private ObservableList dataList; private Stage stage; private Emp emp; public void setEmp(Emp emp) { this.emp = emp; //数据回显 empIdTF.setText(emp.getEmpId()); empNameTF.setText(emp.getEmpName()); emailTF.setText(emp.getEmail()); } public void setStage(Stage stage) { this.stage = stage; } public void setDataList(ObservableList dataList) { this.dataList = dataList; } @FXML void onSubmit(ActionEvent event) { //员工登记 if(emp==null) { String empId = empIdTF.getText(); String empName = empNameTF.getText(); String email = emailTF.getText(); Emp emp = new Emp(); emp.setEmpId(empId); emp.setEmpName(empName); emp.setEmail(email); //保存数据 empDao.saveEmp(emp); dataList.add(emp); }else { //更新员工信息 String empId=empIdTF.getText(); String empName=empNameTF.getText(); String email=emailTF.getText(); this.emp.setEmpId(empId); this.emp.setEmpName(empName); this.emp.setEmail(email); //更新数据库 empDao.updateEmp(this.emp); } stage.close(); } @FXML void onCancel(ActionEvent event) { stage.close(); } } |
Dao
/** * 员工报表访问接口 */ public interface EmpFormDao { /** * 查询所有的员工工资信息,进行统计 * @return */ List<EmpForm> queryAll(); /** * 根据员工姓名去查找员工报表 * @param empName * @return */ List<EmpForm> queryByName(String empName); } |
实现类
public class EmpFormDaoImpl implements EmpFormDao { @Override public List<EmpForm> queryAll() { String sql = "SELECT emp.emp_id,dept_name,emp_name,fix_salary FROM emp LEFT JOIN dept ON emp.dept_id=dept.dept_id LEFT JOIN fixed_salary ON emp.emp_id=fixed_salary.emp_id"; try { Connection connection = JdbcUtil.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); //创建一个集合,用于存储查询出来的结果 List<EmpForm> empFormList = new ArrayList<>(); while (resultSet.next()) { EmpForm empForm = new EmpForm(); empForm.setEmpId(resultSet.getString("emp_id")); empForm.setEmpName(resultSet.getString("emp_name")); empForm.setDeptName(resultSet.getString("dept_name")); empForm.setRealSalary(resultSet.getDouble("fix_salary")); empFormList.add(empForm); } JdbcUtil.close(connection, statement, resultSet); return empFormList; } catch (SQLException e) { e.printStackTrace(); } return null; } @Override public List<EmpForm> queryByName(String empName) { String sql = "SELECT emp.emp_id,dept_name,emp_name,fix_salary FROM emp LEFT JOIN dept ON emp.dept_id=dept.dept_id LEFT JOIN fixed_salary ON emp.emp_id=fixed_salary.emp_id where emp_name like'%" + empName + "%'"; System.out.println(sql); try { Connection connection = JdbcUtil.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); //创建一个集合,用于存储查询出来的结果 List<EmpForm> empFormList = new ArrayList<>(); while (resultSet.next()) { EmpForm empForm = new EmpForm(); empForm.setEmpId(resultSet.getString("emp_id")); empForm.setEmpName(resultSet.getString("emp_name")); empForm.setDeptName(resultSet.getString("dept_name")); empForm.setRealSalary(resultSet.getDouble("fix_salary")); empFormList.add(empForm); } JdbcUtil.close(connection, statement, resultSet); if (empFormList.size() != 0) { return empFormList; } } catch (SQLException e) { e.printStackTrace(); } return null; } } |
第二阶段(工资数据维护):
- 固定工资维护
- 实现工资列表功能
![](https://i-blog.csdnimg.cn/blog_migrate/39ddcde6b410b4988fba6e0a21dfb66d.png)
第一步:绘制固定工资的页面
![](https://i-blog.csdnimg.cn/blog_migrate/1ea189c2a5cd7f0d9dda1571a4a488b3.png)
第二步:读取固定工资表,并显示在TableView中
FixedSalaryController
/** * 固定工资维护控制器 */ public class FixedSalaryController {
@FXML private TableColumn<FixedSalary, Double> baseSalaryCol; //基本工资 @FXML private TableView<FixedSalary> salaryTableView; @FXML private TableColumn<FixedSalary, String> empIdCol; @FXML private TableColumn<FixedSalary, Double> trafficSubsideCol; @FXML private ChoiceBox<String> queryConditionCB; @FXML private TableColumn<FixedSalary, String> empNameCol; @FXML private TableColumn<FixedSalary, Double> restSubsideCol; @FXML private TextField queryTextTF;
//dao private FixedSalaryDao fixedSalaryDao = new FixedSalaryDaoImpl();
//ObservableList private ObservableList dataList = FXCollections.observableArrayList();
/** * 初始化方法 */ public void initialize(){ //显示工资数据, dao ObservableList
}
@FXML void updateSalary(ActionEvent event) {
}
@FXML void querySalaryInfo(ActionEvent event) {
} } |
FixedSalaryDao
/** * 该类用于对固定工资表进行增删改查的操作 */ public interface FixedSalaryDao {
/** * 通过表的主键查找数据 * @param fsId * @return */ FixedSalary queryById(Integer fsId);
/** * 通过员工的姓名查找 * @param name * @return */ FixedSalary queryByEmpName(String name);
/** * 查询所有的工资信息 * @return */ List<FixedSalary> queryAll();
/** * 根据条件查询 * @param condition 查询的条件 * @param keyword 查询的关键词 * @return */ List<FixedSalary> queryByCondition(String condition, String keyword);
/** * 更新工资项 * @param fixedSalary * @return */ boolean updateFixedSalary(FixedSalary fixedSalary);
} |
FixedSalaryDaoImpl
/** * 固定工资dao的实现类 */ public class FixedSalaryDaoImpl implements FixedSalaryDao {
@Override public FixedSalary queryById(Integer fsId) { return null; }
@Override public FixedSalary queryByEmpName(String name) { return null; }
@Override public List<FixedSalary> queryAll() { String sql = "select * from fixed_salary"; try { Connection connection = JdbcUtil.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); //定义一个集合容器 List<FixedSalary> fixedSalaryList = new ArrayList<>(); while(resultSet.next()){ //next方法用于判断表是否有未读取的数据 FixedSalary fs = new FixedSalary(); int fs_id = resultSet.getInt("fs_id"); fs.setFsId(fs_id); double fiex_salary = resultSet.getDouble("fiex_salary"); fs.setFiexSalary(fiex_salary); double bus_subsidy = resultSet.getDouble("bus_subsidy"); fs.setBusSubsidy(bus_subsidy); double food_subsidy = resultSet.getDouble("food_subsidy"); fs.setFoodSubsidy(food_subsidy); String emp_id = resultSet.getString("emp_id"); //处理关乎员工名字查询 String sqlEmpName = "select emp_name from emp where emp_id="+emp_id; Statement statement1 = connection.createStatement(); ResultSet resultSet1 = statement1.executeQuery(sqlEmpName); resultSet1.next(); String emp_name = resultSet1.getString("emp_name"); fs.setEmpName(emp_name); //到此为止,就把记录中所的字段查出 fixedSalaryList.add(fs); }
//关闭资源 JdbcUtil.close(connection, statement, resultSet); return fixedSalaryList; } catch (SQLException e){ e.printStackTrace(); } return null; }
@Override public List<FixedSalary> queryByCondition(String condition, String keyword) { return null; }
@Override public boolean updateFixedSalary(FixedSalary fixedSalary) { return false; } } |
第三步:显示固定工资维护的页面
@FXML void showFixedSalaryVIew(ActionEvent event) { try { FXMLLoader fxmlLoader = new FXMLLoader(); fxmlLoader.setLocation(MainController.class.getResource("/com/neuedu/neuedusystem/FixedSalaryView.fxml")); AnchorPane deptPane = fxmlLoader.load(); mainView.setCenter(deptPane);
FixedSalaryController deptController = fxmlLoader.getController(); //deptController.setPrimaryStage(this.primaryStage);
} catch (IOException e){ e.printStackTrace(); } } |
- 更新工资项
![](https://i-blog.csdnimg.cn/blog_migrate/569701353b33854048e7d4fc40c7e9cd.png)
第一步: 绘制编辑界面
![](https://i-blog.csdnimg.cn/blog_migrate/bed8a663ef924a3111f127818db98941.png)
第二步:弹出窗体
@FXML void showFixedSalaryVIew(ActionEvent event) { try { FXMLLoader fxmlLoader = new FXMLLoader(); fxmlLoader.setLocation(MainController.class.getResource("/com/neuedu/neuedusystem/FixedSalaryView.fxml")); AnchorPane deptPane = fxmlLoader.load(); mainView.setCenter(deptPane);
FixedSalaryController fixedSalaryController = fxmlLoader.getController(); fixedSalaryController.setPrimaryStage(this.primaryStage);
} catch (IOException e){ e.printStackTrace(); } } |
第三步:编写FixedSalaryEditController
/** * 更新工资项的窗口控制器 */ public class FixedSalaryEditController {
@FXML private TextField baseSalaryTF; @FXML private TextField empIdTF; @FXML private TextField empNameTF; @FXML private TextField trafficSubsidyTF; @FXML private TextField foodSubsidyTF;
private FixedSalary fixedSalary; private Stage mStage; private FixedSalaryDao fixedSalaryDao;
public void setFixedSalaryDao(FixedSalaryDao fixedSalaryDao) { this.fixedSalaryDao = fixedSalaryDao; }
public void setStage(Stage mStage) { this.mStage = mStage; }
public void setFixedSalary(FixedSalary fixedSalary) { this.fixedSalary = fixedSalary; //回显要编辑的数据 empIdTF.setText(String.valueOf(fixedSalary.getFsId())); empNameTF.setText(fixedSalary.getEmpName()); baseSalaryTF.setText(String.valueOf(fixedSalary.getFiexSalary())); trafficSubsidyTF.setText(String.valueOf(fixedSalary.getBusSubsidy())); foodSubsidyTF.setText(String.valueOf(fixedSalary.getFoodSubsidy())); }
@FXML public void initialize(){
}
@FXML void onSubmit(ActionEvent event) { //需要对数据进行更新,一部分是ObservableList(FixedSalary) 另一个是更新数据库Dao fixedSalary.setFsId(Integer.parseInt(empIdTF.getText())); fixedSalary.setEmpName(empNameTF.getText()); fixedSalary.setFiexSalary(Double.parseDouble(baseSalaryTF.getText())); fixedSalary.setBusSubsidy(Double.parseDouble(trafficSubsidyTF.getText())); fixedSalary.setFoodSubsidy(Double.parseDouble(foodSubsidyTF.getText()));
//调用dao更新数据库中的数据 fixedSalaryDao.updateFixedSalary(fixedSalary);
mStage.close(); }
@FXML void onCancel(ActionEvent event) { //放弃, 关闭窗体, Stage.close() mStage.close(); }
} |
第四步:编写DAO层
/** * 固定工资dao的实现类 */ public class FixedSalaryDaoImpl implements FixedSalaryDao {
@Override public FixedSalary queryById(Integer fsId) { return null; }
@Override public FixedSalary queryByEmpName(String name) { return null; }
@Override public List<FixedSalary> queryAll() { String sql = "select * from fixed_salary"; try { Connection connection = JdbcUtil.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); //定义一个集合容器 List<FixedSalary> fixedSalaryList = new ArrayList<>(); while(resultSet.next()){ //next方法用于判断表是否有未读取的数据 FixedSalary fs = new FixedSalary(); int fs_id = resultSet.getInt("fs_id"); fs.setFsId(fs_id); double fiex_salary = resultSet.getDouble("fiex_salary"); fs.setFiexSalary(fiex_salary); double bus_subsidy = resultSet.getDouble("bus_subsidy"); fs.setBusSubsidy(bus_subsidy); double food_subsidy = resultSet.getDouble("food_subsidy"); fs.setFoodSubsidy(food_subsidy); String emp_id = resultSet.getString("emp_id"); //处理关乎员工名字查询 String sqlEmpName = "select emp_name from emp where emp_id="+emp_id; Statement statement1 = connection.createStatement(); ResultSet resultSet1 = statement1.executeQuery(sqlEmpName); resultSet1.next(); String emp_name = resultSet1.getString("emp_name"); fs.setEmpName(emp_name); JdbcUtil.close(null, statement1, resultSet1); //到此为止,就把记录中所的字段查出 fixedSalaryList.add(fs); }
//关闭资源 JdbcUtil.close(connection, statement, resultSet); return fixedSalaryList; } catch (SQLException e){ e.printStackTrace(); } return null; }
@Override public List<FixedSalary> queryByCondition(String condition, String keyword) { return null; }
@Override public boolean updateFixedSalary(FixedSalary fixedSalary) { String sql = "update fixed_salary set fiex_salary=?, bus_subsidy=?, food_subsidy=? where fs_id="+fixedSalary.getFsId(); try { Connection connection = JdbcUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(sql); ps.setDouble(1, fixedSalary.getFiexSalary()); ps.setDouble(2, fixedSalary.getBusSubsidy()); ps.setDouble(3, fixedSalary.getFoodSubsidy()); //更新了基本信息、交通补助、餐补 boolean execute = ps.execute();
//根据fs_id获取emp_id String sqlId = "select emp_id from fixed_salary where fs_id="+fixedSalary.getFsId(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sqlId); resultSet.next(); String emp_id = resultSet.getString("emp_id"); JdbcUtil.close(null, statement, resultSet);
//根据员工的id更新员工的姓名 String sqlName = "update emp set emp_name=? where emp_id="+emp_id; PreparedStatement ps2 = connection.prepareStatement(sqlName); ps2.setString(1, fixedSalary.getEmpName()); boolean execute1 = ps2.execute(); JdbcUtil.close(null, ps2, null);
//关闭资源 JdbcUtil.close(connection, ps, null); return execute; } catch (SQLException e){ e.printStackTrace(); } return false; } } |
- 导入数据维护
![](https://i-blog.csdnimg.cn/blog_migrate/e2738fde3541642df20b9daed61de2ea.png)
- 创建表
# 考勤表 CREATE TABLE info( info_id INT(11) PRIMARY KEY AUTO_INCREMENT, sick_leave INT(6), absence_leave INT(6), late INT(6), leave_early INT(6), over_time INT(6),`neuedu_system``info` year_ VARCHAR(10), month_ VARCHAR(10), emp_id VARCHAR(10), FOREIGN KEY(emp_id) REFERENCES emp(emp_id) ) |
外键:
- 绘制界面
![](https://i-blog.csdnimg.cn/blog_migrate/d5bbc1aa47b65b537b8bfc021f46db2a.png)
- 编写逻辑处理(控制器、实体类)
控制器
public class ImportInfoController {
@FXML private TableColumn<?, ?> empIdColumn; @FXML private TableColumn<?, ?> yearColumn; @FXML private TableColumn<?, ?> monthColumn; @FXML private TableColumn<?, ?> empNameColumn; @FXML private TableColumn<?, ?> leaveEarlyCountColumn; @FXML private TableColumn<?, ?> leaveDaysColumn; @FXML private TableColumn<?, ?> overTimeColumn; @FXML private TableColumn<?, ?> sickDaysColumn; @FXML private TableColumn<?, ?> lateCountColumn; @FXML private TableView<?> importInfoTV;
private Stage primaryStage; //主窗体
public void setPrimaryStage(Stage primaryStage) { this.primaryStage = primaryStage; }
@FXML void onImportData(ActionEvent event) { FileChooser fileChooser = new FileChooser(); fileChooser.setTitle("选择需要导入的文件"); File file = fileChooser.showOpenDialog(this.primaryStage); System.out.println(file.getName()); }
@FXML void onEditData(ActionEvent event) { }
@FXML void onFind(ActionEvent event) { } } |
实体类
/** * 导入数据的实体类 */ public class ImportInfo {
private IntegerProperty infoId; private IntegerProperty sickLeave; //病假天数 private IntegerProperty absenceLeave; //事假天数 private IntegerProperty late; //迟到次数 private IntegerProperty leaveEarly; //早退次数 private IntegerProperty overTime; //加班的天数 private StringProperty year; //年 private StringProperty month; //月 private StringProperty empId; //通过构造函数对以上成员变量进行初始化 public ImportInfo(){ infoId = new SimpleIntegerProperty(0); sickLeave = new SimpleIntegerProperty(0); absenceLeave = new SimpleIntegerProperty(0); late = new SimpleIntegerProperty(0); leaveEarly = new SimpleIntegerProperty(0); overTime = new SimpleIntegerProperty(0); year = new SimpleStringProperty(""); month = new SimpleStringProperty(""); empId = new SimpleStringProperty(""); }
public IntegerProperty infoIdProperty(){ return infoId; } public IntegerProperty sickLeaveProperty(){ return sickLeave; } public IntegerProperty absenceLeaveProperty(){ return absenceLeave; } public IntegerProperty lateProperty(){ return late; } public IntegerProperty leaveEarlyProperty(){ return leaveEarly; } public IntegerProperty overTimeProperty(){ return overTime; } public StringProperty yearProperty(){ return year; } public StringProperty monthProperty(){ return month; } public StringProperty empIdProperty(){ return empId; }
public int getInfoId() { return infoId.get(); }
public void setInfoId(int infoId) { this.infoId.set(infoId); }
public int getSickLeave() { return sickLeave.get(); }
public void setSickLeave(int sickLeave) { this.sickLeave.set(sickLeave); }
public int getAbsenceLeave() { return absenceLeave.get(); }
public void setAbsenceLeave(int absenceLeave) { this.absenceLeave.set(absenceLeave); }
public int getLate() { return late.get(); }
public void setLate(int late) { this.late.set(late); }
public int getLeaveEarly() { return leaveEarly.get(); }
public void setLeaveEarly(int leaveEarly) { this.leaveEarly.set(leaveEarly); }
public int getOverTime() { return overTime.get(); }
public void setOverTime(int overTime) { this.overTime.set(overTime); }
public String getYear() { return year.get(); }
public void setYear(String year) { this.year.set(year); }
public String getMonth() { return month.get(); }
public void setMonth(String month) { this.month.set(month); }
public String getEmpId() { return empId.get(); }
public void setEmpId(String empId) { this.empId.set(empId); }
@Override public String toString() { return "ImportInfo{" + "infoId=" + infoId + ", sickLeave=" + sickLeave + ", absenceLeave=" + absenceLeave + ", late=" + late + ", leaveEarly=" + leaveEarly + ", overTime=" + overTime + ", year=" + year + ", month=" + month + ", empId=" + empId + '}'; } } |
- Dao层
public class ImportInfoDaoImpl implements ImportInfoDao {
@Override public List<ImportInfo> queryAll() { String sql = "select * from info"; try { Connection connection = JdbcUtil.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); //创建集合 List<ImportInfo> importInfoList = new ArrayList<>(); while(resultSet.next()){ ImportInfo importInfo = new ImportInfo(); importInfo.setInfoId(resultSet.getInt("info_id")); importInfo.setAbsenceLeave(resultSet.getInt("absence_leave")); importInfo.setSickLeave(resultSet.getInt("sick_leave")); importInfo.setLate(resultSet.getInt("late")); importInfo.setLeaveEarly(resultSet.getInt("leave_early")); importInfo.setOverTime(resultSet.getInt("over_time")); importInfo.setYear(resultSet.getString("year_")); importInfo.setMonth(resultSet.getString("month_")); importInfo.setEmpId(resultSet.getString("emp_id")); importInfoList.add(importInfo); } //关闭资源 JdbcUtil.close(connection, statement, resultSet); return importInfoList; } catch (SQLException e){ e.printStackTrace(); } return null; }
@Override public boolean addBatch(List<ImportInfo> importInfos) { if (importInfos != null){ String sql = "insert into info values(?,?,?,?,?,?,?,?,?)"; for (int i = 0; i < importInfos.size(); i++) { ImportInfo importInfo = importInfos.get(i); try { Connection connection = JdbcUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(sql); ps.setInt(1, importInfo.getInfoId()); ps.setInt(2, importInfo.getSickLeave()); ps.setInt(3, importInfo.getAbsenceLeave()); ps.setInt(4, importInfo.getLate()); ps.setInt(5, importInfo.getLeaveEarly()); ps.setInt(6, importInfo.getOverTime()); ps.setString(7, importInfo.getYear()); ps.setString(8, importInfo.getMonth()); ps.setString(9, importInfo.getEmpId()); boolean execute = ps.execute(); //关闭资源 JdbcUtil.close(connection, ps, null); return execute; } catch (SQLException e){ e.printStackTrace(); } } } return false; }
@Override public boolean updateImportInfo(ImportInfo importInfo) { return false; }
@Override public List<ImportInfo> queryByCondition(String condition1, String condition2) { return null; } } |
- 实现数据导入功能
![](https://i-blog.csdnimg.cn/blog_migrate/e2ddd66b8c6964b25671f2586ea8dce8.png)
需要使用的jar包,POI相关的jar,它是用于操作office办公软件的,我们该功能需要利用它去读取Excel文件。
第一步:引入jar包
![](https://i-blog.csdnimg.cn/blog_migrate/83bad93a9489efc0933419965411e716.png)
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>5.0.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>5.0.0</version> </dependency> |
第二步:读取Excel
FileChooser fileChooser = new FileChooser(); fileChooser.setTitle("选择需要导入的文件"); File file = fileChooser.showOpenDialog(this.primaryStage); try { FileInputStream fis = new FileInputStream(file); //XSSFWorkbook该对象代表着那个importdata.excel文件
XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet sheet1 = workbook.getSheet("Sheet1"); //获取有数据的行数 int lastRowNum = sheet1.getLastRowNum(); //lastRowNum=3 List<ImportInfo> importInfoList = new ArrayList<>(); for (int i = 1; i <= lastRowNum; i++) { XSSFRow row = sheet1.getRow(i); short lastCellNum = row.getLastCellNum(); //lastCellNum=8 ImportInfo importInfo = new ImportInfo(); for (int j = 0; j < lastCellNum; j++) { //循环一行 XSSFCell cell = row.getCell(j); //cell对象代表一个具体的单元格 DataFormatter formatter = new DataFormatter(); String cellValue = formatter.formatCellValue(cell); switch (j){ case 0: importInfo.setInfoId(Integer.parseInt(cellValue)); break; case 1: importInfo.setSickLeave(Integer.parseInt(cellValue)); break; case 2: importInfo.setAbsenceLeave(Integer.parseInt(cellValue)); break; case 3: importInfo.setLate(Integer.parseInt(cellValue)); break; case 4: importInfo.setLeaveEarly(Integer.parseInt(cellValue)); break; case 5: importInfo.setOverTime(Integer.parseInt(cellValue)); break; case 6: importInfo.setYear(cellValue); break; case 7: importInfo.setMonth(cellValue); break; case 8: importInfo.setEmpId(cellValue); break; } } importInfoList.add(importInfo); } //数据读取完毕 initTableView(importInfoList); } catch (IOException e){ e.printStackTrace(); } |
查询员工的姓名
@Override public String getEmpName(String empId) { String sql = "select emp_name from emp where emp_id="+empId; try { Connection connection = JdbcUtil.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); resultSet.next(); //指向数据所在的行 String empName = resultSet.getString("emp_name"); return empName; }catch (SQLException e){ e.printStackTrace(); } return null; } |
![](https://i-blog.csdnimg.cn/blog_migrate/dbe603699a113b1f41ba666cdc6e1f62.png)
添加数据的过程中做去重处理
/** * 从新导入的数据中去掉数据库中已有的数据 * @param dbInfoList 数据库中的数据 * @param importInfoList 新导入的数据 */ private void removeRepeat(List<ImportInfo> dbInfoList, List<ImportInfo> importInfoList){ int size = dbInfoList.size(); if (size == 0){ //数据库中是没有数据的 return; }
for (int i = 0; i < size; i++) { ImportInfo importInfo = dbInfoList.get(i); String empIdDb = importInfo.getEmpId(); for (int j = 0; j < importInfoList.size(); j++) { ImportInfo importInfo1 = importInfoList.get(j); String importEmpId = importInfo1.getEmpId(); if (importEmpId.equals(empIdDb)){ //两个员工id是一样 importInfoList.remove(importInfo1); break; //中断当前循环 } } } } |
依据数据中的数据刷新TableView
/** * 从数据库加载数据 */ private void loadDataToTableView() { List<ImportInfo> importInfoList = importInfoDao.queryAll(); if (importInfoList == null){ System.out.println("importInfoList 空指针异常"); return; } //清空dataList dataList.clear(); dataList.addAll(importInfoList); } |
- 编辑数据
第一步:绘制界面
![](https://i-blog.csdnimg.cn/blog_migrate/64332687dd6a1658ce5bec85fcfa6a82.png)
第二步:编写controller
显示对话框
@FXML void onEditData(ActionEvent event) { //用户要选中一条数据 ImportInfo selectedItem = importInfoTV.getSelectionModel().getSelectedItem(); if (selectedItem != null){ //加载对话框的视图 FXMLLoader fxmlLoader = new FXMLLoader(); fxmlLoader.setLocation(ImportInfoController.class.getResource("/com/neuedu/neuedusystem/ImportInfoEditDialogView.fxml")); //获取对话框的视图对象 try { AnchorPane anchorPane = fxmlLoader.load(); //把视图对象交给场景 Scene scene =new Scene(anchorPane); //搭建舞台,表演这个场景 Stage stage =new Stage(); stage.setScene(scene); stage.setTitle("修改考勤数据"); stage.initOwner(primaryStage); stage.initModality(Modality.WINDOW_MODAL);
//操作Controller ImportInfoEditDialogController dialogController = fxmlLoader.getController(); dialogController.setImportInfo(selectedItem); dialogController.setStage(stage); dialogController.setImportInfoDao(importInfoDao); dialogController.setDataList(dataList);
stage.showAndWait();
} catch (IOException e){ e.printStackTrace(); } } } |
对话框控制器
public class ImportInfoEditDialogController {
@FXML private TextField sickDaysTF; @FXML private TextField overTimeTF; @FXML private TextField earlyCountTF; @FXML private TextField absenceDaysTF; @FXML private TextField lateCountTF; @FXML private TextField empIdTF; @FXML private TextField monthTF; @FXML private TextField yearTF; @FXML private TextField empNameTF;
private ImportInfo importInfo; private Stage stage; private ImportInfoDao importInfoDao; private ObservableList dataList;
public void setDataList(ObservableList dataList) { this.dataList = dataList; }
public void setImportInfoDao(ImportInfoDao importInfoDao) { this.importInfoDao = importInfoDao; }
public void setStage(Stage stage) { this.stage = stage; }
@FXML public void initialize(){ //设置TextField可修改状态为不可修改 empIdTF.setEditable(false); }
public void setImportInfo(ImportInfo importInfo) { this.importInfo = importInfo; //回显数据 sickDaysTF.setText(String.valueOf(importInfo.getSickLeave())); overTimeTF.setText(String.valueOf(importInfo.getOverTime())); earlyCountTF.setText(String.valueOf(importInfo.getLeaveEarly())); absenceDaysTF.setText(String.valueOf(importInfo.getAbsenceLeave())); lateCountTF.setText(String.valueOf(importInfo.getLate())); empIdTF.setText(importInfo.getEmpId()); monthTF.setText(importInfo.getMonth()); yearTF.setText(importInfo.getYear()); empNameTF.setText(importInfo.getEmpName()); }
@FXML void onSubmit(ActionEvent event) { //从对话框中获取修改后的数据,然后存入到一个ImportInfo对象 importInfo.setSickLeave(Integer.parseInt(sickDaysTF.getText())); importInfo.setOverTime(Integer.parseInt(overTimeTF.getText())); importInfo.setLeaveEarly(Integer.parseInt(earlyCountTF.getText())); importInfo.setAbsenceLeave(Integer.parseInt(absenceDaysTF.getText())); importInfo.setLate(Integer.parseInt(lateCountTF.getText())); importInfo.setEmpId(empIdTF.getText()); importInfo.setYear(yearTF.getText()); importInfo.setMonth(monthTF.getText()); importInfo.setEmpName(empNameTF.getText()); importInfo.setEmpId(empIdTF.getText()); System.out.println(importInfo.toString());
//调用Dao更新考勤数据 importInfoDao.updateImportInfo(importInfo); stage.close(); }
@FXML void onCancel(ActionEvent event) { stage.close(); } } |
- 工资计算
![](https://i-blog.csdnimg.cn/blog_migrate/dfc20f85e4a58d245c17b07aa9eb933e.png)
![](https://i-blog.csdnimg.cn/blog_migrate/ddfc19dfcbdc16b468161906a720378a.png)
- 创建数据库表
# 实际工资表 CREATE TABLE real_salary( rs_id INT(11) PRIMARY KEY AUTO_INCREMENT, income DOUBLE(20,2), fs_id INT(11), emp_id VARCHAR(10), sick_cut DOUBLE(18, 2), absence_cut DOUBLE(18,2), late_cut DOUBLE(18,2), leave_early_cut DOUBLE(18,2), overtime_pay DOUBLE(18,2),`real_salary` reserve DOUBLE(18,2), old_assurece DOUBLE(18,2), medical_assurece DOUBLE(18,2), fire_assurece DOUBLE(18,2), tax DOUBLE(18,2), FOREIGN KEY(fs_id) REFERENCES fixed_salary(fs_id), FOREIGN KEY(emp_id) REFERENCES emp(emp_id) ) |
- 绘制界面并显示
![](https://i-blog.csdnimg.cn/blog_migrate/17846c300006666ca14a93d379028128.png)
@FXML void showRealSalaryView(ActionEvent event){ try { FXMLLoader fxmlLoader = new FXMLLoader(); fxmlLoader.setLocation(MainController.class.getResource("/com/neuedu/neuedusystem/SalaryDealView.fxml")); AnchorPane deptPane = fxmlLoader.load(); mainView.setCenter(deptPane);
SalaryDealController salaryDealController = fxmlLoader.getController();
} catch (IOException e){ e.printStackTrace(); } } |
- 编写控制器
SalaryDealController
/** * 工资计算的控制器 */ public class SalaryDealController {
@FXML private TableColumn<RealSalary, String> empIdColumn; @FXML private TableColumn<RealSalary, Double> taxColumn; @FXML private TableColumn<RealSalary, String> empNameColumn; @FXML private TableColumn<RealSalary, Double> absenceCutColumn; @FXML private TableColumn<RealSalary, Double> realSalaryColumn; @FXML private TableColumn<RealSalary, Double> fixedSalaryColumn; @FXML private TableColumn<RealSalary, Double> foodSubsidyColumn; @FXML private TableColumn<RealSalary, Double> overTimePayColumn; @FXML private TableColumn<RealSalary, Double> medicalAssurenceColumn; @FXML private TableColumn<RealSalary, Double> leaveEarlyCutColumn; @FXML private TableColumn<RealSalary, Double> busSubsidyColumn; @FXML private TableColumn<RealSalary, Double> oldAssurenceColumn; @FXML private TableColumn<RealSalary, Double> lateCutCloumn; @FXML private TableColumn<RealSalary, Double> fireAssurenceColumn; @FXML private TableColumn<RealSalary, Double> sickCutColumn; @FXML private TableView<RealSalary> realSalaryTableView;
private RealSalaryDao realSalaryDao = new RealSalaryDaoImpl(); private ObservableList dataList = FXCollections.observableArrayList();
@FXML void initialize(){
empIdColumn.setCellValueFactory(new PropertyValueFactory<>("empId")); taxColumn.setCellValueFactory(new PropertyValueFactory<>("tax")); empNameColumn.setCellValueFactory(new PropertyValueFactory<>("empName")); absenceCutColumn.setCellValueFactory(new PropertyValueFactory<>("absenceCut")); realSalaryColumn.setCellValueFactory(new PropertyValueFactory<>("income")); fixedSalaryColumn.setCellValueFactory(new PropertyValueFactory<>("baseSalary")); foodSubsidyColumn.setCellValueFactory(new PropertyValueFactory<>("foodSubsidy")); busSubsidyColumn.setCellValueFactory(new PropertyValueFactory<>("busSubsidy")); overTimePayColumn.setCellValueFactory(new PropertyValueFactory<>("overtimePay")); medicalAssurenceColumn.setCellValueFactory(new PropertyValueFactory<>("medicalAssurece")); leaveEarlyCutColumn.setCellValueFactory(new PropertyValueFactory<>("leaveEarlyCut")); oldAssurenceColumn.setCellValueFactory(new PropertyValueFactory<>("oldAssurece")); lateCutCloumn.setCellValueFactory(new PropertyValueFactory<>("lateCut")); fireAssurenceColumn.setCellValueFactory(new PropertyValueFactory<>("fireAssurece")); sickCutColumn.setCellValueFactory(new PropertyValueFactory<>("sickCut"));
List<RealSalary> realSalaryList = realSalaryDao.queryAll(); dataList.addAll(realSalaryList); realSalaryTableView.setItems(dataList); }
@FXML void onNewRealSalary(ActionEvent event) {
}
@FXML void onCalculateSalary(ActionEvent event) {
}
@FXML void onFrozenSalary(ActionEvent event) {
}
@FXML void onPaySlip(ActionEvent event) {
} } |
封装实体类RealSalary
/** * 实际工资 */ public class RealSalary {
private IntegerProperty rsId; private DoubleProperty income; //实际工资 private IntegerProperty fsId; //固定工资id private DoubleProperty baseSalary; //基本工资 private DoubleProperty foodSubsidy; //餐补 private DoubleProperty busSubsidy; //交通补贴 private ObjectProperty<FixedSalary> fixedSalary; private StringProperty empId; private ObjectProperty<Emp> emp; //员工信息 private StringProperty empName; private DoubleProperty sickCut; //病假扣款 private DoubleProperty absenceCut; //事假扣款 private DoubleProperty lateCut; //迟到扣款 private DoubleProperty leaveEarlyCut; //早退扣款 private DoubleProperty overtimePay; //加班挣钱 private DoubleProperty reserve; //公积金 private DoubleProperty oldAssurece; //养老保险 private DoubleProperty medicalAssurece; //医疗保险 private DoubleProperty fireAssurece; //失业保险 private DoubleProperty tax; //个人所得税
public RealSalary(){ rsId = new SimpleIntegerProperty(1); income = new SimpleDoubleProperty(0); fsId = new SimpleIntegerProperty(1); fixedSalary = new SimpleObjectProperty<>(); empId = new SimpleStringProperty(""); emp = new SimpleObjectProperty<>(); sickCut = new SimpleDoubleProperty(0); absenceCut = new SimpleDoubleProperty(0); lateCut = new SimpleDoubleProperty(0); leaveEarlyCut = new SimpleDoubleProperty(0); overtimePay = new SimpleDoubleProperty(0); reserve = new SimpleDoubleProperty(0); oldAssurece = new SimpleDoubleProperty(0); medicalAssurece = new SimpleDoubleProperty(0); fireAssurece = new SimpleDoubleProperty(0); tax = new SimpleDoubleProperty(0); empName = new SimpleStringProperty(""); baseSalary = new SimpleDoubleProperty(0); foodSubsidy = new SimpleDoubleProperty(0); busSubsidy = new SimpleDoubleProperty(0); }
public DoubleProperty busSubsidyProperty(){ return busSubsidy; }
public double getBusSubsidy() { return busSubsidy.get(); }
public void setBusSubsidy(double busSubsidy) { this.busSubsidy.set(busSubsidy); }
public DoubleProperty foodSubsidyProperty(){ return foodSubsidy; }
public double getFoodSubsidy() { return foodSubsidy.get(); }
public void setFoodSubsidy(double foodSubsidy) { this.foodSubsidy.set(foodSubsidy); }
public DoubleProperty baseSalaryProperty(){ return baseSalary; }
public DoubleProperty incomeProperty(){ return income; }
public StringProperty empNameProperty(){ return empName; }
public DoubleProperty sickCutProperty(){ return sickCut; } public DoubleProperty absenceCutProperty(){ return absenceCut; } public DoubleProperty lateCutProperty(){ return lateCut; }
public DoubleProperty leaveEarlyCutProperty(){ return leaveEarlyCut; } public DoubleProperty overtimePayProperty(){ return overtimePay; }
public DoubleProperty reserveProperty(){ return reserve; } public DoubleProperty oldAssureceProperty(){ return oldAssurece; }
public DoubleProperty medicalAssureceProperty(){ return medicalAssurece; } public DoubleProperty fireAssureceProperty(){ return fireAssurece; }
public DoubleProperty taxProperty(){ return tax; }
public int getRsId() { return rsId.get(); }
public IntegerProperty rsIdProperty() { return rsId; }
public void setRsId(int rsId) { this.rsId.set(rsId); }
public double getIncome() { return income.get(); }
public void setIncome(double income) { this.income.set(income); }
public int getFsId() { return fsId.get(); }
public IntegerProperty fsIdProperty() { return fsId; }
public void setFsId(int fsId) { this.fsId.set(fsId); }
public FixedSalary getFixedSalary() { return fixedSalary.get(); }
public ObjectProperty<FixedSalary> fixedSalaryProperty() { return fixedSalary; }
public void setFixedSalary(FixedSalary fixedSalary) { this.fixedSalary.set(fixedSalary); }
public String getEmpId() { return empId.get(); }
public StringProperty empIdProperty() { return empId; }
public void setEmpId(String empId) { this.empId.set(empId); }
public Emp getEmp() { return emp.get(); }
public ObjectProperty<Emp> empProperty() { return emp; }
public void setEmp(Emp emp) { this.emp.set(emp); }
public String getEmpName() { return empName.get(); }
public void setEmpName(String empName) { this.empName.set(empName); }
public double getSickCut() { return sickCut.get(); }
public void setSickCut(double sickCut) { this.sickCut.set(sickCut); }
public double getAbsenceCut() { return absenceCut.get(); }
public void setAbsenceCut(double absenceCut) { this.absenceCut.set(absenceCut); }
public double getLateCut() { return lateCut.get(); }
public void setLateCut(double lateCut) { this.lateCut.set(lateCut); }
public double getLeaveEarlyCut() { return leaveEarlyCut.get(); }
public void setLeaveEarlyCut(double leaveEarlyCut) { this.leaveEarlyCut.set(leaveEarlyCut); }
public double getOvertimePay() { return overtimePay.get(); }
public void setOvertimePay(double overtimePay) { this.overtimePay.set(overtimePay); }
public double getReserve() { return reserve.get(); }
public void setReserve(double reserve) { this.reserve.set(reserve); }
public double getOldAssurece() { return oldAssurece.get(); }
public void setOldAssurece(double oldAssurece) { this.oldAssurece.set(oldAssurece); }
public double getMedicalAssurece() { return medicalAssurece.get(); }
public void setMedicalAssurece(double medicalAssurece) { this.medicalAssurece.set(medicalAssurece); }
public double getFireAssurece() { return fireAssurece.get(); }
public void setFireAssurece(double fireAssurece) { this.fireAssurece.set(fireAssurece); }
public double getTax() { return tax.get(); }
public void setTax(double tax) { this.tax.set(tax); }
public double getBaseSalary() { return baseSalary.get(); }
public void setBaseSalary(double baseSalary) { this.baseSalary.set(baseSalary); }
@Override public String toString() { return "RealSalary{" + "rsId=" + rsId + ", income=" + income + ", fsId=" + fsId + ", fixedSalary=" + fixedSalary + ", empId=" + empId + ", emp=" + emp + ", empName=" + empName + ", sickCut=" + sickCut + ", absenceCut=" + absenceCut + ", lateCut=" + lateCut + ", leaveEarlyCut=" + leaveEarlyCut + ", overtimePay=" + overtimePay + ", reserve=" + reserve + ", oldAssurece=" + oldAssurece + ", medicalAssurece=" + medicalAssurece + ", fireAssurece=" + fireAssurece + ", tax=" + tax + '}'; } } |
- 编写实际工资的数据访问层Dao
public class RealSalaryDaoImpl implements RealSalaryDao {
@Override public List<RealSalary> queryAll() { String sql = "select * from real_salary"; List<RealSalary> realSalaryList = new ArrayList<>(); try { Connection connection = JdbcUtil.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); while(resultSet.next()){ //先做判断 RealSalary rs = new RealSalary(); rs.setRsId(resultSet.getInt("rs_id")); rs.setIncome(resultSet.getDouble("income")); int fs_id = resultSet.getInt("fs_id"); rs.setFsId(fs_id); //查询fs_id对应的固定薪资记录 String sqlFixedSalary = "select * from fixed_salary where fs_id="+fs_id; Statement fixedSalaryStatement = connection.createStatement(); ResultSet fixedSalaryResultSet = fixedSalaryStatement.executeQuery(sqlFixedSalary); if (fixedSalaryResultSet.next()){ FixedSalary fs = new FixedSalary(); fs.setFsId(fs_id); fs.setFiexSalary(fixedSalaryResultSet.getDouble("fiex_salary")); fs.setFoodSubsidy(fixedSalaryResultSet.getDouble("food_subsidy")); fs.setBusSubsidy(fixedSalaryResultSet.getDouble("bus_subsidy")); rs.setFixedSalary(fs); }
//查询员工信息 String emp_id = resultSet.getString("emp_id"); rs.setEmpId(emp_id); String sqlEmp = "select * from emp where emp_id="+emp_id; Statement empStatement = connection.createStatement(); ResultSet empResultSet = empStatement.executeQuery(sqlEmp); if (empResultSet.next()){ Emp emp = new Emp(); emp.setEmpId(emp_id); emp.setEmpName(empResultSet.getString("emp_name")); emp.setEmail(empResultSet.getString("email")); emp.setDeptId(empResultSet.getString("dept_id")); emp.setJobId(empResultSet.getString("job_id")); rs.setEmp(emp); }
//考勤信息 rs.setSickCut(resultSet.getDouble("sick_cut")); rs.setAbsenceCut(resultSet.getDouble("absence_cut")); rs.setLateCut(resultSet.getDouble("late_cut")); rs.setLeaveEarlyCut(resultSet.getDouble("leave_early_cut")); rs.setOvertimePay(resultSet.getDouble("overtime_pay"));
//社保信息 rs.setReserve(resultSet.getDouble("reserve")); rs.setOldAssurece(resultSet.getDouble("old_assurece")); rs.setMedicalAssurece(resultSet.getDouble("medical_assurece")); rs.setFireAssurece(resultSet.getDouble("fire_assurece"));
//税 rs.setTax(resultSet.getDouble("tax")); realSalaryList.add(rs); } return realSalaryList; } catch (SQLException e){ e.printStackTrace(); } return null; } } |
- 计算工资
在RealSalary类中实现计算方法
/** * 计算工资的过程 */ public void calculateSalary(){ //基本工资 baseSalary
//计算考勤(迟到、早退、事假、病假、加班) calAttendance(); //计算社保缴纳(公积金、养老、医疗、失业) calInsurance(); //个人所得税 calTax(); }
//计算考勤 private void calAttendance() { // private DoubleProperty sickCut; //病假扣款 // private DoubleProperty absenceCut; //事假扣款 // private DoubleProperty lateCut; //迟到扣款 50 // private DoubleProperty leaveEarlyCut; //早退扣款 50 // private DoubleProperty overtimePay; //加班挣钱
//第一步:查出该员工的考勤信息 ImportInfoDao importInfoDao = new ImportInfoDaoImpl(); ImportInfo importInfo = importInfoDao.queryByEmpId(this.getEmpId()); //计算出工作一天挣多少钱 int monthWorkDays = 22; //每个月工作的天数 Double daySalary = this.getBaseSalary() / monthWorkDays;
//刚刚同学 int sickLeave = importInfo.getSickLeave(); int absenceLeave = importInfo.getAbsenceLeave(); int late = importInfo.getLate(); int leaveEarly = importInfo.getLeaveEarly(); int overTime = importInfo.getOverTime();
this.setSickCut(daySalary * sickLeave * 0.5); this.setAbsenceCut(daySalary * absenceLeave); this.setLateCut(50 * late); this.setLeaveEarlyCut(50 * leaveEarly); this.setOvertimePay(daySalary * overTime); }
//计算社保 private double calInsurance() { //假定每个月缴纳的公积金为2000, 个人出1000, 公司出1000 Double reserve = 2000d; this.setReserve(reserve * 0.5);
//养老保险 30000 0.07 //医疗保险 0.08 //失业保险 0.07 this.setOldAssurece(baseSalary.get() * 0.08); this.setMedicalAssurece(baseSalary.get() * 0.08); this.setFireAssurece(baseSalary.get() * 0.07);
return this.reserve.get() + this.oldAssurece.get() + this.medicalAssurece.get()+this.fireAssurece.get(); }
//计算个人所得税 private void calTaxAndIncome() { //个人所得税的起征点:5000 // 全月应纳税额不超过1500元3%0 // 全月应纳税额超过1500元至4500元10%105 // 全月应纳税额超过4500元至9000元20%555 // 全月应纳税额超过9000元至35000元25%1005 // 全月应纳税额超过35000元至55000元30%2755 // 全月应纳税额超过55000元至80000元35%5505 // 全月应纳税额超过80000元45%13505
//baseSalary - 考勤 + 加班 - 公积金 - 社保 - 5000 //纳税的基数 double baseTax = this.baseSalary.get() + calAttendance() - this.calInsurance() - 5000; double tax_ = 0d; if (baseTax < 1500){ tax_ = baseTax * 0.03; } else if (baseTax > 1500 && baseTax < 4500){ tax_ = baseTax * 0.1; } else if (baseTax > 4500 && baseTax < 9000){ tax_ = baseTax * 0.2; } else if (baseTax > 9000 && baseTax < 35000){ tax_ = baseTax * 0.25; } else if (baseTax > 35000 && baseTax < 55000){ tax_ = baseTax * 0.3; } else if (baseTax > 55000 && baseTax < 80000){ tax_ = baseTax * 0.35; } else if (baseTax > 80000){ tax_ = baseTax * 0.45; }
this.setTax(tax_);
//计算实际收入 income this.setIncome(this.baseSalary.get() + this.calAttendance() - this.calInsurance() - tax_ +busSubsidy.get()+foodSubsidy.get()); } |
第三阶段(报表管理):
- 员工工资统计报表界面的绘制
第一步:绘制界面内容
其中用了Tableview控件,SplitePane用于把页面划分出上下两部分, HBox水平摆放控件。
Button控件、Label控件、ChoiceBox控件。
![](https://i-blog.csdnimg.cn/blog_migrate/8b28d72e5a6bf2940d8f3edce42ebebb.png)
其中表格中的表头信息有:部门名称、部门编号、员工姓名、实发工资
第二步:显示部门页面
在MainController中传入mainView对象,用于切换主页面中的布局内容
private BorderPane mainView;
public void setMainView(BorderPane mainView) { this.mainView = mainView; } |
在MainApp中获取MainController,把mainView传递过去
![](https://i-blog.csdnimg.cn/blog_migrate/e6b80010b23b9776f2100286ef47fb89.png)
完成显示部门页面的逻辑
@FXML void showEmpFormView(){ try { FXMLLoader fxmlLoader = new FXMLLoader(); fxmlLoader.setLocation(MainController.class.getResource("/com/neuedu/neuedusystem/ExportEmpFormView.fxml")); AnchorPane deptPane = fxmlLoader.load(); mainView.setCenter(deptPane);
ExportEmpFormController controller = fxmlLoader.getController();
} catch (IOException e){ e.printStackTrace(); } } |
效果图如下:
![](https://i-blog.csdnimg.cn/blog_migrate/941f75bac64e0b4469f1a5edaaa0bbae.png)
- 员工工资统计报表中的内容
第一步:编写控制器ExportEmpFormController
import java.io.*; import java.util.List;
public class ExportEmpFormController { @FXML private TableColumn<EmpForm, String> empIdColumn;
@FXML private TableColumn<EmpForm, String> deptNameColumn;
@FXML private TableColumn<EmpForm, String> empNameColumn;
@FXML private TableColumn<EmpForm, Double> realSalaryColumn;
@FXML private TextField keywordTF; @FXML private TableView<EmpForm> empFormEmp; private EmpFormDao empFormDao = new EmpFormDaoImpl(); private ObservableList<EmpForm> dataList = FXCollections.observableArrayList(); private Stage primaryStage;
public void setPrimaryStage(Stage primaryStage) { this.primaryStage = primaryStage; }
@FXML public void initialize() { //绑定列----属性(又该做) deptNameColumn.setCellValueFactory(new PropertyValueFactory<>("deptName")); empIdColumn.setCellValueFactory(new PropertyValueFactory<>("empId")); empNameColumn.setCellValueFactory(new PropertyValueFactory<>("empName")); realSalaryColumn.setCellValueFactory(new PropertyValueFactory<>("realSalary"));
//查询数据库,加载报表数据,sql语句怎么写? List<EmpForm> empFormList = empFormDao.queryAll(); dataList.addAll(empFormList); empFormEmp.setItems(dataList); }
@FXML void find(ActionEvent event) { //获取要搜索的员工姓名 String keyword = keywordTF.getText(); if (keyword != null && !keyword.equals("")) {//当它不是空字符串的时候 List<EmpForm> empFormList = empFormDao.queryByName(keyword);//调用dao查找方法 if (empFormList != null) { //dataList清空一下 dataList.clear(); //添加查找的结果集合 dataList.addAll(empFormList); } else { //假设查出来 DialogUtil.showWarnDialog(this.primaryStage, "查询结果", "请重新输入搜索词"); } } else { //提示 DialogUtil.showWarnDialog(this.primaryStage, "缺少关键字", "请在输入框中输入要查询的关键词"); } }
@FXML void onexportForm(ActionEvent event) throws IOException { //导出统计报告 dataList ---POI---> excel文件 if (dataList.size() > 0) { //创建一个与excel文件对应的对象 XSSFWorkbook workbook = new XSSFWorkbook(); //创建一张表格 XSSFSheet empFormSheet = workbook.createSheet("员工报表"); //创建首行 XSSFRow headerRow = empFormSheet.createRow(0); //创建首行单元格 String[] headerCell = new String[]{"部门名称", "员工编号", "员工姓名", "实发工资"}; for (int i = 0; i < headerCell.length; i++) { XSSFCell cell = headerRow.createCell(i); cell.setCellValue(headerCell[i]); }
// XSSFCell cell2 = headerRow.createCell(1); // XSSFCell cell3 = headerRow.createCell(2); // XSSFCell cell4 = headerRow.createCell(3); // //存入数据到单元格 // cell2.setCellValue("员工编号"); // cell3.setCellValue("员工姓名"); // cell4.setCellValue("实发工资");
for (int i = 0; i < dataList.size(); i++) { XSSFRow dataRow = empFormSheet.createRow(i + 1);//已经有首行了 EmpForm empForm = dataList.get(i); for (int j = 0; j < 4; j++) { XSSFCell cell = null; switch (j) { case 0: cell = dataRow.createCell(j); cell.setCellValue(empForm.getDeptName()); break; case 1: cell = dataRow.createCell(j); cell.setCellValue(empForm.getEmpId()); break; case 2: cell = dataRow.createCell(j); cell.setCellValue(empForm.getEmpName()); break; case 3: cell = dataRow.createCell(j); cell.setCellValue(empForm.getRealSalary()); break; } } } //保存文档 String filePath = "D:\\export\\export.xlsx"; //定义文档路径 OutputStream os = new FileOutputStream(new File(filePath)); workbook.write(os); os.close();//输入流的关闭 } } } |
创建实体类EmpForm
/** * 员工报表对应的实体类 */ public class EmpForm { private StringProperty deptName; private StringProperty empId; private StringProperty empName; private DoubleProperty realSalary;
public EmpForm(){ deptName=new SimpleStringProperty(""); empId=new SimpleStringProperty(""); empName=new SimpleStringProperty(""); realSalary=new SimpleDoubleProperty(0); }
//双向绑定 public StringProperty deptNameProperty(){ return deptName; } public StringProperty empIdProperty(){ return empId; } public StringProperty empNameProperty(){ return empName; } public DoubleProperty realSalaryProperty(){ return realSalary; }
public String getDeptName() { return deptName.get(); }
public void setDeptName(String deptName) { this.deptName.set(deptName); }
public String getEmpId() { return empId.get(); }
public void setEmpId(String empId) { this.empId.set(empId); }
public String getEmpName() { return empName.get(); }
public void setEmpName(String empName) { this.empName.set(empName); }
public double getRealSalary() { return realSalary.get(); }
public void setRealSalary(double realSalary) { this.realSalary.set(realSalary); }
@Override public String toString() { return "EmpForm{" + "deptName=" + deptName + ", empId=" + empId + ", empName=" + empName + ", realSalary=" + realSalary + '}'; } } |
编写Dao及其实现类
/** * 员工报表访问接口 */ public interface EmpFormDao { /** * 查询所有的员工工资信息,进行统计 * @return */ List<EmpForm> queryAll();
/** * 根据员工姓名去查找员工报表 * @param empName * @return */ List<EmpForm> queryByName(String empName); } |
编写实现类EmpFormDaoImpl
public class EmpFormDaoImpl implements EmpFormDao { @Override public List<EmpForm> queryAll() { String sql = "SELECT emp.emp_id,dept_name,emp_name,fix_salary FROM emp LEFT JOIN dept ON emp.dept_id=dept.dept_id LEFT JOIN fixed_salary ON emp.emp_id=fixed_salary.emp_id"; try { Connection connection = JdbcUtil.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); //创建一个集合,用于存储查询出来的结果 List<EmpForm> empFormList = new ArrayList<>(); while (resultSet.next()) { EmpForm empForm = new EmpForm(); empForm.setEmpId(resultSet.getString("emp_id")); empForm.setEmpName(resultSet.getString("emp_name")); empForm.setDeptName(resultSet.getString("dept_name")); empForm.setRealSalary(resultSet.getDouble("fix_salary")); empFormList.add(empForm); } JdbcUtil.close(connection, statement, resultSet); return empFormList; } catch (SQLException e) { e.printStackTrace(); } return null; }
@Override public List<EmpForm> queryByName(String empName) { String sql = "SELECT emp.emp_id,dept_name,emp_name,fix_salary FROM emp LEFT JOIN dept ON emp.dept_id=dept.dept_id LEFT JOIN fixed_salary ON emp.emp_id=fixed_salary.emp_id where emp_name like'%" + empName + "%'"; //System.out.println(sql); try { Connection connection = JdbcUtil.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql);
//创建一个集合,用于存储查询出来的结果 List<EmpForm> empFormList = new ArrayList<>(); while (resultSet.next()) { EmpForm empForm = new EmpForm(); empForm.setEmpId(resultSet.getString("emp_id")); empForm.setEmpName(resultSet.getString("emp_name")); empForm.setDeptName(resultSet.getString("dept_name")); empForm.setRealSalary(resultSet.getDouble("fix_salary")); empFormList.add(empForm); } JdbcUtil.close(connection, statement, resultSet); if (empFormList.size() != 0) { return empFormList; } } catch (SQLException e) { e.printStackTrace(); } return null; } } |
测试:
- 显示数据,如图所示:
![](https://i-blog.csdnimg.cn/blog_migrate/cd2b8ca9f3cee525234ca9f04e6b28f3.png)
- 通过员工姓名查询员工信息报表
(1)如果输入为空,则会弹出警告,如图所示:
![](https://i-blog.csdnimg.cn/blog_migrate/7ccfb276e2361739b3614b2822fff270.png)
(2)如果输入的员工姓名不存在,则会弹出警告,如图所示:
![](https://i-blog.csdnimg.cn/blog_migrate/a5772ecbc602cdf384527315abc43206.png)
(3)假设输入“王”字,那么员工姓名中含有“王”字的员工报表会查询出来,如图所示:
![](https://i-blog.csdnimg.cn/blog_migrate/34f1749eab2d81da27dc4edec1751754.png)
导出数据到excel中
定义文件路径,没导出数据前,文件为空,如图所示:
![](https://i-blog.csdnimg.cn/blog_migrate/9f352758bf55d7df3c62c7e4bc2fbd41.png)
点击“导出”,在文件夹中会自动生成一个excel文件,里面有我们导出的数据,如图所示:
![](https://i-blog.csdnimg.cn/blog_migrate/ea0d7fc1895b2a565fdfa165744a254f.png)
五、实验结果及分析(遇到的问题与解决)
问题:在Idea中,与数据库数据绑定时,出现表中列名拼写错误错误,无法匹配并绑定。
解决:同步改正Idea与数据库中列名。
![](https://i-blog.csdnimg.cn/blog_migrate/075ce8bcb3eacf41d6a1688779303607.png)
问题:反射异常。
解决:未将libs中的jar包导入到Libraries中。
![](https://i-blog.csdnimg.cn/blog_migrate/ea13015c817ce784b444a9952ec86587.png)
![](https://i-blog.csdnimg.cn/blog_migrate/2ded07b94ade0d2ce01f071eee938aba.png)
问题:fxml.LoadException;
解决:用try-catch()抛出异常即可。
![](https://i-blog.csdnimg.cn/blog_migrate/c0147d6643cc3845d30af064f42a8765.png)
问题:未定义List列表集合;
解决:在实现类中,添加<List>。
![](https://i-blog.csdnimg.cn/blog_migrate/5b5f22ae6e2f83b725c5d5078ec62b14.png)
- 问题:Object未经过初始化;
解决:对Object进行初始化处理。
![](https://i-blog.csdnimg.cn/blog_migrate/9c6636d86987da26898cffb28d716c91.png)
- 问题:Java找不到模块:javafx.controls
解决:百度文件路径尝试使用建议的模式进行编译:javac --module-path $ PATH_TO_FX --add-modules javafx.controls HelloFX.java
![](https://i-blog.csdnimg.cn/blog_migrate/c63457df1273b06467b7d7d1e99a586f.png)
六、实验体会
在这次的学习中,学习并掌握Java开发运行、JavaFX、数据库的链接与应用。从讲解Java、数据库的基础知识,到灵活运用。本次短实习锻炼我们的编程能力,培养我们的开发能力。我们的团队合作能力得到了加强,通过这个项目,我们对Java开发和数据库的联系认知也更加深刻性。同时感谢指导老师和助教老师在学习和生活中对我们的教导和帮助。