java实训（3）

接口功能的完成

各接口具体代码：
CollegeDaoImpl部分


StatusDaoImpl部分
package net.zzq.student.dao.impl;
import net.zzq.student.bean.Status;
import net.zzq.student.dao.StatusDao;
import net.zzq.student.dbutil.ConnectionManager;

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

• 功能：状态数据访问接口实现类

• 作者：郑志强

• 日期：2019年6月28日
  */
  public class StatusDaoImpl implements StatusDao{
  @Override
  public Status findById(int id){
  Status status =null;
  Connection conn =ConnectionManager.getConnection();
  String strSQL=“select * from t_status Where id=?”;
  try {
  PreparedStatement pstmt =conn.prepareStatement(strSQL);
  pstmt.setInt(1,id);
  ResultSet rs =pstmt.executeQuery();
  if (rs.next()){
  status =new Status();
  status.setId(rs.getInt(“id”));
  status.setCollege(rs.getString(“college”));
  status.setVersion(rs.getString(“version”));
  status.setAuthor(rs.getString(“author”));
  status.setTelephone(rs.getString(“telephone”));
  status.setAddress(rs.getString(“address”));
  status.setEmail(rs.getString(“email”));
  }
  pstmt.close();
  rs.close();
  }catch (SQLException e){
  e.printStackTrace();
  }finally {
  ConnectionManager.closeConnection(conn);
  }
  return status;
  }
  @Override
  public int update(Status status){
  int count =0;
  Connection conn =ConnectionManager.getConnection();
  String strSQL=“update t_status set college =?,version=?,author=?,”
  +“telephone =?,address =?,email=? where id= ?”;
  try {
  PreparedStatement pstmt =conn.prepareStatement(strSQL);
  pstmt.setString(1,status.getCollege());
  pstmt.setString(2,status.getVersion());
  pstmt.setString(3,status.getAuthor());
  pstmt.setString(4,status.getTelephone());
  pstmt.setString(5,status.getAddress());
  pstmt.setString(6,status.getEmail());
  pstmt.setInt(7,status.getId());

       count =pstmt.executeUpdate();
       pstmt.close();
   }catch (SQLException e){
       e.printStackTrace();
   }finally {
       ConnectionManager.closeConnection(conn);
   }
   return count;
  

  }
  }
  StudentDaoImpl部分：

package net.zzq.student.dao.impl;

import net.zzq.student.bean.Student;
import net.zzq.student.dao.StudentDao;
import net.zzq.student.dbutil.ConnectionManager;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
/**

• 功能：学生数据访问接口实现类

• 作者：郑志强

• 日期：2019年6月28日
  */
  public class StudentDaoImpl implements StudentDao{
  @Override
  public int insert(Student student){
  int count =0;
  Connection conn =ConnectionManager.getConnection();
  String strSQL=“insert into t_student (id,name,sex,age,department,class,telephone)”
  +“values(?,?,?,?,?,?,?)”;
  try {
  PreparedStatement pstmt =conn.prepareStatement(strSQL);
  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());
  count =pstmt.executeUpdate();
  pstmt.close();
  }catch (SQLException e){
  e.printStackTrace();
  }finally {
  ConnectionManager.closeConnection(conn);
  }
  return count;
  }
  @Override
  public int deleteById(String id){
  int count =0;
  Connection conn =ConnectionManager.getConnection();
  String strSQL=“delete from t_student where id=?”;
  try {
  PreparedStatement pstmt =conn.prepareStatement(strSQL);
  pstmt.setString(1,id);
  count =pstmt.executeUpdate();
  pstmt.close();

   }catch (SQLException e){
       e.printStackTrace();
   }finally {
       ConnectionManager.closeConnection(conn);
   }
   return  count;
  

  }
  @Override
  public int deleteByClass(String clazz) {
  int count = 0;
  Connection conn = ConnectionManager.getConnection();
  String strSQL = “delete from t_student where class = ?”;
  try {
  PreparedStatement pstmt = conn.prepareStatement(strSQL);
  pstmt.setString(1, clazz);
  count = pstmt.executeUpdate();
  pstmt.close();
  } catch (SQLException e) {
  e.printStackTrace();
  } finally {
  ConnectionManager.closeConnection(conn);
  }
  return count;
  }

  @Override
  public int deleteByDepartment(String department){
  int count =0;
  Connection conn =ConnectionManager.getConnection();
  String strSQL=“delete from t_student where department =?”;
  try {
  PreparedStatement pstmt =conn.prepareStatement(strSQL);
  pstmt.setString(1,department);
  count =pstmt.executeUpdate();
  pstmt.close();
  }catch (SQLException e){
  e.printStackTrace();
  }finally {
  ConnectionManager.closeConnection(conn);
  }
  return count;
  }
  @Override
  public int update(Student student){
  int count =0;
  Connection conn =ConnectionManager.getConnection();
  String strSQL=“update t_student set name=?,sex=?,age=?,”
  +“department=?,class=?,telephone=? where id=?”;
  try {
  PreparedStatement pstmt =conn.prepareStatement(strSQL);
  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());
  count =pstmt.executeUpdate();
  pstmt.close();
  }catch (SQLException e){
  e.printStackTrace();
  }finally {
  ConnectionManager.closeConnection(conn);
  }
  return count;

  }
  @Override
  public Student findById(String id){
  Student student=null;
  Connection conn =ConnectionManager.getConnection();
  String strSQL=“select * from t_student where id =?”;
  try {

       PreparedStatement pstmt = conn.prepareStatement(strSQL);
       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 findByName(String name){
  List students =new ArrayList();
  Connection conn = ConnectionManager.getConnection();
  String strSQL = “select * from t_student where name like ?”;
  try {
  PreparedStatement pstmt = conn.prepareStatement(strSQL);
  pstmt.setString(1, name + “%”);
  ResultSet rs = pstmt.executeQuery();
  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);
  }
  rs.close();
  pstmt.close();
  } catch (SQLException e) {
  e.printStackTrace();
  } finally {
  ConnectionManager.closeConnection(conn);
  }
  return students;

  }

  @Override
  public List findByClass(String clazz){
  List students = new ArrayList();
  Connection conn = ConnectionManager.getConnection();
  String strSQL = “select * from t_student where class like ?”;
  try {
  PreparedStatement pstmt = conn.prepareStatement(strSQL);
  pstmt.setString(1, clazz + “%”);
  ResultSet rs = pstmt.executeQuery();
  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);
  }
  rs.close();
  pstmt.close();
  } catch (SQLException e) {
  e.printStackTrace();
  } finally {
  ConnectionManager.closeConnection(conn);
  }
  return students;
  }
  @Override
  public List findByDepartment(String department){
  List students = new ArrayList();
  Connection conn = ConnectionManager.getConnection();
  String strSQL = “select * from t_student where department like ?”;
  try {
  PreparedStatement pstmt = conn.prepareStatement(strSQL);
  pstmt.setString(1, department + “%”);
  ResultSet rs = pstmt.executeQuery();
  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);
  }
  rs.close();
  pstmt.close();
  } catch (SQLException e) {
  e.printStackTrace();
  } finally {
  ConnectionManager.closeConnection(conn);
  }
  return students;
  }
  @Override
  public List findAll(){
  List students = new ArrayList();
  Connection conn = ConnectionManager.getConnection();
  String strSQL = “select * from t_student”;
  try {
  Statement stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery(strSQL);
  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);
  }
  rs.close();
  stmt.close();
  } catch (SQLException e) {
  e.printStackTrace();
  } finally {
  ConnectionManager.closeConnection(conn);
  }
  return students;
  }
  @Override
  public Vector findRowsBySex(){
  Vector rows = new Vector();
  Connection conn =ConnectionManager.getConnection();
  String strSQL=“select sex as ‘性别’,count() as ‘人数’"
  +“from t_student group by sex order by sex desc”;
  try {
  Statement stmt =conn.createStatement();
  ResultSet rs =stmt.executeQuery(strSQL);
  while (rs.next()){
  Vector 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();
  Connection conn = ConnectionManager.getConnection();
  String strSQL = "select class as ‘班级’, count() as ‘人数’”
  + " from t_student group by class order by class desc";
  try {
  Statement stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery(strSQL);
  while (rs.next()) {
  Vector 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();
  Connection conn = ConnectionManager.getConnection();
  String strSQL = “select department as ‘系部’, count(*) as ‘人数’”
  + " from t_student group by department order by department desc";
  try {
  Statement stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery(strSQL);
  while (rs.next()) {
  Vector 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;
  }

}
UserDaoImpl部分

package net.zzq.student.dao.impl;

import net.zzq.student.bean.Student;
import net.zzq.student.bean.User;
import net.zzq.student.dao.UserDao;
import net.zzq.student.dbutil.ConnectionManager;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**

• 功能：用户数据访问接口实现类

• 作者：郑志强

• 日期：2019年6月28日
  */
  public class UserDaoImpl implements UserDao {
  @Override
  public int insert(User user) {
  int count = 0;
  Connection conn = ConnectionManager.getConnection();
  String strSQL = “insert into t_user (username, password, telephone, register_time)”
  + " values (?, ?, ?, ?)";
  try {
  PreparedStatement pstmt = conn.prepareStatement(strSQL);
  pstmt.setString(1, user.getUsername());
  pstmt.setString(2, user.getPassword());
  pstmt.setString(3, user.getTelephone());
  pstmt.setTimestamp(4, new Timestamp(user.getRegisterTime().getTime()));
  count = pstmt.executeUpdate();
  pstmt.close();
  } catch (SQLException e) {
  e.printStackTrace();
  } finally {
  ConnectionManager.closeConnection(conn);
  }

   return count;
  

  }

  @Override
  public int deleteById(int id) {
  int count = 0;
  Connection conn = ConnectionManager.getConnection();
  String strSQL = “delete from t_user where id = ?”;
  try {
  PreparedStatement pstmt = conn.prepareStatement(strSQL);
  pstmt.setInt(1, id);
  count = pstmt.executeUpdate();
  pstmt.close();
  } catch (SQLException e) {
  e.printStackTrace();
  } finally {
  ConnectionManager.closeConnection(conn);
  }

   return count;
  

  }

  @Override
  public int update(User user) {
  int count = 0;
  Connection conn = ConnectionManager.getConnection();
  String strSQL = “update t_user set username = ?, password = ?, telephone = ?,”
  + " register_time = ? where id = ?";
  try {
  PreparedStatement pstmt = conn.prepareStatement(strSQL);
  pstmt.setString(1, user.getUsername());
  pstmt.setString(2, user.getPassword());
  pstmt.setString(3, user.getTelephone());
  pstmt.setTimestamp(4, new Timestamp(user.getRegisterTime().getTime()));
  pstmt.setInt(5, user.getId());
  count = pstmt.executeUpdate();
  pstmt.close();
  } catch (SQLException e) {
  e.printStackTrace();
  } finally {
  ConnectionManager.closeConnection(conn);
  }

   return count;
  

  }

  @Override
  public User findById(int id) {
  User user = null;
  Connection conn = ConnectionManager.getConnection();
  String strSQL = “select * from t_user where id = ?”;
  try {
  PreparedStatement pstmt = conn.prepareStatement(strSQL);
  pstmt.setInt(1, id);
  ResultSet rs = pstmt.executeQuery();
  if (rs.next()) {
  user = new User();
  user.setId(rs.getInt(“id”));
  user.setUsername(rs.getString(“username”));
  user.setPassword(rs.getString(“password”));
  user.setTelephone(rs.getString(“telephone”));
  user.setRegisterTime(rs.getTimestamp(“register_time”));
  }
  } catch (SQLException e) {
  e.printStackTrace();
  } finally {
  ConnectionManager.closeConnection(conn);
  }

   return user;
  

  }

  @Override
  public List findAll() {
  List users = new ArrayList();

   Connection conn = ConnectionManager.getConnection();
   String strSQL = "select * from t_user";
   try {
       Statement stmt = conn.createStatement();
       ResultSet rs = stmt.executeQuery(strSQL);
       while (rs.next()) {
           User user = new User();
           user.setId(rs.getInt("id"));
           user.setUsername(rs.getString("username"));
           user.setPassword(rs.getString("password"));
           user.setTelephone(rs.getString("telephone"));
           user.setRegisterTime(rs.getTimestamp("register_time"));
           users.add(user);
       }
       rs.close();
       stmt.close();
   } catch (SQLException e) {
       e.printStackTrace();
   } finally {
       ConnectionManager.closeConnection(conn);
   }
  
   return users;
  

  }

  @Override
  public User login(String username, String password) {
  User user = null;

   Connection conn = ConnectionManager.getConnection();
   String strSQL = "select * from t_user where username = ? and password = ?";
   try {
       PreparedStatement pstmt = conn.prepareStatement(strSQL);
       pstmt.setString(1, username);
       pstmt.setString(2, password);
       ResultSet rs = pstmt.executeQuery();
       if (rs.next()) {
           user = new User();
           user.setId(rs.getInt("id"));
           user.setUsername(rs.getString("username"));
           user.setPassword(rs.getString("password"));
           user.setTelephone(rs.getString("telephone"));
           user.setRegisterTime(rs.getTimestamp("register_time"));
       }
   } catch (SQLException e) {
       e.printStackTrace();
   } finally {
       ConnectionManager.closeConnection(conn);
   }
   return user;
  

  }

  @Override
  public boolean isUsernameExisted(String username) {
  boolean existed = false;
  Connection conn = ConnectionManager.getConnection();
  String strSQL = “select * from t_user where username=?”;
  try {
  PreparedStatement pstmt = conn.prepareStatement(strSQL);
  pstmt.setString(1, username);
  ResultSet rs = pstmt.executeQuery();
  if (rs.next()) {
  existed = true;
  } else
  pstmt.close();
  rs.close();
  } catch (SQLException e) {
  e.printStackTrace();
  } finally {
  ConnectionManager.closeConnection(conn);
  }
  return existed;
  }

}