Java与数据库实现(1) 能实现管理员与学生用户的登陆 (2) 管理员能对学生基本情况的录入、修改、删除等基本操作 (3) 管理员能对学生的基本信息进行灵活的查询 (4) 管理员实现学生成绩的录入、修改、删除等基本操作 (5) 能对学生的成绩进行查询的具体代码
时间: 2023-06-25 18:08:13 浏览: 34
以下是Java与数据库实现管理员与学生用户的登陆、基本信息管理、成绩管理和查询的具体代码:
1. 登陆功能:
管理员登陆:
```java
public boolean adminLogin(String username, String password) throws SQLException {
Connection conn = getConnection();
String sql = "select * from admin where username = ? and password = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, password);
ResultSet rs = pstmt.executeQuery();
boolean result = rs.next();
rs.close();
pstmt.close();
conn.close();
return result;
}
学生用户登陆:
```java
public boolean studentLogin(String username, String password) throws SQLException {
Connection conn = getConnection();
String sql = "select * from student where username = ? and password = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, password);
ResultSet rs = pstmt.executeQuery();
boolean result = rs.next();
rs.close();
pstmt.close();
conn.close();
return result;
}
```
2. 基本信息管理:
管理员对学生基本情况的录入:
```java
public boolean addStudent(Student student) throws SQLException {
Connection conn = getConnection();
String sql = "insert into student values(?, ?, ?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, student.getUsername());
pstmt.setString(2, student.getPassword());
pstmt.setString(3, student.getName());
pstmt.setInt(4, student.getAge());
pstmt.setString(5, student.getGender());
pstmt.setString(6, student.getMajor());
int result = pstmt.executeUpdate();
pstmt.close();
conn.close();
return result > 0;
}
管理员对学生基本情况的修改:
```java
public boolean updateStudent(Student student) throws SQLException {
Connection conn = getConnection();
String sql = "update student set name = ?, age = ?, gender = ?, major = ? where username = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, student.getName());
pstmt.setInt(2, student.getAge());
pstmt.setString(3, student.getGender());
pstmt.setString(4, student.getMajor());
pstmt.setString(5, student.getUsername());
int result = pstmt.executeUpdate();
pstmt.close();
conn.close();
return result > 0;
}
管理员对学生基本情况的删除:
```java
public boolean deleteStudent(String username) throws SQLException {
Connection conn = getConnection();
String sql = "delete from student where username = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, username);
int result = pstmt.executeUpdate();
pstmt.close();
conn.close();
return result > 0;
}
```
3. 灵活的查询:
管理员能对学生的基本信息进行灵活的查询:
```java
public List<Student> searchStudent(String keyword) throws SQLException {
Connection conn = getConnection();
String sql = "select * from student where username like ? or name like ? or major like ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "%" + keyword + "%");
pstmt.setString(2, "%" + keyword + "%");
pstmt.setString(3, "%" + keyword + "%");
ResultSet rs = pstmt.executeQuery();
List<Student> list = new ArrayList<>();
while (rs.next()) {
Student student = new Student();
student.setUsername(rs.getString("username"));
student.setPassword(rs.getString("password"));
student.setName(rs.getString("name"));
student.setAge(rs.getInt("age"));
student.setGender(rs.getString("gender"));
student.setMajor(rs.getString("major"));
list.add(student);
}
rs.close();
pstmt.close();
conn.close();
return list;
}
```
4. 成绩管理:
管理员实现学生成绩的录入:
```java
public boolean addScore(Score score) throws SQLException {
Connection conn = getConnection();
String sql = "insert into score values(?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, score.getUsername());
pstmt.setString(2, score.getCourse());
pstmt.setInt(3, score.getScore());
int result = pstmt.executeUpdate();
pstmt.close();
conn.close();
return result > 0;
}
管理员实现学生成绩的修改:
```java
public boolean updateScore(Score score) throws SQLException {
Connection conn = getConnection();
String sql = "update score set score = ? where username = ? and course = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, score.getScore());
pstmt.setString(2, score.getUsername());
pstmt.setString(3, score.getCourse());
int result = pstmt.executeUpdate();
pstmt.close();
conn.close();
return result > 0;
}
管理员实现学生成绩的删除:
```java
public boolean deleteScore(String username, String course) throws SQLException {
Connection conn = getConnection();
String sql = "delete from score where username = ? and course = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, course);
int result = pstmt.executeUpdate();
pstmt.close();
conn.close();
return result > 0;
}
```
5. 成绩查询:
能对学生的成绩进行查询:
```java
public List<Score> searchScore(String username) throws SQLException {
Connection conn = getConnection();
String sql = "select * from score where username = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, username);
ResultSet rs = pstmt.executeQuery();
List<Score> list = new ArrayList<>();
while (rs.next()) {
Score score = new Score();
score.setUsername(rs.getString("username"));
score.setCourse(rs.getString("course"));
score.setScore(rs.getInt("score"));
list.add(score);
}
rs.close();
pstmt.close();
conn.close();
return list;
}
```