使用传统的JDBC对MySQL数据库的CRUD

使用传统的JDBC对MySQL数据库的CRUD

Scroll Down

一个简单的学生管理系统只进行了后端和数据库的连接,实现了对数据库的CRUD。

代码里面用到的JDBCUtils是写的一个对JDBC工具类,在我的上一篇文章中有详细介绍。

public class StudentTest {
    	public static Scanner sc = new Scanner(System.in);
	public static void main(String[] args) {
		System.out.println("=======================欢迎使用学生管理系统================================");
		outer :while(true) {
		System.out.println("请输入你要选择的操作:");
		System.out.println("1、添加学生");
		System.out.println("2、删除学生");
		System.out.println("3、修改学生");
		System.out.println("4、查询单个学生");
		System.out.println("5、查询所有学生");
		System.out.println("6、退出系统");
		int key = sc.nextInt();
		switch (key) {
			case 1:
				try {
				//添加学生方法
					addStudent();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				break;
			case 2:
				try {
				//删除学生方法
					deleteStudent();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				break;
			case 3:
				try {
				修改学生方法
					updateStudent();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				break;
			case 4:
			try {
			//查询单个学生方法
				getOneStudent();
			} catch (Exception e) {
				e.printStackTrace();
			}
				break;
			case 5:
			try {
			//查询所有学生方法
				gwtAllStudent();
			} catch (Exception e) {
				e.printStackTrace();
			}
				break;
			case 6:
				
				break outer;
	
			default:
				System.out.println("你输入的操作有误,请重新输入!");
				break;
			}
		}
		System.out.println("=======================谢谢使用学生管理系统================================");

	}

	public static void gwtAllStudent() throws Exception {
		Connection conn = JDBCUtils.getConnection();
		Statement stmt = conn.createStatement();
		String sql = "select * from student";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()) {
			System.out.print("学生学号:" + rs.getString("id") + "\t");
			System.out.print("学生姓名:" + rs.getString("sname") + "\t");
			System.out.print("学生年龄:" + rs.getString("sage") + "\t");
			System.out.print("学生性别:" + rs.getString("gender") + "\t");
			System.out.print("学生分数:" + rs.getString("score") + "\t");
			System.out.println("学生生日:" + rs.getString("brithday"));
		}
			
		
		JDBCUtils.close(conn, stmt, rs);
	}

	public static void getOneStudent() throws Exception {
		System.out.println("请输入你要查询的学生姓名");
		String sname = sc.next();
		Connection conn = JDBCUtils.getConnection();
		Statement stmt = conn.createStatement();
		String sql = "select * from student where sname= '" + sname + "'";
		ResultSet rs = stmt.executeQuery(sql);
		if (rs.next()) {
			System.out.print("学生学号:" + rs.getString("id") + "\t");
			System.out.print("学生姓名:" + rs.getString("sname") + "\t");
			System.out.print("学生年龄:" + rs.getString("sage") + "\t");
			System.out.print("学生性别:" + rs.getString("gender") + "\t");
			System.out.print("学生分数:" + rs.getString("score") + "\t");
			System.out.println("学生生日:" + rs.getString("brithday"));
		}else {
			System.out.println("没有查到学生信息");
		}
		JDBCUtils.close(conn, stmt, rs);
	}

	public static void updateStudent() throws SQLException {
		System.out.println("请输入你要修改的学生姓名");
		String sname = sc.next();
		Connection conn = JDBCUtils.getConnection();
		Statement stmt = conn.createStatement();
		String sql = "select * from student where sname= '" + sname + "'";
		ResultSet rs = stmt.executeQuery(sql);
		if (rs.next()) {
			System.out.print("学生学号:" + rs.getString("id") + "\t");
			System.out.print("学生姓名:" + rs.getString("sname") + "\t");
			System.out.print("学生年龄:" + rs.getString("sage") + "\t");
			System.out.print("学生性别:" + rs.getString("gender") + "\t");
			System.out.print("学生分数:" + rs.getString("score") + "\t");
			System.out.println("学生生日:" + rs.getString("brithday"));

			System.out.println("请输入你要修改的学生信息具体项:");
			System.out.println("1、修改学生年龄");
			System.out.println("2、修改学生性别");
			System.out.println("3、修改学生分数");
			System.out.println("4、修改学生生日");
			int key = sc.nextInt();
			switch (key) {
			case 1:
				System.out.println("请输入要修改的学生年龄");
				int sage = sc.nextInt();
				sql = "update student set sage = " + sage + " where sname = '" + sname + "'";
				int b = stmt.executeUpdate(sql);
				if (b > 0) {
					System.out.println("修改学生年龄成功");
				} else {
					System.out.println("修改学生年龄失败");
				}
				break;
			case 2:
				System.out.println("请输入你要修改的学生性别");
				String gender = sc.next();
				sql = "update student set gender = '" + gender + "'where sname = '" + sname + "'";
				b = stmt.executeUpdate(sql);
				if (b > 0) {
					System.out.println("修改学生性别成功");
				} else {
					System.out.println("修改学生性别失败");
				}
				break;
			case 3:
				System.out.println("请输入你要修改的学生分数");
				double score = sc.nextDouble();
				sql = "update student set score = " + score + "where sname = '" + sname + "'";
				b = stmt.executeUpdate(sql);
				if (b > 0) {
					System.out.println("修改学生分数成功");
				} else {
					System.out.println("修改学生分数失败");
				}

				break;
			case 4:
				System.out.println("请输入你要修改的学生生日");
				String brithday = sc.next();
				sql = "update student set brithday = '" + brithday + "'where sname = '" + sname + "'";
				b = stmt.executeUpdate(sql);
				if (b > 0) {
					System.out.println("修改学生生日成功");
				} else {
					System.out.println("修改学生生日失败");
				}
				break;

			default:
				break;
			}
		} else {
			System.out.println("没有查到学生信息");
		}
		JDBCUtils.close(conn, stmt, rs);

	}

	public static void deleteStudent() throws SQLException {
		System.out.println("请输入你要删除的学生姓名");
		String sname = sc.next();
		Connection conn = JDBCUtils.getConnection();
		Statement stmt = conn.createStatement();
		String sql = "delete from student where sname = '" + sname + "'";
		int b = stmt.executeUpdate(sql);
		if (b > 0) {
			System.out.println("删除学生成功");
		} else {
			System.out.println("删除学生失败");
		}
		JDBCUtils.close(conn, stmt);

	}

	public static void addStudent() throws SQLException {
		System.out.println("请输入学生姓名");
		String sname = sc.next();
		System.out.println("请输入学生年龄");
		int sage = sc.nextInt();
		System.out.println("请输入学生性别");
		String gender = sc.next();
		System.out.println("请输入学生分数");
		double score = sc.nextDouble();
		System.out.println("请输入学生生日");
		String brithday = sc.next();
		Connection conn = JDBCUtils.getConnection();
		String sql = "insert into student values(null," + sage + ",'" + sname + "','" + gender + "'," + score + ",'"
				+ brithday + "')";
		Statement stmt = conn.createStatement();
		int b = stmt.executeUpdate(sql);
		if (b > 0) {
			System.out.println("添加学生成功");
		} else {
			System.out.println("添加学生失败");
		}
		JDBCUtils.close(conn, stmt);

	}

}