JDBC

原理

JDBC是一种执行SQL语句的JavaAPI,为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。

image-20240418223203980

所以我们就是要学习JDBC规范来应用这些jar包里面的类。

JDBC访问数据库编码步骤：

1、加载驱动Driver

2、创建数据库连接Connection

3、创建一个发送Sql的发送器Statement

4、通过Statement发送sql语句

5、处理sql语句的结果

6、关闭资源 ResultSet Statement Connection

具体代码步骤

首先我们要先导入lib包才能用里面的类，或者导入依赖

image-20240418230416679

增加数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
package com.bitzh;

import java.sql.*;

/**
 * @Auther: oyy0v0
 * @Date: 2024/4/18 - 04 - 18 - 23:01
 * @Description: com.bitzh
 * @version: 1.0
 */
public class testJDBC {
    public static void main(String[] args) throws SQLException {
        //1、加载并注册驱动Driver
        Driver driver = new com.mysql.cj.jdbc.Driver();
        DriverManager.registerDriver(driver);
        //2、创建连接Connection
        String url = "jdbc:mysql://127.0.0.1:3306/mytestdb3?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url,user,password);
        //3、创建发送sql的statement
        Statement statement = connection.createStatement();
        //4、利用statement发送sql
        String sql = "insert into user values(2,'小红','深圳莆田','女')";
        int rows = statement.executeUpdate(sql);
        System.out.println("影响的数据："+rows);
        //5、由于这里是增加数据，不用处理数据，如果是select的话就需要处理返回的数据
        //6、关闭资源
        statement.close();
        connection.close();
    }
}

删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
package com.bitzh;


import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Statement;

/**
 * @Auther: oyy0v0
 * @Date: 2024/4/19 - 04 - 19 - 23:06
 * @Description: com.bitzh
 * @version: 1.0
 */
public class testJDBC2 {
    public static void main(String[] args) throws Exception {
        //1、加载驱动
        Driver driver = new com.mysql.cj.jdbc.Driver();
        //注册驱动
        DriverManager.registerDriver(driver);
        //2、创建连接connection
        String url = "jdbc:mysql://127.0.0.1:3306/mytestdb3?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, user, password);
        //3、创建发送sql的statement
        Statement statement = connection.createStatement();
        //4、执行sql语句获取返回结果
        String sql = "delete from user where id = 2";
        int i = statement.executeUpdate(sql);
        System.out.println("影响的数据"+i);
        //5、本来是处理结果但是这里不用处理所以就直接关闭资源就好了
        statement.close();
        connection.close();
    }
}

修改数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
package com.bitzh;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Statement;

/**
 * @Auther: oyy0v0
 * @Date: 2024/4/19 - 04 - 19 - 23:37
 * @Description: com.bitzh
 * @version: 1.0
 */
public class testJDBC3 {
    public static void main(String[] args) throws Exception{
        //1、加载并注册驱动
        Driver driver = new com.mysql.cj.jdbc.Driver();
        DriverManager.registerDriver(driver);
        //2、创建连接数据库的链接
        String url = "jdbc:mysql://127.0.0.1:3306/mytestdb3?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url,user,password);
        //3、创建发送sql的statement
        Statement statement = connection.createStatement();
        //4、利用statement发送sql
        String sql = "update user set username='小明' where id = 1";
        int i = statement.executeUpdate(sql);
        System.out.println("影像数据"+i);
        //5、处理数据，这里不用处理所以就关闭资源
        statement.close();
        connection.close();


    }
}

查询数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
package com.bitzh;

import java.sql.*;

/**
 * @Auther: oyy0v0
 * @Date: 2024/4/20 - 04 - 20 - 0:05
 * @Description: com.bitzh
 * @version: 1.0
 */
public class testJDBC4 {
    public static void main(String[] args) throws Exception{
        //1、加载驱动并注册驱动
        Driver driver = new com.mysql.cj.jdbc.Driver();
        DriverManager.registerDriver(driver);
        //2、创建连接数据库的链接Connection
        String url = "jdbc:mysql://127.0.0.1:3306/mytestdb3?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url,user,password);
        //3、创建发送sql的statement
        Statement statement = connection.createStatement();
        //4、利用statement发送sql
        String sql = "select * from user";
        ResultSet resultSet = statement.executeQuery(sql);
        //5、处理sql语句的返回结果
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String address = resultSet.getString("address");
            String gender = resultSet.getString("gender");
            System.out.println("id:"+id+"\t"+"username:"+username+"\t"+"address:"+address+"\t"+"gender:"+gender);
        }
        //6、关闭资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}