PreparedStatement测试CRUD

pom.xml

<dependencies>
   <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.27</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13.2</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.junit.jupiter</groupId>
        <artifactId>junit-jupiter</artifactId>
        <version>RELEASE</version>
        <scope>compile</scope>
    </dependency>
</dependencies>

代码

/**
 * 使用PreparedStatement测试增删改查
 */
public class PreparedStatementTestCRUD {

	@Test
	/**
	 * 查询所有数据  并把结果放到一个List<Map> list集合中
	 */
	public void testQuery3() throws Exception {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
		String sql = "select * from test.t_user";

		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		ResultSet resultSet = prepareStatement.executeQuery();
		ArrayList<HashMap<Object, Object>> list = new ArrayList<>();


		ResultSetMetaData metaData = resultSet.getMetaData();
		// metaData封装的是一条数据的所有列名信息  可以根据下角标获取对应的名称
		int columnCount = metaData.getColumnCount();

		while (resultSet.next()){
			HashMap<Object, Object> map = new HashMap<>();
			for (int i = 1; i <= columnCount; i++) {
				// 获取指定列下角标的名称
				// getColumnName和getColumnLabel的区别：getColumnLabel可以获取sql语句中取的别名
				String key = metaData.getColumnLabel(i);
				// 获取指定列下角标的名称对应的值
				Object values = resultSet.getObject(i);
				map.put(key, values);
			}
			list.add(map);
		}
		System.out.println(list);
		prepareStatement.close();
		connection.close();
	}


	@Test
	/**
	 * 查询所有数据  并把结果放到一个List<Map> list集合中
	 */
	public void testQuery2() throws Exception {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
		String sql = "select * from test.t_user";

		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		ResultSet resultSet = prepareStatement.executeQuery();
		ArrayList<HashMap<String, String>> list = new ArrayList<>();

		HashMap<String, String> map = new HashMap<>();
		while (resultSet.next()){
			map.put("id", String.valueOf(resultSet.getInt("id")));
			map.put("account", resultSet.getString("account"));
			map.put("PASSWORD", resultSet.getString("PASSWORD"));
			map.put("nickname", resultSet.getString("nickname"));
			list.add(new HashMap<>(map));
			map.clear();
		}
		System.out.println(list);
		prepareStatement.close();
		connection.close();
	}



	@Test
	public void testInsert() throws Exception {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");

		String sql = "insert into test.t_user (account, password, nickname) values(?,?,?);";

		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		prepareStatement.setObject(1,"test");
		prepareStatement.setObject(2,"345678");
		prepareStatement.setObject(3,"testtest");

		int row = prepareStatement.executeUpdate();

		if (row > 0){
			System.out.println("数据插入成功！");
		}else {
			System.out.println("数据插入失败！");
		}

	}

	/**
	 * 删除id=3的数据
	 */
	@Test
	public void testDelete() throws Exception{
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
		String sql = "delete from test.t_user where id = ?";

		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		prepareStatement.setObject(1, 3);

		int rows = prepareStatement.executeUpdate();

		if (rows > 0){
			System.out.println("数据删除成功！");
		}else {
			System.out.println("数据删除失败！");
		}

	}
	
	@Test
	public void testQuery() throws Exception {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
		String sql = "select * from test.t_user";

		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		ResultSet resultSet = prepareStatement.executeQuery();

		while (resultSet.next()){
			int id = resultSet.getInt("id");
			String account = resultSet.getString("account");
			String password = resultSet.getString("PASSWORD");
			String nickname = resultSet.getString("nickname");
			System.out.println(id + " " + account + " " + password +" " + nickname);
		}


	}

	/**
	 * 修改id=3的nickname为"hello"
	 */
	@Test
	public void testUpdate() throws Exception {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");

		String sql = "update test.t_user set nickname = ? where id = ?";
		PreparedStatement prepareStatement = connection.prepareStatement(sql);

		prepareStatement.setObject(1,"hello");
		prepareStatement.setObject(2,3);

		int rows = prepareStatement.executeUpdate();

		if (rows > 0){
			System.out.println("数据更新成功！");
		}else {
			System.out.println("数据更新失败！");
		}


	}

}