1.增删改查操作

(1)练习题1：从控制台向数据库的表customers中插入一条数据

package com.lty4.exer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Scanner;

import org.junit.Test;

import com.lty3.util.JDBCUtils;
    // 练习题1：从控制台向数据库的表customers中插入一条数据
public class Exer1Test {
    
    @Test
    public void testInsert() {
        
        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入用户名:");
        String name = scanner.next();
        System.out.print("请输入邮箱:");
        String email = scanner.next();
        System.out.print("请输入生日:");
        String birthday = scanner.next();
        
        String sql = "insert into customers(name , email , birth)values(? , ? , ?)";
        int insertCount = update(sql, name , email , birthday);
        
        if(insertCount > 0) {
            System.out.println("添加成功！");
        } else {
            System.out.println("添加失败！");
        }
        
    }
    
    // 通用的增删改操作
    public int update(String sql , Object ...args) {//sql中占位符的个数与可变形参(Object ...args)的长度相同
                        //Object ...args 可变形参 ----> 数组
        
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            // 1.获取数据库的连接
            conn = JDBCUtils.getConnection();
            
            // 2.预编译SQL语句,返回PreparedStatement的实例对象
            ps = conn.prepareStatement(sql);
            
            // 3.填充占位符
            for(int i = 0 ; i < args.length ; i++) {
                ps.setObject( i+1 , args[i]);
            }
            
            // 4.执行
            /*
             * ps.execute():
             * 如果执行的是查询操作,有返回结果,则此方法返回true
             * 如果执行的是增、删、改操作,没有返回结果,则此方法返回false
             */
            // 方式一 
//          return ps.execute();
            
            // 方式二
            return ps.executeUpdate();
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            
            // 5.关闭资源
        JDBCUtils.closeResource(conn, ps);
        }
        
        return 0;
        
    }
    
}

(2)练习题2:
问题1: 向examstudent表中添加一条记录

    // 问题1: 向examstudent表中添加一条记录
    /*
     * 字段:
     * Type
     * IDCard
     * ExamCard
     * StudentName
     * Location
     * Grade
     */
    @Test
    public void testInsert() {
        Scanner scanner = new Scanner(System.in);
        System.out.print("四级/六级:");
        int type = scanner.nextInt();
        System.out.print("身份证号:");
        String IDCard = scanner.next();
        System.out.print("准考证号:");
        String examCard = scanner.next();
        System.out.print("学生姓名:");
        String studentName = scanner.next();
        System.out.print("所在城市:");
        String location = scanner.next();
        System.out.print("考试成绩:");
        String grade = scanner.next();
        
        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("添加失败！");
        }
    }
    
    // 通用的增删改操作
    public int update(String sql , Object ...args) {//sql中占位符的个数与可变形参(Object ...args)的长度相同
                        //Object ...args 可变形参 ----> 数组
        
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            // 1.获取数据库的连接
            conn = JDBCUtils.getConnection();
            
            // 2.预编译SQL语句,返回PreparedStatement的实例对象
            ps = conn.prepareStatement(sql);
            
            // 3.填充占位符
            for(int i = 0 ; i < args.length ; i++) {
                ps.setObject( i+1 , args[i]);
            }
            
            // 4.执行
            /*
             * ps.execute():
             * 如果执行的是查询操作,有返回结果,则此方法返回true
             * 如果执行的是增、删、改操作,没有返回结果,则此方法返回false
             */
            // 方式一 
//          return ps.execute();
            
            // 方式二
            return ps.executeUpdate();
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            
            // 5.关闭资源
        JDBCUtils.closeResource(conn, ps);
        }
        
        return 0;
        
    }

问题2 : 根据身份证号或者准考证号查询学生成绩信息

    // 问题2 : 根据身份证号或者准考证号查询学生成绩信息
    
    @Test
    public void queryWithIDCardOrExamCard() {
        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.print("请输入准考证号:");
            String examCard = scanner.next();
            String sql = "select FlowId flowId , Type type , IDCard , ExamCard examCard , StudentName name , Location location , Grade 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.print("请输入身份证号:");
            String IDCard = scanner.next();
            String sql = "select FlowId flowId , Type type , IDCard , ExamCard examCard , StudentName name , Location location , Grade 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("您的输入有误,请重新进入程序！");
        }
    }
    public <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
            ResultSetMetaData rsmd = rs.getMetaData();

            // 通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();

            if (rs.next()) {
                T t = clazz.newInstance();

                // 处理结果集一行数据中的每一个列
                for (int i = 0; i < columnCount; i++) {

                    // 获取列对应的值
                    Object columValue = rs.getObject(i + 1);

                    // 获取每个列的列名
//                  String columName = rsmd.getColumnName(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    // 给t对象指定的某一个columName属性,赋值为columValue : 通过反射(动态实现)
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true); // 将私有属性可视
                    field.set(t, columValue);
                }

                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            JDBCUtils.closeResource(conn, ps, rs);
        }
        return null;
    }

Student实体类

package com.lty4.exer;
    /*
     * 字段:
     * FlowId:
     * Type: 
     * IDCard: 
     * ExamCard: 
     * StudentName: 
     * Location: 
     * Grade:
     */
public class Student {  
    private int flowId; // 流水号
    private int type; // 考试类型
    private String IDCard; // 身份证号
    private String examCard; // 准考证号
    private String name; // 学生姓名
    private String location; // 所在城市
    private int grade; // 考试成绩
    
    public Student() {
        super();
    }

    public Student(int flowId, int type, String iDCard, String examCard, String name, String location, int grade) {
        super();
        this.flowId = flowId;
        this.type = type;
        IDCard = iDCard;
        this.examCard = examCard;
        this.name = name;
        this.location = location;
        this.grade = grade;
    }

    public int getFlowId() {
        return flowId;
    }

    public void setFlowId(int flowId) {
        this.flowId = flowId;
    }

    public int getType() {
        return type;
    }

    public void setType(int type) {
        this.type = type;
    }

    public String getIDCard() {
        return IDCard;
    }

    public void setIDCard(String iDCard) {
        IDCard = iDCard;
    }

    public String getExamCard() {
        return examCard;
    }

    public void setExamCard(String examCard) {
        this.examCard = examCard;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getLocation() {
        return location;
    }

    public void setLocation(String location) {
        this.location = location;
    }

    public int getGrade() {
        return grade;
    }

    public void setGrade(int grade) {
        this.grade = grade;
    }

    @Override
    public String toString() {
        System.out.println("===========查询结果===========");
        return info();
    }

    private String info() {
        return "流水号:" + flowId +"\n四级/六级:" + type + "\n身份证号" + IDCard + " \n准考证号:" 
    + examCard + "\n学生姓名:" + name + "\n区域:" + location + "\n成绩:" + grade;
    }
}

问题3 : 删除指定的学生信息

    // 问题3 : 删除指定的学生信息
    
    @Test
    public void testDeleteByExamCard() {
        System.out.println("请输入学生的考号:");
        Scanner scanner = new Scanner(System.in);
        String examCard = scanner.next();
        
        // 查询指定考号的学生信息
        String sql = "select FlowId flowId , Type type , IDCard , ExamCard examCard , StudentName name , Location location , Grade grade from examstudent where ExamCard = ?";
        Student student = getInstance(Student.class, sql, examCard);
        if(student == null) {
            System.out.println("查无此人,请重新输入！");
        } else {
            String sql1 = "delete from examstudent where examCard = ?";
            int deleteCount = update(sql1, examCard);
            if(deleteCount > 0) {
                System.out.println("删除成功！");
            }
        }
        
    }
    // 问题3 的优化
    @Test
    public void testDeleteByExamCard1() { // 问题3 的优化
        System.out.println("请输入学生的考号:");
        Scanner scanner = new Scanner(System.in);
        String examCard = scanner.next();
        
        String sql = "delete from examstudent where examCard = ?";
        int deleteCount = update(sql, examCard);
        if(deleteCount > 0) {
            System.out.println("删除成功！");
        } else {
            System.out.println("查无此人,请重新输入！");
        }
    }