学生管理系统(java+mysql)

课程作业使用了基础的jdbc操作数据库。

仓库连接:

https://github.com/SuZui-cn/javaStuday

https://gitee.com/north_gate/javaStuday

下面示例代码

student.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
`sn` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学号',
`sex` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓别: 男, 女',
`source` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学生来源: 老学员推荐,知乎,社招',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (2, '张小二', '1002', '男', '老学员推荐');
INSERT INTO `student` VALUES (3, '张小三', '1003', '男', '知乎');
INSERT INTO `student` VALUES (4, '张小四', '1004', '女', '社招');
INSERT INTO `student` VALUES (5, '张小五', '1005', '女', '社招');
INSERT INTO `student` VALUES (7, '靓仔一号·', '14250', '男', '靓仔学院');

SET FOREIGN_KEY_CHECKS = 1;

dao类,

StuDao.class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
package com.dao;

import com.db.Dbclose;
import com.db.Dbconn;
import com.entity.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
* 对数据库的操作
*
* @author 北落燕门
* @date 2021.05.11
*/
public class StuDao {
//初始化配置
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;

/**
* @显示所有学生数据
*/
public ArrayList<Student> showStuList() {
ArrayList<Student> stuList = new ArrayList<Student>();
conn = Dbconn.getconn();
String sql = "SELECT * FROM student";
try {
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
String sn = rs.getString(3);
String sex = rs.getString(4);
String source = rs.getString(5);
Student student = new Student(id, name, sn, sex, source);
stuList.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
Dbclose.getclose(conn, pstmt, rs);
}
return stuList;
}

/**
* @添加学生
*/
public boolean addStu(Student stu) {
boolean bool = false;
conn = Dbconn.getconn();
String sql = "INSERT INTO student VALUES(?,?,?,?,?)";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, stu.getId());
pstmt.setString(2, stu.getName());
pstmt.setString(3, stu.getSn());
pstmt.setString(4, stu.getSex());
pstmt.setString(5, stu.getSource());
int rs = pstmt.executeUpdate();
if (rs > 0) {
bool = true;
}

} catch (SQLException e) {
e.printStackTrace();
} finally {
Dbclose.getclose(conn, pstmt, rs);
}
return bool;
}

/**
* @通过ID删除学生
*/
public boolean delStu(int id) {
boolean bool = false;
conn = Dbconn.getconn();
String sql = "DELETE FROM student WHERE id=?";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
int rs = pstmt.executeUpdate();
if (rs > 0) {
bool = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
Dbclose.getclose(conn, pstmt, rs);
}
return bool;
}

/**
* @修改学生信息
*/
public boolean updataStu(int id,String name, String sn, String sex, String source) {
boolean bool = false;
conn = Dbconn.getconn();
String sql = "UPDATE student SET name=?,sn=?,sex=?,source=? WHERE id=?";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setString(2, sn);
pstmt.setString(3, sex);
pstmt.setString(4, source);
pstmt.setInt(5,id);
int rs = pstmt.executeUpdate();
if (rs > 0) {
bool = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
Dbclose.getclose(conn, pstmt, rs);
}
return bool;
}

/**
* @通过ID查找学生数据
*/
public void findStu(int id) {
conn = Dbconn.getconn();
String sql = "SELECT * FROM student WHERE id = ?";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
rs = pstmt.executeQuery();
if (!rs.next()){
System.out.println("查无此人....");
}else{
String name = rs.getString(2);
String sn = rs.getString(3);
String sex = rs.getString(4);
String source = rs.getString(5);
System.out.println("id:" + id + "," + "name:" + name + "," + "sn:" + sn + "," + "sex:" + sex + "," + "source:" + source);
}

} catch (SQLException e) {
e.printStackTrace();
} finally {
Dbclose.getclose(conn, pstmt, rs);
}
}
}

db类

Dbconn.class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
package com.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
* 连接数据库
* @author 北落燕门
* @date 2021.05.11
*/
public final class Dbconn {
public static Connection getconn(){
Connection conn = null;
String Driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test4?UseUnicode=True&characterEncoding=UTF-8";
String user = "root";
String password = "123456";
try{
Class.forName(Driver);
conn = DriverManager.getConnection(url,user,password);
}catch (SQLException | ClassNotFoundException e){
e.printStackTrace();
}
return conn;
}
}

Dbclose.class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
package com.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
* 关闭连接
* @author 北落燕门
* @date 2021.05.11
*/
public final class Dbclose {
public static void getclose(Connection conn, PreparedStatement pstmt, ResultSet rs){
try{
if (pstmt!=null){
pstmt.close();
}
}catch (SQLException e){
e.printStackTrace();
}
try{
if (rs!=null){
rs.close();
}
}catch (SQLException e){
e.printStackTrace();
}
try {
if (conn!=null){
conn.close();
}
}catch (SQLException e){
e.printStackTrace();
}
}
}

entity类

Student.class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
package com.entity;

/**
* @author 北落燕门
* @date 2021.05.11
*/
public class Student {
private int id;
private String name;
private String sn;
private String sex;
private String source;

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public String getSn() {
return sn;
}

public void setSn(String sn) {
this.sn = sn;
}

public String getSex() {
return sex;
}

public void setSex(String sex) {
this.sex = sex;
}

public String getSource() {
return source;
}

public void setSource(String source) {
this.source = source;
}

public Student(int id, String name, String sn, String sex, String source) {
this.id = id;
this.name = name;
this.sn = sn;
this.sex = sex;
this.source = source;
}

public Student() {
}
}

tool类

Tool.class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
package com.tool;

import com.dao.StuDao;
import com.entity.Student;

import java.util.ArrayList;
import java.util.Scanner;

/**
* @author 北落燕门
* @date 2021.05.12
*/
public class Tool {
Scanner sc = new Scanner(System.in);

/**
* @显示菜单
*/
public void menu(){
System.out.println("欢迎使用学生管理系统(MySql版)");
System.out.println("--------1、查看所有学生-------");
System.out.println("--------2、添加学生-----------");
System.out.println("--------3、删除学生-----------");
System.out.println("--------4、修改学生-----------");
System.out.println("--------5、查询学生-----------");
System.out.println("--------0、退出系统-----------");
System.out.println("请输入您的选择:");
}
/**
* @遍历ArrayList输出数据
*/
public void showArray() {
ArrayList<Student> students = new StuDao().showStuList();
if (students.size() <= 0) {
System.out.println("数据库为空...");
} else {
for (Student stu : students) {
System.out.println("id:" + stu.getId() + "," + "name:" + stu.getName() + "," +
"sn:" + stu.getSn() + "," + "sex:" + stu.getSex() + ","
+ "source:" + stu.getSource());
}
}
System.out.println("按任意键返回上一界面");
String i = sc.nextLine();
}

/**
* @添加学生,调用dao层
*/
public void add(){
System.out.println("请输入学生的编号:");
int id = sc.nextInt();
String test = sc.nextLine();
System.out.println("请输入学生的姓名:");
String name = sc.nextLine();
System.out.println("请输入学生的学号:");
String sn = sc.nextLine();
System.out.println("请输入学生的性别:");
String sex = sc.nextLine();
System.out.println("请输入学生的生源:");
String source = sc.nextLine();
Student stu = new Student(id,name,sn,sex,source);
boolean flag = new StuDao().addStu(stu);
if (flag){
System.out.println("添加成功");
}else {
System.out.println("添加失败...");
}
System.out.println("按1继续添加,按其他任意键返回上一界面");
String i = sc.nextLine();
if (i.equals("1")){
add();
}
}
/**
* @删除学生,调用dao层
*/
public void delStu(){
System.out.print("请输入需要删除的学生的编号:");
int id = sc.nextInt();
boolean flag = new StuDao().delStu(id);
if (flag){
System.out.println("删除成功");
}else {
System.out.println("删除失败...");
}
}

/**
* 修改学生,调用dao层
*/
public void editStu(){
System.out.print("请输入需要编辑的学生编号:");
int id = sc.nextInt();
String test = sc.nextLine();
System.out.println("请输入学生的姓名:");
String name = sc.nextLine();
System.out.println("请输入学生的学号:");
String sn = sc.nextLine();
System.out.println("请输入学生的性别:");
String sex = sc.nextLine();
System.out.println("请输入学生的生源:");
String source = sc.nextLine();
boolean flag = new StuDao().updataStu(id,name,sn,sex,source);
if (flag){
System.out.println("编辑成功");
}else {
System.out.println("编辑失败...");
}
System.out.println("按1继续编辑,按其他任意返回上一界面");
String i = sc.nextLine();
if (i.equals("1")){
editStu();
}
}

/**
* 查找学生,调用dao层
*/
public void findStu(){
System.out.print("请输入需要查找的学生编号:");
int id = sc.nextInt();
new StuDao().findStu(id);
System.out.println("查询结束");
String i = sc.nextLine();
if (i.equals("1")){
findStu();
}
}
}

main类

main.class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
package com.page;

import com.tool.Tool;

import java.util.Scanner;


/**
* @author 北落燕门
* @date 2021.05.12
*/
public class Main {
public static void main(String[] args) {
Tool tool = new Tool();
Scanner sc = new Scanner(System.in);

while (true){
tool.menu();
String i = sc.nextLine();
switch (i){
case "0":
System.out.println("谢谢使用,退出系统...");
System.exit(0);
break;
case "1":
tool.showArray();
break;
case "2":
tool.add();
break;
case "3":
tool.delStu();
break;
case "4":
tool.editStu();
break;
case "5":
tool.findStu();
break;
default:
System.out.println("错误输入,请重新输入...");
break;
}
}
}
}
  • Copyright: Copyright is owned by the author. For commercial reprints, please contact the author for authorization. For non-commercial reprints, please indicate the source.

扫一扫,分享到微信

微信分享二维码
  • Copyrights © 2021 John Doe
  • Visitors: | Views:

请我喝瓶快乐水吧~