jdbc java连接mysql数据库

JDBC

以mysql为例，可选择mysql8.x与mysql5.7

数据库地址：127.0.0.1

mysql端口：3306

用户名：root

密码：123456

测试数据库：test

一、导入mysql连接驱动

当前以maven为例工程为例

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.30</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.24</version>
    <scope>provided</scope>
</dependency>

二 、JDBC常规步骤

基本常量

/**
 * 驱动类
 * 建议使用8.x版本的驱动，因为8.x完全兼容5.7
 * 8.x
 */
public static final String DRIVER = "com.mysql.cj.jdbc.Driver";
//public static final String DRIVER = "com.mysql.jdbc.Driver";  //5.x
/**
 * 连接路径
 */
public static final String URL = "jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8";

/**
 * 用户名
 */
public static final String USER = "root";

/**
 * 密码
 */
public static final String PWD = "123456";

/**
 * 连接对象
 */
public static Connection conn = null;

1.加载驱动

static {
    try {
        //加载对象
        Class.forName(DRIVER);
    } catch (ClassNotFoundException e) {
        throw new RuntimeException(e);
    }
}

2.获得连接对象

public static Connection getConnection() {
    try {
        conn = DriverManager.getConnection(URL, USER, PWD);
    } catch (SQLException e) {
        throw new RuntimeException(e);

    }
    return conn;
}

3.关闭连接处理

public static void closeAll(ResultSet rs, PreparedStatement ps, Connection conn) {
    try {
        if (rs != null) {
            rs.close();
        }
        if (ps != null) {
            ps.close();
        }
        if (conn != null) {
            conn.close();
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

4.完整DBUtil.java

public class DBUtil {
    /**
     * 驱动类
     * 8.x
     */
    public static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    //public static final String DRIVER = "com.mysql.jdbc.Driver";  //5.x
    /**
     * 连接路径
     */
    public static final String URL = "jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8";

    /**
     * 用户名
     */
    public static final String USER = "root";

    /**
     * 密码
     */
    public static final String PWD = "123456";

    /**
     * 连接对象
     */
    public static Connection conn = null;

    static {
        try {
            //加载对象
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    public static Connection getConnection() {
        try {
            conn = DriverManager.getConnection(URL, USER, PWD);
        } catch (SQLException e) {
            throw new RuntimeException(e);

        }
        return conn;
    }

    public static void closeAll(ResultSet rs, PreparedStatement ps, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

5.创建数据库表student进行测试

建表SQL

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `stu_id` int(11) NOT NULL,
  `stu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `stu_age` int(11) NULL DEFAULT NULL,
  `stu_sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`stu_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '小红', 18, '女');
INSERT INTO `student` VALUES (2, '小明', 20, '男');

SET FOREIGN_KEY_CHECKS = 1;

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int stuId;
    private String stuName;
    private int stuAge;
    private String suSex;
}

6.执行SQL查询，获得数据集

List<Student> students = new ArrayList<>();
Connection conn = DBUtil.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "SELECT * FROM student";
try {
    //执行sql
    ps = conn.prepareStatement(sql);
    // 获得结果集
    rs = ps.executeQuery();
    while (rs.next()) {
        // 数据封装
        Student student = new Student();
        student.setStuId(rs.getInt("stu_id"));
        student.setStuAge(rs.getInt("stu_age"));
        student.setStuName(rs.getString("stu_name"));
        student.setSuSex(rs.getString("stu_sex"));
        students.add(student);
    }
} catch (SQLException e) {
    throw new RuntimeException(e);
} finally {
    DBUtil.closeAll(rs, ps, conn);
}
System.out.println(students);

结果

[Student(stuId=1, stuName=小红, stuAge=18, suSex=女), Student(stuId=2, stuName=小明, stuAge=20, suSex=男)]

7.带参数查询

Student student = null;
Connection conn = DBUtil.getConnection();
String sql = "SELECT * FROM student WHERE stu_id = ?";
ResultSet rs = null;
PreparedStatement ps = null;
try {
    ps = conn.prepareStatement(sql);
    ps.setInt(1, 1);
    rs = ps.executeQuery();
    while (rs.next()) {
        student = new Student(rs.getInt("stu_id"),
                rs.getString("stu_name"),
                rs.getInt("stu_age"),
                rs.getString("stu_sex"));


    }
} catch (SQLException e) {
    throw new RuntimeException(e);
} finally {
    DBUtil.closeAll(rs, ps, conn);
}
System.out.println(student);

结果

Student(stuId=1, stuName=小红, stuAge=18, suSex=女)