用JDBC实现学生管理系统

DBLink

package com.zzu.tool.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBLink {

	private Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");// 加载驱动
			String url = "jdbc:mysql://127.0.0.1:3306/test";
			return DriverManager.getConnection(url, "root", "root");// 获取连接
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public ResultSet select(String sql, IRowMapper rowMapper) {// 接口无法创建对象，所以rowMapper参数一定指向IRowMapper接口实现类对象
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = getConnection();// 获取连接
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);// 执行sql,将查询的数据存到ResultSet类型的变量中
			rowMapper.rowMapper(resultSet);// 因为rowMapper参数指向IRowMapper接口实现类对象，所以此处将调用接口实现类中所实现的rowMapper方法 多态
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(resultSet, statement, connection);
		}
		return null;
	}

	public boolean exist(String sql) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select id,name,mobile,address from student");
			return resultSet.next();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(resultSet, statement, connection);
		}
		return false;
	}

	public boolean update(String sql) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = getConnection();
			statement = connection.createStatement();
			int result = statement.executeUpdate(sql);
			// statement.close();//如果上面代码出现异常，则该行代码及其下面代码无法执行，比如sql语句语法错误，则statement和connection无法释放
			// connection.close();
			return result > 0;// 处理结果
		} catch (Exception e) {
			e.printStackTrace();
		} finally {// 即便有异常也会执行代码
			close(statement, connection);
		}
		return false;
	}

	private void close(Statement statement, Connection connection) {
		try {
			if (statement != null) {// 可能由于异常导致statement没有赋值，比如url出错
				statement.close();
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void close(ResultSet resultSet, Statement statement, Connection connection) {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		close(statement, connection);
	}
}

接口

package com.zzu.tool.db;

import java.sql.ResultSet;

@FunctionalInterface
public interface IRowMapper {

	void rowMapper(ResultSet rs);
}

学生管理系统

package com.zzu.main;

import java.sql.SQLException;
import java.util.Scanner;

import com.zzu.tool.db.DBLink;

public class Main {

	private static DBLink db = new DBLink();

	public static void main(String[] args) {
		System.out.println("*********************************");
		System.out.println("*\t\t\t\t*");
		System.out.println("*\t欢迎使用学生信息管理系统\t*");
		System.out.println("*\t\t\t\t*");
		System.out.println("*********************************");
		while (true) {
			menu();
		}
	}

	static void menu() {
		System.out.println("1、添加学生信息");
		System.out.println("2、删除学生信息");
		System.out.println("3、修改学生信息");
		System.out.println("4、查询学生信息");
		System.out.println("请输入操作，以Enter键结束:");
		Scanner scanner = new Scanner(System.in);
		int option = scanner.nextInt();
		switch (option) {
		case 1: {
			System.out.println("请输入学号：");
			String id = scanner.next();
			String sql = "select name from student where id = '" + id + "'";
			if (db.exist(sql)) {
				System.out.println("学号已存在,操作中止！");
				return;
			}
			System.out.println("请输入姓名：");
			String name = scanner.next();
			System.out.println("请输入手机号：");
			String mobile = scanner.next();
			System.out.println("请输入地址：");
			String address = scanner.next();
			sql = "insert into student (id,name,mobile,address) values ('" + id + "','" + name + "','" + mobile + "','" + address + "')";
			if (db.update(sql)) {
				System.out.println("添加成功");
				return;
			}
			System.out.println("添加失败！");
			break;
		}
		case 2: {
			System.out.println("请输入学号：");
			String id = scanner.next();
			String sql = "select name from student where id = '" + id + "'";
			if (db.exist(sql)) {
				System.out.println("学号已找到");
				return;
			}
			sql = "delete from student where id ='" + id + "'";
			if (db.update(sql)) {
				System.out.println("删除成功");
				return;
			}
			System.out.println("删除失败！");
			break;
		}
		case 3:{
			System.out.println("请输入学号：");
			String id = scanner.next();
			String sql = "select name from student where id = '" + id + "'";
			if (db.exist(sql)) {
				System.out.println("学号已存在,操作中止！");
				return;
			}
			System.out.println("请输入新的姓名：");
			String name = scanner.next();
			System.out.println("请输入新的手机号：");
			String mobile = scanner.next();
			System.out.println("请输入新的地址：");
			String address = scanner.next();
			sql="update student set name=' " + name + " ',' " + mobile + " ',' " + address + " ',where id = ' " + id + " '";
			if (db.update(sql)) {
				System.out.println("修改成功");
				return;
			}
			System.out.println("修改失败！");
			break;
		}
		case 4:{
			System.out.println("请输入学号：");
			String id = scanner.next();
			String sql = "select name from student where id = '" + id + "'";
			if (!db.exist(sql)) {
				System.out.println("学号不存在,操作终止！");
				return;
			}
			sql = "select id,name,mobile,address from student where id = ' " + id + " '";
			
			//有名内部类
			/*class RowMapper implements IRowMapper {
				@Override
				public void rowMapper(ResultSet rs) {
					try {
						if (rs.next()) {
							String id = rs.getString("id");
							String name = rs.getString("name");
							String mobile = rs.getString("mobile");
							String address = rs.getString("address");
							System.out.println(id + "," + name + "," + mobile + "," + address);
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
			db.select(sql, new RowMapper());*/
			
			//匿名内部类
			/*db.select(sql, new IRowMapper() {
				@Override
				public void rowMapper(ResultSet rs) {
					try {
						if (rs.next()) {
							String id = rs.getString("id");
							String name = rs.getString("name");
							String mobile = rs.getString("mobile");
							String address = rs.getString("address");
							System.out.println(id + "," + name + "," + mobile + "," + address);
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			});*/
			
			//Lambada表达式
			db.select(sql, (rs) -> {
				try {
					if (rs.next()) {
						String name = rs.getString("name");
						String mobile = rs.getString("mobile");
						String address = rs.getString("address");
						System.out.println(id + "," + name + "," + mobile + "," + address);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			});
			
			break;
		}
		default:
			System.out.println("I'm Sorry,there is not the " + option + " option,please try again.");
		}
	}
}