//课后练习2
public class Exer2Test {
/* 问题1：向examstudent表中添加一条数据 Type: IDCard: ExamCard: StudentName: Location: Grade: */
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("添加失败");
}
}
//通用的增删改操作的方法
public static int update(String sql, Object... args) {
//占位符的个数与可变形参的长度一致
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取数据库的连接
conn = JDBCUtil.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 ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.关闭资源
JDBCUtil.closeResource(conn, ps);
}
return 0;
}
}
public class Exer3Test {
public static void main(String[] args) {
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 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.println("请输入身份证号:");
String IDCard = scanner.next();
String sql1="select FlowID flowID,Type type,IDCard,ExamCard examCard,StudentName name,Location location,Grade grade from examstudent where IDCard=?";
Student student = getInstance(Student.class, sql1,IDCard);
if (student!=null){
System.out.println(student);
}else {
System.out.println("输入的身份证号有误");
}
}else{
System.out.println("输入有误，请重新进入程序");
}
}
//问题2：根据身份证号或准考证号查询学生成绩信息
public static <T> T getInstance(Class<T> clazz,String sql,Object ...args){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtil.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++){
//获取每个列的值:通过ResultSet
Object columnValue = rs.getObject(i + 1);
//获取每个列的列名：通过ResultSetMetaData
//获取的是列名
//String columnName = rsmd.getColumnName(i + 1);
//获取列的别名
String columnLabel = rsmd.getColumnLabel(i + 1);
//通过反射将对象指定名columnName的属性赋值给指定的值columnValue
Field field =clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.closeResource(conn, ps,rs);
}
return null;
}
}
//问题3：删除指定的学生信息
public class Exer4Test {
public static void main(String[] args) {
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("删除成功");
}
}
}
public static <T> T getInstance(Class<T> clazz,String sql,Object ...args){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtil.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++){
//获取每个列的值:通过ResultSet
Object columnValue = rs.getObject(i + 1);
//获取每个列的列名：通过ResultSetMetaData
//获取的是列名
//String columnName = rsmd.getColumnName(i + 1);
//获取列的别名
String columnLabel = rsmd.getColumnLabel(i + 1);
//通过反射将对象指定名columnName的属性赋值给指定的值columnValue
Field field =clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.closeResource(conn, ps,rs);
}
return null;
}
//通用的增删改操作的方法
public static int update(String sql, Object... args) {
//占位符的个数与可变形参的长度一致
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取数据库的连接
conn = JDBCUtil.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 ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.关闭资源
JDBCUtil.closeResource(conn, ps);
}
return 0;
}
}