java实训总结3

今天实训内容的小目标就是完成那几个数据库增删改查的功能，由于课时的原因，也因为自己的原因，导致了这里的内容都是没有怎么学习的，所以我在敲代码的过程中首先去认真的看老师的注释，先去理解这个内容的思路，之后再照着敲，因为这个jdbc的思路基本上差不多，我照着先敲了一遍，之后再去背着敲，通过自己对jdbc的理解把代码敲出来，遇到记不住的或者没有理解的再去看老师的代码。通过这样的学习方法，自我感觉已经会了。

看见有一块代码块长达500行，就被下到了，在想着能不能偷懒的情况下，思考着怎么能够把这些代码变少。

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

 

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

public class StudentDaoImpl implements StudentDao {
    Connection conn = ConnectionManager.getConnection();

    /**
     * 添加学生记录
     * @param student
     * @return
     */
    @Override
    public int insert(Student student) {
        int count = 0;


        String sql = "insert into t_student (id, name, sex, age, department, class, telephone)"
                 + " values (?, ?, ?, ?, ?, ?, ?)";

        try{
            PreparedStatement pstmt = conn.prepareStatement(sql);

            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(7,student.getClazz());

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


        return count;
    }

    /**
     * 按学号删除记录
     * @param id
     * @return
     */
    @Override
    public int deleteById(String id) {
        int count = 0;


        String sql = "delete from t_student where id = ?";

        delete(sql, id, count);


        return count;
    }

    /**
     * 按照班级 删除记录
     * @param clazz
     * @return
     */
    @Override
    public int deleteByClass(String clazz) {
        int count = 0;

        String sql = "delete from t_student where class = ?";

        count = delete(sql,clazz,count);

        return count;
    }

    /**
     * 按照系部删除记录
     * @param department
     * @return
     */
    @Override
    public int deleteByDepartment(String department) {
        int count = 0;

        String sql = "delete from t_student where department = ?";

        delete(sql,department,count);

        return count;
    }

    /**
     * 更新学生记录
     * @param student
     * @return
     */
    @Override
    public int update(Student student) {
        int count = 0;

        Connection conn = ConnectionManager.getConnection();
        String sql = "update t_student set name = ?, sex = ?, age = ?,"
                + " department = ?, class = ?, telephone = ? where id = ?";

        try{
            PreparedStatement pstms = conn.prepareStatement(sql);

            pstms.setString(1,student.getName());
            pstms.setString(2,student.getSex());
            pstms.setInt(3,student.getAge());
            pstms.setString(4,student.getDepartment());
            pstms.setString(5,student.getClazz());
            pstms.setString(6,student.getTelephone());
            pstms.setString(7,student.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count;
    }

    /**
     * 查询学生记录
     * @param id
     * @return
     */
    @Override
    public Student findById(String id) {

        Student student = null;

        String sql = "select * from t_student where id = ?";

        student = select(sql,id);

        ConnectionManager.closeConnection(conn);

        return student;
    }


    List<Student> students = new ArrayList<Student>();

    /**
     * 按照姓名查询学生信息
     * @param name
     * @return
     */
    @Override
    public List<Student> findByName(String name) {

        String sql = "select * from t_student where name like ?";

        students.add(select(sql, name+"%"));

        return students;
    }

    /**
     * 按照班级查询学生信息
     * @param clazz
     * @return
     */
    @Override
    public List<Student> findByClass(String clazz) {

        String sql = "select * from t_student where class like ?";

        students.add(select(sql,"%"+clazz+"%"));
        ConnectionManager.closeConnection(conn);

        return students;
    }

    /**
     * 按照系部查询学生记录
     * @param department
     * @return
     */
    @Override
    public List<Student> findByDepartment(String department) {

        String sql = "select * from t_student where department like ?";

        students.add(select(sql,department+"%"));
        ConnectionManager.closeConnection(conn);

        return students;
    }

    /**
     * 查找全部学生
     * @return
     */
    @Override
    public List<Student> findAll() {

        String sql = "select * from t_student";

        try {
            Statement stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery(sql);

            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;
    }

    //定义Vector全局
    Vector rows = new Vector();

    /**
     * 按性别统计学生人数
     * @return
     */
    @Override
    public Vector findRowsBySex() {
        String sql = "select sex as '性别', count(*) as '人数'"
                + " from t_student group by sex order by sex desc";

        try {
            Statement stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery(sql);

            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;
    }

    /**
     * 按班级统计人数
     * @return
     */
    @Override
    public Vector findRowsByClass() {
        String sql = "select class as '班级', count(*) as '人数'"
                + " from t_student group by class order by class desc";
        try{
            Statement stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery(sql);

            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;
    }

    /**
     * 按系部统计人数
     * @return
     */
    @Override
    public Vector findRowsByDepartment() {
            String sql = "select department as '系部', count(*) as '人数'"
                    + " from t_student group by department order by department desc";

            try {
                Statement stmt = conn.createStatement();

                ResultSet rs = stmt.executeQuery(sql);

                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;
    }


    private int delete(String sql, String s, int count){
        try{
            PreparedStatement pstmt = conn.prepareStatement(sql);

            pstmt.setString(1,s);

            count = pstmt.executeUpdate();

            pstmt.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    private  Student select(String sql, String s){
        Connection conn = ConnectionManager.getConnection();

        Student student = null;

        try{
            PreparedStatement pstmt = conn.prepareStatement(sql);

            pstmt.setString(1,s);
            ResultSet rs = pstmt.executeQuery();

            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"));
            }

            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return student;
    }
}

皇天不负有心人，只要自己努力总会成功的。自己写着的并没有看见那里有语法错误。心里还是有一点小满足的。

不过还是遇到了一些小问题，遇到了一个问题就是：No operations allowed after connection closed，他告诉我说我的数据库连接已经关闭了，可是之前我明明有打开数据库的代码，他却没有打开。问了一下老师，老师告诉我说，他没有试过把打开数据连接的代码当作全局变量来做，都是用的局部变量，让我试一试，是不是因为这个，之后试了一下，果然没有报错了。但是在另一个java文件中，设置了这个，但是却没有报错，然而我找了很久都没有找到原因。但是我不会放弃的，我会下去慢慢找原因的。