查询和删除

查询代码：

/**
     * MVC: Model View Controller
     * 
     * 把 View 视图(看得见的, 需要进行输入的)、 Model 模型(实际的逻辑代码: Dao 在这一部分)、 Controller
     * 控制器(连接视图和模型的代码: 测试方法) 分开
     */
    @Test
    public void testGetStudent() throws SQLException, Exception {
        // 1. 获取输入的信息: 可能是一个身份证号也可能是一个准考证号.
        String cardId = getCardIdFromConsole();

        if (cardId == null) {
            return;
        }
        // 2. 执行查询
        Student student = null;
        StudentDao studentDao = new StudentDao();

        // 准考证号
        if (cardId.length() == 15) {
            student = studentDao.getByExamCard(cardId);
        }
        // 身份证号
        else {
            student = studentDao.getByIdCard(cardId);
        }

        // 3. 打印学员信息
        printStudent(student);
    }

    private void printStudent(Student student) {
        System.out.println("==========查询结果==========");

        System.out.println("流水号:" + student.getId());
        System.out.println("四级\\六级:" + student.getType());
        System.out.println("身份证号:" + student.getIdCard());

        System.out.println("准考证号:" + student.getExamCard());
        System.out.println("学生姓名:" + student.getStudentName());
        System.out.println("区域:" + student.getLocation());

        System.out.println("成绩:" + student.getGrade());
    }

    private String getCardIdFromConsole() {
        System.out.println("选择您要输入的类型.");
        System.out.println("a:准考证号");
        System.out.println("b:身份证号");

        Scanner scanner = new Scanner(System.in);
        String type = scanner.next();

        if (!("a".equals(type) || "b".equals(type))) {
            System.out.println("您的输入有误, 请重新进入程序");
            return null;
        }

        if ("a".equals(type)) {
            System.out.println("请输入准考证号:");
        } else {
            System.out.println("请输入身份证号:");
        }
        return scanner.next();
    }

StudentDao.java

public Student getByExamCard(String cardId) throws SQLException, Exception {
    //QueryRunner 是线程安全的!
    private QueryRunner queryRunner = new QueryRunner();
        String sql = "SELECT flow_id id, type, id_card idCard, exam_card examCard, "
                + "student_name studentName, location, grade "
                + "FROM examstudent "
                + "WHERE exam_card = ?";
        return queryRunner.query(JdbcUtils.getConnection(), sql, new BeanHandler(Student.class), cardId);
    }

    public Student getByIdCard(String cardId) throws SQLException, Exception {
        //QueryRunner 是线程安全的!
    private QueryRunner queryRunner = new QueryRunner();
        String sql = "SELECT flow_id id, type, id_card idCard, exam_card examCard, "
                + "student_name studentName, location, grade "
                + "FROM examstudent "
                + "WHERE id_card = ?";
        return queryRunner.query(JdbcUtils.getConnection(), sql, new BeanHandler(Student.class), cardId);
    }

=========================
删除代码：

@Test
    public void testDeleteByExamCard() throws SQLException, Exception {
        String examCardId = getExamCardIdFromConsole();

        StudentDao2 studentDao = new StudentDao2();

        Connection connection = null;

        try {
            connection = JdbcUtils.getConnection();

            // 开启事务
            connection.setAutoCommit(false);

            int rowNumber = studentDao.deleteByExamCard(connection, examCardId);
            if (rowNumber == 1) {
                System.out.println("删除成功!");
            } else if (rowNumber == 0) {
                System.out.println("查无此人, 请重新输入.");
            }

            // 提交事务
            DbUtils.commitAndCloseQuietly(connection);
        } catch (Exception e) {
            e.printStackTrace();
            // 回滚事务
            DbUtils.rollbackAndClose(connection);
        }

    }

StudentDao.java

public int deleteByExamCard(String examCardId) throws SQLException, Exception {
        String sql = "DELETE FROM examstudent WHERE exam_card = ?";
        //对于 update 而言, 返回值实际上是该条 SQL 语句作用的行数. 
        //以删除为例, 若删除了 n 行, 则返回值为 n
        return queryRunner.update(JdbcUtils.getConnection(), sql, examCardId);
    }

========================
疑问：

    // 问题1: 数据库事务: 如何关闭数据库连接呢 ? 在后面说完数据库事务后来重构代码 ?
    // 问题2: 批量处理: 可以同时录入多条记录吗 ? 若录入多条记录, 和录入一条记录有区别吗 ?
    // 问题3: 更加通用的 DAO: 适用于更多实体类的 DAO. 需要用到反射技术. 同时使用 DBUtils
    // 问题4: 如何向数据表中插入一张图片 ? 以及如何把图片从数据表中获取出来
    // 问题5: 数据库连接池