JDBC快速上手

GiantApe發表於2020-09-27

JDBC快速上手

前置條件

  1. 安裝了mysql8.0,在mysql中建立一個字符集為utf8mb4,名字為imooc的資料庫

  2. 執行以下sql語句

    /*
     Navicat Premium Data Transfer
    
     Source Server         : localhost
     Source Server Type    : MySQL
     Source Server Version : 80016
     Source Host           : localhost:3306
     Source Schema         : imooc
    
     Target Server Type    : MySQL
     Target Server Version : 80016
     File Encoding         : 65001
    
     Date: 24/02/2020 17:13:16
    */
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for employee
    -- ----------------------------
    DROP TABLE IF EXISTS `employee`;
    CREATE TABLE `employee`  (
      `eno` int(11) NOT NULL,
      `ename` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
      `salary` float(10, 2) NOT NULL,
      `dname` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
      `hiredate` date NULL DEFAULT NULL,
      PRIMARY KEY (`eno`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of employee
    -- ----------------------------
    INSERT INTO `employee` VALUES (1000, '員工1000', 5000.00, '市場部', '1992-03-04');
    INSERT INTO `employee` VALUES (1001, '員工1001', 3500.00, '市場部', '1988-02-18');
    INSERT INTO `employee` VALUES (1002, '員工1002', 4000.00, '市場部', '1996-02-01');
    INSERT INTO `employee` VALUES (1003, '員工1003', 4000.00, '市場部', '2001-08-21');
    INSERT INTO `employee` VALUES (1004, '員工1004', 4000.00, '市場部', '2003-01-02');
    INSERT INTO `employee` VALUES (1005, '員工1005', 4000.00, '市場部', '1996-07-28');
    INSERT INTO `employee` VALUES (1006, '員工1006', 4000.00, '市場部', '1999-12-30');
    INSERT INTO `employee` VALUES (1007, '員工1007', 4000.00, '市場部', '2009-05-30');
    INSERT INTO `employee` VALUES (1008, '員工1008', 4000.00, '市場部', '1984-05-30');
    INSERT INTO `employee` VALUES (1009, '員工1009', 4000.00, '市場部', '2004-05-07');
    INSERT INTO `employee` VALUES (3308, '張三', 6000.00, '研發部', '2011-05-08');
    INSERT INTO `employee` VALUES (3420, '李四', 8700.00, '研發部', '2006-11-11');
    INSERT INTO `employee` VALUES (3610, '王五', 4550.00, '市場部', '2009-10-01');
    
    SET FOREIGN_KEY_CHECKS = 1;
    
    

    3.在工程中匯入mysql驅動程式的jar包 – mysql-connector-java-8.0.16.jar

JDBC開發流程

  1. 載入並註冊JDBC驅動

    Class.forName("com.mysql.cj.jdbc.Driver");
    
  2. 建立資料庫連線

    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&character=UTF-8&serverTimezone=Asia/Shanghai","root","root");
    
  3. 建立Statement物件

                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery("select * from employee");
    
  4. 遍歷查詢結果

    while (rs.next()) {
        Integer eno = rs.getInt(1);
        String ename = rs.getString("ename");
        Float salary = rs.getFloat("salary");
        String dname = rs.getString("dname");
        System.out.println(dname + "-" + eno + "-" + ename + "-" + salary);
    }
    
  5. 關閉連線,釋放資源

try {
    if (conn != null && conn.isClosed() == false) {
        //5. 關閉連線,釋放資源
        conn.close();
    }
}catch (Exception ex){
    ex.printStackTrace();
}

完整程式碼【例項】

package com.imooc.jdbc.sample;

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

/**
 * 標準JDBC操作五步驟
 * @author CubeMonkey
 * @create 2020-09-27 11:27
 */
public class StandardJDBCSample {
    public static void main(String[] args) {
        Connection conn = null;
        try {
            //1. 載入並註冊JDBC驅動
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.建立資料庫連線
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&character=UTF-8&serverTimezone=Asia/Shanghai",
                    "root",
                    "root"
            );
            //3. 建立Statement物件
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from employee");
            //4. 遍歷查詢結果
            while (rs.next()) {
                Integer eno = rs.getInt(1);
                String ename = rs.getString("ename");
                Float salary = rs.getFloat("salary");
                String dname = rs.getString("dname");
                System.out.println(dname + "-" + eno + "-" + ename + "-" + salary);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                if (conn != null && conn.isClosed() == false) {
                    //5. 關閉連線,釋放資源
                    conn.close();
                }
            }catch (Exception ex){
                ex.printStackTrace();
            }
        }
    }
}

執行效果:

在這裡插入圖片描述

相關文章