JDBC的几种获取方法，Statement，PreparedStatement，事务处理

jdbc：java数据库连接

目录

1、首先新建个jbdc.properties文件，引用mysql

2、新建Course.java文件

3、新建一个jdbc测试类

4、新建一个jdbc工具类

5、体会插入代码

6、体会查询代码，因为要展现结果，所以要有ResultSet

7、之前提到的sql注入问题

8、用PreparedStatement修改数据

9、用PreparedStatement查询数据

10、事务处理

---

1、首先新建个jbdc.properties文件，引用mysql

mysql.username=root
mysql.password=123456
mysql.url=jdbc:mysql://127.0.0.1:3306/aa?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false
mysql.dirvername=com.mysql.cj.jdbc.Driver

2、新建Course.java文件

主要是数据库表里有什么类型的数据，就在这里写出来，然后创建构造函、toString、getter和setter

public class Course {

    private Integer id;
    private String name;
    private Integer t_id;

    public Course(Integer id, String name, Integer t_id) {
        this.id = id;
        this.name = name;
        this.t_id = t_id;
    }

    @Override
    public String toString() {
        return "Course{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", t_id=" + t_id +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getT_id() {
        return t_id;
    }

    public void setT_id(Integer t_id) {
        this.t_id = t_id;
    }
}

3、新建一个jdbc测试类

以下是4种连接jdbc的方法，一个比一个精简，但普通的statement会导致sql注入的问题，这里给大家看一下，后面会介绍PreparedStatement，用PreparedStatement替代Statement，就不会出现sql注入的问题了

这里为了省事全部用了throws Exception，但在实际项目中不可这样做

@Test
    public void test1() throws Exception{
        //1.数据库连接的4个基本要素：
        String url = "jdbc:mysql://127.0.0.1:3306/aa? " +
                "useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPub" +
                "licKeyRetrieval=true&useSSL=false";
        String user = "root";
        String password = "123456";
        //8.0之后名字改了 com.mysql.cj.jdbc.Driver
        //5.7之后名字改了 com.mysql.jdbc.Driver
        String driverName = "com.mysql.cj.jdbc.Driver";

        //2.实例化Driver
        Class clazz = Class.forName(driverName);
        Driver driver = (Driver) clazz.newInstance();
        //3.注册驱动
        DriverManager.registerDriver(driver);
        // 4.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
    }

    @Test
    public void test2() throws Exception{
        //1.数据库连接的4个基本要素：
        String url = "jdbc:mysql://127.0.0.1:3306/aa? " +
                "useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPub" +
                "licKeyRetrieval=true&useSSL=false";
        String user = "root";
        String password = "123456";
        String driverName = "com.mysql.cj.jdbc.Driver";

        //2.实例化Driver
        Class.forName(driverName);

        // 4.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
    }

    @Test
    public void test3() throws Exception{
        //1.数据库连接的4个基本要素：
        String url = "jdbc:mysql://127.0.0.1:3306/aa? " +
                "useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPub" +
                "licKeyRetrieval=true&useSSL=false";
        String user = "root";
        String password = "123456";
        String driverName = "com.mysql.cj.jdbc.Driver";

        // 4.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
    }

    @Test
    public void test4() throws Exception{
        Properties properties = new Properties();
        properties.load(JdbcTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
        String url = properties.getProperty("mysql.url");
        String username = properties.getProperty("mysql.username");
        String password = properties.getProperty("mysql.password");

        // 4.获取连接
        Connection conn = DriverManager.getConnection(url, username, password);
        System.out.println(conn);
    }

4、新建一个jdbc工具类

把jdbc的连接和关闭流的方法写进去，这样调用jdbc时就不用每次要用的时候都要写一遍了

public class JdbcUtil {

    public static Connection getConnection(){
        Connection conn = null;
        try{
            Properties properties = new Properties();
            properties.load(JdbcTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
            String url = properties.getProperty("mysql.url");
            String username = properties.getProperty("mysql.username");
            String password = properties.getProperty("mysql.password");

            // 4.获取连接
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

5、体会插入代码

@Test
    public void testStatemate(){
        Connection connection = null;
        try{
            String sql1 = "insert into course values (6,'生物',6)";
            String sql2 = "update course set name = 1 where id > 1";
            connection = JdbcUtil.getConnection();
            Statement statement = connection.createStatement();
            int i = statement.executeUpdate(sql1);
            System.out.println(i);
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            if (connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

6、体会查询代码，因为要展现结果，所以要有ResultSet

@Test
    public void testStatemate1(){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try{
            String sql1 = "select * from course";
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql1);
            List<Course> courses = new ArrayList<>();

            while (resultSet.next()){
                int anInt = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int t_id = resultSet.getInt("t_id");
                courses.add(new Course(anInt,name,t_id));
            }
            System.out.println(courses);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.closeAll(connection,statement,resultSet);
        }
    }

7、之前提到的sql注入问题

放几行代码展示若是使用Statement，就会造成随便谁都能登录任一用户。Statement可以有空子钻。

public static boolean login(String username,String password){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try{
            String sql1 = "select id,username,password from user where username = '"
                    + username + "'and password='" + password + "'";
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql1);
            List<Course> courses = new ArrayList<>();

            return resultSet.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.closeAll(connection,statement,resultSet);
        }
        return false;
    }


    public static void main(String[] args) {
        boolean login = login("张三", "123");
        boolean login1 = login("张三", "123' or '1' = '1");
        System.out.println(login1);
    }

8、用PreparedStatement修改数据

    @Test
    public void testStatemate3(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try{
            String sql1 = "update user set username = '王五' where id = 1";
            connection = JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.closeAll(connection,preparedStatement,resultSet);
        }
    }

    @Test
    public void testStatemate4(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try{
            String sql1 = "update user set username = ? where id = ?";
            connection = JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setString(1,"赵六");
            preparedStatement.setInt(2,2);
            preparedStatement.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.closeAll(connection,preparedStatement,resultSet);
        }
    }

9、用PreparedStatement查询数据

@Test
    public void testStatemate5(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try{
            String sql1 = "select * from user where id > ?";
            connection = JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setInt(1,0);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()){
                System.out.println(resultSet.getInt("id"));
                System.out.println(resultSet.getString("username"));
                System.out.println(resultSet.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.closeAll(connection,preparedStatement,resultSet);
        }
    }

10、事务处理

数据一旦提交，就不可回滚。

数据什么时候意味着提交？

• 当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。
• 关闭数据库连接，数据就会自动的提交。如果多个操作，每个操作使用的是自己单独的连接， 则无法保证事务。即同一个事务的多个操作必须在同一个连接下。

DBUtils 程序中为了让多个 SQL 语句作为一个事务执行：

• 调用 Connection 对象的 setAutoCommit(false); 以取消自动提交事务
• 在所有的 SQL 语句都成功执行后，调用 commit(); 方法提交事务
• 在出现异常时，调用 rollback(); 方法回滚事务

@Test
    public void testStatemate6(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try{
            // 获取连接，并关闭自动提交
            connection = JdbcUtil.getConnection();
            connection.setAutoCommit(false);
            // 数据库操作
            String sql1 = "update user set username = ? where id = ?";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setString(1,"test1");
            preparedStatement.setInt(2,1);
            int i = preparedStatement.executeUpdate();
            System.out.println(i);

            String sql2 = "update user set username = ? where id = ?";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setString(1,"test2");
            preparedStatement.setInt(2,2);
            int i2 = preparedStatement.executeUpdate();
            System.out.println(i2);

            connection.commit();

        } catch (SQLException e) {
            e.printStackTrace();
            try {
                // 若有异常就回滚
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally {
            JdbcUtil.closeAll(connection,preparedStatement,resultSet);
        }
    }