JDBC快速上手

GiantApe發表於2020-09-27
JDBC

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();
            }
        }
    }
}

執行效果：

在這裡插入圖片描述

相關文章