package com.lsy.jdbc;
import java.lang.Thread.State;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
import javax.management.RuntimeErrorException;
import javax.naming.spi.DirStateFactory.Result;
import org.junit.Test;
public class JDBCTest {
@Test
public void testGetStudnet(){
//1.得到查询的类型
int searchType = getSearchTypeFromConsole();
//2.具体查询的学生信息
Student student = searchStudent(searchType);
//3.打印学生信息
printStudent(student);
}
/**
* 打印学生信息,若存在直接打印,不存在提示
* @param student
*/
private void printStudent(Student student) {
if(student != null){
System.out.println(student);
}else{
System.out.println("查无此人");
}
}
/**
*
* @param searchType
* @return 1 / 2
*/
private Student searchStudent(int searchType) {
String sql = "select FlowID,type,IDCard,ExameCard,StudentName,Grade "
+ "from examestudent where ";
Scanner sc = new Scanner(System.in);
// 1.根据输入的 searchType 来提示用户输入
// (1.如果输入 1 ,输入身份证号 ,输入 2 ,提示输入 准考证号
// 2.根据 searchType 确定 SQL
if(searchType == 1 ){
System.out.println("请输入身份证号");
String idCard = sc.next();
sql = sql + "IDCard = "+idCard;
System.out.println(sql);
}else{
System.out.println("请输入准考证号");
String exameCard = sc.next();
sql = sql + "exameCard = "+exameCard;
System.out.println(sql);
}
// 3.执行 查询
Student student = getStudent(sql);
// 4.r若存在结果,封装为 一个 Student 对象。
return student;
}
/**
* 根据传入的sql 语句,查询
* @param sql
* @return
*/
private Student getStudent(String sql) {
Student student = null;
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCTools.getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
if(resultSet.next()){
student = new Student(resultSet.getInt(1),
resultSet.getInt(2),resultSet.getString(3),
resultSet.getString(4), resultSet.getString(5),
resultSet.getInt(6));
}
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCTools.releaseDB(resultSet, statement, connection);
}
return student;
}
/**
* 从控制台读入一个整数,确定要查询的类型
* @return 1、身份证号码 2、准考证号
* 其他无效,并提示用户重新输入
*/
private int getSearchTypeFromConsole() {
System.out.println("请输入查询类型:1、身份证号码 2、准考证号");
Scanner sc = new Scanner(System.in);
int type = sc.nextInt();
if(type != 1 && type != 2){
System.out.println("输入有误请重新输入");
// throw new RuntimeException();
getSearchTypeFromConsole();
}
return type;
}
@Test
public void testAddStudent(){
Student student = getStudentFromConsole();
addStudent(student);
}
/**
* 从控制台输入学生信息
* @return
*/
private Student getStudentFromConsole() {
Scanner sc = new Scanner(System.in);
Student student = new Student();
System.out.print("FlowID:");
student.setFlowID(sc.nextInt());
System.out.print("type:");
student.setType(sc.nextInt());
System.out.print("IDCard:");
student.setIDCard(sc.next());
System.out.print("ExameCard:");
student.setExameCard(sc.next());
System.out.print("StudentName:");
student.setStudentName(sc.next());
System.out.print("Grade:");
student.setGrade(sc.nextInt());
return student;
}
public void addStudent(Student student){
//1.准备数据sql 语句
// String sql = "insert into examestudent values(0,4,'123456','123','小明',100);";
String sql = "insert into examestudent"
+" values("+student.getFlowID()
+","+student.getType()
+",'"+student.getIDCard()
+"','"+student.getExameCard()
+"','"+student.getStudentName()
+"',"+student.getGrade()
+");";
System.out.println(sql);
//2.调用 JDBCTools 中的 update(String sql)方法
JDBCTools.update(sql);
}
}
网友评论