[Java知识库]JDBC阶段练习_通过控制台交互的方式实现对数据库表的增删改查

JDBC课下练习-通过控制台交互的方式实现对数据库表的增删改查

作者水平有限，仅供学习参考！

题目详情

练习题1
从控制台向数据库的表customers中插入一条数据，表结构如下：

练习题2：
创立数据库表 examstudent，表结构如下：

向数据表中添加如下数据：

代码实现1：插入一个新的student 信息

请输入考生的详细信息
Type:
IDCard:
ExamCard:
StudentName:
Location:
Grade:
信息录入成功!

代码实现2：在 eclipse中建立 java 程序：输入身份证号或准考证号可以查询到学生的基本信息。
结果如下：

代码实现3：完成学生信息的删除功能

案例源码

package com.Etui4.exercises;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Scanner;

import org.junit.Test;

public class ExerciseTest {
	/** examstudent表结构
	 * 	Type: 
		IDCard:
		ExamCard:
		StudentName:
		Location:
		Grade:
	 */
	Scanner sc = new Scanner(System.in);
	// 测试函数
	@Test
	public void testExamStudent() {
		System.out.println("请选择需要进行的操作：\rA:插入数据\rB:删除数据\rC:查询数据");
		String sign = sc.nextLine();
		if(sign.equals("A")) {
			testInsert();
		} else if(sign.equals("B")) {
			deleteForExamStudentTest();
		} else if(sign.equals("C")) {
			queryForExamStudentTest();
		} else {
			System.out.println("您的输入有误！请重新进入程序！");
		}
	}
	
	
	// 问题1： 向examstudent表插入数据
	public void testInsert() {
		System.out.println("请输入考生的详细信息：");
		System.out.println("4/6：");
		String type = sc.nextLine();
		System.out.println("身份证号：");
		String idCard = sc.nextLine();
		System.out.println("准考证号：");
		String examCard = sc.nextLine();
		System.out.println("学生姓名：");
		String studentName = sc.nextLine();
		System.out.println("所在城市：");
		String location = sc.nextLine();
		System.out.println("考试成绩：");
		String grade = sc.nextLine();
		
		String sql = "insert into examstudent(Type, IDCard, ExamCard, StudentName, Location, Grade) values (?,?,?,?,?,?)";
		int resultCount = updateForExamStudent(sql, type, idCard, examCard, studentName, location, grade);
		
		if(resultCount > 0) {
			System.out.println("添加成功！");
		} else {
			System.out.println("添加失败！");
		}
	}
	
	// 问题二：通过身份证号或准考证号查询到学生的基本信息
	public void queryForExamStudentTest() {
		String card = ""; // 输入的准考证号/身份证号
		System.out.println("请选择您要输入的类型：\ra:准考证号\rb:身份证号");
		String sign = sc.nextLine();
		if(sign.equals("a")) {
			System.out.println("请输入准考证号：");
			card = sc.nextLine();
		} else if(sign.equals("b")) {
			System.out.println("请输入身份证号：");
			card = sc.nextLine();
		} else {
			System.out.println("您输入有误！请重新进入程序！");
			System.exit(0);
		}
		
		// sql语句
		String sql = "";
		if(sign.equals("a")) {
			sql = "select FlowID, Type, IDCard, ExamCard, StudentName, Location, Grade from examstudent where ExamCard = ?;";
		} else {
			sql = "select FlowID, Type, IDCard, ExamCard, StudentName, Location, Grade from examstudent where IDCard = ?;";
		}
		Examstudent es = QueryForExamStudent(sql, card);
		if(es != null) {
			System.out.println("===========查询结果==========");
			System.out.println("流水号：\t" + es.getFlowID());
			System.out.println("四级/六级：\t" + es.getType());
			System.out.println("身份证号：\t" + es.getIDCard());
			System.out.println("准考证号：\t" + es.getExamCard());
			System.out.println("学生姓名：\t" + es.getStudentName());
			System.out.println("区域：\t" + es.getLocation());
			System.out.println("成绩：\t" + es.getGrade());
		} else {
			System.out.println("查无此人！请重新进入程序！");
		}
	}
	
	// 问题三：完成学生信息的删除功能
	public void deleteForExamStudentTest() {
		System.out.println("请输入学生的考号：");
		String examcard = sc.nextLine();
		String sql = "select FlowID, Type, IDCard, ExamCard, StudentName, Location, Grade from examstudent where ExamCard = ?;";
		Examstudent es = QueryForExamStudent(sql, examcard);
		if(es != null) {
			updateForExamStudent("delete from examstudent where ExamCard = ?", examcard);
			System.out.println("删除成功！");
		} else {
			System.out.println("查无此人，请重新输入！");
		}
	}
	
	// 通用的查询函数
	public Examstudent QueryForExamStudent(String sql,Object ...args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// 获取登录连接信息
			conn = 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()) {
				Examstudent es = Examstudent.class.getDeclaredConstructor().newInstance();
				for(int i = 0; i < columnCount; i++) {
					Object columnValue = rs.getObject(i + 1);
					String columnLabel = rsmd.getColumnLabel(i + 1);
					
					Field field = Examstudent.class.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(es, columnValue);
				}
				return es;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeResource(conn, ps, rs);
		}
		return null;
	}
	
	// 通用的增删改函数
	public int updateForExamStudent(String sql,Object ...args) {
		Connection conn = null;
		PreparedStatement ps = null; 
		try {
			// 获取登录连接信息
			conn = getConnection();
			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 {
			closeResource(conn, ps);
		}
		return 0;
	}
	
	public Connection getConnection() throws Exception{
		InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
		Properties prop = new Properties();
		prop.load(is);
		String user = prop.getProperty("user");
		String password = prop.getProperty("password");
		String url = prop.getProperty("url");
		String driverClass = prop.getProperty("driverClass");
		
		Class.forName(driverClass);
		Connection conn = DriverManager.getConnection(url, user, password);
		return conn;
	}
	
	// 关闭资源函数
	public void closeResource(Connection conn, PreparedStatement ps) {
		try {
			if(conn != null) {
				conn.close();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if(ps != null) {
				ps.close();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	// 关闭资源函数
	public void closeResource(Connection conn, PreparedStatement ps, ResultSet rs) {
		try {
			if(conn != null) {
				conn.close();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if(ps != null) {
				ps.close();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if(rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}