資料庫學習筆記02

使用JDBC連接資料庫：

首先下載好JDBC連接的包：

鏈接：https://pan.baidu.com/s/1OmjCCbrTy6giJNjGJuMxEQ 
提取碼：5qhp 
--來自百度網盤超級會員V3的分享

然后首先注冊驅動：(注意mysql8.0版本以上加cj)

        Class.forName("com.mysql.cj.jdbc.Driver");

獲取資料庫連接：

 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/study", "root", "root");

3.定義 sql 陳述句

 String sql1  = .............

4.獲取執行 sql 的物件

Statement stmt = conn.createStatement();

執行 sql 陳述句：

        int count1 = stmt.executeUpdate(sql1);
        int count2 = stmt.executeUpdate(sql2);

釋放資源：

        stmt.close();
        conn.close();

全部代碼：

package 任務十六__JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

/**
 * @author ${范濤之}
 * @Description
 * @create 2021-12-05 9:12
 */
public class Test {
    public static void main(String[] args) throws Exception {

        /**
         * 1:注冊驅動
         */
        Class.forName("com.mysql.cj.jdbc.Driver");

        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest","root","root");

        String sql1= "CREATE TABLE employers_table(\n" +
                "id INTEGER(8) NOT NULL AUTO_INCREMENT,\n" +
                "department_id INTEGER(8) NOT NULL,\n" +
                "emp_name CHAR(16) NOT NULL,\n" +
                "gender CHAR(8),\n" +
                "age INTEGER(8),\n" +
                "join_date CHAR(32),\n" +
                "PRIMARY KEY(id)\n" +
                ");";

        String sql2 = "CREATE TABLE departments_table(\n" +
                "department_id\tINTEGER(4) NOT NULL AUTO_INCREMENT,\n" +
                "department_name\tCHAR(16)   NOT NULL,\n" +
                "PRIMARY KEY(department_id)\n" +
                ");";


        Statement statement = connection.createStatement();
     statement.executeUpdate(sql1);
     statement.executeUpdate(sql2);
    

        statement.close();
        connection.close();

    }
}

運行結果：

:

插入十萬條資料：

package 任務十六__JDBC;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Statement;

/**
 * @author ${范濤之}
 * @Description
 * @create 2021-12-05 15:53
 */
public class Test3 {
    public static void main(String[] args) throws  Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/csdn", "root", "root");
        String sql1 = "INSERT INTO employee (dpmid,empname,age)\n" +
                      "VALUES\n" +
                      "(1,SUBSTRING(MD5(RAND()),1,20),FLOOR(RAND()*80));";
        Statement statement = connection.createStatement();

        int num = statement.executeUpdate(sql1);

        long startTime = System.currentTimeMillis();

        for (int i = 1; i < 10000; i++) {
            String sql = "INSERT INTO employee (dpmid,empname,age)\n" +
                    "VALUES\n" +
                    "(0,SUBSTRING(MD5(RAND()),1,20),FLOOR(RAND()*80));";
            num += statement.executeUpdate(sql);

        }
        long endTime = System.currentTimeMillis();
        System.out.println("一共添加了"+num+"條資料");
        System.out.println("耗時："+(endTime-startTime));
        statement.close();
        connection.close();






    }
}

結果：

批處理：

package 任務十六__JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

/**
 * @author ${范濤之}
 * @Description
 * @create 2021-12-05 16:07
 */
public class Test4 {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/csdn", "root", "root");
        Statement statement = connection.createStatement();
        long startTime = System.currentTimeMillis();

        for (int i = 1; i < 10000; i++) {
            String sql = "INSERT INTO employee (dpmid,empname,age)\n" +
                    "VALUES\n" +
                    "(0,SUBSTRING(MD5(RAND()),1,20),FLOOR(RAND()*80));";
            statement.addBatch(sql);
            statement.executeBatch();
        }
        long endTime = System.currentTimeMillis();
        System.out.println("耗時：" + (endTime - startTime));
        statement.close();
        connection.close();


    }
}

結果：