数据库实现学生管理系统

 主体

package com.zzu.main;

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

import com.zzu.tool.db.DBLink;
import com.zzu.tool.db.IRowMapper;

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、查询学生信息");//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 id 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 id from student where id = '"+id+"'";
				if(db.exist(sql)) {
				sql = "delete from student where id = '"+id+"'";
				if(db.update(sql)) {
					System.out.println("删除成功");
					return;
				}
				System.out.println("删除失败");
				}
				System.out.println("学号不存在！删除失败");
				
				break;
			}
			case 3:{
				System.out.println("请输入学号：");
				String id = scanner.next();
				String sql = "select id 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 = '"+mobile+"',address = '"+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 id from student where id = '"+id+"'";
				if(!db.exist(sql)) {
					System.out.println("学号不存在！");
					return;
				}
				sql = "select id,name,mobile,address from student where id = '"+id+"'";
				db.select(sql, (rs) -> {//4.Lambda表达式
					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();
					}
				});
				/*class RowMapper implements IRowMapper{//1.直接用内部类

					@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(){//2.匿名内部类
					@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();
						}
					}
				});*/
				/*IRowMapper rowMapper = new IRowMapper(){//3.同上，形式更清晰
						@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,rowMapper);*/
				break;
			}
			default:
				System.out.println("I'm Sorry,there is not the "+option+" option,please try again.");
		}
	}
}

工具类(尚未完善，完整详见博客jdbc工具类：https://blog.csdn.net/Gong_Zheng6/article/details/106306017)

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 {
	public static 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 boolean update(String sql) {
		Connection connection =null;
		Statement statement = null;
		try {
			connection = getConnection();
			System.out.println(connection);
			statement = connection.createStatement();//获取statement对象
			int affect = statement.executeUpdate(sql);//执行sql语句，返回影响行数
			if(affect>0) {
				System.out.println("OK");
			}else
			System.out.println("NO");
			/*statement.close();//如果上面代码出现异常，则该行代码及其下面代码无法执行，所以资
			 * 源无法释放；比如sql语句语法错误，则statement和connection无法释放
		      connection.close();*/
			return affect>0;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
		      close(statement,connection);
		}
		return false;
	}
	public void select(String sql,IRowMapper rowMapper) {//使用接口，达到了公用性
	    Connection connection = null;
	    Statement statement =null;
	    ResultSet resultSet=null;
	    try {
	      connection = getConnection();
	      statement = connection.createStatement();
	      resultSet= statement.executeQuery(sql);
	      rowMapper.rowMapper(resultSet);
	    } catch (Exception e) {
	      e.printStackTrace();
	    }finally {
		      close(statement,connection,resultSet);
	    }
	}
	public static boolean exist(String sql) {
	    Connection connection = null;
	    Statement statement =null;
	    ResultSet resultSet=null;
	    try {
	      connection = getConnection();
	      statement = connection.createStatement();
	      resultSet= statement.executeQuery(sql);//执行sql,将查询的数据存到ResultSet类型的变量中
	      return resultSet.next();
	    } catch (Exception e) {
	      e.printStackTrace();
	    }finally {
	      close(statement,connection,resultSet);
	    }
	    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(Statement statement,Connection connection,ResultSet resultSet) {
		 try {
		        if(resultSet!=null) {
		          resultSet.close();
		        }
		      } catch (SQLException e) {
		        e.printStackTrace();
		      }
		
		try {
	        if(statement!=null) {
	          statement.close();
	        }
	      } catch (SQLException e) {
	        e.printStackTrace();
	      }
	      try {
	        if(connection!=null) {
	          connection.close();
	        }
	      } catch (SQLException e) {
	        e.printStackTrace();
	      }
	}
	
	
}

package com.zzu.tool.db;

import java.sql.ResultSet;
@FunctionalInterface
public interface IRowMapper {
	void rowMapper(ResultSet rs);
}

关于使用接口，我曾有所疑问：

public static void select(String sql) {//为何这样写不合理？
	    Connection connection = null;
	    Statement statement =null;
	    ResultSet resultSet=null;
	    try {
	      connection = getConnection();
	      statement = connection.createStatement();
	      resultSet= statement.executeQuery(sql);
	      while(resultSet.next()) {
	    	  String id = resultSet.getString("id");
	    	  String name = resultSet.getString("name");
	    	  String address = resultSet.getString("address");
	    	  String mobile = resultSet.getString("mobile");
	      }
	    } catch (Exception e) {
	      e.printStackTrace();
	    }finally {
		      close(statement,connection,resultSet);
	    }
	  }
#######################################

public void select(String sql,IRowMapper rowMapper) {//使用接口，达到了公用性
	    Connection connection = null;
	    Statement statement =null;
	    ResultSet resultSet=null;
	    try {
	      connection = getConnection();
	      statement = connection.createStatement();
	      resultSet= statement.executeQuery(sql);
	      rowMapper.rowMapper(resultSet);
	    } catch (Exception e) {
	      e.printStackTrace();
	    }finally {
		      close(statement,connection,resultSet);
	    }
	}

解答：

这样写虽然可以实现查询功能，但是作为工具类，他就丧失了公用性，要实现不同功能都需要修改此方法，无法达到工具类的效果。