学生管理系统 （数据库版）

要求

登入数据库
建表

插入数据

Java语句

 
	 
	import java.sql.Connection;
	 
	import java.sql.DriverManager;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	 
	import com.mysql.cj.jdbc.Driver;
	import java.util.Scanner;
	public class JDBCDemo1 {
		Connection connection;
		public int id;
		public String name;
		public String tel;
		public String sex;
		
		//注册驱动 获取连接
			public void DriverManager() throws SQLException {
				//第一步:注册驱动		DriverManagere驱动
				DriverManager.registerDriver(new Driver());
				//第二步:获取连接
				connection = DriverManager.getConnection
						("jdbc:mysql://localhost:3306/school_java","root","1234");		//1234密码
				//localhost：本地主机		3306：数据库端口号		school_java：具体数据库名字	root：用户名
			}
			
			//添加数据
			public void add(int id, String name, String tel, String sex) throws SQLException {
				//第三步:获取statement对象
				PreparedStatement preparedStatement = 
						connection.prepareStatement("insert into student values(?,?,?,?);");
				preparedStatement.setInt(1, id);
				preparedStatement.setString(2, name);
				preparedStatement.setString(3, tel);
				preparedStatement.setString(4, sex);
				//第四步: 执行SQL语句返回结果集	
				preparedStatement.executeUpdate();//executeUpdate只要修改数据就用，不管是增删改
				}
			
			//获取couse表中的所有数据
			public void Statement() throws SQLException {
				//第三步:获取statement对象
				PreparedStatement preparedStatement = 
						connection.prepareStatement("select * from student;");//MySQL语句
				//第四步: 执行SQL语句返回结果集
			 	ResultSet resultset = preparedStatement.executeQuery();//executeQuery只有查询用
			 	//集合存储
			 	
			 	//第五步:遍历结果集
			 	while(resultset.next()) {
			 		//next：判断是否有下一条数据
			 		System.out.print(resultset.getInt("id") + " ");
			 									//数据库里什么字段，get方法就填什么类型
			 		System.out.println(resultset.getString("name")+ " " + resultset.getString("tel")
			 		+ " " + resultset.getString("sex"));
	 
			 	}
			 	//第六步:关闭连接释放资源
			 	resultset.close();
			 	preparedStatement.close();
			}
			
			//关闭连接
			public void close() throws SQLException {
				connection.close();
			}
			
			//删除数据
					private void delete(int id) throws SQLException {
						PreparedStatement preparedStatement =
								connection.prepareStatement("delete from student where id = ?");
						preparedStatement.setInt(1, id);
						//执行Sql语句
						preparedStatement.executeUpdate();
					}
					
					//修改名字数据方法
					private void Name(int id, String name) throws SQLException {
						PreparedStatement preparedStatement = 
								connection.prepareStatement("update student set name = ? where id = ?;");
						preparedStatement.setString(1, name);
						preparedStatement.setInt(2, id);
						//执行Sql语句
						preparedStatement.executeUpdate();
					}
					
					//查询电话
					public void Tel(String tel) throws SQLException {
						//第三步:获取statement对象
						PreparedStatement preparedStatement = 
								connection.prepareStatement("select * from student where tel like ?;");//不能写入通配符
						preparedStatement.setString(1, tel);
						//第四步: 执行SQL语句返回结果集
					 	ResultSet resultset = preparedStatement.executeQuery();
					 	while(resultset.next()) {
					 		//next：判断是否有下一条数据
					 		System.out.print(resultset.getInt("id") + " ");
					 									//数据库里什么字段，get方法就填什么类型
					 		System.out.println(resultset.getString("name")+ " " + resultset.getString("tel")
					 		+ " " + resultset.getString("sex"));
	 
					 	}
					}
					
					//修改电话数据方法
					private void Tel1(int id, String tel) throws SQLException {
						PreparedStatement preparedStatement = 
								connection.prepareStatement("update student set tel = ? where id = ?;");
						preparedStatement.setString(1, tel);
						preparedStatement.setInt(2, id);
						//执行Sql语句
						preparedStatement.executeUpdate();
					}
					
					//查询性别
					public void Sex(String sex) throws SQLException {
						//第三步:获取statement对象
						PreparedStatement preparedStatement = 
								connection.prepareStatement("select * from student where sex = ? ");//MySQL语句
						preparedStatement.setString(1,sex);
						//第四步: 执行SQL语句返回结果集
					 	ResultSet resultset = preparedStatement.executeQuery();
					 	while(resultset.next()) {
					 		//next：判断是否有下一条数据
					 		System.out.print(resultset.getInt("id") + " ");
					 									//数据库里什么字段，get方法就填什么类型
					 		System.out.println(resultset.getString("name")+ " " + resultset.getString("tel")
					 		+ " " + resultset.getString("sex"));
	 
					 	}
					}
		public static void main(String[] args) throws SQLException {
			// TODO Auto-generated method stub
			JDBCDemo1 s = new JDBCDemo1();
			Scanner in = new Scanner(System.in);
			//连接数据库
			s.DriverManager();
			System.out.println("查询所有人信息：");
			s.Statement();
//			System.out.println("向表中新增三条数据：");
//			s.add(11, "王总","16688775522", "男");
//			s.add(12, "芳芳","15184057973", "女");
//			s.add(13, "黄总","18199058864", "男");
//			s.Statement();
			System.out.println("查询表中电话以137开头的数据：");
			s.Tel(in.next());
 
//			System.out.println("修改第5条数据，电话改为“13124235300");
//			s.Tel1(5, "13124235300");
//			s.Statement();
			System.out.println("查询性别为男的数据：");
			s.Sex(in.next());
//			System.out.println("修改id为10的名字改为大帅哥：");
//			s.Name(10, "大帅哥");
//			s.Statement();
			System.out.println("查询表中电话以131开头的数据：");
			s.Tel(in.next());
//			System.out.println("删除表中第3条数据：");
//			s.delete(3);
//			s.Statement();
			System.out.println("查询性别为女的数据：");
			s.Sex(in.next());
			System.out.println("查询所有人信息：");
			s.Statement();
			s.close();
		}
	 
	}