用JDBC编写学生管理系统

首先准备前期需要的配置:class文件,一个jar包。

添加jar包过程:右键项目名->new->Folder->Folder name中填入lib(名称可以任意填),之后jar文件拷入lib其中,之后,右键lib中的jar包Build Path -> Add to Build Path

这样前期准备就ok了

Main.java文件代码:

package com.zzu.main;

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

import com.zzu.tool.db.DLink;

public class Main {
	private static DLink db = new DLink();

	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 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='" + mobile + "',address='" + address + "'";
			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(); }
			 * 
			 * } });
			 */ /// 匿名内部类
//改进:
			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.");
		}
	}
}

DLink.java代码:

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 DLink {
	

	
	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 void 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, connection,statement);
		}
	}
	public  boolean exist(String sql) {
	    Connection connection = null;
	    Statement statement =null;
	    ResultSet resultSet=null;
	    try {
	      Class.forName("com.mysql.jdbc.Driver");//加载驱动
	      String url = "jdbc:mysql://127.0.0.1:3306/test";
	      connection = DriverManager.getConnection(url, "root", "root");//获取连接
	      statement = connection.createStatement();
	      resultSet= statement.executeQuery(sql);//执行sql,将查询的数据存到ResultSet类型的变量中
	      return resultSet.next();
	    } catch (Exception e) {
	      e.printStackTrace();
	    }finally {
	    	close(resultSet, connection,statement);
	    }
		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 (connection,statement);
		}
		return false;
		
	}
	private void close(Connection connection,Statement statement) {
		 try {
			 if(connection!=null) {//如果不加,上面代码若出错,则此时connection为null,会出现空指针异常
				 connection.close();
			 } 
		} catch (SQLException e) {
		
			e.printStackTrace();
		}
		 
		 try {
			 if(statement!=null) {//如果不加,上面代码若出错,则此时statement为null,会出现空指针异常
				 statement.close();
			 }
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
	}
	private void close(ResultSet resultSet,Connection connection,Statement statement) {
		try {
	        if(resultSet!=null) {
	          resultSet.close();
	        }
	      } catch (SQLException e) {
	        e.printStackTrace();
	      }
		close (connection,statement);
	}
	
}

IRowMapper.java文件:

package com.zzu.tool.db;
import java.sql.ResultSet;

@FunctionalInterface
    public interface IRowMapper{
    	void rowMapper (ResultSet rs);
    }

 

展开阅读全文
©️2019 CSDN 皮肤主题: 深蓝海洋 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读