跳至主要內容

JDBC

Yang大约 9 分钟

此笔记所用的示例代码基于 imooc.sql

链接: https://pan.baidu.com/s/1YA11c26LBcHJRA-rlt7d7Qopen in new window 密码: pwp3

  • JDBC 中所有的索引都是从 1 开始的

描述

Java 数据库链接（Java DataBase Connectivity）

  • JDBC 可以让 Java 通过程序操作关系型数据库
  • JDBC 基于驱动程序实现与数据库的连接与操作

优点

  • 统一的 API，提供一致的开发过程
  • 易于学习，易于上手，代码结构稳定
  • 功能强大，执行效率高，可处理海量数据

连接字符串

不同厂商的连接字符串

数据库JDBC 驱动连接字符串
MySQL 5com.mysql.jdbc.Driverjdbc:mysql://主机ip:端口/数据库名
MySQL 8com.mysql.cj.jdbc.Driverjdbc:mysql://主机ip:端口/数据库名
Oracleoracle.jdbc.driver.OracleDriverjdbc:oracle:thin:@主机ip:端口:数据库名
SQL Servercom.mircosoft.sqlserver.jdbc.SQLServerDriverjdbc:mircosoft:sqlserver:主机ip:端口;databasename=数据库名

MySQL 连接字符串常用参数

参数名建议参数值说明
useSSLtrue (生产)，false (开发)是否禁用 ssl
useUnicodetrue启用 unicode 编码传输数据
characterEncodingUTF-8使用 UTF-8 编码传输数据
serverTimezoneAsia/Shanghai使用东 8 时区时间，UTC+8
allowPublicKeyRetrievaltrue允许从客户端获取公钥加密传输

DriverManager

用于注册/管理 JDBC 驱动

  • 语法：DriverManager.getConnection(连接字符串,用户名,密码)
    • 返回 Connection 对象，对应数据库的物理网络

Connection

该对象是用于 JDBC 与数据库的网络通信对象

  • java.sql.Connection 是一个接口，具体由驱动厂商实现
  • 所有数据库的操作都建立在 Connection 的基础上

开发流程

  • 加载并注册 JDBC 驱动
  • 创建数据库连接
  • 创建 Statement 对象
  • 遍历查询结果
  • 关闭连接，释放资源
package com.imooc.jdbc.sample;

import java.sql.*;

public class StandarJDBCDSample {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        // JDBC驱动类
        String dbDriver = "com.mysql.cj.jdbc.Driver";
        // 连接字符串
        String dbURL = "jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        // 数据库用户名
        String dbUsername = "root";
        // 数据库密码
        String dbPassword = "11160328";
        // 查询字符串
        String sql = "select * from employee";


        try {
            // 1. 加载指定JDBC驱动，本质是通知JDBC注册这个驱动类
            Class.forName(dbDriver);
            // 2. 创建数据库连接
            conn = DriverManager.getConnection(dbURL, dbUsername, dbPassword);
            // 3. 创建 Statement 对象
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            // 4 .遍历查询结果
            while (rs.next()) {
                // 按索引第 1 列将数据作为整型返回
                Integer eno = rs.getInt(1);
                // 按字段名称将数据作为字符串型返回
                String ename = rs.getString("ename");
                Float salary = rs.getFloat("salary");
                String dname = rs.getString("dname");
                Date hiredate = rs.getDate("hiredate");
                System.out.println(dname + "-" + eno + "-" + ename + "-" + salary + "-" + hiredate);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                // 5. 关闭连接，释放资源
                if (conn != null) {
                    conn.close();
                }
                if (stmt != null) {
                    conn.close();
                }
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

封装通用工具类

// DbUtils.java
package com.imooc.jdbc.common;

import java.sql.*;

public class DbUtils {
    /**
     * 创建新的数据库连接
     * @return 新的Connection对象
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        //1. 加载并注册JDBC驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2. 创建数据库连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true", "root", "root");
        return conn;
    }

    /**
     * 关闭连接,释放资源
     * @param rs 结果集对象
     * @param stmt Statement对象
     * @param conn Connection对象
     */
    public static void closeConnection(ResultSet rs , Statement stmt , Connection conn){
        try {
            if(rs != null){
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(stmt != null){
                stmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn != null && !conn.isClosed() ) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

SQL 注入攻击

将用户输入的特殊格式的字符嵌入原有 SQL 语句中，破坏原有 SQL 筛选条件的数据攻击方式，称之为 SQL 注入攻击

PreparedStatement

预编译 Statement ，是 Statement 子接口

  • 对 SQL 进行参数化，预防 SQL 注入攻击
  • 比 Statement 执行效率高
String sql = "select * from employee where dname=?";
PreparedStatement pstemt = conn.preparedStatement(sql);
// 设置 SQL 参数，参数从 1 开始
pstmt.setString(1,dname);
ResultSet rs = pstmt.executeQuery();
while(rs.next(){
	...
}

查询

package com.imooc.jdbc.sample;

import java.sql.*;

public class StandarJDBCDSample {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // JDBC驱动类
        String dbDriver = "com.mysql.cj.jdbc.Driver";
        // 连接字符串
        String dbURL = "jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        // 数据库用户名
        String dbUsername = "root";
        // 数据库密码
        String dbPassword = "11160328";
        // 查询字符串
        String sql = "select * from employee where dname=? AND eno > ?";
        try {
            // 1. 加载指定JDBC驱动，本质是通知JDBC注册这个驱动类
            Class.forName(dbDriver);
            // 2. 创建数据库连接
            conn = DriverManager.getConnection(dbURL, dbUsername, dbPassword);
            // 3. 创建 Statement 对象
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "市场部");
            pstmt.setString(2, "1006");
            rs = pstmt.executeQuery();
            // 4 .遍历查询结果
            while (rs.next()) {
                // 按索引第 1 列将数据作为整型返回
                Integer eno = rs.getInt(1);
                // 按字段名称将数据作为字符串型返回
                String ename = rs.getString("ename");
                Float salary = rs.getFloat("salary");
                String dname = rs.getString("dname");
                Date hiredate = rs.getDate("hiredate");
                System.out.println(dname + "-" + eno + "-" + ename + "-" + salary + "-" + hiredate);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                // 5. 关闭连接，释放资源
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    conn.close();
                }
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

插入

String sql = "insert into employee(eno,ename) values(?,?)";
PreparedStatement pstemt = conn.preparedStatement(sql);
pstmt.setInt(1,10);
pstmt.setString(2,"张三");
// executeUpdate 方法返回记录数
int cnt = pstmt.executeUpdate(); // cnt=1
System.out.print("数据新增成功");

新增

String sql = "update employee set salary=salary+100 where dname=?";
PreparedStatement pstemt = conn.preparedStatement(sql);
pstmt.setString(1,"研发部");
// executeUpdate 方法返回记录数
int cnt = pstmt.executeUpdate(); // cnt=1
System.out.print("数据更新成功");

删除

String sql = "delete from employee where eno=?";
PreparedStatement pstemt = conn.preparedStatement(sql);
pstmt.setInt(1,3395);
// executeUpdate 方法返回记录数
int cnt = pstmt.executeUpdate(); // cnt=1
System.out.print("数据删除成功");

事务管理

事务是一种可靠的、一致的方式，访问和操作数据库的程序单元

由一个或多个 SQL 语句组成的整体，要么全部执行成功，要么全部执行失败

事务依赖与数据库实现，MySQL 通过事务区违数据缓冲地带

模式

自动提交

每执行一次写操作 SQL，自动提交事务

  • 开启方法：conn.setAutoCommit(true)
  • JDBC 默认行为，此模式无法保证多数据一致性

手动提交

显示调用 commit() 和 rollback() 方法管理事务

  • 开启方法：conn.setAutoCommit(false)
  • 可以保证多数据一致性，但必须手动调用提交/回滚方法
// 批量向数据库增加员工
package com.imooc.jdbc.sample;

import com.imooc.jdbc.common.DbUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * JDBC中的事务控制
 */
public class TransactionSample {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DbUtils.getConnection();
            //JDBC默认使用自动提交模式
            conn.setAutoCommit(false);//关闭自动提交
            String sql = "insert into employee(eno,ename,salary,dname) values(?,?,?,?)";
            for (int i=1000;i<2000;i++){
                if(i==1005){
                    //throw new RuntimeException("插入失败");
                }
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1, i);
                pstmt.setString(2, "员工" + i);
                pstmt.setFloat(3, 4000f);
                pstmt.setString(4, "市场部");
                pstmt.executeUpdate();
            }
            conn.commit();//提交数据
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if(conn != null && !conn.isClosed()) {
                    conn.rollback();//回滚数据
                }
            }catch (SQLException ex){
                ex.printStackTrace();
            }
        } finally {
            DbUtils.closeConnection(null, pstmt, conn);
        }
    }
}

日期处理

// 将 String 转为 java.sql.Date

// 1. String 转为 java.util.Date
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date udHireDate = null;
try {
  udHireDate = sdf.parse(strHireDate);
} catch (ParseException e) {
  e.printStackTrace();
}

// 2. java.util.Date 转为 java.sql.Date
long time = udHireDate.getTime(); // 获取自1970年到现在的毫秒数
java.sql.Date sdHireDate = new java.sql.Date(time);

批处理

package com.imooc.jdbc.sample;

import com.imooc.jdbc.common.DbUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

/**
 * JDBC批处理
 */
public class BatchSample {
    //标准方式插入若干数据
    private static void tc1(){
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            long startTime = new Date().getTime();
            conn = DbUtils.getConnection();
            //JDBC默认使用自动提交模式
            conn.setAutoCommit(false);//关闭自动提交
            String sql = "insert into employee(eno,ename,salary,dname) values(?,?,?,?)";
            for (int i=100000;i<200000;i++){
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1, i);
                pstmt.setString(2, "员工" + i);
                pstmt.setFloat(3, 4000f);
                pstmt.setString(4, "市场部");
                pstmt.executeUpdate();
            }
            conn.commit();//提交数据
            long endTime = new Date().getTime();
            System.out.println("tc1()执行时长：" + (endTime-startTime));
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if(conn != null && !conn.isClosed()) {
                    conn.rollback();//回滚数据
                }
            }catch (SQLException ex){
                ex.printStackTrace();
            }
        } finally {
            DbUtils.closeConnection(null, pstmt, conn);
        }
    }

    //使用批处理插入若干数据
    private static void tc2(){
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            long startTime = new Date().getTime();
            conn = DbUtils.getConnection();
            //JDBC默认使用自动提交模式
            conn.setAutoCommit(false);//关闭自动提交
            String sql = "insert into employee(eno,ename,salary,dname) values(?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            for (int i=200000;i<300000;i++){
                pstmt.setInt(1, i);
                pstmt.setString(2, "员工" + i);
                pstmt.setFloat(3, 4000f);
                pstmt.setString(4, "市场部");
                pstmt.addBatch();//将参数加入批处理任务
            }
            pstmt.executeBatch();//执行批处理任务
            conn.commit();//提交数据
            long endTime = new Date().getTime();
            System.out.println("tc2()执行时长：" + (endTime-startTime));
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if(conn != null && !conn.isClosed()) {
                    conn.rollback();//回滚数据
                }
            }catch (SQLException ex){
                ex.printStackTrace();
            }
        } finally {
            DbUtils.closeConnection(null, pstmt, conn);
        }
    }
    public static void main(String[] args) {
        tc1();
        tc2();
    }
}

连接池

在应用程序启动时，统一的对数据库的连接进行集中管理

  • 既是容器用来保存数据库连接，也是容器的管理者，分配数据库连接回收数据库连接的工作都是由连接池组件完成的

  • 对数据库进行有效管理与重用，最大化程序执行效率

Druid

阿里巴巴开源连接池组件，是最好的连接池之一

# druid-config.properties
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
username=root
password=11160328
initialSize=20
maxActive=20

// DruidSample.java
package com.imooc;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
import java.util.Properties;

public class DruidSample {
    public static void main(String[] args) {
        // 1. 加载属性文件
        Properties properties = new Properties();
        String propertyFile = DruidSample.class.getResource("/druid-config.properties").getPath();
        try {
            propertyFile = new URLDecoder().decode(propertyFile, "UTF-8");
            properties.load(new FileInputStream(propertyFile));
        } catch (Exception e) {
            e.printStackTrace();
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            // 2. 获取 DataSource 数据源对象
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            // 3. 创建数据库链接
            conn = dataSource.getConnection();
            pstmt = conn.prepareStatement("select * from employee limit 0,10");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                // 按索引第 1 列将数据作为整型返回
                Integer eno = rs.getInt(1);
                // 按字段名称将数据作为字符串型返回
                String ename = rs.getString("ename");
                Float salary = rs.getFloat("salary");
                String dname = rs.getString("dname");
                Date hiredate = rs.getDate("hiredate");
                System.out.println(dname + "-" + eno + "-" + ename + "-" + salary + "-" + hiredate);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            /**
             * 不使用连接池：conn.close() 关闭连接
             * 使用连接池：conn.close() 将连接回收至连接池
             */
            DbUtils.closeConnection(rs, pstmt, conn);
        }
    }
}

工具组件

Apache Commons DBUtils

  • commons-dbutils 是 Apache 提供的开源 JDBC 工具类库
  • 是对 JDBC 的简单封装，学习成本极低
  • 极大简化 JDBC 编码工作量

查询

package com.imooc;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.net.URLDecoder;
import java.util.List;
import java.util.Properties;

public class DbUtilsSample {
    public static void query() {
        Properties properties = new Properties();
        String propertyFile = DruidSample.class.getResource("/druid-config.properties").getPath();
        try {
            propertyFile = new URLDecoder().decode(propertyFile, "UTF-8");
            properties.load(new FileInputStream(propertyFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            QueryRunner qr = new QueryRunner(dataSource);
            List<Employee> list = qr.query(
                    "select * from employee limit ?,10",
                    new BeanListHandler<>(Employee.class),
                    new Object[]{10});
            for (Employee emp : list) {
                System.out.println(emp.getEname());
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static void main(String[] args) {
        query();
    }
}

更新

package com.imooc;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

public class DbUtilsSample {
    public static void update() {
        Properties properties = new Properties();
        String propertyFile = DruidSample.class.getResource("/druid-config.properties").getPath();
        Connection conn = null;
        try {
            propertyFile = new URLDecoder().decode(propertyFile, "UTF-8");
            properties.load(new FileInputStream(propertyFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            String sql1 = "update employee set salary=salary+1000 where eno=?";
            String sql2 = "update employee set salary=salary-500 where eno=?";
            QueryRunner qr = new QueryRunner();
            qr.update(conn, sql1, new Object[]{1000});
            qr.update(conn, sql2, new Object[]{1001});
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public static void main(String[] args) {
        update();
    }
}
上次编辑于:
贡献者: sunzhenyang
今日事，今日毕！
Copyright © 2024 Yang