Java学习日记（day14）

最新推荐文章于 2024-09-15 09:47:04 发布

肆氿

最新推荐文章于 2024-09-15 09:47:04 发布

阅读量742

点赞数 19

文章标签： java

本文链接：https://blog.csdn.net/qq_59031302/article/details/140855912

版权

一、JDBC简介

面向接口编程

在JDBC里面Java这个公司只是提供了一套接口Connection、Statement、ResultSet，每个数据库厂商实现了这套接口，例如MySql公司实现了：MySql驱动程序里面实现了这套接口，Java程序员只要调用实现了这些方法就可以实现对 MySql数据库的增删改查。

二、JDBC开发步骤

下载对应SQL的驱动

在项目目录下建立lib文件夹，把下载好的Jar包粘贴进入，然后根据提示添加进库

再建立一个名为resources的文件夹并把它设置成资源根目录

再在其下建立一个叫db.properties的文件

1、加载驱动Class.forName("");

2、获得连接对象Connection

3、写sql语句

4、创建Statement(一艘船)

5、执行sql语句

(1) 更新类（更改了表里面数据）：delete/update/insert executeUpdate()

返回值：int，表示你影响的行数

(2)查询（没有改变表里面数据）: select executeQuery()

返回值：结果集ResultSet

6、关闭连接

//1、加载驱动Class.forName(""); Class.forName("com.mysql.cj.jdbc.Driver"); 
//2、获得连接对象
Connection Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/
study?useSSL=false&useUnicode=true&characterEncoding
=utf8&serverTimezone=GMT%2b8", "root", "1234");

public class JDBCUtil {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;

    //静态代码块,在类加载时候只会执行一次
    static {
        try {
            //1.通过当前类获取类加载器
            ClassLoader classLoader = JDBCUtil.class.getClassLoader();
            //2.通过类加载器的方法获取一个输入流
            InputStream inputStream = classLoader.getResourceAsStream("db.properties");
            //3.创建一个Properties对象
            Properties properties = new Properties();
            properties.load(inputStream);
            //4.获取配置文件里面的参数的值
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
        } catch (IOException e) {
            e.printStackTrace();
        }

        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(url, username, password);
        return connection;
    }

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

@Test
public void test1() {
    try {
        //1、加载驱动Class.forName("");
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2、获得连接对象Connection
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/study?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2b8", "root", "1234");
        //3、写sql语句 
        String sql = "SELECT id,name,age,gender FROM student";
        //4、创建Statement(一艘船)
        Statement statement = connection.createStatement();
        //5、执行sql语句
        //   (1) 更新类（更改了表里面数据）：delete/update/insert     executeUpdate()
        //   返回值：int，表示你影响的行数
        //   (2)查询（没有改变表里面数据）:  select                   executeQuery()
        //   返回值：结果集ResultSet
        ResultSet resultSet = statement.executeQuery(sql);
        List<Student> list = new ArrayList<>();
        while (resultSet.next()) {//判断下一个有没有，如果没有返回false，如果有返回true，并且指向这一行
            //当前resultSet指向第一行
            //while循环每遍历一次，把这一行每个字段的值拿出来，封装成一个Student对象
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String gender = resultSet.getString("gender");
            Student student = new Student(id, name, age, gender);
            list.add(student);
        }
        for (Student student : list) {
            System.out.println(student);
        }
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally {
        //6、关闭连接
    }
}
@Test
public void test2() {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/java230701?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2b8", "root", "1234");
        String sql = "SELECT id,name,age,gender FROM student";
        statement = connection.createStatement();
        resultSet = statement.executeQuery(sql);
        List<Student> list = new ArrayList<>();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String gender = resultSet.getString("gender");
            Student student = new Student(id, name, age, gender);
            list.add(student);
        }
        for (Student student : list) {
            System.out.println(student);
        }
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

@Test
public void testPreparedStatement() {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        connection = JDBCUtil.getConnection();
        String sql = "SELECT id,name,age,gender FROM student";
        //预编译
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        List<Student> list = new ArrayList<>();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String gender = resultSet.getString("gender");
            Student student = new Student(id, name, age, gender);
            list.add(student);
        }
        for (Student student : list) {
            System.out.println(student);
        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally {
        JDBCUtil.close(connection, preparedStatement, resultSet);
    }
}

@Test
public void testInsert() {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        connection = JDBCUtil.getConnection();
        //String name = "zhansgan";
        //String sql = "insert into student(name,age,gender) values("+name+",?,?)";
        //? 占位符
        String sql = "insert into student(name,age,gender) values(?,?,?)";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, "张三");
        preparedStatement.setInt(2, 23);
        preparedStatement.setString(3, "女");
        System.out.println(preparedStatement);
        int count = preparedStatement.executeUpdate();
        System.out.println("count: " + count);
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally {
        JDBCUtil.close(connection, preparedStatement, null);
    }
}

@Test
public void testDelete() {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        connection = JDBCUtil.getConnection();
        String sql = "delete from student where id=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, 10);
        System.out.println(preparedStatement);
        int count = preparedStatement.executeUpdate();
        System.out.println("count: " + count);
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally {
        JDBCUtil.close(connection, preparedStatement, null);
    }
}

@Test
public void testUpdate() {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        connection = JDBCUtil.getConnection();
        String sql = "update student set name=?,age=?,gender=? where id=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, "小张");
        preparedStatement.setInt(2, 23);
        preparedStatement.setString(3, "男");
        preparedStatement.setInt(4, 9);
        System.out.println(preparedStatement);
        int count = preparedStatement.executeUpdate();
        System.out.println("count: " + count);
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
}

@Test
public void testLike() {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        connection = JDBCUtil.getConnection();
        String sql = "select id,name,age,gender from student where name like ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, "%张%");
        System.out.println(preparedStatement);
        resultSet = preparedStatement.executeQuery();
        List<Student> list = new ArrayList<>();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String gender = resultSet.getString("gender");
            Student student = new Student(id, name, age, gender);
            list.add(student);
        }
        for (Student student : list) {
            System.out.println(student);
        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
}

三、JDBC接口核心的API

|- DriverManager类：驱动管理器类，用于管理所有注册的驱动程序

|-registerDriver(driver) : 注册驱动类对象

|-Connection getConnection(url,user,password); 获取连接对象

|- Connection接口：表示java程序和数据库的连接对象。

|- Statement createStatement() ：创建Statement对象

|- PreparedStatement prepareStatement(String sql) 创建PreparedStatement对象

|- CallableStatement prepareCall(String sql) 创建CallableStatement对象(调用写好的存储过程)

|- Statement接口：用于执行静态的sql语句

|- int executeUpdate(String sql) ：执行静态的更新sql语句

|- ResultSet executeQuery(String sql) ：执行的静态的查询sql语句

|-PreparedStatement接口：用于执行预编译sql语句

|- int executeUpdate() ：执行预编译的更新sql语句

|-ResultSet executeQuery() ：执行预编译的查询sql语句

|- ResultSet接口：用于封装查询出来的数据

|- boolean next() ：将光标移动到下一行

|-getXX() : 获取列的值

四、PreparedStatement（预编译)和Statement区别

1、语法不同：

PreparedStatement可以使用预编译的sql，只需要发送一次sql语句，后面只要发送参数即可，公用一个sql语句。

Statement只能使用静态的sql。字符串拼接。

2、效率不同：PreparedStatement使用了sql缓冲区，效率要比Statement高。

3、安全性不同：PreparedStatement可以有效的防止sql注入，而Statement不能防止sql注入。

-- 演示Sql注入问题
CREATE TABLE users(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10),
    `password` VARCHAR(10)
);
INSERT INTO users(`name`, `password`) VALUES('lisi',123);
SELECT * FROM users WHERE 1=1;  -- 1=1   true
SELECT * FROM users WHERE `name`='lisi' AND `password`='123';
--  zhangsan' OR 1=1 -- y 
SELECT * FROM users WHERE `name`='zhangsan' OR 1=1 -- y' AND `password`='343';