DBManager工具类

DBManager.java

import java.sql.*;

public class DBManager {
   private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
   private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=mydb";
   private static final String USERNAME = "sa";
   private static final String PASSWORD = "admin123";
   static {
      try {
         Class.forName(DRIVER);
      } catch (ClassNotFoundException e) {
         e.printStackTrace();
      }
   }

   public static Connection getConnection() {
      Connection conn = null;
      try {
         conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
      } catch (SQLException e) {
         e.printStackTrace();
      }
      return conn;
   }
 
   public static void close(ResultSet rs, Statement ps, Connection conn) {
      if (rs != null) {
         try {
            rs.close();
         } catch (SQLException e) {
            rs = null;
            e.printStackTrace();
         }
      }
      if (ps != null) {
         try {
            ps.close();
         } catch (SQLException e) {
            ps = null;
            e.printStackTrace();
         }
      }
      if (conn != null) {
         try {
            conn.close();
         } catch (SQLException e) {
            conn = null;
            e.printStackTrace();
         }
      }
   }
}

StudentDTO.java

import java.io.Serializable;

public class StudentDTO implements Serializable {
   private String stuNo;
   private String stuName;
   private String stuSex;
   private int stuAge;
   private String stuDept;
   public StudentDTO() {
      super();
   }

   public StudentDTO(String stuNo, String stuName, String stuSex, int stuAge,
         String stuDept) {
      super();
      this.stuNo = stuNo;
      this.stuName = stuName;
      this.stuSex = stuSex;
      this.stuAge = stuAge;
      this.stuDept = stuDept;
   }

   public String getStuNo() {
      return stuNo;
   }

   public void setStuNo(String stuNo) {
      this.stuNo = stuNo;
   }

   public String getStuName() {
      return stuName;
   }

   public void setStuName(String stuName) {
      this.stuName = stuName;
   }

   public String getStuSex() {
      return stuSex;
   }

   public void setStuSex(String stuSex) {
      this.stuSex = stuSex;
   }

   public int getStuAge() {
      return stuAge;
   }

   public void setStuAge(int stuAge) {
      this.stuAge = stuAge;
   }

   public String getStuDept() {
      return stuDept;
   }

   public void setStuDept(String stuDept) {
      this.stuDept = stuDept;
   }
   public String toString() {
      return stuNo + "\t" + stuName + "\t" + stuSex + "\t" + stuAge + "\t"
            + stuDept;
   }
}

StudentDAO.java
import java.sql.*;

import java.util.*;

public class StudentDAO {
   private Connection conn = null;
   private Statement st = null;
   private PreparedStatement ps = null;
   private ResultSet rs = null;
   private CallableStatement cs = null;

   public boolean insert(StudentDTO stu) {
      boolean flag = false;
      try {
         conn = DBManager.getConnection();
         String sql = "insert into student (sno,sname,ssex,sage,sdept) values(?,?,?,?,?)";
         ps = conn.prepareStatement(sql);
         ps.setString(1, stu.getStuNo());
         ps.setString(2, stu.getStuName());
         ps.setString(3, stu.getStuSex());
         ps.setInt(4, stu.getStuAge());
         ps.setString(5, stu.getStuDept());
         flag = !ps.execute();
      } catch (SQLException e) {
         e.printStackTrace();
      } finally {
         DBManager.close(null, ps, conn);
      }
      return flag;
   }
 
   public List<StudentDTO> list() {
      ArrayList<StudentDTO> stus = new ArrayList<StudentDTO>();
      try {
         conn = DBManager.getConnection();
         String sql = "select * from student";
         ps = conn.prepareStatement(sql);
         rs = ps.executeQuery();
         while (rs.next()) {
            StudentDTO stu = new StudentDTO(rs.getString("sno"),
                   rs.getString("sname"), rs.getString("ssex"),
                   rs.getInt("sage"), rs.getString("sdept"));
            stus.add(stu);
         }
      } catch (SQLException e) {
         e.printStackTrace();
      } finally {
         DBManager.close(rs, ps, conn);
      }
      return stus;
   }

   public StudentDTO queryBySno(String sno) {
      StudentDTO stu = null;
      try {
         conn = DBManager.getConnection();
         ps = conn.prepareStatement("select * from student where sno=?");
         ps.setString(1, sno);
         rs = ps.executeQuery();
         while (rs.next()) {
            stu = new StudentDTO(rs.getString("sno"),
                   rs.getString("sname"), rs.getString("ssex"),
                   rs.getInt("sage"), rs.getString("sdept"));
         }
      } catch (SQLException e) {
         e.printStackTrace();
      } finally {
         DBManager.close(rs, ps, conn);
      }
      return stu;
   }
 
   public boolean delete(String sno) {
      boolean flag = false;
      try {
         conn = DBManager.getConnection();
         String sql = "delete from student where sno=?";
         ps = conn.prepareStatement(sql);
         ps.setString(1, sno);
         flag = !ps.execute();
      } catch (SQLException e) {
         e.printStackTrace();
      } finally {
         DBManager.close(null, ps, conn);
      }
      return flag;
   }

   public boolean update(StudentDTO stu) {
      boolean flag = false;
      try {
         conn = DBManager.getConnection();
         String sql = "update student set sname=?,ssex=?,sage=?,sdept=? where sno=?";
         ps = conn.prepareStatement(sql);
         ps.setString(1, stu.getStuName());
         ps.setString(2, stu.getStuSex());
         ps.setInt(3, stu.getStuAge());
         ps.setString(4, stu.getStuDept());
         ps.setString(5, stu.getStuNo());
         flag = !ps.execute();
      } catch (SQLException e) {
         e.printStackTrace();
      } finally {
         DBManager.close(null, ps, conn);
      }
      return flag;
   }
 
    // 调用存储过程
   public List<StudentDTO> splitPage(int pageSize, int pageNum) {
      ArrayList<StudentDTO> stus = new ArrayList<StudentDTO>();
      try {
         conn = DBManager.getConnection();
         String sql = "{call proc_split(?,?,?,?)}";
         cs = conn.prepareCall(sql);
         cs.setInt(1, pageSize);
         cs.setInt(2, pageNum);
         // 调用CS中所特有用来处理存储过程中输入输出参数的方法:注册输出参数
         cs.registerOutParameter(3, Types.INTEGER);
         cs.registerOutParameter(4, Types.INTEGER);
         rs = cs.executeQuery();
         while (rs.next()) {
            StudentDTO stu = new StudentDTO();
            stu.setStuNo(rs.getString("sno"));
            stu.setStuName(rs.getString("sname"));
            stu.setStuSex(rs.getString("ssex"));
            stu.setStuAge(rs.getInt("sage"));
            stu.setStuDept(rs.getString("sdept"));
            stus.add(stu);
         }
         // 获取两个输出参数的值
         int recordCount = cs.getInt(3);
         int pageCount = cs.getInt(4);
         System.out.println("总记录数:" + recordCount);
         System.out.println("第" + pageNum + "页");
         System.out.println("如果每页显示" + pageSize + "条,可分为" + pageCount + "页");
      } catch (SQLException e) {
         e.printStackTrace();
      }
      return stus;
   }
}