JDBC-CURD

查询

在关系型数据库中，查询会返回三种结果

• 单行单列
• 单行多列
• 多行多列

查询单行单列

确定表中有多少条数据

@Test
public void testQuerySingleRowAndCol() throws SQLException {
    Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.2.2:3306/xiaobai", "root", "Zhuwenxue2002");

    PreparedStatement preparedStatement = connection.prepareStatement("SELECT COUNT(*) as count FROM `user`");//如果结果集的列名不是很明确，可以给列名定义别名
    ResultSet resultSet = preparedStatement.executeQuery();

    //遍历结果集
//        while (resultSet.next()) {
//            System.out.println(resultSet.getInt(1));//使用下标获取，第一行第一个数据
//        }

    //如果确定只有一个结果，也要至少执行一次next的判断
    if (resultSet.next()) {
        System.out.println(resultSet.getInt("count"));//使用列名的别名获取
    }

    connection.close();
    preparedStatement.close();
    resultSet.close();
}

查询单行多列

输入ID查询整条数据

@Test
public void testQuerySingleRow() throws SQLException {
    Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.2.2:3306/xiaobai", "root", "Zhuwenxue2002");

    PreparedStatement preparedStatement = connection.prepareStatement("select * from user where u_id=?");//如果结果集的列名不是很明确，可以给列名定义别名
    preparedStatement.setInt(1, 1);//给占位符赋值
    ResultSet resultSet = preparedStatement.executeQuery();

    //这里我们确定结果只有一行，所以仍然不用遍历（遍历的是行）
    if (resultSet.next()) {
        int id = resultSet.getInt("u_id");
        String name = resultSet.getString("u_name");
        double salary = resultSet.getDouble("u_salary");
        int age = resultSet.getInt("u_age");
        System.out.println(id + "\t" + name + "\t" + salary + "\t" + age);
    }

    connection.close();
    preparedStatement.close();
    resultSet.close();
}

插入

插入一条数据

@Test
public void testInsert() throws SQLException {
    Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.2.2:3306/xiaobai", "root", "Zhuwenxue2002");

    PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO `user`(u_name,u_salary,u_age) VALUES(?,?,?)");
    preparedStatement.setString(1, "xiaoxue");
    preparedStatement.setDouble(2, 4320.23);
    preparedStatement.setInt(3, 24);

    //根据受影响行数做判断
    System.out.println(preparedStatement.executeUpdate());

    connection.close();
    preparedStatement.close();
}

修改

修改一条数据

输入被修改的人的ID和要修改的工资数目

@Test
public void testUpdate() throws SQLException {
    Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.2.2:3306/xiaobai", "root", "Zhuwenxue2002");

    PreparedStatement preparedStatement = connection.prepareStatement("UPDATE `user`SET u_salary = ? WHERE u_id = ?");
    //将id为5的人的工资改为5320.23
    preparedStatement.setDouble(1, 5320.23);
    preparedStatement.setInt(2, 5);

    //根据受影响行数做判断
    System.out.println(preparedStatement.executeUpdate());

    connection.close();
    preparedStatement.close();
}

删除

删除一条数据

@Test
public void testDelete() throws SQLException {
    Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.2.2:3306/xiaobai", "root", "Zhuwenxue2002");

    PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM `user` WHERE u_id = ?");
    //删除ID为5的这条数据
    preparedStatement.setInt(1, 5);

    //根据受影响行数做判断
    System.out.println(preparedStatement.executeUpdate());

    connection.close();
    preparedStatement.close();
}