JDBC 数据库操作

一、引言 1.1 如何操作数据库

使用客户端工具访问数据库，需要手工建立连接，输入用户名和密码登录，编写 SQL 语句，点击执行，查看操作结果

客户端操作数据库步骤

1.2 实际开发中，会采用客户端操作数据库吗？

在实际开发中，当用户的数据发生改变时，不可能通过客户端操作执行 SQL 语句，因为操作了过大，无法保证效率和正确率

二、JDBC （Java Database Connectivity） 2.1 什么是 JDBC?

JDBC（Java Database Connectivity）Java 连接数据库，可以使用 Java 语言连接数据库完成 CRUD 操作。

2.2 JDBC 核心思想

Java zhong 定义了访问数据库的接口，可以为多种关系型数据库提供统一的访问方式。渔鸥数据库厂商提供驱动实现类（Driver 数据库驱动）。

核心思想

2.2.1 Mysql 数据库驱动

mysql-connector-java-5.1.X.jar 适用于 5.X 版本mysql-connector-java-8.0.X.jar 适用于 8.X 版本

2.2.2 JDBC API

JDBC 是由多个接口和类进行功能实现。

类型	权限定名	简介
class	java.sql.DriverManager	管理多个数据库驱动类，提供了获取数据库连接的方法
interface	java.sql.Connection	代表一个数据库连接（当 connection 不是 null 时，表示已连接数据库）
interface	java.sql.Statement	发送 SQL 语句到数据库工具
interface	java.sql.ResultSet	保存 SQL 查询语句的结果数据（结果集）
class	java.sql.SQLException	处理数据库运用程序时所发生的异常

2.3 环境搭建

在项目下新建 lib 文件夹，用于存放 jar 文件将 mysql 驱动 mysql-connector-java-8.0.18.jar 复制到项目的 lib 文件夹中选中 lib 文件夹右键 Add as Libraay，点击 ok

三、JDBC 开发步骤【重点】 3.1 注册驱动

使用 Class.forName(“com.mysql.cj.jdbbc.Driver”); 手动加载字节码文件到 JVM 中

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

3.2 连接数据库

通过 DriveManager.getConnection(url,user,password) 获取数据库连接对象
URL=jdbc:mysql://locallhost:3306/databaseusername=rootPassword=199610fan

DriverManager.getConnection("jdbc:mysql://localhost:3306/day17","root","199610fan");

URL（Uniform Resource Locator）统一资源定位符：由协议、IP、端口、SID（程序实例名称）组成 3.3 获取发送 SQL 对象

通过 Connection 对象获得 Statement 对象，用于数据库进行通用访问

Statement statement = connection.createStatement();

3.4 执行 SQL 语句

执行 SQL 语句并接收执行结果

Statement statement = connection.createStatement();

String sql = "INSERT INTO users(user_id,user_name,user_pwd,user_realname,user_img) VALUES(12,'luogenhua','199610','罗根华','ing.jpg')";

int result = statement.executeUpdate(sql);

注意：在编写 DML 语句时，一定要注意字符串参数的符号是单引号 ‘值’DML 语句：增删改时，返回受影响行数（int 类型）DQL 语句：查询时，返回结果数据（ResultSet 结果集） 3.5 处理结果

接收处理操作结果

if(result == 1){
  System.out.println("Success");
}

受影响行数：逻辑判断，方法返回查询结果集：迭代、一次获取 3.6 释放资源

遵循先开后关原则

statement.close();
connection.close();

3.7 整个代码

import com.mysql.jdbc.Driver;

import java.sql.*;

public class JDBCTest {
  public static void main(String[] args) throws ClassNotFoundException, SQLException {
      //1、加载驱动 加载驱动
      Class.forName("com.mysql.cj.jdbc.Driver");

      String url = "jdbc:mysql://localhost:3306/day17";
      String user = "root";
      String password = "199610fan";

      //2、获得连接
      Connection connection = DriverManager.getConnection(url, user, password);

      if (connection != null){
        System.out.println("连接成功");
      }else {
        System.out.println("连接失败");
      }

      //3、获得执行 sql 语句的对象
      Statement statement = connection.createStatement();


      //4、编写 sql 语句，并执行语句
      String sql = "INSERT INTO users(user_id,user_name,user_pwd,user_realname,user_img) 			           VALUES(12,'luogenhua','199610','罗根华','ing.jpg')";

      int i = statement.executeUpdate(sql);

      //5、处理接收结果
      if (i == 1){
        System.out.println("执行成功");
      }else {
        System.out.println("执行失败");
      }

      //6、释放资源，先开后关
      statement.close();
      connection.close();
    }
}

四、ResultSet（结果集）

在执行查询 SQL 后，存放查询到的结果集数据

4.1 接收结果集

ResultSet resultSet = statement.executeQuery(sql);

ResultSet resultSet = statement.executeQuery(SELECT * FROM USERS);

4.2 遍历 ResultSet 中的数据

ResultSet 以表（table）结构进行临时结果的存储，需要通过 JDBC API 将其中数据进行一次获取

数据行指针：初始位置在第一行数据前，每调用一次 boolean next() 方法 ResultSet 的指针向下移动一行，结果为 true，表示当前行有数据rs.getXxx(整数)，代表根据列的编号顺序获得，从1开始。rs.getXxx(“列名”)，代表根据列名获得。

boolean next = resultSet.next();

4.3 遍历方法

int getInt(int columnIndex)
int getInt(String columnLabel)
...
String getString(int columnIndex)
String getString(String columnLabel)
...

4.4 整个代码

package com.luo;

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

public class QueryJdbc {
    public static void main(String[] args) throws Exception {
        //1 加载驱动 加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3306/day17";
        String user = "root";
        String password = "199610fan";

        //2 获得连接
        Connection connection = DriverManager.getConnection(url, user, password);

        if (connection != null){
            System.out.println("连接成功");
        }else {
            System.out.println("连接失败");
        }

        //3 获得执行 sql 语句的对象
        Statement statement = connection.createStatement();

        // 4 编写 sql 语句 并执行
        String sql = "SELECt * FROM USERS";
        ResultSet resultSet = statement.executeQuery(sql);

        //5 处理结果
        // 判断是否有数据
        while (resultSet.next()){
            String user_id = resultSet.getString("user_id");

            String user_name = resultSet.getString("user_name");

            String user_pwd = resultSet.getString("user_pwd");

            String user_realname = resultSet.getString("user_realname");

            String user_img = resultSet.getString("user_img");

            System.out.println(user_id+"t"+user_name +"t"+ user_pwd +"t"+ user_realname +"t"+ user_img);
        }

        // 6 释放资源，先开后关
        statement.close();
        connection.close();
    }
}

五、常见错误

Java.lang.ClassNotFoundException：找不到类（类名书写错误，没有导入 jar 包）java.sql.SQLException：与 sql 语句相关的错误（约束错误、表名列名书写错误）建议：在客户端工具中测试 SQL 语句之后再粘贴到代码中

六、综合案列【登入】 6.1 创建一张表

创建一张用户表 user
id：主键、自动增长用户名：字符串类型，非空密码：字符串类型，非空手机号码：字符串类型插入 2 测试语句

CREATE TABLE user(
		id INT PRIMARY KEY AUTO_INCREMENT,
		username VARCHAR(20) UNIQUE NOT NULL,
		password VARCHAR(20) NOT NULL,
		phone VARCHAR(11)
);

6.2 实现登入

通过控制台用户输入用户名和密码用户输入的用户名和密码作为条件，编写查询 SQL 语句如果该用户存在，提示登入成功，否则提示失败

package com.luo;

import java.sql.*;
import java.util.Scanner;

public class LogJdbc {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Scanner scanner = new Scanner(System.in);

        System.out.print("请输入用户名：");

        String username = scanner.next();

        System.out.print("请输入密码：");

        String pwd = scanner.next();

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

        String url = "jdbc:mysql://localhost:3306/day14";
        String user = "root";
        String password = "199610fan";

        Connection connection = DriverManager.getConnection(url, user, password);

        Statement statement = connection.createStatement();

        String sql = "select * from user where username = '"+username+"' and password = '"+pwd+"'";

        ResultSet resultSet = statement.executeQuery(sql);

        if (resultSet.next()){
            System.out.println("登入成功");
        }else{
            System.out.println("登入失败");
        }

        statement.close();
        connection.close();

    }
}

七、SQL 注入问题 7.1 什么是 SQL 注入

用户输入的数据中有 SQL 关键字或语句并且参与了 SQL 语句的编译，导致 SQL 语句编译后的条件含义为 true，一直得到正确的结果。这种现象称为 SQL 注入。

7.2 如何避免 SQL 注入

由于编写的 SQL 语句时用户输入的数据，整合后在进行编译。所以为了避免 SQL 注入的问题，我们要使用 SQL 语句中在用户输入数据前就以及进行编译完整的 SQL 语句，在进行填充数据。

八、PreparedStatement【重点】

作用：1、欲编译 SQL 语句，效率高

2、安全，避免 SQL 注入

3、可以动态的填充数据，执行多个同构的 SQL 语句

8.1.1 参数标记

// 预编译 SQL 语句
PreparedStatement pstmt = connection.prepareStatement("select * from user where username = ? and password = ?");

注意：JDBC 中的所有参数都是由 ? 符号占位，这被称为参数标记。在执行 SQL 语句之前，必须为每个参数提供值 8.1.2 动态参数绑定

pstmt.setXxx(下标，值) 参数下标从 1 开始，为指定参数下标才绑定值

PreparedStatement pstmt = connection.prepareStatement("select * from user where username = ? and password = ?");

pstmt.setString(1,username);
pstmt.setString(2,pwd);

8.1.3 整个代码

package com.luo;

import java.sql.*;
import java.util.Scanner;

public class PrepareStatement {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Scanner scanner = new Scanner(System.in);

        System.out.print("请输入用户名：");

        String username = scanner.next();

        System.out.print("请输入密码：");

        String pwd = scanner.next();

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

        String url = "jdbc:mysql://localhost:3306/day14";
        String user = "root";
        String password = "199610fan";

        Connection connection = DriverManager.getConnection(url, user, password);

        // 预编译 SQL 语句
        PreparedStatement pstmt = connection.prepareStatement("select * from user where username = ? and password = ?;");

        pstmt.setString(1,username);
        pstmt.setString(2,pwd);

        ResultSet resultSet = pstmt.executeQuery();

        if (resultSet.next()){
            System.out.println("登入成功");
        }else {
            System.out.println("登入失败，用户名或密码错误");
        }

        pstmt.close();
        connection.close();
    }
}

九、封装工具类

在实际 JDBC 使用中，存在着大量的重复代码：列如连接数据库、关闭数据库等这些操作我们需要把传统的 JDBC 代码进行重构，抽取出通用的 JDBC 工具类。以后连接任何数据库、释放资源都可以使用这个工具类

9.1 重用性方案

封装获取连接、释放资源两个方法。
提供 public static Connection getConnection(){} 方法提供 public static void closeAll(Connection conn, Statement sm,ResultSet rs){} 方法。

9.2 整儿代码

package com.luo;

import java.sql.*;

public class DBUtils {

    // 类加载，只加载一次
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //1.获取连接
    public static Connection getConnection(){

        String url = "jdbc:mysql://localhost:3306/day14";
        String user = "root";
        String password = "199610fan";

        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //2、释放资源
    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){

        try{
            if (connection != null){
                connection.close();
            }
            if (statement != null){
                statement.close();
            }
            if (resultSet != null){
                resultSet.close();
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
}

9.3跨平台方案

定义 public static final Properties prop = new Properties(); //获取配置文件的 Map

定义 static{

//首次使用工具类时，加载驱动

InputStream is = JDBCUtil.class.getResourceAsStream(“路径”); // 通过复用本类自带流，读取 jdbc.properties 配置文件

prop.load(is); //通过 prop 对象将流中的配置信息分割成键值对

String driverName = prop.getProperty(“driver”) // 通过 driverName 的键获取对应的值 com.mysql.cj.jdbc.Driver

Class.forName(driverName); // 加载驱动

}

9.3.1 跨平台工具类实现

在 src 目录下新建 db.properties 文件

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/day14
user=root
password=199610fan

工具类的封装

package com.luo2;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class DBUtils {
    private static final Properties PROPERTIES = new Properties();
    static {
        InputStream is = DBUtils.class.getResourceAsStream("/db.properties");
        try {
            PROPERTIES.load(is);
            Class.forName(PROPERTIES.getProperty("driver"));
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    // 获取 Connection 对象
    public static Connection getConnection() {
        Connection connection = null;

        try {
           connection = DriverManager.getConnection(
                                                   PROPERTIES.getProperty("url"),
                                                   PROPERTIES.getProperty("user"),
                                                   PROPERTIES.getProperty("password"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    // 关闭 资源 方法
    public static void closeAll(Connection connection, Statement statement, ResultSet resultset){
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (resultset != null){
            try {
                resultset.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

十、ORM

ORM （Object Relational Mapping）

从数据库查询到的结果集（ResultSet）再进行遍历的时候，逐行遍历，取出的都是零散的数据。在实际应用开发中，我们需要将零散的数据进行封装整理

10.1 实体类（entity）：零散数据的载体

一行数据中，多个零散的数据进行整理通过 entity 的规则对表中的数据进行对象的封装表名 = 类名；列名 = 属性名；提供多个属性的 get、set 方法提供无参结构方法，视情况添加有参构造

10.2 表对应的实体类代码

package com.luo2;

public class User {
    private String id;
    private String username;
    private String password;
    private String phone;

    public User() {
    }

    public User(String id, String username, String password, String phone) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.phone = phone;
    }

    @Override
    public String toString() {
        return "user{" +
                "id='" + id + ''' +
                ", username='" + username + ''' +
                ", password='" + password + ''' +
                ", phone='" + phone + ''' +
                '}';
    }

    public String getId() {
        return id;
    }

    public void setId(String 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 String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }
}

10.3 测试类代码

package com.luo2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TestORM {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List list = new ArrayList();

        try {
            connection = DBUtils.getConnection();

            preparedStatement = connection.prepareStatement("select * from user");

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()){
                String id = resultSet.getString("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String phone = resultSet.getString("phone");

//                System.out.println(id +"t"+ username +"t"+ password +"t"+ phone);

                User user = new User();
                user.setId(id);
                user.setUsername(username);
                user.setPassword(password);
                user.setPhone(phone);

                // 没遍历一次得到对象，存放在集合里，方便后续的使用
                list.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,resultSet);
        }

        //遍历集合
        for (User u:list) {
            System.out.println(u);
        }
    }
}

十一、DAO 数据访问对象（Data Access Object）

DAO 实现了业务逻辑与数据库访问相分离
对同一张表的所有操作封装在 XxxDaoImpl 对象中根据增删改查的不同功能实现具体的方法（insert、update、delete、select、selectAll）

11.1 创建数据库

创建一张表 Person，有以下列
id：int，主键，自动增长name：varchar(20)，非空age：int，非空bornDate：Dateemail：字符串address：字符串

11.2 数据库代码

CREATE TABLE Person(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL,
	age INT NOT NULL,
	bornDate DATE,
	email VARCHAR(20),
	address VARCHAR(20)
)CHARSET=utf8;

11.3 封装实体类

创建 entity 实体类 Person ，编写属性私有化、构造方法、get/set 方法

package com.person;

import java.util.Date;

public class Person {

    private int id;
    private String name;
    private int age;
    private Date bornDate;
    private String email;
    private String address;

    public Person() {
    }

    public Person(String name, int age, Date bornDate, String email, String address) {
        this.name = name;
        this.age = age;
        this.bornDate = bornDate;
        this.email = email;
        this.address = address;
    }

    public Person(int id, String name, int age, Date bornDate, String email, String address) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.bornDate = bornDate;
        this.email = email;
        this.address = address;
    }

    @Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", age=" + age +
                ", date=" + bornDate +
                ", email='" + email + ''' +
                ", address='" + address + ''' +
                '}';
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

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

    public Date getBornDate() {
        return bornDate;
    }

    public void setBornDate(Date date) {
        this.bornDate = date;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

11.4 编写 DaoImpl 类

编写 DaoImpl 类，提供增、删、改、查方法，使用 JDBC 开发步骤，完成功能

package com.person;

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

public class PersonDaoImpl {
    // 新增方法
    public int insert(Person person){
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        String sql = "insert into person(name,age,borndate,email,address) values(?,?,?,?,?)";

        try {
            connection = DBUtils.getConnection();
            preparedStatement =  connection.prepareStatement(sql);
            preparedStatement.setString(1,person.getName());
            preparedStatement.setInt(2,person.getAge());
            preparedStatement.setDate(3,null);
            preparedStatement.setString(4,person.getEmail());
            preparedStatement.setString(5,person.getAddress());

            int result = preparedStatement.executeUpdate();

            return result;
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,null);
        }
        return 0;
    }

    //根据 id 更新
    public int update(Person person){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
      
        String sql = "update  Person set name = ?,age = ?,bornDate = ?,email = ?,address = ? where id= ?";
        try {

            connection = DBUtils.getConnection();

            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1,person.getName());
            preparedStatement.setInt(2,person.getAge());
            preparedStatement.setDate(3,null);
            preparedStatement.setString(4,person.getEmail());
            preparedStatement.setString(5,person.getAddress());
            preparedStatement.setInt(6,person.getId());

            int i = preparedStatement.executeUpdate();

            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,null);
        }
        return  0;
    }

    //根据 id 删除
    public int delete(int id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql = "delete from Person where id = ?";

        connection = DBUtils.getConnection();

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);

            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,null);
        }
        return 0;
    }

    // 根据 id 查单个
    public Person select(int id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Person person = null;

        String sql = "select * from  Person where id = ?";

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()){
                int pid = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                Date bornDate = resultSet.getDate("bornDate");
                String email = resultSet.getString("email");
                String address = resultSet.getString("address");

                person = new Person();

                person.setId(pid);
                person.setName(name);
                person.setAge(age);
                person.setBornDate(bornDate);
                person.setEmail(email);
                person.setAddress(address);
            }
            return person;

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,resultSet);
        }
        return null;
    }

    // 查所有
    public List selectAll(){

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Person person = null;
        List personList = new ArrayList();

        String sql = "select * from Person";

        connection = DBUtils.getConnection();

        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();

           while (resultSet.next()){
                   int pid = resultSet.getInt("id");
                   String name = resultSet.getString("name");
                   int age = resultSet.getInt("age");
                   Date bornDate = resultSet.getDate("bornDate");
                   String email = resultSet.getString("email");
                   String address = resultSet.getString("address");

                   person = new Person(pid,name,age,bornDate,email,address);

                   personList.add(person);
               }

           return personList;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,resultSet);
        }
        return null;
    }
}

JDBC 数据库操作

Java相关栏目本月热门文章