表名: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身份证号:"+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("请输入身份证号码:");// 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.身份证号:");// 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.请输入身份证号:");// 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.身份证号:");// 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.请输入身份证号:");// 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;
}
}
