用JDBC执行DML语句

        在已经建立数据库链接的基础上，如何执行SQL语句呢？这时候，我们就会用到Statement对象和ResultSet对象

Statement

        对于增删改操作，我们只需要用到Statement对象，用法如下：

        总的五步骤：

1. 声明Statement变量：Statement stmt = null;

2. 获取Statement对象：stmt = conn.createStatement();

3. 编写sql语句：String sql = "update emp set sal=sal+100 where ename='tony'";

4. 执行sql语句：int result = stmt.executeUpdate(sql);

5. 关闭Statement对象，释放资源：stmt.close();

        用法如下：

1、Delete

public class TestStatementDelete {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@192.168.200.128:1521:XE";
        String username = "HR";
        String password = "123456";

        Connection con = null;
        Statement stm = null;

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection(url, username, password);
            System.out.println("连接成功！");

            stm = con.createStatement();
            String sql = "delete from mark where studentno='666'";
            int result = stm.executeUpdate(sql);
            if (result > 0) {
                System.out.println("删除成功");
            }else{
                System.out.println("删除失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception throwables) {
                throwables.printStackTrace();
            }
            System.out.println("断开连接…");
        }
    }
}

2、Update

public class TestStatementUpdate {
    public static void main(String[] args) {
        // 数据连接信息
        String url = "jdbc:oracle:thin:@192.168.200.128:1521:XE";
        String username = "HR";
        String password = "123456";

        Connection connection = null;
        Statement statement = null;

        try {
            // 1、加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            // 2、创建连接对象
            connection =
                    DriverManager.getConnection(url, username, password);
            // 3、进行相关操作（更改学号为1001学生的电话号码）
            // 3.1 准备sql语句
            String sql = "update student set phone='123456' where studentno='1001'";
            // 3.2 创建statement对象
            statement = connection.createStatement();
            // 3.3 执行sql语句
            int result = statement.executeUpdate(sql);
            if (result > 0) {
                System.out.println("修改成功");
            } else {
                System.out.println("修改失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 4、释放资源
            try {
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            System.out.println("断开连接…");
        }
    }
}

3、Inserte

public class TestStatementInsert {
    public static void main(String[] args) {

        Scanner scanner = new Scanner(System.in);

        // 数据库连接信息
        String url = "jdbc:oracle:thin:@192.168.200.128:1521:XE";
        String username = "HR";
        String password = "123456";

        Connection connection = null;
        Statement statement = null;
        try {
            // 1.加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            // 2.创建连接对象
            connection =
                    DriverManager.getConnection(url, username, password);
            System.out.println("连接成功！");

            // 3.新增学生信息
            // 3.1 准备sql语句
            System.out.print("请输入年级编号：");
            int gradeId = scanner.nextInt();
            System.out.print("请输入年级名称：");
            String gradeName = scanner.next();

            StringBuilder sb = new StringBuilder();
            sb.append("insert into grade values(");
            sb.append(gradeId);
            sb.append(",'");
            sb.append(gradeName);
            sb.append("')");

            // 3.2 创建statement对象
            statement = connection.createStatement();
            // 3.3 执行sql语句
            int result = statement.executeUpdate(sb.toString());
            if (result > 0) {
                System.out.println("插入成功");
            } else {
                System.out.println("插入失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                // 4.释放资源
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        System.out.println("断开连接…");
    }
}

ResultSet

        在上述操作中，不需要返回结果的查看；当我们需要对查询结果进行操作时，ResultSet派上了用场

        ResultSet的用法如下

         举个栗子

public class TestStatementResultSet {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@192.168.200.128:1521:XE";
        String username = "HR";
        String password = "123456";

        Connection con = null;
        Statement stm = null;
        ResultSet rs = null;

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection(url, username, password);
            System.out.println("连接成功！");

            String sql = "select * from grade";
            stm = con.createStatement();
            rs = stm.executeQuery(sql);

            List<Grade> gradeList = new ArrayList();
            Grade grade = null;
            while (rs.next()) {
                grade = new Grade();
                grade.setGradeId(rs.getInt("gradeid"));
                grade.setGradeName(rs.getString("gradename"));
                gradeList.add(grade);
            }

            System.out.println("数据已保存至本地，查询结果如下：");
//            for (Grade g : gradeList) {
//                System.out.println(g.getGradeId() + "-" + g.getGradeName());
//            }
            System.out.println(gradeList);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                // 4.释放资源
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            System.out.println("断开连接！");
        }
    }
}