学生管理系统数据库版

要求：

插入数据：

代码：

package Package13;

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

import com.mysql.cj.jdbc.Driver;
	
public class mysqlDemoTest {
	Connection connection;
	public static void main(String[] args) throws SQLException {
		// TODO Auto-generated method stub
		mysqlDemoTest mysqlDemoTest = new mysqlDemoTest();
		mysqlDemoTest.getConnection();
		System.out.println("查询学生表数据，结果为：");
		mysqlDemoTest.selectAll();
		System.out.println("新增学生表数据，结果为：");
		mysqlDemoTest.add(11,"刘备","18273643837","男");
		System.out.println("新增学生表数据，结果为：");
		mysqlDemoTest.add(12,"曹操","12873247638","男");
		System.out.println("新增学生表数据，结果为：");
		mysqlDemoTest.add(13,"小花","13928327828","女");
		System.out.println("查询学生表数据，结果为：");
		mysqlDemoTest.selectAll();
		System.out.println("查询电话号码是182开头的所有学生信息，结果为：");
		mysqlDemoTest.query();
		System.out.println("修改student表中第10条数据，电话改为13124235300，结果为：");
		mysqlDemoTest.modify(5,"13124235300");
		mysqlDemoTest.selectAll();
		System.out.println("查询student表中所有男性的学生信息，结果为：");
		mysqlDemoTest.query1();
		System.out.println("修改student表中第10条数据，姓名改为“吴名”，结果为：");
		mysqlDemoTest.modify1(10,"吴名");
		mysqlDemoTest.selectAll();
		System.out.println("查询电话号码是139开头的所有学生信息，结果为：");
		mysqlDemoTest.query2();
		System.out.println("删除student表中第3条数据，结果为：");
		mysqlDemoTest.delete(3);
		mysqlDemoTest.selectAll();
		System.out.println("查询student表中所有女性的学生信息，结果为：");
		mysqlDemoTest.query3();
		System.out.println("查询student表中所有数据，结果为：");
		mysqlDemoTest.selectAll();
		mysqlDemoTest.close();
			}
	private void query3() throws SQLException {
		// TODO Auto-generated method stub
		//第三步：获取statement对象
		PreparedStatement preparedStatement = connection.prepareStatement("select*from student where sex='女';");
		//第四步：执行SQL语句返回结果集
		ResultSet resultSet1 = preparedStatement.executeQuery();
		//第五步：遍历结果集
		while(resultSet1.next()) {
			System.out.print(resultSet1.getInt("id")+"  ");
			System.out.print(resultSet1.getString("name")+"  ");
			System.out.print(resultSet1.getString("tel")+"  ");
			System.out.print(resultSet1.getString("sex")+"  ");
			System.out.println();
		}
		//第六步：关闭连接释放资源
		preparedStatement.close();
	}
	public void delete(int id) throws SQLException {
		// TODO Auto-generated method stub
		PreparedStatement preparedstatement = connection.prepareStatement("delete from student where id = ?;");
		preparedstatement.setInt(1, id);
			//第四步: 执行SOL语句
			preparedstatement.executeUpdate();
			//第六步: 关闭连接释放资源
			preparedstatement.close();
	}
	public void query2() throws SQLException {
		// TODO Auto-generated method stub
		//第三步：获取statement对象
		PreparedStatement preparedStatement = connection.prepareStatement("select*from student where tel like'139%';");
		//第四步：执行SQL语句返回结果集
		ResultSet resultSet2 = preparedStatement.executeQuery();
				//第五步：遍历结果集
				while(resultSet2.next()) {
				System.out.print(resultSet2.getInt("id")+"  ");
				System.out.print(resultSet2.getString("name")+"  ");
				System.out.print(resultSet2.getString("tel")+"  ");
				System.out.print(resultSet2.getString("sex")+"  ");
				System.out.println();
			}
			//第六步：关闭连接释放资源
			preparedStatement.close();
	}
	public void modify1(int id,String name) throws SQLException {
		// TODO Auto-generated method stub
		// 第三步: 获取statement对象
		PreparedStatement preparedstatement = connection.prepareStatement("update student set name = ? where id = ?;");
		preparedstatement.setString(1,name);
		preparedstatement.setInt(2, id);
		//第四步: 执行SOL语句
		preparedstatement.executeUpdate( );
		//第六步: 关闭连接释放资源
			preparedstatement.close();
	}
	public void query1() throws SQLException {
		// TODO Auto-generated method stub
		//第三步：获取statement对象
		PreparedStatement preparedStatement = connection.prepareStatement("select*from student where sex='男';");
		//第四步：执行SQL语句返回结果集
		ResultSet resultSet1 = preparedStatement.executeQuery();
		//第五步：遍历结果集
		while(resultSet1.next()) {
			System.out.print(resultSet1.getInt("id")+"  ");
			System.out.print(resultSet1.getString("name")+"  ");
			System.out.print(resultSet1.getString("tel")+"  ");
			System.out.print(resultSet1.getString("sex")+"  ");
			System.out.println();
			}
		//第六步：关闭连接释放资源
		preparedStatement.close();
	}
	public void modify(int id,String tel) throws SQLException {
		// TODO Auto-generated method stub
		// 第三步: 获取statement对象
		PreparedStatement preparedstatement = connection.prepareStatement("update student set tel = ? where id = ?;");
		preparedstatement.setString(1,tel);
		preparedstatement.setInt(2, id);
		//第四步: 执行SOL语句
		preparedstatement.executeUpdate( );
		//第六步: 关闭连接释放资源
		preparedstatement.close();
		}
	public void query() throws SQLException {
		// TODO Auto-generated method stub
		//第三步：获取statement对象
		PreparedStatement preparedStatement = connection.prepareStatement("select*from student where tel like'182%';");
		//第四步：执行SQL语句返回结果集
		ResultSet resultSet1 = preparedStatement.executeQuery();
		//第五步：遍历结果集
		while(resultSet1.next()) {
			System.out.print(resultSet1.getInt("id")+"  ");
			System.out.print(resultSet1.getString("name")+"  ");
			System.out.print(resultSet1.getString("tel")+"  ");
			System.out.print(resultSet1.getString("sex")+"  ");
			System.out.println();
			}
		//第六步：关闭连接释放资源
		preparedStatement.close();
		}
	public  void add(int id, String name, String tel, String sex) throws SQLException {
		// TODO Auto-generated method stub
		PreparedStatement preparedStatement = connection.prepareStatement("insert into student values(?,?,?,?);");
		//第三步：获取statement对象
		preparedStatement.setInt(1, id);
		preparedStatement.setString(2,name);
		preparedStatement.setString(3, tel);
		preparedStatement.setString(4, sex);
		//第四步：执行SQL语句
		preparedStatement.executeUpdate();
		//第六步：关闭连接释放资源
		preparedStatement.close();
		}
		//注册驱动、获取连接
	public  void getConnection() throws SQLException {
		//第一步：注册驱动
		DriverManager.registerDriver(new Driver());
		//第二步：获取连接
		connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/school_javas","root","12345");
		}
		//查询学生表中所有信息
	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.getString("tel")+"  ");
			System.out.print(resultSet.getString("sex")+"  ");
			System.out.println();
			}
		//第六步：关闭连接释放资源
		resultSet.close();
		preparedStatement.close();
		}
		//关闭连接
	public void close() throws SQLException {
		connection.close();
	}
}