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

运行效果：