MySQL | JDBC 基础

MySQL | JDBC 基础

基本概念

  • JDBC 即 Java DataBase Connectivity,Java 数据库连接,简而言之就是使用 Java 语言操作数据库
  • JDBC 的本质是 Java 官方定义的一套操作所有关系型数据库的规则(Java 中体现为接口),再由各个数据库厂商实现这套接口,提供数据库驱动 jar 包,程序员可以使用这套接口(JDBC)编程,而真正执行 SQL 语句的代码是驱动 jar 包中的实现类。

JDBC本质

简单实例

使用 JDBC 完成数据库的增删改查操作是有一套固定流程的

  1. 导入驱动 jar 包,这里使用 5.1.37 版本(文章尾部有下载地址),如果使用 IDEA ,可以在 project 或 moduel 下新建一个目录 libs,专门用于存放外部类库,将 jar 包放到 libs 下,右键 Add As Library
  2. 注册驱动
  3. 获取数据库连接对象 Connection
  4. 定义 sql 语句
  5. 获取执行 sql 语句的对象 Statement
  6. 执行 sql,接收返回结果
  7. 处理结果
  8. 释放资源
package cool.yzt.jdbc;

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

public class JdbcDemo {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        try {
			// 注册驱动
			Class.forName("com.mysql.jdbc.Driver");
            // 获取数据库连接
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=utf8","root","root");
            // 定义sql语句,向emp表中插入一条记录
            String sql = "insert into emp(name,gender,salary,join_date,dept_id) values('紫霞仙子','女',10000,'2020-05-01',1)";
			// 从数据库连接获取执行sql的Statement对象
            statement = connection.createStatement();
			// 执行sql并接收结果
            int count = statement.executeUpdate(sql);
			// 处理结果
            System.out.println(count);
            if(count>0) {
                System.out.println("添加成功!");
            }else {
                System.out.println("添加失败!");
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            if(statement!=null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

JDBC 中的重要对象

DriverManager 驱动管理对象

功能

  1. 注册给定的驱动程序驱动,即告诉程序该使用哪一个数据库驱动 jar 包。这个功能由静态方法 static void registerDriver(Driver driver) 完成,但是实际代码中使用Class.forName("com.mysql.jdbc.Driver"),这是因为 com.mysql.jdbc.Driver 类中存在静态代码块,随着该类的加载而执行

    	static {
    		try {
    			java.sql.DriverManager.registerDriver(new Driver());
    		} catch (SQLException E) {
    			throw new RuntimeException("Can't register driver!");
    		}
    	}
    

    注意:MySQL5 之后的驱动 jar 包可以省略注册驱动的步骤

  2. 获取数据库连接

    • 方法:static Connection getConnection(String url, String user, String password)
    • 参数:
      1. url:指定连接的路径
        • 语法:jdbc:mysql://ip地址(域名):端口号/数据库名称
        • 例子:jdbc:mysql://localhost:3306/db3
        • 细节:如果连接的是本机mysql服务器,并且mysql服务默认端口是3306,则url可以简写为:jdbc:mysql:///数据库名称
      2. user:用户名
      3. password:密码

Connection 数据库连接对象

功能

  1. 获取执行 sql 的 Statement/PreparedStatement 对象
    • Statement createStatement()
    • PreparedStatement prepareStatement(String sql)
  2. 管理事务
    • 开启事务:setAutoCommit(boolean autoCommit),如果调用该方法设置参数为 false,即开启事务
    • 提交事务:commit()
    • 回滚事务:rollback()

Statement:执行 SQL 的对象

功能:执行sql

  1. boolean execute(String sql) :可以执行任意的sql 了解
  2. int executeUpdate(String sql) :执行DML(insert、update、delete)语句和DDL(create,alter、drop)语句
    • 返回值:影响的行数,可以通过这个影响的行数判断DML语句是否执行成功 返回值>0的则执行成功,反之,则失败。
  3. ResultSet executeQuery(String sql):执行DQL(select)查询语句

ResultSet:结果集对象

功能:封装查询结果

  • boolean next(): 游标向下移动一行(游标初始位置可以看做在结果集表头),并判断当前行是否是最后一行末尾(是否有数据),如果是,则返回false,如果不是则返回true
  • getXxx(参数):获取数据
    • Xxx:代表数据类型,如:int getInt() , String getString()
    • 参数:
      1. int:代表字段的编号,从1开始,如:getString(1)
      2. String:代表字段名称,如:getDouble("balance")
  • 一般的使用方法
    1. 游标向下移动一行
    2. 判断是否有数据
    3. 如果有则获取数据
//循环判断游标是否是最后一行末尾。
while(rs.next()){
    //获取数据
    int id = rs.getInt(1);
    String name = rs.getString("name");
    double balance = rs.getDouble(3);
    System.out.println(id + "---" + name + "---" + balance);
}

实例 查询 emp 表并将结果集封装为对象,存储在 List 中

package cool.yzt.domain;

import java.util.Date;

/**
 * @Author: yzt
 * @Description: 封装一个实体类,对应emp表
 */
public class Emp {
    // 实体类的各个属性对应emp表的各个字段
    private int id;
    private String name;
    private String gender;
    private int salary;
    private Date join_date;
    private int dept_id;

    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 String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public int getSalary() {
        return salary;
    }

    public void setSalary(int salary) {
        this.salary = salary;
    }

    public Date getJoin_date() {
        return join_date;
    }

    public void setJoin_date(Date join_date) {
        this.join_date = join_date;
    }

    public int getDept_id() {
        return dept_id;
    }

    public void setDept_id(int dept_id) {
        this.dept_id = dept_id;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", gender='" + gender + '\'' +
                ", salary=" + salary +
                ", join_date=" + join_date +
                ", dept_id=" + dept_id +
                '}' + '\n';
    }
}
package cool.yzt.jdbc;

import cool.yzt.domain.Emp;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Author: yzt
 * @Description: 完成实例的需求
 */
public class JdbcDemo {
    public static void main(String[] args) {
        JdbcDemo demo = new JdbcDemo();
        List<Emp> list = demo.findAll();
        System.out.println(list);
    }

    public List<Emp> findAll() {
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;

        // 用于存储结果对象
		List<Emp> list = new ArrayList<>();

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2","root","root");
            statement = connection.createStatement();
            String sql = "select * from emp";

            // 执行查询语句,并获取结果集对象
			rs = statement.executeQuery(sql);
            Emp emp = null;

            // 获取结果集中的每一条记录,数据封装进emp对象,最后把对象存储在一个List中
            while(rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                int salary = rs.getInt("salary");
                Date join_date = rs.getDate("join_date");
                int dept_id = rs.getInt("dept_id");
                emp = new Emp();
                emp.setId(id);
                emp.setName(name);
                emp.setGender(gender);
                emp.setSalary(salary);
                emp.setJoin_date(join_date);
                emp.setDept_id(dept_id);
                list.add(emp);
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // ResultSet 作为资源也需要手动释放
            if(rs!=null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

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

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

PreparedStatement 执行 SQL 的对象

SQL 注入问题

  • 如果使用字符串拼接的方式定义 SQL 语句,有一些 SQL 的特殊关键字参与字符串的拼接,会造成安全性问题
  • 例如,用户在密码框内输入 a' or 'a' = 'a,实际拼接出来的要执行的 SQL 语句如下,这样的 where 条件必为真
select * from user where username = '任意字符串' and password = 'a' or 'a' = 'a' 

使用 PreparedStatement 对象和预编译的 SQL 语句可以解决 SQL 注入问题

用法

  1. 导入驱动 jar 包
  2. 注册驱动
  3. 获取数据库连接对象 Connection
  4. 定义sql
    • 注意:sql 的参数使用 ? 作为占位符,如
    select * from user where username = ? and password = ?;
    
  5. 从数据库连接获取执行 sql 语句的对象 PreparedStatement
    PreparedStatement preparedStatement = Connection.prepareStatement(String sql);
    
  6. 给参数占位符 ? 赋值:
    • 方法: setXxx(参数1,参数2)
      • 参数1:? 的位置编号 从 1 开始
      • 参数2:? 的值
  7. 执行 sql,不需要传递 sql 语句,接受返回结果,
  8. 处理结果
  9. 释放资源
    注意
    以后都会使用 PreparedStatement 来完成增删改查的所有操作,可以防止 SQL 注入,且效率更高

抽取 JDBC 工具类

可以看到,使用 JDBC 操作数据库会存在大量的重复代码,可以抽取重复代码,形成一个 JDBCUtils 工具类,以简化书写

需求:

  1. 抽取注册驱动操作
  2. 抽取一个方法获取连接对象,无需输入参数,且要保证工具类的通用性
    • 解决方法:使用配置文件,例如 jdbc.properties,写入数据库 url,用户名,密码,驱动 jar 包等参数
  3. 抽取一个方法释放资源
package cool.yzt.utils;

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

/**
 * @Author: yzt
 * @Description: JDBC 工具类
 */
public class JdbcUtils {
    public static String url;
    public static String user;
    public static String password;
    public static String driver;

    // 静态代码块,读取配置文件,完成驱动注册
    static {
        try {
            // 用于存储jdbc.properties的内容
            Properties pros = new Properties();

            // 获取本类(JdbcUtils)的类加载器
            ClassLoader classLoader = JdbcUtils.class.getClassLoader();

            // 通过类加载器的getResourceAsStream()方法将文件转换为输入流,最后通过 Properties的load()方法加载
            // jdbc.properties配置文件放在 Module(或Project)/src/ 目录下
            pros.load(classLoader.getResourceAsStream("jdbc.properties"));
            url = pros.getProperty("url");
            user = pros.getProperty("user");
            password = pros.getProperty("password");
            driver = pros.getProperty("driver");
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    // 获取数据库连接,向上抛出异常
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }


    // 关闭 Statement 资源
    public static void close(Statement statement){
        if(statement!=null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

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

jdbc.properties 文件内容

url=jdbc:mysql://localhost:3306/db1
user=root
password=root
driver=com.mysql.jdbc.Driver

应用实例

需求

  1. 使用 JDBC 工具类
  2. 通过键盘录入用户名和密码
  3. 判断用户是否登录成功,即
    • select * from user where username = "" and password = "";
    • 如果这个 sql 有查询结果,则成功,反之,则失败

实现

package cool.yzt.jdbc;

import cool.yzt.utils.JdbcUtils;
import java.sql.*;
import java.util.Scanner;

public class JdbcDemo {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = sc.nextLine();
        System.out.println("请输入密码");
        String password = sc.nextLine();

        if(new JdbcDemo().login(username,password)) {
            System.out.println("登陆成功!");
        }else {
            System.out.println("用户名或密码错误!");
        }

    }

    public boolean login(String username,String password) {
        if(username==null || password==null) return false;
        Connection connection = null;
        //Statement statement = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;

        try {
            // 从工具类获取连接
            connection = JdbcUtils.getConnection();

            // 预编译的sql语句,使用?作为参数的占位符
            String sql = "select * from user where username = ? and password = ?";

            //statement = connection.createStatement();
            // 使用 PreparedStatement,防止sql注入,这里需要传入sql语句作为参数
            preparedStatement = connection.prepareStatement(sql);

            // 调用 PreparedStatement 的方法给?占位符赋值
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);

            // 执行查询语句,并获取结果集
            rs = preparedStatement.executeQuery();

            // 直接返回 rs.next(),若结果集中有数据,即查询成功,返回true
            return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(rs,preparedStatement,connection);
        }
        return false;
    }
}

JDBC控制事务

使用 Connection 对象管理事务

  • 开启事务:setAutoCommit(boolean autoCommit) :调用该方法并设置参数为 false,即开启事务(取消自动提交),注意,必须在执行 sql 之前开启事务
  • 提交事务:commit()
    • 当所有 sql 都执行完提交事务
  • 回滚事务:rollback()
    • 在 catch 块中回滚事务

实例

需求

package cool.yzt.jdbc;

import cool.yzt.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @Author: yzt
 * @Description: JDBC 控制事务
 */
public class JdbcDemo10 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement1 = null;
        PreparedStatement preparedStatement2 = null;

        try {
            // 获取数据库连接
            connection = JdbcUtils.getConnection();
            // 开启事务
            connection.setAutoCommit(false);

            // 定义sql
            String sql1 = "update account set balance = balance - ? where id = ?";
            String sql2 = "update account set balance = balance + ? where id = ?";

            // 获取PreparedStatement对象
            preparedStatement1 = connection.prepareStatement(sql1);
            preparedStatement2 = connection.prepareStatement(sql2);

            preparedStatement1.setDouble(1,500);
            preparedStatement1.setInt(2,1);
            preparedStatement2.setDouble(1,500);
            preparedStatement2.setInt(2,2);

            preparedStatement1.executeUpdate();
            
            // 假设执行两句sql过程中间出现了问题,会进入catch块进行回滚
            int i = 3/0;

            preparedStatement2.executeUpdate();

            // 提交事务
            connection.commit();


        } catch (Exception e) {
            if(connection!=null) {
                try {
                    // catch块中执行事务的回滚
                    connection.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            // 关闭资源
            JdbcUtils.close(preparedStatement1,connection);
            JdbcUtils.close(preparedStatement2);
        }

    }
}

参考

黑马 JavaWeb
数据库复习笔记

使用的 jar 包
MySQL-JDBC

Copyright: 采用 知识共享署名4.0 国际许可协议进行许可

Links: https://yzt.cool/archives/mysqljdbc基础