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

运行效果:

在这里插入图片描述

©️2020 CSDN 皮肤主题: 游动-白 设计师:上身试试 返回首页