学生管理系统作业

package 案例;

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 数据库 {
	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 couse 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 couse;");//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 couse where id = ?");
					preparedStatement.setInt(1, id);
					//执行Sql语句
					preparedStatement.executeUpdate();
				}
				
				//修改名字数据方法
				private void Name(int id, String name) throws SQLException {
					PreparedStatement preparedStatement = 
							connection.prepareStatement("update couse 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 couse 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 couse 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 couse 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
		数据库 s = new 数据库();
		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("查询表中电话以123开头的数据：");
		s.Tel(in.next());
		s.Statement();
		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();
	}

}

运行结果