JDBC实现学生管理系统(连接到MySQL数据库)

工具：

package main;

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

public class DBlink {
	/*try {
		Class.forName("com.mysql.jdbc.Driver");//加载驱动
		String url = "jdbc:mysql://127.0.0.1:3306/test1";//指定连接到哪个数据库，127.0.0.1是本机
		connection=DriverManager.getConnection(url, "root", "root");获取数据库连接
		statement = connection.createStatement();
		long result = statement.executeLargeUpdate(sql);
		statement.close();//如果上面代码出现异常，则该行代码及其下面代码无法执行，所以资源无法释放；比如sql语句语法错误，则statement和connection无法释放
    	connection.close();*/
	private static Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://127.0.0.1:3306/test1";
			return DriverManager.getConnection(url, "root","root");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static void select(String sql,IRowMapper rowMapper ) {//接口无法创建对象，所以rowMapper参数一定指向IRowMapper接口实现类对象，rowMapper上转型对象
		//此处的rowMapper就是一个形参，到时候接口实现类里的实参再传给它
		Connection connection =null;
		Statement statement =null;
		ResultSet resultSet =null;
		
		try {
			
			connection = getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			rowMapper.rowMapper(resultSet);//因为rowMapper参数指向IRowMapper接口实现类对象，所以此处将调用接口实现类中所实现的rowMapper方法  多态。
			
			
		} catch (Exception e) {

			e.printStackTrace();
		}finally {
			close(resultSet,statement,connection);
		}
	}


	public boolean exist(String sql) {
		Connection connection =null;
		Statement statement =null;
		ResultSet resultSet =null;
		
		try {		
			connection = getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			return resultSet.next();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(resultSet!=null){
				resultSet.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			close(resultSet,statement,connection);
		
		}
		return false;
	}
	
	
	
	Connection connection=null;
	Statement statement=null;
	public boolean update(String sql) {
		try {


			connection=getConnection();
			statement = connection.createStatement();
			long result = statement.executeLargeUpdate(sql);
			
			return result>0;
			
		} catch (Exception e) {
			
			e.printStackTrace();
		}finally {
			close(statement,connection);
		}
		return false;
	}
	private static void close(Statement statement,Connection connection) {
		try {
			if(statement!=null) {
			statement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(connection!=null) {			
			connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
	}
	
	
	private static void close(ResultSet resultSet,Statement statement,Connection connection) {//重载
		try {
			if(resultSet!=null){
			resultSet.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		close(statement,connection);
	
	}

	public static boolean upDate(String sql) {
		return false;
	}

	public static boolean select(String sql) {
		return false;
	}

	
	
}

package main;

import java.sql.ResultSet;

public interface IRowMapper {

	void rowMapper(ResultSet rs);
}

学生管理系统：

package main;

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

public class Main {

	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、查询学生信息");//name
		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 students where id = '"+id+"'";
				
				if(DBlink.select(sql)) {
					System.out.println("存在"+id+"失败");
					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 students (id,name,mobile,address) values ('','','','','')";
				if(DBlink.upDate(sql)) {
					System.out.println("添加成功");
				}
				System.out.println("失败");
			
				
			break;
			}
			case 2:{
				System.out.println("请输入学号：");
				String id = scanner.next();
				String sql = "select name from students where id = '"+id+"'";
				
				if(!DBlink.select(sql)) {
					System.out.println("不存在"+id+"失败");
					return;
				}
				sql="delete from user_info where id = '"+id+"";
				if(DBlink.upDate(sql)) {
					System.out.println("删除成功");
				}
				System.out.println("删除失败");
				break;
			}
			case 3:{
				System.out.println("请输入学号：");
				String id = scanner.next();
				String sql = "select name from students where id = '"+id+"'";
				
				if(!DBlink.select(sql)) {
					System.out.println("不存在"+id+"失败");
					return;
				}
				System.out.println("请输入姓名：");
				String name = scanner.next();
				System.out.println("请输入号码：");
				String mobile = scanner.next();
				System.out.println("请输入地址：");
				String address = scanner.next();
				sql = "upDate students set name = '"+name+"',mobile='"+mobile+"',address = '"+address+"'";
				if(DBlink.upDate(sql)) {
					System.out.println("添加成功");
				}
				System.out.println("添加失败");
				break;
			}
			case 4:{//代码使用内部类，可简化：
				System.out.println("请输入学号：");
				String id = scanner.next();
				String sql = "select name from students where id = '"+id+"'";
				
				if(!DBlink.select(sql)) {
					System.out.println("不存在"+id+"失败");
					return;
					
				}
				/*class RowMapper implements IRowMapper{//定义一个内部类

					@Override
					public void rowMapper(ResultSet rs) {
						try {
							while(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();
						}
						
					}
					DBlink.select(sql,new RowMapper());
					break;
				}*/
				
				/*DBlink.select(sql,new IRowMapper()){//匿名内部类的调用
				@Override
					public void rowMapper(ResultSet rs) {
						try {
							while(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();
						}
						
					}
				});
				break;
			}*/

			DBlink.select(sql,( rs)-> {//函数式接口简化
					try {
						while(rs.next()) {
							//String id = rs.getString("id");//上面已经使用id了，这里不是内部类了，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();
					}	
				});
				break;
			}
			default:
				System.out.println("I'm Sorry,there is not the "+option+" option,please try again.");
		}
	}
}