JDBC原理

JDBC(Java Database Connectivity)是Java提供一套用于数据库操作的接口API，Java程序员只需要面向这套接口编程即可。不同的数据库厂商，需要针对这套接口提供不同实现
java制定接口规范，不同数据库厂商提供自己的数据库操作实现类(即使用的驱动包)

JDBC编写步骤：
1.获取Driver
2.获取Connection
3.执行SQL
4.释放资源

public class JDBCTest {

    @Test
    public void test1() throws SQLException {

        String url = "jdbc:mysql://localhost:3306/testdb";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "123456");

        //1.获取Driver
        Driver driver = new Driver();

        //2.获取Connection
        Connection connect = driver.connect(url, properties);

        //3.执行SQL
        String createSql = "CREATE TABLE IF NOT EXISTS student(id INT,`name` VARCHAr(32))";
        Statement statement = connect.createStatement();
        statement.execute(createSql);
        String insertSql = "INSERT INTO student VALUES(1,'小明')";
        statement.execute(insertSql);
		String selectSql = "SELECT id,name FROM student";
        ResultSet resultSet = statement.executeQuery(selectSql);
        while (resultSet.next()){
            System.out.println(resultSet.getInt(1));
            System.out.println(resultSet.getString(2));
        }

        //4.释放资源
        statement.close();
        connect.close();
    }

    @Test
    public void test2() throws Exception {

        String url = "jdbc:mysql://localhost:3306/testdb";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "123456");

        //1.获取Driver（反射获取）
        Class aClass = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance();

        //2.获取Connection
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);
    }

    @Test
    public void test3() throws Exception {

        String url = "jdbc:mysql://localhost:3306/testdb";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "123456");

        //1.获取Driver（反射获取）
        Class aClass = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance();

        //2.获取Connection（DriverManager管理driver）
        DriverManager.registerDriver(driver);
        Connection connection = DriverManager.getConnection(url, properties);
        System.out.println(connection);

    }

    @Test
    public void test4() throws Exception {

        String url = "jdbc:mysql://localhost:3306/testdb";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "123456");

        //1.获取Driver（反射获取）
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取Connection
        Connection connection = DriverManager.getConnection(url, properties);
        System.out.println(connection);

        
    }

    @Test
    public void test5() throws Exception {

        //获取相关值（从配置文件中获取）
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\mysql.properties"));
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        //1.获取Driver
        Class.forName(driver);

        //2.获取Connection
        Connection connection = DriverManager.getConnection(url, properties);
        System.out.println(connection);
    }

    @Test
    public void test6() throws Exception {
        //演示SQL注入,SQL注入测试数据： id = 1,name = ' OR '1' = '1
        int id = 1;
        String name = "' OR '1' = '1";

        String url = "jdbc:mysql://localhost:3306/testdb";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "123456");

        //1.获取Driver（反射获取）
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取Connection
        Connection connection = DriverManager.getConnection(url, properties);
        Statement statement = connection.createStatement();

        //3.执行SQL
        String sql = "SELECT * FROM student WHERe id=" + id + " AND name = '" + name + "'";
        ResultSet resultSet = statement.executeQuery(sql);
        System.out.println(resultSet.next() ? "登录成功！" : "登录失败！");

        //4.释放资源
        statement.close();
        connection.close();
    }

    @Test
    public void test7() throws Exception {
        //使用PreparedStatement预处理SQL
        //1.不再使用+拼接SQL语句，减少语法错误
        //2.有效解决SQL注入问题
        //3.大大减少编译次数，提高效率

        int id = 1;
        String name = "' OR '1' = '1";

        String url = "jdbc:mysql://localhost:3306/testdb";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "123456");

        //1.获取Driver（反射获取）
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取Connection
        Connection connection = DriverManager.getConnection(url, properties);

        //3.执行SQL
        String sql = "SELECt * FROM student WHERe id = ? AND name = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        preparedStatement.setString(2, name);
        ResultSet resultSet = preparedStatement.executeQuery();
        System.out.println(resultSet.next() ? "登录成功！" : "登录失败！");

        //4.释放资源
        preparedStatement.close();
        connection.close();
    }
}