数据库学生管理系统(StudentManagerSystemDB_V1.0)

package cn.sxb.day1102;

public class Student {
	private String name;
	private int number;
	private int age;
	private int sex;//0:男  1：女
	
	public Student(){
		
	}
	public Student(String name, int number, int age, int sex) {
		this.name = name;
		this.number = number;
		this.age = age;
		this.sex = sex;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getNumber() {
		return number;
	}

	public void setNumber(int number) {
		this.number = number;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public int getSex() {
		return sex;
	}

	public void setSex(int sex) {
		this.sex = sex;
	}
}

package cn.sxb.day1102;

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

public class UtilDB {
	private static final String URL = "jdbc:mysql://192.168.5.17:3306/db1606";
	private static final String USER = "test";
	private static final String PASSWORD = "123321";

	/**
	 * 构造方法加载驱动
	 */
	public UtilDB() {
		try {
			Class.forName("com.mysql.jdbc.Driver");// 加载mysql驱动
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 建立连接数据库
	 */
	public Connection getConnection() {
		Connection connect = null;
		try {
			connect = DriverManager.getConnection(URL, USER, PASSWORD);//试图建立到给定数据库URL的连接
			if (connect == null) {
				System.out.println("数据库连接失败");
			} else {
				System.out.println("数据库连接成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connect;
	}

	/**
	 * 关闭数据库连接
	 */
	public void closeConnection(Connection connect) {
		try {
			if (connect != null)
				connect.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

package cn.sxb.day1102;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class StudentDB extends UtilDB {
	public StudentDB() {

	}

	/**
	 * 创建表
	 */
	public void createTable() {
		Statement statement = null;
		Connection connect = getConnection();
		try {
			String sql = "create table  studentThree (" + "id int(11) not null auto_increment  primary key," // 主键
					+ "name varchar(10) not null," // 姓名
					+ "number int(4)  not null," // 学号
					+ "age int(3)," // 年龄
					+ "sex int(1)" // 性别
					+ ")";
			statement = connect.createStatement();
			statement.execute(sql);//执行给定的sql语句

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null)
					statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			closeConnection(connect);
		}
	}

	/**
	 * 向表中添加学生
	 */
	public void addStudent() {
		Student stu = new Student();
		Scanner scanner = new Scanner(System.in);
		System.out.println("请输入学生姓名：");
		String s = scanner.next();
		stu.setName(s);

		System.out.println("请输入学生学号：");
		stu.setNumber(scanner.nextInt());

		System.out.println("请输入学生年龄：");
		stu.setAge(scanner.nextInt());

		System.out.println("请输入学生性别：");
		stu.setSex(scanner.nextInt());
		
		Connection connect = getConnection();
		PreparedStatement ps = null;//sql语句被预编译并存储在PreparedStatement对象中
		String sql = "insert into studentThree(name,number,age,sex)values(?,?,?,?)";
		try {
			ps = connect.prepareStatement(sql); // 执行SQL语句
			
			ps.setString(1, stu.getName());
			ps.setInt(2, stu.getNumber());
			ps.setInt(3, stu.getAge());
			ps.setInt(4, stu.getSex());
			
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(ps != null)
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			closeConnection(connect);
		}
	}
	
	/**
	 * 显示学生
	 */
	public void showStudent(){
		Connection connect = getConnection();
		Statement statement = null;
		try {
			statement = connect.createStatement();
			String sql = "select * from studentThree";
			ResultSet rs = statement.executeQuery(sql);//执行给定的sql语句，单个ResultSet对象
			//ResultSet表示数据库结果集的数据表，通常通过执行查询数据库的语句生成。
			System.out.println("姓名\t学号\t年龄\t性别");
			while (rs.next()) {
				String name = rs.getString("name");
				int number = rs.getInt("number");
				int age =rs.getInt("age");
				int sex = rs.getInt("sex");
				System.out.println(name + "\t" + number + "\t" + age + "\t" + sex);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null)
					statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			closeConnection(connect);
		}
	}
	/**
	 * 查找学生
	 */
	public void selectStudent(){
		System.out.println("请输入查找的学生学号");
		Scanner scanner = new Scanner(System.in);
		int num = scanner.nextInt();
		Connection connect = getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "select * from studentThree where number = ?";
			ps = connect.prepareStatement(sql);
			ps.setInt(1,num);
			
			ResultSet rs = ps.executeQuery();// 返回结果集

			while (rs.next()) {
				String name = rs.getString("name");
				int number = rs.getInt("number");
				int age =rs.getInt("age");
				int sex = rs.getInt("sex");
					System.out.println("你要查找的学生是："+name);	
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			closeConnection(connect);
		}
	}
	/**
	 * 删除学生
	 */
	public void deleteStudent(){
		System.out.println("请输入删除的学生学号：");
		Scanner scanner = new Scanner(System.in);
		int num = scanner.nextInt();
		Connection connect = getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "delete from studentThree where number = ?";
			ps = connect.prepareStatement(sql);
			ps.setInt(1,num);
			ps.execute();
			
			System.out.println("删除成功");	
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			closeConnection(connect);
		}
		
	}
//	public static void main(String[] args){
//		StudentDB sdb = new StudentDB();
//		sdb.createTable();
		sdb.keyBoard();
		sdb.addStudent(stu);
		sdb.showStudent();
		sdb.selectStudent();
		sdb.deleteStudent();
//		
//	}
}

package cn.sxb.day1102;
import java.util.Scanner;
/**
 * 项目名称: 《学生信息管理系统》 英文:StudentManagerSystem
版本号:   V1.0
功能描术: 学生信息管理系统具有能对学生信息进行显示，添加，删除，查找(根据学号查找)，退出功能.
要求：
 1>.创建一个学生类 Student至少包含：姓名(name)、学号(number)两个属性;
         在Student类中封装操作方法:
         添加学生(addStudent)、显示学生列表(showAllStudent)、删除学生(deleteStudent)
         查找学生(findStudentByNumber)、退出系统(exit)
 2>在添加，删除学生时要求检查该学生是否存在，如果不存在需给出提示。
 3>.通过输入命令进行交互操作,控制台输出效果如下：

 =====  欢迎进入《学生信息管理系统》！ ====== 
 1 添加学生,2 显示学生列表,3 删除学生,4 查找学生,5 退出
<输入操作命令:>
1
======添加学生信息操作=========
请输入姓名:
小明
请输入学号:
1001
======添加成功 ! 学生姓名是 :小明,学号是 : 1001=========
<输入操作命令:>
2
姓名      学号
小明     1001
<输入操作命令:>
4
======查找学生信息操作=========
请输入学号:
1001
===您查找的学生是:小明====
<输入操作命令:>
3
======删除学生信息操作=========
请输入学号:
1001
====删除学生成功!学号是:1001的学生已从学生管理系统中删除====
<输入操作命令:>
5
==== 您已经退出《学生信息管理系统》  ====
 * 
 *
 */
public class StudentTest {	
	public static void main(String[] args) {
		StudentDB sdb = new StudentDB();
		System.out.println("=====欢迎进入学生管理系统=====");
		System.out.println(" 1 添加学生,2 显示学生列表,3 删除学生,4 查找学生,5 退出");
		Scanner scanner = new Scanner(System.in);
		System.out.println("=====添加学生信息操作====="); 
		
		for(;;){
			System.out.println("<请输入操作命令:>");
			int num =scanner.nextInt();
			switch(num){
			case 1:
				sdb.addStudent();
				break;
 		    case 2:
 		    	sdb.showStudent();
 		    	break;
 		    case 3:
 		    	sdb.deleteStudent();
 		    	break;
 		   case 4:
		    	sdb.selectStudent();
		    	break;
 		  case 5:
 			  System.out.println("你已经退出系统！");
		    	System.exit(0);
		    	break;
			default :
				break;
			}
		}
	}
}