69期-Java SE-037_JDBC-1

 

### JDBC

Java Database Connectivity 是一个独立于特定数据库的管理系统，通用的 SQL 数据库存取和操作的公共接口。

定义了一组标准，为访问不同数据库提供了统一途径。

JDBC 体系结构

两个层面：

- 面向应用的 API，供开发人员调用。
- 面向数据库的 API，供数据库开发厂商开发数据库驱动程序。

JDBC API

提供者：Java 官方

内容：供开发者调用的接口

java.sql 或者 javax.sql 包中

DriverManager 类：管理数据库驱动

Connection 接口：连接数据库

Statement 接口：执行 SQL 

ResultSet 接口：封装结果集



Driver Manager

提供者：Java 官方

作用：为不同的数据库产品提供统一的接入标准。



JDBC 驱动

提供者：数据库厂商

作用：让 Java 完成与特定数据库的对接。



### 使用 JDBC 原理

1、加载数据库驱动，Java Application 和 数据库的桥梁。

2、获取 Connection，一次连接。

3、通过 Connection 对象产生 Statement，执行 SQL 语句。

4、ResultSet 保存 Statment 执行后所产生的结果。



```java
package com.southwind.test;

import com.southwind.entity.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Test {
    public static void main(String[] args) {
        delete();
    }

    public static void save(){
        try {
            Connection connection = getConn();
            //3、定义 SQL
            String sql = "insert into t_user(username,password,age) values('conn','000',18)";
            //4、执行 SQL
            Statement statement = connection.createStatement();
            int result = statement.executeUpdate(sql);
            System.out.println(result);
            statement.close();
            connection.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

    public static void find(){
        try {
            Connection connection = getConn();
            String sql = "select * from t_user where id = 2";
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            User user = new User();
            if(resultSet.next()){
                long id = resultSet.getLong(1);
                String username = resultSet.getString(2);
                String password2 = resultSet.getString(3);
                int age = resultSet.getInt(4);
                user.setId(id);
                user.setUsername(username);
                user.setPassword(password2);
                user.setAge(age);
            }
            System.out.println(user);
            statement.close();
            connection.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

    public static void findAll(){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = getConn();
            String sql = "select * from t_user";
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            List<User> list = new ArrayList<>();
            User user = null;
            while(resultSet.next()){
                long id = resultSet.getLong(1);
                String username = resultSet.getString(2);
                String password2 = resultSet.getString(3);
                int age = resultSet.getInt(4);
                user = new User(id,username,password2,age);
                list.add(user);
            }
            for(User user1 :list){
                System.out.println(user1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(connection != null){
                    connection.close();
                }
                if(statement != null){
                    statement.close();
                }
                if(resultSet != null){
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void update(){
        Connection connection = null;
        Statement statement = null;
        try {
            connection = getConn();
            String sql = "update t_user set username = 'tom',password = '100',age = 16";
            statement = connection.createStatement();
            System.out.println(statement.executeUpdate(sql));
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(connection!=null){
                    connection.close();
                }
                if(statement!=null){
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public static void delete(){
        Connection connection = null;
        Statement statement = null;
        try{
            connection = getConn();
            String sql = "delete from t_user where id = 2";
            statement = connection.createStatement();
            System.out.println(statement.executeUpdate(sql));
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(connection != null){
                    connection.close();
                }
                if(statement != null){
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static Connection getConn(){
        Connection connection = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/mbtest?useUnicode=true&characterEncoding=UTF-8";
            String user = "root";
            String password = "root";
            connection = DriverManager.getConnection(url,user,password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
}
```



根据数据表创建相应的类，叫做实体类。

```java
package com.southwind.entity;

public class User {
    private Long id;
    private String username;
    private String password;
    private Integer age;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", age=" + age +
                '}';
    }

    public User(Long id, String username, String password, Integer age) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.age = age;
    }

    public User() {
    }
}
```



### 加载驱动

Class.forName("com.mysql.cj.jdbc.Driver");

反射机制，获取运行时类，什么是运行时类？Java 程序是由类组成的，运行时，会将所有的类添加到 JVM 内存中，并且每个类只有一份，保证在 JVM 内存中的类就叫做运行时类。

运行时类是一个动态概念，只有当程序运行的时候，才有运行时类。



Statement 的方法，Statement 是通过 Connection 产生的，是用来执行 SQL 语句的，常用的方法：

- ResultSet executeQuery(String sql) 用来执行查询操作。

- int executeUpdate(String sql) 用来执行新增，修改，删除操作。

- boolean execute(String sql) 可以执行任意的 CRUD 操作。

  true 表示返回结果是 ResultSet，执行的是查询操作。

  false 表示返回结果不是 ResultSet，执行的是新增、修改、删除操作。

  

/entity/User.java

package com.southwind.entity;

public class User {
    private Long id;
    private String username;
    private String password;
    private Integer age;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", age=" + age +
                '}';
    }

    public User(Long id, String username, String password, Integer age) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.age = age;
    }

    public User() {
    }
}

test/Test.java

package com.southwind.test;

import com.southwind.entity.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Test {
    public static void main(String[] args) {
        test();
    }

    public static void save(){
        try {
            Connection connection = getConn();
            //3、定义 SQL
            String sql = "insert into t_user(username,password,age) values('conn','000',18)";
            //4、执行 SQL
            Statement statement = connection.createStatement();
            int result = statement.executeUpdate(sql);
            System.out.println(result);
            statement.close();
            connection.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

    public static void find(){
        try {
            Connection connection = getConn();
            String sql = "select * from t_user where id = 2";
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            User user = new User();
            if(resultSet.next()){
                long id = resultSet.getLong(1);
                String username = resultSet.getString(2);
                String password2 = resultSet.getString(3);
                int age = resultSet.getInt(4);
                user.setId(id);
                user.setUsername(username);
                user.setPassword(password2);
                user.setAge(age);
            }
            System.out.println(user);
            statement.close();
            connection.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

    public static void findAll(){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = getConn();
            String sql = "select * from t_user";
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            List<User> list = new ArrayList<>();
            User user = null;
            while(resultSet.next()){
                long id = resultSet.getLong(1);
                String username = resultSet.getString(2);
                String password2 = resultSet.getString(3);
                int age = resultSet.getInt(4);
                user = new User(id,username,password2,age);
                list.add(user);
            }
            for(User user1 :list){
                System.out.println(user1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(connection != null){
                    connection.close();
                }
                if(statement != null){
                    statement.close();
                }
                if(resultSet != null){
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void update(){
        Connection connection = null;
        Statement statement = null;
        try {
            connection = getConn();
            String sql = "update t_user set username = 'tom',password = '100',age = 16";
            statement = connection.createStatement();
            System.out.println(statement.executeUpdate(sql));
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(connection!=null){
                    connection.close();
                }
                if(statement!=null){
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public static void delete(){
        Connection connection = null;
        Statement statement = null;
        try{
            connection = getConn();
            String sql = "delete from t_user where id = 2";
            statement = connection.createStatement();
            System.out.println(statement.executeUpdate(sql));
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(connection != null){
                    connection.close();
                }
                if(statement != null){
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static Connection getConn(){
        Connection connection = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/mbtest?useUnicode=true&characterEncoding=UTF-8";
            String user = "root";
            String password = "root";
            connection = DriverManager.getConnection(url,user,password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void test(){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try{
            connection = getConn();
//            String sql = "select * from t_user where id = 3";
//            String sql = "insert into t_user(username,password,age) values('aaa','111',22)";
//            String sql = "update t_user set username = 'aa',password='11',age=11";
            String sql = "delete from t_user";
            statement = connection.createStatement();
            boolean flag = statement.execute(sql);
            System.out.println(flag);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(connection!=null){
                    connection.close();
                }
                if(statement!=null){
                    statement.close();
                }
                if(resultSet!=null){
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}