JDBC操作2：PreparedStatement对象、实现获取自动增长的值、批处理以及事务操作

目录

1. Statement对象

2. PreparedStatement对象

3. 实现三个操作

3.1 获取自动增长的值

3.2 批处理操作

3.3 事务操作

---

1. Statement对象

1、Statement对象主要作用执行sql语句的

2、Statement对象缺点：

（1）sql语句拼接

@Test
public void test01() {
    Connection conn = null;
    Statement statement = null;
    ResultSet rs = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "root");
        String dname = "安保部";
      
        String sql1 = "select * from dept where dname='安保部'";
        String sql = "select * from dept where dname='"+dname+"'";
        System.out.println(sql);
        statement = conn.createStatement();
        rs = statement.executeQuery(sql);

        while(rs.next()) {
            String did = rs.getString("did");
            String dnameValue = rs.getString("dname");
            System.out.println(did+"::"+dnameValue);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {rs.close();
            statement.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

（2）sql注入问题

* 模拟登录过程演示sql注入问题

* 创建用户表

/**
 * 演示sql注入问题
 */
public class JDBCDemo3 {

    public static void main(String[] args) {
        login("mary' or '1'='1","666");
    }

    //模拟登录的方法
    public static void login(String username,String password) {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "root");

            // select * from users where username='mary' or '1'='1' and password='666'
            //  mary' or '1'='1
            String sql = "select * from users where username='"+username+"' and password='"+password+"'";

            System.out.println(sql);
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);

            while(rs.next()) {
                String usernameValue = rs.getString("username");
                String passwordValue = rs.getString("password");
                System.out.println(usernameValue+"::"+passwordValue);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                statement.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
} 

（3）不能处理Blob类型数据

* Blob类型是二进制类型

*使用Statement不能操作二进制类型数据

2. PreparedStatement对象

1、PreparedStatement对象是预编译对象，解决sql注入问题，弥补Statement缺陷

2、执行sql语句时候，把sql语句先进行编译，最后再执行

3、具体使用

//预编译模拟登录的方法
public static void loginPrepared(String username,String password) {
    Connection conn = null;
    PreparedStatement preparedStatement = null;
    ResultSet rs = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "root");

        //预编译对象使用过程
        // 使用?代表占位符，需要输入参数
        String sql = "select * from users where username=? and password=?";
        // 创建预编译对象
        preparedStatement = conn.prepareStatement(sql);
        // 设置占位符需要值
        // setString两个参数：第一个参数？位置，第二个参数？对应值
        preparedStatement.setString(1,username);
        preparedStatement.setString(2,password);
        //执行sql语句
        rs = preparedStatement.executeQuery();

        while(rs.next()) {
            String usernameValue = rs.getString("username");
            String passwordValue = rs.getString("password");
            System.out.println(usernameValue+"::"+passwordValue);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            rs.close();
            preparedStatement.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

//删除
@Test
public void test04() {
    Connection conn = null;
    PreparedStatement preparedStatement = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "root");

        String sql = "delete from dept where did=?";
        //创建预编译对象
        preparedStatement = conn.prepareStatement(sql);
        //设置值
        preparedStatement.setInt(1,100);

        //执行sql
        int row = preparedStatement.executeUpdate();
        System.out.println(row);

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            preparedStatement.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

//修改
@Test
public void test03() {
    Connection conn = null;
    PreparedStatement preparedStatement = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "root");

        String sql = "update dept set dname=? where did=?";
        //创建预编译对象
        preparedStatement = conn.prepareStatement(sql);
        //设置值
        preparedStatement.setString(1,"行政部");
        preparedStatement.setInt(2,100);

        //执行sql
        int row = preparedStatement.executeUpdate();
        System.out.println(row);

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            preparedStatement.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

//添加
@Test
public void test02() {
    Connection conn = null;
    PreparedStatement preparedStatement = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "root");
        String sql = "insert into dept values(?,?)";
        //创建预编译对象
        preparedStatement = conn.prepareStatement(sql);
        //设置值
        preparedStatement.setInt(1,100);
        preparedStatement.setString(2,"互动部");
        //执行sql
        int row = preparedStatement.executeUpdate();
        System.out.println(row);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            preparedStatement.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3. 实现三个操作

3.1 获取自动增长的值

（1）创建表，表id是主键，自动增长

//获取自动增长id值
 @Test
 public void test01() {
     Connection conn = null;
     PreparedStatement preparedStatement = null;
     try {
         Class.forName("com.mysql.jdbc.Driver");
         conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "root");

         //编写sql语句
         String sql = "insert into book values(null,?,?)";
         //创建预编译对象
         preparedStatement = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
         //设置值
         preparedStatement.setString(1,"上下五千年");
         preparedStatement.setString(2,"无名氏");
         //执行
         preparedStatement.executeUpdate();

         //获取添加之后自动增长id值
         ResultSet rs = preparedStatement.getGeneratedKeys();
         while(rs.next()) {
             Object id = rs.getObject(1);
             System.out.println(id);
         }
     } catch (Exception e) {
         e.printStackTrace();
     } finally {
         try {
             preparedStatement.close();
             conn.close();
         } catch (SQLException e) {
             e.printStackTrace();
         }
     }
 }

3.2 批处理操作

//批处理向表添加10条记录
@Test
public void test03() {
    Connection conn = null;
    PreparedStatement preparedStatement = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "root");

        //编写sql语句
        String sql = "insert into book values(null,?,?)";
        //创建预编译对象
        preparedStatement = conn.prepareStatement(sql);

        //循环10次
        for (int i = 1; i <=10; i++) {
            preparedStatement.setString(1,"上下五千年2"+i);
            preparedStatement.setString(2,"无名氏2"+i);

            //把每次添加数据放到批处理对象
            preparedStatement.addBatch();
        }

        //把批处理数据执行添加
        preparedStatement.executeBatch();

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            preparedStatement.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3.3 事务操作

//事务操作
@Test
public void test04() {
    Connection conn = null;
    PreparedStatement preparedStatement = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "root");

        //1 开启事务（让事务不是自动提交）
        conn.setAutoCommit(false);

        //2 具体操作 
        //编写sql语句
        String sql = "insert into book values(null,?,?)";
        //创建预编译对象
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1,"世界旅游");
        preparedStatement.setString(2,"无名氏");
        preparedStatement.executeUpdate();
        
        //3 事务结束 回滚和提交
        conn.commit();
    } catch (Exception e) {
        e.printStackTrace();
        //事务回滚
        try {
            conn.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    } finally {
        try {
            preparedStatement.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}