使用java和mysql设计一个学生宿舍管理系统

好的，下面是一个简单的学生宿舍管理系统的设计，包含学生信息管理、宿舍信息管理、宿舍分配管理等功能。

1. 数据库设计

学生信息表（student_info）：

| 字段名 | 类型 | 说明 |
| ---------- | ----------- | -------- |
| id | int | 学生ID |
| name | varchar(20) | 学生姓名 |
| gender | varchar(2) | 学生性别 |
| age | int | 学生年龄 |
| phone | varchar(20) | 学生电话 |
| dormitory | varchar(20) | 学生宿舍 |

宿舍信息表（dormitory_info）：

| 字段名 | 类型 | 说明 |
| ------ | ----------- | -------- |
| id | int | 宿舍ID |
| name | varchar(20) | 宿舍名称 |
| type | varchar(20) | 宿舍类型 |
| floor | int | 所在楼层 |
| count | int | 可住人数 |

2. Java代码实现

首先需要建立数据库连接，可以使用JDBC或者ORM框架如MyBatis等。

（1）学生信息管理

添加学生信息：

public boolean addStudent(Student student) {
    String sql = "INSERT INTO student_info (id, name, gender, age, phone, dormitory) VALUES (?, ?, ?, ?, ?, ?)";
    Connection conn = null;
    PreparedStatement psmt = null;
    try {
        conn = getConnection();
        psmt = conn.prepareStatement(sql);
        psmt.setInt(1, student.getId());
        psmt.setString(2, student.getName());
        psmt.setString(3, student.getGender());
        psmt.setInt(4, student.getAge());
        psmt.setString(5, student.getPhone());
        psmt.setString(6, student.getDormitory());
        int result = psmt.executeUpdate();
        if (result > 0) {
            return true;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeConnection(conn, psmt, null);
    }
    return false;
}

修改学生信息：

public boolean updateStudent(Student student) {
    String sql = "UPDATE student_info SET name = ?, gender = ?, age = ?, phone = ?, dormitory = ? WHERE id = ?";
    Connection conn = null;
    PreparedStatement psmt = null;
    try {
        conn = getConnection();
        psmt = conn.prepareStatement(sql);
        psmt.setString(1, student.getName());
        psmt.setString(2, student.getGender());
        psmt.setInt(3, student.getAge());
        psmt.setString(4, student.getPhone());
        psmt.setString(5, student.getDormitory());
        psmt.setInt(6, student.getId());
        int result = psmt.executeUpdate();
        if (result > 0) {
            return true;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeConnection(conn, psmt, null);
    }
    return false;
}

删除学生信息：

public boolean deleteStudent(int id) {
    String sql = "DELETE FROM student_info WHERE id = ?";
    Connection conn = null;
    PreparedStatement psmt = null;
    try {
        conn = getConnection();
        psmt = conn.prepareStatement(sql);
        psmt.setInt(1, id);
        int result = psmt.executeUpdate();
        if (result > 0) {
            return true;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeConnection(conn, psmt, null);
    }
    return false;
}

查询学生信息：

public List<Student> getStudentList() {
    String sql = "SELECT * FROM student_info";
    Connection conn = null;
    PreparedStatement psmt = null;
    ResultSet rs = null;
    List<Student> list = new ArrayList<Student>();
    try {
        conn = getConnection();
        psmt = conn.prepareStatement(sql);
        rs = psmt.executeQuery();
        while (rs.next()) {
            Student student = new Student();
            student.setId(rs.getInt("id"));
            student.setName(rs.getString("name"));
            student.setGender(rs.getString("gender"));
            student.setAge(rs.getInt("age"));
            student.setPhone(rs.getString("phone"));
            student.setDormitory(rs.getString("dormitory"));
            list.add(student);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeConnection(conn, psmt, rs);
    }
    return list;
}

（2）宿舍信息管理

添加宿舍信息：

public boolean addDormitory(Dormitory dormitory) {
    String sql = "INSERT INTO dormitory_info (id, name, type, floor, count) VALUES (?, ?, ?, ?, ?)";
    Connection conn = null;
    PreparedStatement psmt = null;
    try {
        conn = getConnection();
        psmt = conn.prepareStatement(sql);
        psmt.setInt(1, dormitory.getId());
        psmt.setString(2, dormitory.getName());
        psmt.setString(3, dormitory.getType());
        psmt.setInt(4, dormitory.getFloor());
        psmt.setInt(5, dormitory.getCount());
        int result = psmt.executeUpdate();
        if (result > 0) {
            return true;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeConnection(conn, psmt, null);
    }
    return false;
}

修改宿舍信息：

public boolean updateDormitory(Dormitory dormitory) {
    String sql = "UPDATE dormitory_info SET name = ?, type = ?, floor = ?, count = ? WHERE id = ?";
    Connection conn = null;
    PreparedStatement psmt = null;
    try {
        conn = getConnection();
        psmt = conn.prepareStatement(sql);
        psmt.setString(1, dormitory.getName());
        psmt.setString(2, dormitory.getType());
        psmt.setInt(3, dormitory.getFloor());
        psmt.setInt(4, dormitory.getCount());
        psmt.setInt(5, dormitory.getId());
        int result = psmt.executeUpdate();
        if (result > 0) {
            return true;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeConnection(conn, psmt, null);
    }
    return false;
}

删除宿舍信息：

public boolean deleteDormitory(int id) {
    String sql = "DELETE FROM dormitory_info WHERE id = ?";
    Connection conn = null;
    PreparedStatement psmt = null;
    try {
        conn = getConnection();
        psmt = conn.prepareStatement(sql);
        psmt.setInt(1, id);
        int result = psmt.executeUpdate();
        if (result > 0) {
            return true;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeConnection(conn, psmt, null);
    }
    return false;
}

查询宿舍信息：

public List<Dormitory> getDormitoryList() {
    String sql = "SELECT * FROM dormitory_info";
    Connection conn = null;
    PreparedStatement psmt = null;
    ResultSet rs = null;
    List<Dormitory> list = new ArrayList<Dormitory>();
    try {
        conn = getConnection();
        psmt = conn.prepareStatement(sql);
        rs = psmt.executeQuery();
        while (rs.next()) {
            Dormitory dormitory = new Dormitory();
            dormitory.setId(rs.getInt("id"));
            dormitory.setName(rs.getString("name"));
            dormitory.setType(rs.getString("type"));
            dormitory.setFloor(rs.getInt("floor"));
            dormitory.setCount(rs.getInt("count"));
            list.add(dormitory);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeConnection(conn, psmt, rs);
    }
    return list;
}

（3）宿舍分配管理

宿舍分配可以通过修改学生信息的dormitory字段来实现。

public boolean allocateDormitory(int studentId, int dormitoryId) {
    String sql = "UPDATE student_info SET dormitory = ? WHERE id = ?";
    Connection conn = null;
    PreparedStatement psmt = null;
    try {
        conn = getConnection();
        psmt = conn.prepareStatement(sql);
        psmt.setInt(1, dormitoryId);
        psmt.setInt(2, studentId);
        int result = psmt.executeUpdate();
        if (result > 0) {
            return true;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeConnection(conn, psmt, null);
    }
    return false;
}

3. 总结

以上是一个简单的学生宿舍管理系统的设计和实现。需要注意的是，除了上述的基本功能外，还需要考虑一些特殊情况，例如宿舍已满无法分配、学生已有宿舍无法重复分配等等。此外，还可以添加登录、注册、权限管理等功能，以增加系统的安全性和可用性。