Day49.JDBC — 数据库链接、实现CRUD、注入、自增长、事物、图片、批量插入

焰火青年·

已于 2022-04-29 13:55:09 修改

阅读量665

点赞数

分类专栏： MySQL 文章标签： java 数据库 JDBC

于 2022-04-23 23:20:34 首次发布

本文链接：https://blog.csdn.net/a111042555/article/details/124346195

版权

MySQL 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

一、JDBC概述

二、获取数据库连接 — 数据库驱动

数据库连接方式

三、使用PreparedStatement实现CRUD操作

增删改

查询

解决增删改查中注入与拼接问题：

四、获取自增长、添加Blob(图片|视频等)、批量插入、事物

获取当前自增长键值

Mysql插入图片 — SetBlob (InputStream is)

批处理 —— 批量插入数据(Batch)

事物操作 — setAutoCommit (false)

Web技术体系

技术体系

一、JDBC概述

关于数据持久化

持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。大多数情况下，特别是企业级应用，数据持久化意味着将内存中的数据保存到硬盘上加以”固化”，而持久化的实现过程大多通过各种关系数据库来完成。

Java中的数据存储技术

在Java中，数据库存取技术可分为如下几类：

JDBC直接访问数据库

JDO (Java Data Object )技术

第三方O/R工具，如Hibernate, Mybatis 等

JDBC是java访问数据库的基石，JDO、Hibernate、MyBatis等只是更好的封装了JDBC。

JDBC介绍

JDBC(Java Database Connectivity)是一个独立于特定数据库管理系统、通用的SQL数据库存取和操作的公共接口（一组API），定义了用来访问数据库的标准Java类库，（java.sql,javax.sql）使用这些类库可以以一种标准的方法、方便地访问数据库资源。

JDBC为访问不同的数据库提供了一种统一的途径，可以连接任何提供了JDBC驱动程序的数据库系统。

JDBC体系结构

JDBC接口（API）包括两个层次：
- 面向应用的API：Java API，抽象接口，供应用程序开发人员使用（连接数据库，执行SQL语句，获得结果）。
- 面向数据库的API：Java Driver API，供开发商开发数据库驱动程序用。

>从开发人员的角度：不需要就关注具体的数据库细节

>数据库厂商：只需要提供标准的具体实现

JDBC是sun公司提供一套用于数据库操作的接口，java程序员只需要面向这套接口编程即可。

不同的数据库厂商，需要针对这套接口，提供不同实现。不同的实现的集合，即为不同数据库的驱动。 ————面向接口编程

JDBC编写步骤

二、获取数据库连接 — 数据库驱动

java.sql.Driver 接口是所有 JDBC 驱动程序需要实现的接口。

数据库驱动：数据库厂商针对于JDBC这套接口，提供的具体实现类的集合。

在程序中不需要直接去访问实现了 Driver 接口的类，而是由驱动程序管理器类(java.sql.DriverManager)去调用这些Driver实现。

Oracle的驱动：oracle.jdbc.driver.OracleDriver
mySql的驱动： com.mysql.jdbc.Driver

几种常用数据库的 JDBC URL
MySQL的连接URL编写方式：
jdbc:mysql://主机名称:mysql服务端口号/数据库名称?参数=值&参数=值
jdbc:mysql://localhost:3306/atguigu
jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=utf8（如果JDBC程序与服务器端的字符集不一致，会导致乱码，那么可以通过参数指定服务器端的字符集）
jdbc:mysql://localhost:3306/atguigu?user=root&password=123456
Oracle 9i的连接URL编写方式：
jdbc:oracle:thin:@主机名称:oracle服务端口号:数据库名称
jdbc:oracle:thin:@localhost:1521:atguigu
SQLServer的连接URL编写方式：
jdbc:sqlserver://主机名称:sqlserver服务端口号:DatabaseName=数据库名称
jdbc:sqlserver://localhost:1433:DatabaseName=atguigu

数据库连接方式

最终版：

    //最终版: 将数据库连接需要的4个基本信息声明在配置文件中，通过读取配置文件的方式，获取连接
    @Test
    public void getConnection5() throws Exception{
        /*好处:
         1.实现了数据与代码的分离，解耦
         2.如果需要修改配置文件信息，可以避免程序重新打包
        */
        //1.读取配置文件中的4个基本信息
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        //创建Properties集合接收文件
        Properties pros = new Properties();
        pros.load(is);

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //2.加载驱动
        Class.forName(driverClass);

        //3.获取数据库连接对象  
        Connection conn = DriverManager.getConnection(url, user, password);
        //Connection   ==> 网络编程的Socket
        System.out.println(conn);
    }

jdbc.properties
user=root password=*******

url=jdbc:mysql://localhost:3306/test

driverClass=com.mysql.jdbc.Driver

过程(了解)：

import com.mysql.cj.jdbc.Driver;
import org.junit.Test;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @Date 2022/4/23 23:18
 * @Author by:Plisetsky
 */
//注:旧版本Driver包路径为 com.mysql.jdbc.Driver
public class ConnectionTest {
    // 方式一：
    @Test
    public void testConnection1() throws SQLException {
        // 获取Driver实现类对象
        Driver driver = new Driver();
        // url:http://localhost:8080/gmall/keyboard.jpg
        // jdbc:mysql:协议
        // localhost:ip地址
        // 3306：默认mysql的端口号
        // test:test数据库
        String url = "jdbc:mysql://localhost:3306/test";

        // 将用户名和密码封装在Properties中
        Properties info = new Properties();
        info.setProperty("user", "root");
        info.setProperty("password", "*******");
        //链接
        Connection conn = driver.connect(url, info);

        System.out.println(conn);
    }
    // 方式二：对方式一的迭代:在如下的程序中不出现第三方的api,使得程序具有更好的可移植性
    @Test
    public void testConnection2() throws Exception {
        // 1.获取Driver实现类对象：使用反射
        Class clazz = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();

        // 2.提供要连接的数据库
        String url = "jdbc:mysql://localhost:3306/test";

        // 3.提供连接需要的用户名和密码
        Properties info = new Properties();
        info.setProperty("user", "root");
        info.setProperty("password", "*******");

        // 4.获取连接
        Connection conn = driver.connect(url, info);
        System.out.println(conn);
    }
    // 方式三：使用DriverManager替换Driver
    @Test
    public void testConnection3() throws Exception {
        // 1.获取Driver实现类的对象
        Class clazz = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();

        // 2.提供另外三个连接的基本信息：
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "*******";

        // 注册驱动
        DriverManager.registerDriver(driver);

        // 获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
    }

    // 方式四：
    @Test
    public void testConnection4() throws Exception {
        // 1.提供三个连接的基本信息：
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "*******";

        //注册Driver 已经封装在Driver内，可以省略
        // 2.加载Driver
        Class.forName("com.mysql.cj.jdbc.Driver"); //链接Mysql此处也可以省略

        // 3.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
    }
}

DriveManager类

`static Connection`	`getConnection(String url)` 尝试建立与给定数据库URL的连接。
`static Connection`	`getConnection(String url, Properties info)` 尝试建立与给定数据库URL的连接。
`static Connection`	`getConnection(String url, String user, String password)`
`static void`	`registerDriver(Driver driver)` 注册与给定的驱动程序 `DriverManager` 。

三、使用PreparedStatement实现CRUD操作

Statement 与 PreparedStatement 的异同？

.二者的关系：接口与子接口。
Statement 需要拼写sql语句，并且存在sql注入问题：用户名和密码错误时，也可以操作数据库。
Statement 无法操作Blob类型数据，实现批量插入(Batch)时，效率较低。
PreparedStatement 通过使用占位符(?) 预编译Sql语句，解决了这些问题。

Java与SQL对应数据类型转换表：

Java类型	SQL类型
boolean	BIT
byte	TINYINT
short	SMALLINT
int	INTEGER
long	BIGINT
String	CHAR,VARCHAR,LONGVARCHAR
byte array	BINARY , VAR BINARY
java.sql.Date	DATE
java.sql.Time	TIME
java.sql.Timestamp	TIMESTAMP

增删改

//增加
    @Test
    public void test01() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.建立链接
        String url = "jdbc:mysql://localhost:3306/atguigu";
        Connection conn = DriverManager.getConnection(url, "root", "*******");
        System.out.println(conn);

        //3.准备sql
        String sql = "INSERT INTO temp VALUES ('迪丽热巴',21,'女')";

        //4.创建命令发送器
        PreparedStatement pst = conn.prepareStatement(sql);

        //5.执行sql  获取结果
        int i = pst.executeUpdate();//执行更新 i:影响的行数
        //增删改 调用executeUpdate 查询调用 executeQuery

        //6.输出结果
        System.out.println("i =" + i);
        //7.关闭资源
        conn.close();
    }
    //修改
    @Test
    public void test02() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.建立链接
        String url = "jdbc:mysql://localhost:3306/atguigu";
        Connection conn = DriverManager.getConnection(url, "root", "*******");
        System.out.println(conn);

        //3.准备sql
        String sql = "UPDATE temp SET age = 18 WHERE name = '张三'";

        //4.创建命令发生器

        PreparedStatement pst = conn.prepareStatement(sql);

        //5.输出结果
        int i = pst.executeUpdate();
        System.out.println("影响了"+i+"行");

        //6.关闭资源
        conn.close();
    }
    //删除
    @Test
    public void test03() throws Exception {

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

        String url = "jdbc:mysql://localhost:3306/atguigu";
        Connection conn = DriverManager.getConnection(url, "root", "*******");

        String sql = "DELETE FROM temp WHERE name = '张三'";
        PreparedStatement pst = conn.prepareStatement(sql);

        int i = pst.executeUpdate();
        System.out.println(i);

        conn.close();
    }

查询

//查询
    @Test
    public void test04() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.建立链接
        String url = "jdbc:mysql://localhost:3306/atguigu";
        Connection conn = DriverManager.getConnection(url, "root", "******");

        //3.准备sql
        String sql = "SELECT * FROM temp";
        //4.创建命令发送器
        PreparedStatement pes = conn.prepareStatement(sql);
        //5.执行sql, 获取结果集  //查询为executeQuery
        ResultSet res = pes.executeQuery();

        //6.处理结果集 并展示内容
        while (res.next()){
            String name = res.getString("name");  //通过字段名获取
            int age = res.getInt(2);              //通过下标获取
            Object gender = res.getObject(3);    //使用getObject 建议

            System.out.println("name:"+name+"age:"+age+"gender:"+gender);
        }
        //7.关闭资源
        conn.close();   //关闭链接
        pes.close();    //关闭命令发生器
        res.close();    //关闭结果集

    }

解决增删改查中注入与拼接问题：

//解决sql注入与拼接问题
    @Test
    public void test05() throws Exception {
        //1.创建键盘输入对象
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入姓名:");
        String name = scanner.next();
        System.out.println("请输入年龄:");
        int age = scanner.nextInt();
        System.out.println("请输入性别:");
        String gender = scanner.next();

        //2.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //3。建立链接
        //jdbc:mysql:///atguigu
        Connection conn = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "******");

        //4.预编译sql 语句
        String sql = "INSERT INTO temp VALUES (?,?,?)";//?:占位符

            //5.1创建命令发生器 (返回prepareStatement实例)
            PreparedStatement pst = conn.prepareStatement(sql);

            //5.2 填充数据 pst.setInt | pst.setObject(index,text) 赋值
            pst.setObject(1,name);
            pst.setObject(2,age);
            pst.setObject(3,gender);

        //6.执行，获取结果
        int i = pst.executeUpdate();
        System.out.println("影响了了"+i+"行数据");

        //7.关闭资源
        pst.close();
        conn.close();
    }

四、获取自增长、添加Blob(图片|视频等)、批量插入、事物

获取当前自增长键值

PreparedStatement 加入参数：Statement.RETURN_GENERATED_KEYS

//获取键值增长
    @Test
    public void test() throws Exception {

        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.建立连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu", "root", "*******");

        //3.准备sql
        String sql = "INSERT INTO temp(name,age,gender) VALUE (?,?,?)";

        //4.创建命令发送器 此处不同:加入参数 Statement.RETURN_GENERATED_KEYS
        PreparedStatement pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
        pst.setObject(1,"古力娜扎");
        pst.setObject(2,22);
        pst.setObject(3,"女");

        //5.执行
        int i = pst.executeUpdate();
        System.out.println("影响了"+i+"行数据");

        ResultSet rs = pst.getGeneratedKeys();
        if(rs.next()){
            //5.1展示主键
            Object key = rs.getObject(1);
            System.out.println("key="+key); //key=4
        }   

        //6.关闭资源
        conn.close();
        pst.close();
        rs.close();
    }

Mysql插入图片 — SetBlob (InputStream is)

如果传输过大数据，需要修改my.ini配置文件 (Mysql Data存放目录内)。

max_allowed_packet变量的值，先停止服务，然后修改my.ini文件，再重启服务。

//插入图片、视频
    @Test
    public void test01() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取链接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu", "root", "*******");

        //3.准备sql (id为主键可以为null)
        String sql = "INSERT INTO photo VALUES(null,?,?)";

        //4.创建命令发送器
        PreparedStatement pst = conn.prepareStatement(sql);

        //5.填充数据
        pst.setObject(1,"苍老师");
        //Blob 存储二进制数据，图片文件
        FileInputStream fis = new FileInputStream("E:\\JavaSE\\好康的.jpg");
        pst.setBlob(2,fis);

        //6.发送
        int i = pst.executeUpdate();
        System.out.println(i);

        //7.关闭流
        conn.close();
        fis.close();
    }

批处理 —— 批量插入数据(Batch)

一次插入多条数据，for循环效率低，怎么办？

MySQL服务器端，默认批处理功能没有开启。需要通过参数告知mysql服务器，开启批处理功能。
在url后面再加一个参数：rewriteBatchedStatements=true

pst.addBatch();堆叠数据 pst.executeBatch();批量添加

    //批处理 -- 批量插入数据
    @Test
    public void test01() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接，加入参数?rewriteBatchedStatements=true
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu?rewriteBatchedStatements=true","root","*******");

        String sql = "INSERT INTO temp(name,age,gender,salary) VALUES(?,?,?,?)";

        PreparedStatement pst = conn.prepareStatement(sql);

        for (int i = 0; i < 10000; i++) {
            pst.setObject(1,"迪丽热巴");
            pst.setObject(2,22);
            pst.setObject(3,"女");
            pst.setObject(4,i);

            //6.将任务攒到一起，最后发送，减少IO次数
            pst.addBatch();;
        }
        //7.执行批量添加
        pst.executeBatch();

        conn.close();
        pst.close();
    }

事务操作 — setAutoCommit (false)

JDBC事物操作前提：DML操作在同一个事务内(连接 connection)内。

事务处理原则：保证事物都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事物中执行多个操作时，要么所有的事务都被提交(commit)，要么数据库管理系统放弃当前所做的修改，将事物回滚(rollback)到最初状态

//事务处理
    //如何开始事务？ 通过连接开启 conn.setAutoCommit(false)
    //如何回滚  catch 中 conn.rollbark
    //如何提交  conn.commit
    @Test
    public void test01() throws SQLException {
        Connection conn = null;
        PreparedStatement pst = null;

        try{
            //1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            //2.建立连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu", "root", "*******");

            //3.开启事务
            conn.setAutoCommit(false);

            //4.准备sql
            String sql = "UPDATE temp SET salary = salary + ? where name = ?";

            //5.创建命令发送器
            pst = conn.prepareStatement(sql);

            //6.执行
            //张三向古力娜扎转账1000
            pst.setObject(1,-1000);
            pst.setObject(2,"张三");
            int i1 = pst.executeUpdate();
            System.out.println(i1);

            //System.out.println(1/0); 出错！

            pst.setObject(1,1000);
            pst.setObject(2,"古力娜扎");
            int i2 = pst.executeUpdate();
            System.out.println(i2);

            //7.提交事务
            conn.commit();

        } catch (Exception e) {
            e.printStackTrace();
            conn.rollback();
            System.out.println("有内鬼终止交易！");
        }finally{
            //8.关闭资源
            conn.close();
            pst.close();
        }
    }