[尚硅谷JDBC核心技术（新版jdbc）]idea第三章练习题第二题总结

题目如下：

 下面是实现的代码，都是老师上课讲的，这里写出来就是方便自己看的，如果有需要的也可以使用。首先是学生类然后是实现的方法，用到的工具类、增删改和查询操作都是之前几篇写好的。

package com.atguigu4.exer;

public class Student {
    private int flowID;
    private int Type;
    private String IDCard;
    private String ExamCard;
    private String StudentName;
    private String Location;
    private int  Grade;

    public int getType() {
        return Type;
    }

    public void setType(int type) {
        Type = type;
    }

    public String getIDCard() {
        return IDCard;
    }

    public void setIDCard(String IDCard) {
        this.IDCard = IDCard;
    }

    public String getExamCard() {
        return ExamCard;
    }

    public void setExamCard(String examCard) {
        ExamCard = examCard;
    }

    public String getStudentName() {
        return StudentName;
    }

    public void setStudentName(String studentName) {
        StudentName = studentName;
    }

    public String getLocation() {
        return Location;
    }

    public void setLocation(String location) {
        Location = location;
    }

    public int getGrade() {
        return Grade;
    }

    public void setGrade(int grade) {
        Grade = grade;
    }

    @Override
    public String toString() {
        System.out.println("========查询结果========");
        return "流水号："+flowID+"\n四级、六级："+Type+"\n身份证号："+IDCard+"\n准考证号："+ExamCard+"\n学生姓名："+StudentName
                +"\n区域："+Location+"\n成绩："+Grade;
    }
}

package com.atguigu4.exer;

import com.atguigu3.util.JDBCUtils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.Scanner;

public class Exer2Test {
    public static void main(String[] args) {
        //第一题
        /*Scanner scanner=new Scanner(System.in);
        System.out.println("四/六级：");
        int type=scanner.nextInt();
        System.out.println("身份证号：");
        String IDcard=scanner.next();
        System.out.println("准考证号：");
        String examCard=scanner.next();
        System.out.println("学生姓名：");
        String studentName=scanner.next();
        System.out.println("所在城市：");
        String location=scanner.next();
        System.out.println("考试成绩：");
        int grade=scanner.nextInt();
        String sql="insert into examstudent(type,idcard,examcard,studentname,location,grade)values(?,?,?,?,?,?)";
        int insertCount=update(sql,type,IDcard,examCard,studentName,location,grade);
        if(insertCount>0){
            System.out.println("添加成功");
        }else{
            System.out.println("添加失败");
        }*/
        //第二题
      /*  System.out.println("请选择你要输入的类型：");
        System.out.println("a.准考证号");
        System.out.println("b.身份证号");
        Scanner scanner=new Scanner(System.in);
        String selection =scanner.next();
        if ("a".equalsIgnoreCase(selection)){
            System.out.println("请输入准考证号：" );
            String examCard=scanner.next();
            String sql="select flowID,Type,IDCard,ExamCard,StudentName,Location,Grade from examstudent where examCard=?";
            Student student=getInstance(Student.class,sql,examCard);
            if (student!=null){
                System.out.println(student);
            }else{
                System.out.println("准考证账号信息有误！");
            }
        }else if("b".equalsIgnoreCase(selection)){
            System.out.println("请输入身份证号：" );
            String IDCard=scanner.next();
            String sql="select flowID,Type,IDCard,ExamCard,StudentName,Location,Grade from examstudent where IDCard=?";
            Student student=getInstance(Student.class,sql,IDCard);
            if (student!=null){
                System.out.println(student);
            }else{
                System.out.println("身份证号信息有误！");
            }
        }else{
            System.out.println("您的输入有误，请重新输入");
        }*/
        //第三题第一种方法
/*      System.out.println("请输入学生的考号：");
        Scanner scanner=new Scanner(System.in);
        String examCard=scanner.next();
        String sql="select flowID,Type,IDCard,ExamCard,StudentName,Location,Grade from examstudent where examCard=?";
        Student student=getInstance(Student.class,sql,examCard);
        if (student!=null){
            String sql1="delete from examstudent where examCard=?";
            int deleteCount=update(sql1,examCard);
            if(deleteCount>0){
                System.out.println("删除成功！");
            }
        }else{
            System.out.println("查无此人，请重新输入！");
        }*/
        //第三题第二种方法
        System.out.println("请输入学生的考号：");
        Scanner scanner = new Scanner(System.in);
        String examCard = scanner.next();
        String sql1 = "delete from examstudent where examCard=?";
        int deleteCount = update(sql1, examCard);
        if (deleteCount > 0) {
            System.out.println("删除成功！");
        } else {
            System.out.println("查无此人，请重新输入！");
        }
    }

    public static int update(String sql,Object...args){
        Connection conn= null;
        PreparedStatement ps= null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.closeResource(conn,ps);
        }
        return 0;
    }
    public static <T> T getInstance(Class<T> clazz,String sql,Object...args){
        Connection conn= null;
        PreparedStatement ps= null;
        ResultSet rs= null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i=0;i< args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmd=rs.getMetaData();
            int columnCount=rsmd.getColumnCount();
            if(rs.next()){
                T t=clazz.newInstance();
                for (int i=0;i<columnCount;i++){
                    Object columnValue=rs.getObject(i+1);
                    String columnLabel=rsmd.getColumnLabel(i+1);
                    Field field=clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,ps,rs);
        }
        return null;
    }
}