java22 jdbc作业

最新推荐文章于 2022-04-28 22:25:20 发布

原创最新推荐文章于 2022-04-28 22:25:20 发布 · 317 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java22作业 # jdbc

本文介绍了一个使用Java进行数据库操作的实例，包括查询女性学生数量、更新特定学生信息及查询成绩大于60分的女性学生详情。通过PreparedStatement和Statement对象，演示了如何执行参数化的SQL查询和更新。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb2", "root", "root");
		// (1)查询女性,成绩80以上的学生数量
		String sql = "SELECT COUNT(*) FROM student1 WHERE sex= ? AND score >?";
		PreparedStatement statement = connection.prepareStatement(sql);
		statement.setString(1, "女");
		statement.setInt(2, 80);
		ResultSet rs = statement.executeQuery();
		while (rs.next()) {
			int n = rs.getInt(1);
			System.out.println(n);
		}
		rs.close();
		statement.close();
		connection.close();
	}

	@Test
	public void testUpdate() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb2", "root", "root");
		// (2)将姓张的男同学的的成绩改为100
		String sql = "UPDATE student1 SET score=? WHERE name like ? AND sex =?";
		PreparedStatement statement = connection.prepareStatement(sql);
		statement.setInt(1, 100);
		statement.setString(2, "张%");
		statement.setString(3, "男");
		// Statement statement = connection.createStatement();
		int n = statement.executeUpdate();
		if (n == 1) {
			System.out.println("成功");
		} else {
			System.out.println("不成功");
			statement.close();
			connection.close();
		}
	}

	@Test
	public void testSelect() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb2", "root", "root");
		// (3)查询成绩大于60的女性,显示姓名,性别,成绩
		String sql = "SELECT name,sex,score FROM student1 WHERE sex=? AND score>?";
		PreparedStatement statement = connection.prepareStatement(sql);
		statement.setString(1, "女");
		statement.setInt(2, 60);

		ResultSet rs = statement.executeQuery();
		while (rs.next()) {
			System.out.println(rs.getString(1) + "," + rs.getString(2) + "," + rs.getString(3));
		}
		rs.close();
		statement.close();
		connection.close();
	}

	@Test
	public void testSelect1() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb2", "root", "root");
		// (5)按照分数从小到大的顺序打印分数大于总平均分的学员信息(id-name-sex-score)
		String sql = "SELECT id,name,sex,score FROM student1 where score > (SELECT avg(score) FROM student1) ORDER BY score";
		/*
		 * PreparedStatement statement = connection.prepareStatement(sql);
		 * statement.setString(1, "女"); statement.setInt(2, 60);
		 */
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(sql);
		while (rs.next()) {
			System.out.println(rs.getString(1) + "," + rs.getString(2) + "," + rs.getString(3) + "," + rs.getString(4));
		}
		rs.close();
		statement.close();
		connection.close();
	}