JDBC简单增、删、改、查案例（基于MySQL8.0）

最新推荐文章于 2022-09-24 16:55:40 发布

ZX9727

最新推荐文章于 2022-09-24 16:55:40 发布

阅读量322

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.youkuaiyun.com/ZX9727/article/details/86708835

版权

数据库专栏收录该内容

6 篇文章

订阅专栏

本文展示了一个使用Java JDBC操作MySQL数据库的CRUD（创建、读取、更新、删除）实例，包括连接数据库、执行SQL语句、处理结果集等关键步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本案例使用IDEA编写，编译环境jdk11，MySQL版本8.0.14，使用maven导入依赖包

package com.myhost;

import org.junit.Test;

import java.sql.*;

public class JdbcDemo {
    @Test
//    查多
    public void test1() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
//          1、加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
//          2、获得连接
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/jdbctest?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT%2B8", 
                    "root", 
                    "mypasswd");
//          3、创建sql语句的对象，并执行sql
//          3.1、创建sql对象
            String sql = "SELECT * FROM user;";
            stmt = conn.createStatement();
//          3.2、执行sql
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                int uid = rs.getInt("uid");
                String username = rs.getString("username");
                String password = rs.getString("password");
                String name = rs.getString("name");
                System.out.println("uid:" + uid + "\tusername:" + username + "\tpassword:" + password + "\tname:" + name);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
//          4、释放资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
//              手动置空可以早点被gc回收
                rs = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
    }


    @Test
//  增
    public void test2() {
        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/jdbctest?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT%2B8", 
                    "root", 
                    "mypasswd");
            stmt = conn.createStatement();
            String sql = "INSERT user VALUES (NULL, 'zl', '123', '老王')";
            int i = stmt.executeUpdate(sql);
            if (i > 0) {
                System.out.println("保存成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
    }

    @Test
//  改
    public void test3() {
        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/jdbctest?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT%2B8", 
                    "root", 
                    "mypasswd");
            stmt = conn.createStatement();
            String sql = "UPDATE user SET username='xl', password='456', name='小李' WHERE uid=4";
            int i = stmt.executeUpdate(sql);
            if (i > 0) {
                System.out.println("修改成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
    }

    @Test
//  删
    public void test4() {
        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/jdbctest?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT%2B8", 
                    "root", 
                    "mypasswd");
            stmt = conn.createStatement();
            String sql = "DELETE FROM user WHERE uid=4";
            int i = stmt.executeUpdate(sql);
            if (i > 0) {
                System.out.println("删除成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
    }

    @Test
//  查单
    public void test5() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/jdbctest?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT%2B8", 
                    "root", 
                    "mypasswd");
            stmt = conn.createStatement();
            String sql = "SELECT uid,username,name FROM user WHERE uid =2";
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                System.out.println("uid:" + rs.getInt("uid") + "\tusername:" + rs.getString("username") + "\tname:" + rs.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                rs = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
    }
}

-- 数据库代码
CREATE DATABASE jdbctest;
USE jdbctest;
CREATE TABLE user(
    uid INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20),
    password VARCHAR(20),
    name VARCHAR(20)
);
INSERT user VALUES (NULL, 'aaa', '111', 'Alice'), (NULL, 'bbb', '222', 'Bob'), (NULL,'ccc', '333', 'Cidy');

<!--pom.xml中的dependencies-->
 <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.14</version>
    </dependency>