Title: human resource management system
1, E-R drawing design
Create database:
CREATE TABLE `person` ( `ID` char(8) NOT NULL, `PASSWD` varchar(16) NOT NULL, `AUTHORITY` varchar(8) NOT NULL, `NAME` varchar(8) NOT NULL, `SEX` varchar(2) NOT NULL, `BIRTHDAY` varchar(20) NOT NULL, `DEPARTMENT` varchar(20) NOT NULL, `JOB` varchar(8) NOT NULL, `EDU_LEVEL` varchar(8) NOT NULL, `SPCIALTY` varchar(8) NOT NULL, `ADDRESS` varchar(8) NOT NULL, `TEL` varchar(11) NOT NULL, `EMAIL` varchar(20) NOT NULL, `STATE` varchar(2) NOT NULL, `REMARK` varchar(8) DEFAULT NULL, PRIMARY KEY (`ID`,`DEPARTMENT`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk ROW_FORMAT=COMPACT; CREATE TABLE `personnel` ( `ID` char(8) NOT NULL, `PERSON` varchar(8) NOT NULL, `CHANGE` varchar(8) NOT NULL, `DISCRIPTION` varchar(8) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=gbk; CREATE TABLE `edu_level` ( `CODE` varchar(2) NOT NULL, `DISCRIPTION` varchar(8) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=gbk; CREATE TABLE `job` ( `CODE` varchar(2) NOT NULL, `DISCRIPTION` varchar(8) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=gbk; CREATE TABLE `personnel_chan` ( `CODE` varchar(2) NOT NULL, `DISCRIPTION` varchar(8) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=gbk; CREATE TABLE `department` ( `ID` char(8) NOT NULL, `NAME` varchar(8) NOT NULL, `MANAGER` varchar(8) NOT NULL, `INTRO` varchar(8) NOT NULL, KEY `ID` (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk; To create a foreign key: alter table person add constraint department_to_id foreign key (department) references department (id); alter table person add constraint job_to_code foreign key (job) references job (code); alter table person add constraint edu_level_to_code foreign key (edu_level) references edu_level (code); alter table personnel add constraint person_to_id foreign key (person) references person (id); alter table personnel add constraint change_to_code foreign key (change)references personnel_chan (code);( X navicat (done)
Realize function
1. Add new employees
The administrator has the highest authority and can modify all functions of employees except password, or add new employees and enter information;
2. Personnel changes
Establish a connection between the personnel change table and the person table, and create a trigger. You can make corresponding modifications when the employee position or status changes to keep pace;
3. Employee information query and modification
Employee information can be modified by administrators or by themselves, and the modified contents complement each other. Administrators can only modify some special information other than basic information, such as position, Department, status, etc;
4. Encryption module (password identification)
The encryption module is realized by database password authentication. There will be a check mechanism in the login interface. Only when the information is correct and consistent (identity, employee number and password correspond to a record in the person table) can you enter the operation interface; Disadvantages: encryption algorithm is not adopted (simple encryption such as XOR encryption algorithm can be adopted), and there is only one inspection mechanism;
5. Employee login
Due to the different permissions of employees and administrators, two different operation interfaces (view mechanism) will be entered when logging in. The administrator has large permissions, and employees can only view their own information and modify some of them (basic information);
6. User rights (administrator, employee) & & Database Security
For example, the fifth point is to use the attempt mechanism to control the operation authority and ensure the security of the database;
7. Password view hidden
Because the password is relatively private, the interface view is hidden. Although the administrator has the highest permission to view and modify the information of each employee, she cannot view the employee password. She can only modify the password when the employee forgets the password and cannot log in, so that the employee can retrieve the account number; Therefore, the employee password is "transparent" to the administrator;
2, Interface and core code analysis
Operation flow chart
- Login interface (login.java, D:\Java \ new folder \ db\src\timg2.jpg)
(1) Functions: Database Security (password identification) & & employee login
(2) The input password box adopts the password text box
(3) Pop up prompt of login failure
(4) Login information (account password identity) is synchronized with the database
(5) Core code (identity and password identification) analysis
public void actionPerformed(ActionEvent e) {
//monitor
//User exit
if(e.getActionCommand().equals("sign out")) {
System.out.println("actionPerformed(). User exit");
this.setVisible(false);
}
//After logging in, jump to the main interface
if(e.getActionCommand().equals("Sign in"))
{
if(jRBEmployee.isSelected()) {//Employee radio button selected
try {
String sql = null;
String res = null;
String id = jTFUserName.getText();//Get employee number
sql = "select PASSWD from person where AUTHORITY ='staff' AND ID = '"+id+"'";
System.out.println("executeQuery(). sql = " + sql);
dbProcess.connect();
ResultSet rs = dbProcess.executeQuery(sql);
while(rs.next()){
res=rs.getString("PASSWD");
}
dbProcess.disconnect();
if(jTFPassword.getText().equals(res))//The password entered is correct
{ //Save account and password
username = jTFUserName.getText();
passwd = jTFPassword.getText();
System.out.println("actionPerformed(). User login succeeded");
new employee_view();
} else {//Password error login failed
new login_failure();
}
}catch(SQLException sqle){
System.out.println("sqle = " + sqle);
JOptionPane.showMessageDialog(null,
"Data operation error","error",JOptionPane.ERROR_MESSAGE);
}
}
else if(jRBManager.isSelected()) {//The administrator box is selected
try {
String sql = null;
String res = null;
String id = jTFUserName.getText();//Get employee number
sql = "select PASSWD from person where AUTHORITY ='administrators' AND ID = '"+id+"'";
System.out.println("executeQuery(). sql = " + sql);
dbProcess.connect();
ResultSet rs = dbProcess.executeQuery(sql);
while(rs.next()){
res=rs.getString("PASSWD");
}
dbProcess.disconnect();
if(jTFPassword.getText().equals(res))//The password entered is correct
{//Save account and password
username = jTFUserName.getText();
passwd = jTFPassword.getText();
System.out.println("actionPerformed(). User login succeeded");
new main_screen();
}else {//Password error login failed
new login_failure();
}
}catch(SQLException sqle){
System.out.println("sqle = " + sqle);
JOptionPane.showMessageDialog(null,
"Data operation error","error",JOptionPane.ERROR_MESSAGE);
}
}//The administrator box is selected
}
}//actionPerformed
- Administrator operation interface (main_screen.java, change_password.java, person_change.java)
Implementation functions: add, delete, change and check employee information (except password), modify password and personnel change
Core code analysis:
public void actionPerformed(ActionEvent e) {
if(e.getActionCommand().equals("Return to the previous level"))
this.setVisible(false);
else
if(e.getActionCommand().equals("query")
&& !jTFQueryField.getText().isEmpty()){
System.out.println("actionPerformed(). query");
String sQueryField = jTFQueryField.getText().trim();
queryProcess(sQueryField);
jTFQueryField.setText("");
}else if(e.getActionCommand().equals("Query all records")) {
System.out.println("actionPerformed(). Query all records");
queryAllProcess();
}else if(e.getActionCommand().equals("insert")
&& !jTFID.getText().isEmpty()
&& !jTFAuthority.getText().isEmpty()
&& !jTFName.getText().isEmpty()
&& !jTFSex.getText().isEmpty()
&& !jTFBirthday.getText().isEmpty()
&& !jTFDepartment.getText().isEmpty()
&& !jTFJob.getText().isEmpty()
&& !jTFEdu_Level.getText().isEmpty()
&& !jTFSpcialty.getText().isEmpty()
&& !jTFAddress.getText().isEmpty()
&& !jTFTEL.getText().isEmpty()
&& !jTFEmail.getText().isEmpty()
&& !jTFState.getText().isEmpty()
&& !jTFRemark .getText().isEmpty()
){
System.out.println("actionPerformed(). insert");
insertProcess();
}else if(e.getActionCommand().equals("to update")
&& !jTFID.getText().isEmpty()
&& !jTFAuthority.getText().isEmpty()
&& !jTFName.getText().isEmpty()
&& !jTFSex.getText().isEmpty()
&& !jTFBirthday.getText().isEmpty()
&& !jTFDepartment.getText().isEmpty()
&& !jTFJob.getText().isEmpty()
&& !jTFEdu_Level.getText().isEmpty()
&& !jTFSpcialty.getText().isEmpty()
&& !jTFAddress.getText().isEmpty()
&& !jTFTEL.getText().isEmpty()
&& !jTFEmail.getText().isEmpty()
&& !jTFState.getText().isEmpty()
&& !jTFRemark .getText().isEmpty()
){
System.out.println("actionPerformed(). to update");
updateProcess();
}else if(e.getActionCommand().equals("Delete current record")){
System.out.println("actionPerformed(). Delete current record");
deleteCurrentRecordProcess();
}else if(e.getActionCommand().equals("View personnel changes")) {
new person_change();
}else if(e.getActionCommand().equals("Change Password")) {
new change_password();
}
}
public void queryProcess(String sQueryField)//The query function has been roughly understood
{
try{
// Establish query criteria
String sql = "select * from person where ";
String queryFieldStr = jCBSelectQueryFieldTransfer(SelectQueryFieldStr);//Translation query basis, such as "employee number" translated into "ID"
sql = sql + queryFieldStr;
sql = sql + " = ";
sql = sql + "'" + sQueryField + "';";
System.out.println("queryProcess(). sql = " + sql);
dbProcess.connect();
ResultSet rs = dbProcess.executeQuery(sql);
// Convert the record data obtained from the query into a data form suitable for generating JTable
EmployeeVector.clear();//Clear original data
while(rs.next()){
Vector v = new Vector();
v.add(rs.getString("ID"));
v.add(rs.getString("AUTHORITY"));
v.add(rs.getString("NAME"));
v.add(rs.getString("SEX"));
v.add(rs.getString("BIRTHDAY"));
v.add(rs.getString("DEPARTMENT"));
v.add(rs.getString("JOB"));
v.add(rs.getString("EDU_LEVEL"));
v.add(rs.getString("SPCIALTY"));
v.add(rs.getString("ADDRESS"));
v.add(rs.getString("TEL"));
v.add(rs.getString("EMAIL"));
v.add(rs.getString("STATE"));
v.add(rs.getString("REMARK"));
EmployeeVector.add(v);
}
EmployeeJTable.updateUI();
dbProcess.disconnect();
}catch(SQLException sqle){
System.out.println("sqle = " + sqle);
JOptionPane.showMessageDialog(null,
"Data operation error","error",JOptionPane.ERROR_MESSAGE);
}catch(Exception e){
System.out.println("e = " + e);
JOptionPane.showMessageDialog(null,
"Data operation error","error",JOptionPane.ERROR_MESSAGE);
}
}
public void queryAllProcess()
{
try{
// Establish query criteria
String sql = "select * from person;";
System.out.println("queryAllProcess(). sql = " + sql);
dbProcess.connect();
ResultSet rs = dbProcess.executeQuery(sql);
// Convert the record data obtained from the query into a data form suitable for generating JTable
EmployeeVector.clear();
while(rs.next()){
Vector v = new Vector();
v.add(rs.getString("ID"));
v.add(rs.getString("AUTHORITY"));
v.add(rs.getString("NAME"));
v.add(rs.getString("SEX"));
v.add(rs.getString("BIRTHDAY"));
v.add(rs.getString("DEPARTMENT"));
v.add(rs.getString("JOB"));
v.add(rs.getString("EDU_LEVEL"));
v.add(rs.getString("SPCIALTY"));
v.add(rs.getString("ADDRESS"));
v.add(rs.getString("TEL"));
v.add(rs.getString("EMAIL"));
v.add(rs.getString("STATE"));
v.add(rs.getString("REMARK"));
EmployeeVector.add(v);
}
EmployeeJTable.updateUI();
dbProcess.disconnect();
}catch(SQLException sqle){
System.out.println("sqle = " + sqle);
JOptionPane.showMessageDialog(null,
"Data operation error","error",JOptionPane.ERROR_MESSAGE);
}
}
public void insertProcess()
{
String ID = jTFID.getText().trim();
String AUTHORITY = jTFAuthority.getText().trim();
String NAME = jTFName.getText().trim();
String SEX = jTFSex.getText().trim();
String BIRTHDAY = jTFBirthday.getText().trim();
String DEPARTMENT = jTFDepartment.getText().trim();
String JOB = jTFJob.getText().trim();
String EDU_LEVEL =jTFEdu_Level.getText().trim();
String SPCIALTY = jTFSpcialty.getText().trim();
String ADDRESS = jTFAddress.getText().trim();
String TEL = jTFTEL.getText().trim();
String EMAIL = jTFEmail.getText().trim();
String STATE = jTFState.getText().trim();
String REMARK = jTFRemark.getText().trim();
// Establish insertion conditions
String sql = "insert into person values('";
sql = sql + ID + "','";
sql = sql + ID + "','";//The initialization password is the same as the employee number
sql = sql + AUTHORITY + "','";
sql = sql + NAME + "','";
sql = sql + SEX + "','";
sql = sql + BIRTHDAY + "','";
sql = sql + DEPARTMENT + "','";
sql = sql + JOB + "','";
sql = sql + EDU_LEVEL + "','";
sql = sql + SPCIALTY + "','";
sql = sql + ADDRESS + "','";
sql = sql + TEL + "','";
sql = sql + EMAIL + "','";
sql = sql + STATE + "','";
sql = sql + REMARK + "');";
System.out.println("insertProcess(). sql = " + sql);
try{
if (dbProcess.executeUpdate(sql) < 1) {
System.out.println("insertProcess(). insert database failed.");
}
}catch(Exception e){
System.out.println("e = " + e);
JOptionPane.showMessageDialog(null,
"Data operation error","error",JOptionPane.ERROR_MESSAGE);
}
queryAllProcess();
}
public void updateProcess()
{
String ID = jTFID.getText().trim();
String AUTHORITY = jTFAuthority.getText().trim();
String NAME = jTFName.getText().trim();
String SEX = jTFSex.getText().trim();
String BIRTHDAY = jTFBirthday.getText().trim();
String DEPARTMENT = jTFDepartment.getText().trim();
String JOB = jTFJob.getText().trim();
String EDU_LEVEL =jTFEdu_Level.getText().trim();
String SPCIALTY = jTFSpcialty.getText().trim();
String ADDRESS = jTFAddress.getText().trim();
String TEL = jTFTEL.getText().trim();
String EMAIL = jTFEmail.getText().trim();
String STATE = jTFState.getText().trim();
String REMARK = jTFRemark.getText().trim();
// Establish update conditions
String sql = "update person set AUTHORITY = '";
sql = sql + AUTHORITY + "', NAME = '";
sql = sql + NAME + "', SEX = '";
sql = sql + SEX + "', BIRTHDAY = '";
sql = sql + BIRTHDAY + "', DEPARTMENT = '";
sql = sql + DEPARTMENT + "', JOB = '";
sql = sql + JOB + "', EDU_LEVEL = '";
sql = sql + EDU_LEVEL + "', SPCIALTY = '";
sql = sql + SPCIALTY + "', ADDRESS = '";
sql = sql + ADDRESS + "', TEL = '";
sql = sql + TEL + "', EMAIL = '";
sql = sql + EMAIL + "', STATE = '";
sql = sql + STATE + "', REMARK = '";
sql = sql + REMARK + "'";
sql = sql + " WHERE ID = '" + ID + "';";
System.out.println("updateProcess(). sql = " + sql);
try{
if (dbProcess.executeUpdate(sql) < 1) {
System.out.println("updateProcess(). update database failed.");
}
}catch(Exception e){
System.out.println("e = " + e);
JOptionPane.showMessageDialog(null,
"Data operation error","error",JOptionPane.ERROR_MESSAGE);
}
queryAllProcess();
}
public void deleteCurrentRecordProcess()
{
String ID = jTFID.getText().trim();
// Create delete condition
String sql = "delete from person where ID = '" + ID + "';";
System.out.println("deleteCurrentRecordProcess(). sql = " + sql);
try{
if (dbProcess.executeUpdate(sql) < 1) {
System.out.println("deleteCurrentRecordProcess(). delete database failed.");
}
}catch(Exception e){
System.out.println("e = " + e);
JOptionPane.showMessageDialog(null,
"Data operation error","error",JOptionPane.ERROR_MESSAGE);
}
queryAllProcess();
}
public String jCBSelectQueryFieldTransfer(String InputStr)
{//Translation query basis, such as "employee number" = "ID"
String outputStr = "";
System.out.println("jCBSelectQueryFieldTransfer(). InputStr = " + InputStr);
if(InputStr.equals("Employee number")){
outputStr = "ID";
}else if(InputStr.equals("User rights")){
outputStr = "AUTHORITY";
}else if(InputStr.equals("full name")){
outputStr = "NAME";
}else if(InputStr.equals("Gender")){
outputStr = "SEX";
}else if(InputStr.equals("birthday")){
outputStr = "BIRTHDAY";
}else if(InputStr.equals("Department")){
outputStr = "DEPARTMENT";
}else if(InputStr.equals("post")){
outputStr = "JOB";
}else if(InputStr.equals("Education level")){
outputStr = "EDU_LEVEL";
}else if(InputStr.equals("Professional skills")){
outputStr = "SPCIALTY";
}else if(InputStr.equals("Home address")){
outputStr = "ADDRESS";
}else if(InputStr.equals("contact number")){
outputStr = "TEL";
}else if(InputStr.equals("E-mail")){
outputStr = "EMAIL";
}else if(InputStr.equals("current state")){
outputStr = "STATE";
}else if(InputStr.equals("remarks")){
outputStr = "REMARK";
}
System.out.println("jCBSelectQueryFieldTransfer(). outputStr = " + outputStr);
return outputStr;
}
- Employee operation interface (employee.java)
Implementation function: view all personal information and partially update personal information (permission control)
Core code analysis:
public void actionPerformed(ActionEvent e) {
if(e.getActionCommand().equals("confirm")
&&!jTFNewPassword.getText().isEmpty()
&&!jTFNewPasswordAgain.getText().isEmpty()) {
System.out.println("actionPerformed(). to update");
updateProcess();
new change_succeed();
}
}
public void updateProcess() {
String passwd = jTFNewPassword.getText();
String sql = "update person set PASSWD = '";
sql = sql + passwd ;
sql = sql + "' where ID = '";
sql = sql + login.username +"'" ;
System.out.println("updateProcess(). sql = " + sql);
try{
if (dbProcess.executeUpdate(sql) < 1) {
System.out.println("updateProcess(). update database failed.");
}
}catch(Exception e){
System.out.println("e = " + e);
JOptionPane.showMessageDialog(null,
"Data operation error","error",JOptionPane.ERROR_MESSAGE);
}
}
Summary and experience
1, Difficulties encountered:
1. Login verification password √
2. Database multi table connection query operation √
3. User permission setting (administrator and employee) √
4. When accessing another page, you need to obtain the user's account and password again √
Solution: let the user enter the account and password again
Evaluation: the overall security performance of the database is guaranteed, so that different users (administrators and employees) have different permissions
Disadvantages: it is extremely inconvenient to consider from the perspective of users;
Improvement scheme: set two static strings username and passwd on the login page to obtain the user account and password respectively, and call it when needed in another interface;
4. Since the user's account number is the main code of the table and cannot be changed, when the user changes the password, the query information only needs to be checked according to the account ID. at this time, if the account number and password are used as the query criteria at the same time, an error will occur;
5. The process of experiencing the function of the program interface is also the process of finding bugs. The function is not perfect, and bugs fly all over the sky;
6. Control the user's update permission by whether the text box can be edited, as shown in the following figure
2, Basis of development:
1. Have java (GUI and JDBC) and database operation language sql Foundation
2. Understand and imitate the source code given by the teacher and learn to use it; You don't need to know the principle of each method. It's enough to be able to use it. After all, it's equivalent to self-study, and the requirements won't be too high.
3, Development software: eclipse, navicat for mysql
4, Summary and experience:
At the beginning of this database course design, it was difficult for me to start. Looking at the 500 lines of source code given by the teacher, I was stunned. Because the java class didn't talk about the GUI and JDBC chapters, I thought the course design was so difficult that I suspected I couldn't do it. I spent half a day reading the code, dizzy, and then began to chew books. I just started to read the JDBC chapter carefully, Later, after mastering the doorway, I knocked on the page with a little understanding. The source code given by the teacher was neat and consistent, which was a bit of a feeling of modular design. I absorbed this and used it on my own page. As expected, it was much more labor-saving and convenient to write, and the idea was clear;
After I learned how to make pages, I began to face the more difficult core code layer. From not understanding at the beginning to communicating with my classmates, it became clear that I was slowly overcoming this big BOSS, but I was still a little insufficient in the whole process and made a lot of low-level mistakes (for example, the difficulty encountered above 4, I forgot the magic of static keyword), It should be because I haven't consolidated java for a long time.
Watching myself grow up slowly in the process of setting up this course, I feel that I have spent the right time, mastered a lot of knowledge that I can't learn in theory courses, and made something. It's really good and I feel a sense of achievement!