1. JDBC概述以及入门示例

create database testdb;

use testdb;

create table user(
 id int primary key auto_increment,
 username varchar(20),
 password varchar(20),
 nickname varchar(20)
);

INSERT INTO `USER` VALUES(null,'zs','123456','老张');
INSERT INTO `USER` VALUES(null,'ls','123456','老李');
INSERT INTO `USER` VALUES(null,'wangwu','123','东方不败');

    // 获取数据库连接的方式一
    @Test
    public void test01() throws Exception {

        //1.提供java.sql.Driver接口实现类的对象
        Driver driver = null;
        driver = new com.mysql.jdbc.Driver(); // 将要过期的 Driver 方法

        //2.提供url，指明具体操作的数据
        String url = "jdbc:mysql://localhost:3306/test"; // 指定访问的数据库 jdbc:数据库协议://IP:PORT/DATABASE

        //3.提供Properties的对象，指明用户名和密码
        Properties info = new Properties();
        info.setProperty("user", "root");
        info.setProperty("password", "***********");

        //4.调用driver的connect()，获取连接
        Connection conn = driver.connect(url, info);
        System.out.println(conn);

    }

//2.获取数据库连接的方式二
@Test
public void test02() throws Exception {
    //1.实例化Driver
    //使用反射机制来创建Driver
    String className = "com.mysql.jdbc.Driver"; //将要过期的 Driver 方法
    Class clazz = Class.forName(className);
    Driver driver = (Driver) clazz.newInstance();

    //2.提供url，指明具体操作的数据
    String url = "jdbc:mysql://localhost:3306/test";

    //3.提供Properties的对象，指明用户名和密码
    Properties info = new Properties();
    info.setProperty("user", "root");
    info.setProperty("password", "***********");

    //4.调用driver的connect()，获取连接
    Connection conn = driver.connect(url, info);
    System.out.println(conn);
}

//3.获取数据库连接的方式三
@Test
public void test03() throws Exception {
    //1.数据库连接的4个基本要素：
    String url = "jdbc:mysql://localhost:3306/test";
    String user = "root";
    String password = "*******************";
    String driverName = "com.mysql.jdbc.Driver"; //将要过期的 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);
}

    //4.获取数据库连接的方式四
    @Test
    public void test04() throws Exception {
        //1.数据库连接的4个基本要素：
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "****************";

        // 可以注释掉上述代码的原因，是因为在mysql的Driver类中声明有：
        /**
         * Load the initial JDBC drivers by checking the System property
         * jdbc.properties and then use the {@code ServiceLoader} mechanism
         */
        //        static {
        //            loadInitialDrivers();
        //            println("JDBC DriverManager initialized");
        //        }

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

//4.获取数据库连接的方式四
@Test
public void test04() throws Exception {
    //1.数据库连接的4个基本要素：
    String url = "jdbc:mysql://localhost:3306/test";
    String user = "root";
    String password = "Li***********";

    //2.加载驱动 （①实例化Driver ②注册驱动）
    String driverName = "com.mysql.jdbc.Driver"; //将要过期的 Driver 方法
    Class.forName(driverName);

    // 可以注释掉上述代码的原因，是因为在mysql的Driver类中声明有：
    /**
     * Load the initial JDBC drivers by checking the System property
     * jdbc.properties and then use the {@code ServiceLoader} mechanism
     */
    //        static {
    //            loadInitialDrivers();
    //            println("JDBC DriverManager initialized");
    //        }

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

user=root
password=Lijw************
url=jdbc:mysql://localhost:3306/book?rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false
driverClass=com.mysql.cj.jdbc.Driver

//5.获取数据库连接的方式五（使用配置文件）
@Test
public void test05() throws Exception {
    //1.加载配置文件
    //1.1 使用类加载器，读取resource文件夹下的 jdbc.properties 配置文件
    InputStream is = JDBCTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
    //1.2 创建Properties对象，用来加载读取配置字节输入流的信息
    Properties pros = new Properties();
    pros.load(is);

    //2.读取配置信息
    String user = pros.getProperty("jdbc.username");
    String password = pros.getProperty("jdbc.password");
    String url = pros.getProperty("jdbc.url");
    String driverClass = pros.getProperty("jdbc.driverClass");

    System.out.println("user=" + user + ", password=" + password + ", url=" + url + ", driverClass=" + driverClass);

    //3.加载驱动
    Class.forName(driverClass);

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

// 6. 执行SQL查询
@Test
public void test06() throws Exception {
    //1.加载配置文件
    //1.1 使用类加载器，读取resource文件夹下的 jdbc.properties 配置文件
    InputStream is = JDBCTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
    //1.2 创建Properties对象，用来加载读取配置字节输入流的信息
    Properties pros = new Properties();
    pros.load(is);

    //2.读取配置信息
    String user = pros.getProperty("jdbc.username");
    String password = pros.getProperty("jdbc.password");
    String url = pros.getProperty("jdbc.url");
    String driverClass = pros.getProperty("jdbc.driverClass");

    //3.加载驱动
    Class.forName(driverClass);

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

    //5.创建执行sql语句对象
    Statement statement = conn.createStatement();
    //6.执行sql,处理结果
    String sql = "select * from user";
    ResultSet resultSet = statement.executeQuery(sql);
    while (resultSet.next()) {
        System.out.println(resultSet.getObject(1));
        System.out.println(resultSet.getObject(2));
        System.out.println(resultSet.getObject(3));
        System.out.println(resultSet.getObject(4));

    }
    //7.关闭资源
    resultSet.close();
    statement .close();
    conn.close();
}