package java4.exer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Scanner;
import org.junit.Test;
import java3.util.JDBCUtils;
public class Exer1Test {
@Test
public void testInsert(){
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名：");
String name=scanner.next();
System.out.println("请输入邮箱");
String email = scanner.next();
System.out.println("请输入生日：");
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) {
Connection conn=null;
PreparedStatement ps=null;
try {
conn = JDBCUtils.getConnedtion();
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;
}
}
package java4.exer;
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 java3.util.JDBCUtils;
public class Exer2Test {
/*
* 向examstudent表中添加一条记录
*/
@Test
public void testInsert(){
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 int update(String sql,Object...args) {
Connection conn=null;
PreparedStatement ps=null;
try {
conn = JDBCUtils.getConnedtion();
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;
}
/*
* 根据身份证号或准考证号查询学生成绩信息
*/
@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.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 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.getConnedtion();
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;
}
//删除指定的学生信息
@Test
public void testDeleteByExamCard(){
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("查无此人！");
}
}
}
package java4.exer;
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;
}
}
