Java学生管理选课系统

2301_76549057

已于 2023-10-07 18:21:44 修改

阅读量80

点赞数

文章标签： java 开发语言

于 2023-10-07 18:07:57 首次发布

本文链接：https://blog.csdn.net/2301_76549057/article/details/133596557

版权

一、写SQL语句

1、创建student数据表包含 id、name姓名、tel电话、sex性别字段

CREATE DATABASE schooljava;
USE schooljava;
CREATE TABLE student (
id INT(11),
name VARCHAR(25),
tel INT(11),
sex VARCHAR(6)
);
DESC student;

2、往student表中加10条数据

INSERT INTO student VALUES
    -> (1,'张三',1234567890,'男')，
    -> (2,'李黑',1564481133,'男'),
    -> (3,'小明',1215541841,'男')
    -> (4,'老六',1234567890,'男'),
    -> (5,'小辉',1395482655,'男');
    -> (6,'小红',1595262000,'女'),
    -> (7,'王女',1825523665,'女');
    -> (8,'小兰',1234567890,'女');
    -> (9,'小刘',1234567890,'男'),
    -> (10,'小郑',1234567890,'男');

二、写Java代码（要求只用PreparedStatement对象，变化的值都用?代替）

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;
 
public class schooljava {
	Connection connection;
 
	public static void main(String[] args) throws SQLException {
		schooljava studentsql=new schooljava();
		studentsql.getConnection();
		System.out.println("查询所有学生数据，结果为：");
		studentsql.selectAll();
		System.out.println("添加三条学生数据,结果为：");
		studentsql.add(11,"嘿嘿","133586246","男");
		studentsql.add(12,"黑黑","139565655","男");
		studentsql.add(13,"红红","133656859","女");
		studentsql.selectAll();
		System.out.println("查询中电话以182开头的所有学生信息,结果为：");
		studentsql.selectPart182();
		System.out.println("修改第5条数据,结果为：");
		studentsql.update5(5,"131242353");
		studentsql.selectAll();
		System.out.println("查询男性所有学生信息,结果为：");
		studentsql.selectMan();
		System.out.println("修改第10条数据,结果为：");
		studentsql.update10(10,"大帅哥");
		studentsql.selectAll();
		System.out.println("查询中电话以139开头的所有学生信息,结果为：");
		studentsql.selectPart139();
		System.out.println("删除第3条数据,结果为：");
		studentsql.delete(3);
		studentsql.selectAll();
		System.out.println("查询女性所有学生信息,结果为：");
		studentsql.selectWoman();
		System.out.println("查询所有学生数据，结果为：");
		studentsql.selectAll();
		studentsql.close();
	}	
	
	//查询女性所有学生信息
	public void selectWoman() throws SQLException {
		//获取statement对象
		PreparedStatement preparedStatement=
		connection.prepareStatement("select*from student where sex='女';");
				//执行SQL语句返回结果集
		ResultSet resultSet=preparedStatement.executeQuery();
					//遍历结果集
		while(resultSet.next()) {
			System.out.print(resultSet.getInt("id")+" ");
			System.out.print(resultSet.getString("name")+" ");
			System.out.print(resultSet.getString("tel")+" ");
			System.out.println(resultSet.getString("sex")+" ");
						}
					//关闭连接释放资源
					resultSet.close();
					preparedStatement.close();
	}
 
 
	//删除student表中第3条数据
	public void delete(int id) throws SQLException {
		//获取statement对象
		PreparedStatement preparedStatement=
		connection.prepareStatement("delete from student where id= ?;");
		preparedStatement.setInt(1,id);
					//执行SQL语句返回结果集
    	preparedStatement.executeUpdate();							
	}
 
	//查询中电话以139开头的所有学生信息
	public void selectPart139() throws SQLException {
		//获取statement对象
				PreparedStatement preparedStatement=
				connection.prepareStatement("select*from student where tel like '139%';");
				//执行SQL语句返回结果集
				ResultSet resultSet=preparedStatement.executeQuery();
				//遍历结果集
				while(resultSet.next()) {
					System.out.print(resultSet.getInt("id")+" ");
					System.out.print(resultSet.getString("name")+" ");
					System.out.print(resultSet.getString("tel")+" ");
					System.out.println(resultSet.getString("sex")+" ");
					}
				//关闭连接释放资源
				resultSet.close();
				preparedStatement.close();							
				}
 
	//修改student表中第10条数据，姓名改为“大帅哥”
	public void update10(int id, String name) throws SQLException {
		//获取statement对象
				PreparedStatement preparedStatement=
		connection.prepareStatement("update student set name= ? where id= ?;");
				preparedStatement.setString(1, name);
				preparedStatement.setInt(2, id);
				//获取statement对象
				preparedStatement.executeUpdate();
			}		
 
	//查询所有男性的学生信息
	public void selectMan() throws SQLException {
		//获取statement对象
	PreparedStatement preparedStatement=
	connection.prepareStatement("select*from student where sex='男';");
			//执行SQL语句返回结果集
	ResultSet resultSet=preparedStatement.executeQuery();
				//遍历结果集
	while(resultSet.next()) {
		System.out.print(resultSet.getInt("id")+" ");
		System.out.print(resultSet.getString("name")+" ");
		System.out.print(resultSet.getString("tel")+" ");
		System.out.println(resultSet.getString("sex")+" ");
					}
				//关闭连接释放资源
				resultSet.close();
				preparedStatement.close();					
				}
		
//修改第5条数据
	public void update5(int id,String tel) throws SQLException {
		//获取statement对象
		PreparedStatement preparedStatement=
connection.prepareStatement("update student set tel= ? where id= ?;");
		preparedStatement.setString(1, tel);
		preparedStatement.setInt(2, id);
		//获取statement对象
		preparedStatement.executeUpdate();
	}
	
 
//查询电话以182开头的所有学生信息
public void selectPart182() throws SQLException {
	//获取statement对象
		PreparedStatement preparedStatement=
		connection.prepareStatement("select*from student where tel like '182%';");
		//执行SQL语句返回结果集
		ResultSet resultSet=preparedStatement.executeQuery();
		//遍历结果集
		while(resultSet.next()) {
			System.out.print(resultSet.getInt("id")+" ");
			System.out.print(resultSet.getString("name")+" ");
			System.out.print(resultSet.getString("tel")+" ");
			System.out.println(resultSet.getString("sex")+" ");
			}
		//关闭连接释放资源
		resultSet.close();
		preparedStatement.close();		
		}
 
	//添加课程
	public void add(int id,String name,String tel,String sex) throws SQLException {
//获取statement对象
		PreparedStatement preparedStatement=connection.prepareStatement("insert into student value(?,?,?,?);");
		preparedStatement.setInt(1,id);
		preparedStatement.setString(2,name);
		preparedStatement.setString(3,tel);
		preparedStatement.setString(4,sex);
	//执行SQL语句返回结果集
		preparedStatement.executeUpdate();
	}
 
 
	public void getConnection() throws SQLException {
	//第一步：注册驱动
		DriverManager.registerDriver(new Driver());
	//第二步：获取连接
		connection=DriverManager.getConnection(
"jdbc:mysql://localhost:3306/school_java","root","1234");
	}
	
	//查询所有学生数据
	public void selectAll() throws SQLException {
	//第三步：获取statement对象
	PreparedStatement preparedStatement=
	connection.prepareStatement("select*from student;");
	//第四步：执行SQL语句返回结果集
	ResultSet resultSet=preparedStatement.executeQuery();
	//第五步：遍历结果集
	while(resultSet.next()) {
		System.out.print(resultSet.getInt("id")+" ");
		System.out.print(resultSet.getString("name")+" ");
		System.out.print(resultSet.getLong("tel")+" ");
		System.out.println(resultSet.getString("sex")+" ");
		}
	//第六步：关闭连接释放资源
	resultSet.close();
	preparedStatement.close();
		
	}
	public void close()throws SQLException {
		connection.close();
	}
}

运行结果如下：

三、查询课程



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

import com.mysql.jdbc.Driver; 

public class JDBCDemo {
	Connection connection;

	public static void main(String[] args) throws SQLException {
		JDBCDemo mysqlDemo = new JDBCDemo();
		mysqlDemo.getConnection();
		System.out.println("查询所有课程，结果为：");
		mysqlDemo.selectAll();
		System.out.println("新增4号课程名为WEB");
		mysqlDemo.add(4,"WEB");
		System.out.println("查询所有课程，结果为：");
		mysqlDemo.selectAll();
		System.out.println("修改3号课程，改为PhotoShop");
		mysqlDemo.update(3,"PhotoShop");
		System.out.println("查询所有课程，结果为：");
		mysqlDemo.selectAll();
		System.out.println("删除4号课程");
		mysqlDemo.delete(4);
		System.out.println("查询所有课程，结果为：");
		mysqlDemo.selectAll();
		mysqlDemo.close();
	}
	
	public void delete(int id) throws SQLException {
//		第三步: 获取statement对象
		PreparedStatement prepareStatement = 
				connection.prepareStatement
				("delete from course where id = ?;");
		prepareStatement.setInt(1, id);
//		第四步: 执行SOL语句
		prepareStatement.executeUpdate();
	}

	public void update(int id, String name) {
		
	}

	public void add(int id, String name) throws SQLException {
//		第三步:获取statement对象
		PreparedStatement prepareStatement = 
				connection.prepareStatement
				("insert into course values (?,?);");
		prepareStatement.setInt(1, id);
		prepareStatement.setString(2, name);
	}
	
	//注册驱动并获取链接的方法
	public void getConnection() throws SQLException {
//		第一步:注册驱动
		DriverManager.registerDriver(new Driver());
//		第二步:获取连接
		connection = DriverManager.getConnection
				("jdbc:mysql://localhost:3306/school_java","root","1234");			
	}
	
	//查询表course中的所有数据
	public void selectAll() throws SQLException{
//		第三步:获取statement对象
		PreparedStatement prepareStatement = connection.prepareStatement("select * from course;");
//		第四步: 执行SQL语句返回结果集
		ResultSet resultSet = prepareStatement.executeQuery();
//		第五步:遍历结果集
		while(resultSet.next()) {
			System.out.print(resultSet.getInt("id"));
			System.out.println(resultSet.getString("name"));
			
	}
//		第六步:关闭连接释放资源
		resultSet.close();
		prepareStatement.close();
		
	}
	//关闭链接   
	public void close() throws SQLException{
		connection.close();
	}

}

运行结果为：