mysql实现学生信息的增删查

mysql实现学生信息的增删查

表名:examstudent

Student.java

package com.g03.bean;

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 Student() {
		super();
	}
	public Student(int flowID, int type, String iDCard, String examCard, String studentName, String location,
			int grade) {
		super();
		FlowID = flowID;
		Type = type;
		IDCard = iDCard;
		ExamCard = examCard;
		StudentName = studentName;
		Location = location;
		Grade = grade;
	}
	public int getFlowID() {
		return FlowID;
	}
	public void setFlowID(int flowID) {
		FlowID = flowID;
	}
	public int getType() {
		return Type;
	}
	public void setType(int type) {
		Type = type;
	}
	public String getIDCard() {
		return IDCard;
	}
	public void setIDCard(String iDCard) {
		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 info();
	}
	private String info() {
		return "流水号:"+FlowID+"n四/六级:"+Type+"n身份z号:"+IDCard+"n准考证号:"+ExamCard+"n学生姓名:"+StudentName+"n区域"
				+Location+"n成绩:"+Grade;
	}
}

StudentCrud.java

package com.g03.preparedstatement;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetmetaData;
import java.util.Scanner;
import org.junit.Test;
import com.g03.bean.Student;
import com.g03.util.JDBCUtils;

public class StudentCrud {
	Scanner scanner = new Scanner(System.in);

	@Test
	public void StudentInsert() {

		
		System.out.println("请输入四或六级:");// int(5)
		int Type = scanner.nextInt();

		System.out.println("请输入身份z号码:");// vachar(18)
		String IDCard = scanner.next();

		System.out.println("请输入准考证号码:");// vachar(15)
		String ExamCard = scanner.next();

		System.out.println("请输入学生姓名:");// vachar(20)
		String StudentName = scanner.next();

		System.out.println("请输入区域:");// vachar(20)
		String Location = scanner.next();

		System.out.println("请输入成绩:");// int(10)
		int Grade = scanner.nextInt();

		String sql = "insert into examstudent(Type, IDCard, ExamCard , StudentName , Location , Grade)value(?,?,?,?,?,?)";

		int insertCount = update(sql, Type, IDCard, ExamCard, StudentName, Location, Grade);

		if (insertCount > 0) {
			System.out.println("添加成功!");
		} else
			System.out.println("添加失败!");
	}

	
	@Test
	public void StudentQuery() {

		String sql = "";

		System.out.println("请选择查询方式");
		System.out.println("1.身份z号:");// vachar(18)
		System.out.println("2.准考证号码:");// vachar(15)

		System.out.printf("请输入1or2:");
		int number = scanner.nextInt();

		switch (number) {

		case 1:
			sql = "select FlowID ,Type, IDCard, ExamCard , StudentName , Location , Grade from examstudent where IDCard=?";
			System.out.println("1.请输入身份z号:");// vachar(18)
			String IDCard = scanner.next();
			Student student = getInstance(Student.class, sql, IDCard);
			if (student != null) {
				System.out.println(student);
			} else
				System.out.println("查无此人");
			break;
		case 2:
			sql = "select FlowID ,Type, IDCard, ExamCard , StudentName , Location , Grade from examstudent where ExamCard=?";
			System.out.println("请输入准考证号:");// vachar(18)
			String ExamCard = scanner.next();
			Student student1 = getInstance(Student.class, sql, ExamCard);
			if (student1 != null) {
				System.out.println(student1);
			} else
				System.out.println("查无此人");
			break;
		case 3:
			System.out.println("输入有误，请重新进入程序");
		}
	}
	
	
	@Test
	public void deleteStudent() {
		String sql = "";

		System.out.println("请选择删除方式");
		System.out.println("1.身份z号:");// vachar(18)
		System.out.println("2.准考证号码:");// vachar(15)

		System.out.printf("请输入1or2:");
		int number = scanner.nextInt();

		switch (number) {

		case 1:
			sql = "delete from examstudent where IDCard=?";
			System.out.println("1.请输入身份z号:");// vachar(18)
			String IDCard = scanner.next();
			int insertCount = update(sql,IDCard);
			if (insertCount > 0) {
				System.out.println("删除成功!");
			} else
				System.out.println("删除失败!");
			break;
		case 2:
			sql = "delete from examstudent where ExamCard=?";
			System.out.println("请输入准考证号:");// vachar(18)
			String ExamCard = scanner.next();
			int insertCount1 = update(sql,ExamCard);

			if (insertCount1 > 0) {
				System.out.println("删除成功!");
			} else
				System.out.println("删除失败!");
			break;
		case 3:
			System.out.println("输入有误，请重新进入程序");
		}
	}

	// 通用增删改
	public int update(String 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.执行
			
			return (int) ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 5.资源的关闭
			JDBCUtils.closeResource(conn, ps);
		}
		return 0;
	}

	
	public  T getInstance(Class 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 columnLabel = rsmd.getColumnLabel(i + 1);
					// 给t对象指定的columnClassName属性，赋值为value
					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;
	}
}