package net.tc.student.dao.impl; import net.tc.student.bean.Student; import net.tc.student.dao.StudentDao; import net.tc.student.dbutil.ConnectionManager; import java.sql.*; import java.util.ArrayList; import java.util.List; import java.util.Vector; public class StudentDaoImpl implements StudentDao { // 插入学生记录 @Override public int insert(Student student) { // 定义插入记录数 int count = 0; // 1. 获得数据库连接 Connection conn = ConnectionManager.getConnection(); // 2. 定义SQL字符串 String strSQL = "insert into t_student (id, name, sex, age, department, class, telephone)" + " values (?, ?, ?, ?, ?, ?, ?)"; try { // 3. 创建预备语句对象 PreparedStatement pstmt = conn.prepareStatement(strSQL); // 4. 设置占位符的值 pstmt.setString(1, student.getId()); pstmt.setString(2, student.getName()); pstmt.setString(3, student.getSex()); pstmt.setInt(4, student.getAge()); pstmt.setString(5, student.getDepartment()); pstmt.setString(6, student.getClazz()); pstmt.setString(7, student.getTelephone()); // 5. 执行SQL,返回插入记录数 count = pstmt.executeUpdate(); // 6. 关闭预备语句对象 pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭数据库连接 ConnectionManager.closeConnection(conn); } // 返回插入记录数 return count; } // 按学号删除学生记录 @Override public int deleteById(String id) { // 定义删除记录数 int count = 0; // 1. 获取数据库连接 Connection conn = ConnectionManager.getConnection(); // 2. 定义SQL字符串 String strSQL = "delete from t_student where id = ?";try { // 3. 创建预备语句对象 PreparedStatement pstmt = conn.prepareStatement(strSQL); // 4. 设置占位符的值 pstmt.setString(1, id); // 5. 执行SQL,返回删除记录数 count = pstmt.executeUpdate(); // 6. 关闭预备语句对象 pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭数据库连接 ConnectionManager.closeConnection(conn); } // 返回删除记录数 return count; } // 按班级删除学生记录 @Override public int deleteByClass(String clazz) { // 定义删除记录数 int count = 0; // 1. 获取数据库连接 Connection conn = ConnectionManager.getConnection(); // 2. 定义SQL字符串 String strSQL = "delete from t_student where class = ?"; try { // 3. 创建预备语句对象 PreparedStatement pstmt = conn.prepareStatement(strSQL); // 4. 设置占位符的值 pstmt.setString(1, clazz); // 5. 执行SQL,返回删除记录数 count = pstmt.executeUpdate(); // 6. 关闭预备语句对象 pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭数据库连接 ConnectionManager.closeConnection(conn); } // 返回删除记录数 return 0; } // 按系部删除学生记录 @Override public int deleteByDepartment(String department) { // 定义删除记录数 int count = 0; // 1. 获得数据库连接 Connection conn = ConnectionManager.getConnection(); // 2. 定义SQL字符串 String strSQL = "delete from t_student where department = ?"; try { // 3. 创建预备语句对象 PreparedStatement pstmt = conn.prepareStatement(strSQL); // 4. 设置占位符的值 pstmt.setString(1, department); // 5. 执行SQL,返回删除记录数 count = pstmt.executeUpdate(); // 6. 关闭预备语句对象 pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭数据库连接 ConnectionManager.closeConnection(conn); } // 返回删除记录数 return count; } // 更新学生记录 @Override public int update(Student student) { // 定义更新记录数 int count = 0; // 1. 获得数据库连接 Connection conn = ConnectionManager.getConnection(); // 2. 定义SQL字符串 String strSQL = "update t_student set name = ?, sex = ?, age = ?," + " department = ?, class = ?, telephone = ? where id = ?"; try { // 3. 创建预备语句对象 PreparedStatement pstmt = conn.prepareStatement(strSQL); // 4. 设置占位符的值 pstmt.setString(1, student.getName()); pstmt.setString(2, student.getSex()); pstmt.setInt(3, student.getAge()); pstmt.setString(4, student.getDepartment()); pstmt.setString(5, student.getClazz()); pstmt.setString(6, student.getTelephone()); pstmt.setString(7, student.getId()); // 5. 执行SQL,返回更新记录数 count = pstmt.executeUpdate(); // 6. 关闭预备语句对象 pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭数据库连接 ConnectionManager.closeConnection(conn); } // 返回更新记录 return count; } // 按照学号查找学生记录 @Override public Student findById(String id) { // 声明学生对象 Student student = null; // 1. 获取数据库连接对象 Connection conn = ConnectionManager.getConnection(); // 2. 定义SQL字符串 String strSQL = "select * from t_student where id = ?"; try { // 3. 创建预备语句对象 PreparedStatement pstmt = conn.prepareStatement(strSQL); // 4. 设置占位符的值 pstmt.setString(1, id); // 5. 执行SQL,返回结果集 ResultSet rs = pstmt.executeQuery(); // 6. 判断结果集是否有记录 if (rs.next()) { // 创建学生实体 student = new Student(); // 利用当前记录各字段值设置学生实体属性 student.setId(rs.getString("id")); student.setName(rs.getString("name")); student.setSex(rs.getString("sex")); student.setAge(rs.getInt("age")); student.setDepartment(rs.getString("department")); student.setClazz(rs.getString("class")); student.setTelephone(rs.getString("telephone")); } } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭数据库连接 ConnectionManager.closeConnection(conn); } // 返回学生对象 return student; } // 按照姓名查询学生记录 @Override public List<Student> findByName(String name) { // 声明学生列表 List<Student> students = new ArrayList<Student>(); // 1. 获取数据库连接对象 Connection conn = ConnectionManager.getConnection(); // 2. 定义SQL字符串 String strSQL = "select * from t_student where name like ?"; try { // 3. 创建预备语句对象 PreparedStatement pstmt = conn.prepareStatement(strSQL); // 4. 设置占位符的值 pstmt.setString(1, name + "%"); // 5. 执行SQL,返回结果集 ResultSet rs = pstmt.executeQuery(); // 6. 遍历结果集 while (rs.next()) { // 创建学生实体 Student student = new Student(); // 利用当前记录各字段值设置学生实体属性 student.setId(rs.getString("id")); student.setName(rs.getString("name")); student.setSex(rs.getString("sex")); student.setAge(rs.getInt("age")); student.setDepartment(rs.getString("department")); student.setClazz(rs.getString("class")); student.setTelephone(rs.getString("telephone")); // 将实体添加到学生列表 students.add(student); } // 7. 关闭结果集 rs.close(); // 8. 关闭预备语句对象 pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭数据库连接 ConnectionManager.closeConnection(conn); } // 返回学生列表 return students; } // 按照班级查询学生记录 @Override public List<Student> findByClass(String clazz) { // 声明学生列表 List<Student> students = new ArrayList<Student>(); // 1. 获取数据库连接对象 Connection conn = ConnectionManager.getConnection(); // 2. 定义SQL字符串 String strSQL = "select * from t_student where class like ?"; try { // 3. 创建预备语句对象 PreparedStatement pstmt = conn.prepareStatement(strSQL); // 4. 设置占位符的值 pstmt.setString(1, clazz + "%"); // 5. 执行SQL,返回结果集 ResultSet rs = pstmt.executeQuery(); // 6. 遍历结果集 while (rs.next()) { // 创建学生实体 Student student = new Student(); // 利用当前记录各字段值设置学生实体属性 student.setId(rs.getString("id")); student.setName(rs.getString("name")); student.setSex(rs.getString("sex")); student.setAge(rs.getInt("age")); student.setDepartment(rs.getString("department")); student.setClazz(rs.getString("class")); student.setTelephone(rs.getString("telephone")); // 将实体添加到学生列表 students.add(student); } // 7. 关闭结果集 rs.close(); // 8. 关闭预备语句对象 pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭数据库连接 ConnectionManager.closeConnection(conn); } // 返回学生列表 return students; } // 按照系查询学生记录 @Override public List<Student> findByDepartment(String department) { // 声明学生列表 List<Student> students = new ArrayList<Student>(); // 1. 获取数据库连接对象 Connection conn = ConnectionManager.getConnection(); // 2. 定义SQL字符串 String strSQL = "select * from t_student where department like ?"; try { // 3. 创建预备语句对象 PreparedStatement pstmt = conn.prepareStatement(strSQL); // 4. 设置占位符的值 pstmt.setString(1, department + "%"); // 5. 执行SQL,返回结果集 ResultSet rs = pstmt.executeQuery(); // 6. 遍历结果集 while (rs.next()) { // 创建学生实体 Student student = new Student(); // 利用当前记录各字段值设置学生实体属性 student.setId(rs.getString("id")); student.setName(rs.getString("name")); student.setSex(rs.getString("sex")); student.setAge(rs.getInt("age")); student.setDepartment(rs.getString("department")); student.setClazz(rs.getString("class")); student.setTelephone(rs.getString("telephone")); // 将实体添加到学生列表 students.add(student); } // 7. 关闭结果集 rs.close(); // 8. 关闭预备语句对象 pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭数据库连接 ConnectionManager.closeConnection(conn); } // 返回学生列表 return students; } // 查找全部学生记录 @Override public List<Student> findAll() { // 声明学生列表 List<Student> students = new ArrayList<Student>(); // 1. 获取数据库连接对象 Connection conn = ConnectionManager.getConnection(); // 2. 定义SQL字符串 String strSQL = "select * from t_student"; try { // 3. 创建语句对象 Statement stmt = conn.createStatement(); // 4. 执行SQL,返回结果集 ResultSet rs = stmt.executeQuery(strSQL); // 5. 遍历结果集 while (rs.next()) { // 创建学生实体 Student student = new Student(); // 利用当前记录各字段值设置学生实体属性 student.setId(rs.getString("id")); student.setName(rs.getString("name")); student.setSex(rs.getString("sex")); student.setAge(rs.getInt("age")); student.setDepartment(rs.getString("department")); student.setClazz(rs.getString("class")); student.setTelephone(rs.getString("telephone")); // 将实体添加到学生列表 students.add(student); } // 6. 关闭结果集 rs.close(); // 7. 关闭语句对象 stmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭数据库连接 ConnectionManager.closeConnection(conn); } // 返回学生列表 return students; } // 按照性别统计学生人数 @Override public Vector findRowsBySex() { // 定义行集向量 Vector rows = new Vector(); // 1. 获取数据库连接对象 Connection conn = ConnectionManager.getConnection(); // 2. 定义SQL字符串 String strSQL = "select sex as '性别', count(*) as '人数'" + " from t_student group by sex order by sex desc"; try { // 3. 创建语句对象 Statement stmt = conn.createStatement(); // 4. 执行SQL,返回结果集 ResultSet rs = stmt.executeQuery(strSQL); // 5. 遍历结果集 while (rs.next()) { // 定义当前行向量 Vector<String> currentRow = new Vector(); // 利用当前记录字段值设置当前行向量的元素值 currentRow.addElement(rs.getString("性别")); currentRow.addElement(rs.getInt("人数") + ""); // 将当前行向量添加到行集向量 rows.addElement(currentRow); } } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭数据库连接 ConnectionManager.closeConnection(conn); } // 返回行集向量 return rows; } // 按照班级统计学生人数 @Override public Vector findRowsByClass() { // 定义行集向量 Vector rows = new Vector(); // 1. 获取数据库连接对象 Connection conn = ConnectionManager.getConnection(); // 2. 定义SQL字符串 String strSQL = "select class as '班级', count(*) as '人数'" + " from t_student group by class order by class desc"; try { // 3. 创建语句对象 Statement stmt = conn.createStatement(); // 4. 执行SQL,返回结果集 ResultSet rs = stmt.executeQuery(strSQL); // 5. 遍历结果集 while (rs.next()) { // 定义当前行向量 Vector<String> currentRow = new Vector(); // 利用当前记录字段值设置当前行向量的元素值 currentRow.addElement(rs.getString("班级")); currentRow.addElement(rs.getInt("人数") + ""); // 将当前行向量添加到行集向量 rows.addElement(currentRow); } } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭数据库连接 ConnectionManager.closeConnection(conn); } // 返回行集向量 return rows; } // 按照系部统计学生人数 @Override public Vector findRowsByDepartment() { // 定义行集向量 Vector rows = new Vector(); // 1. 获取数据库连接对象 Connection conn = ConnectionManager.getConnection(); // 2. 定义SQL字符串 String strSQL = "select department as '系部', count(*) as '人数'" + " from t_student group by department order by department desc"; try { // 3. 创建语句对象 Statement stmt = conn.createStatement(); // 4. 执行SQL,返回结果集 ResultSet rs = stmt.executeQuery(strSQL); // 5. 遍历结果集 while (rs.next()) { // 定义当前行向量 Vector<String> currentRow = new Vector(); // 利用当前记录字段值设置当前行向量的元素值 currentRow.addElement(rs.getString("系部")); currentRow.addElement(rs.getInt("人数") + ""); // 将当前行向量添加到行集向量 rows.addElement(currentRow); } } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭数据库连接 ConnectionManager.closeConnection(conn); } // 返回行集向量 return rows; } }
大一实训第三天学生数据访问接口实现类StudentDaoImpl源代码
最新推荐文章于 2023-03-24 18:55:51 发布