JDBC学习笔记

什么是usb呢？

usb是一个技术的统称，我们可以从以下方面理解：

第一部分：USB的规范和设计标准（概念）

第二部分：电脑端的USB接口（接口）

第三部分：外设的USB口和具体发送信号的驱动程序（实现类）

JDBC->Java连接数据库技术

在Java代码中,使用JDCBC提供的方法,可以发送字符串类型的SQL语句到数据库管理软件,并且获取语句执行结果,进而实现数据库CURD操作的技术.

一、jdbc基本使用步骤

注册驱动依赖的jar包进行安装
建立连接 connection
创建发送sql语句的对象 statement
statement对象发送SQL语句到数据库获取返回结果
解析结果集
释放资源

public class StatementQueryPart {

    public static void main(String[] args) throws SQLException {

        //1. 注册驱动 依赖的jar包 进行安装
        DriverManager.registerDriver(new Driver());
        //2. 建立连接 connection
        Connection connection = DriverManager.
                getConnection("jdbc:mysql://127.0.0.1:3306/pass","root","123456");
        //3. 创建发送sql语句的对象 statement
        Statement statement = connection.createStatement();
        //4. statement对象 发送SQL语句到数据库 获取返回结果
        String sql = "select * from news;";
        ResultSet resultSet = statement.executeQuery(sql);
        //5. 解析结果集
        //看看有没有下一行数据 有 你就可以获取
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String title = resultSet.getString("title");
            String addtime = resultSet.getString("addtime");
            String content = resultSet.getString("content");
            System.out.println(id+"--"+title+"--"+addtime+"--"+content);
        }
        //6. 释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

二、statement存在的问题

package com.plume.api.statement;

import com.mysql.cj.jdbc.Driver;


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

public class StatementUserLoginPart {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        //1.获取用户输入信息
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入账号:");
        String account = scanner.nextLine();
        System.out.println("请输入密码:");
        String password = scanner.nextLine();
        //2.注册驱动

        //问题:注册两次驱动
        //1.DriverManager.registerDriver()方法本身会注册一次!
        //2.Driver.static{ DriverManager.registerDriver()}静态代码块也会注册一次!
        //解决:只注册一次
        //只触发静态代码块即可

        //DriverManager.registerDriver(new Driver());

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

        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/pass","root","123456");
        //DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/pass?user=root&password=123456");

        //3.创建发送SQL语句的statement对象
        Statement statement = connection.createStatement();

        //4.发送SQL语句
        String sql = "SELECT * FROM information WHERE name = '"+ account +"'AND password = '"+password+"';";
                                                                  //注入攻击 -> ' or '1' = '1 -> 动态值充当了SQL语句结构 影响原有的查询结果
        //SQL分类:DDL(容器创建,修改,删除) DML(插入,修改,删除) DQL(查询) DCL(事务控制语言)
        //参数:sql 非DQL
        //返回:int
        //  情况1:DML 返回影响的行数  eg:删除了三条数据 return 3;插入了两条数据 return 2;修改了0条数据 return 0;
        //  情况2:非DML return 0;
        //int row = executeUpdate(sql)

        //参数:sql DQL
        //返回:resultSet 结果封装对象
        // ResultSet resultSet = executeQuery(sql);

        //int i = statement.executeUpdate(sql);
        ResultSet resultSet = statement.executeQuery(sql);

        //5.查询结果集解析 resultSet
        //resultSet -> 逐行获取数据 -> 列数据
        //A ResultSet object maintains a cursor pointing to its current row of data.
        // Initially the cursor is positioned before the first row. The next method
        // moves the cursor to the next row, and because it returns false when there
        // are no more rows in the ResultSet object, it can be used in a while loop
        // to iterate through the result set.
        //想要进行数据解析,需要进行两件事:
        //1.游标移动问题
        //  resultSet内部包含一个游标,指定当前行数据,默认指定第一行数据之前
        //  我们可以调用next方法向后移动一行光标,若多行数据可使用 while(next){获取每一行的数据}
        //
        //2.获取列的数据(获取光标指定行的的数据)
        //
        //  resultSet.get类型(String columnlabel | columnIndex);
        //      columnLabel: 列名 如果有别名 写别名
        //      columnLabel: 列的下角标获取 从左向右 从1开始

        //while (resultSet.next()){
        //    //指定当前行
        //    int id = resultSet.getInt(1);
        //    String account1 = resultSet.getString("name");
        //    String password1 = resultSet.getString(3);
        //    String phone = resultSet.getString("phone");
        //    System.out.println(id+"--"+account1+"--"+password1+"--"+phone);
        //
        //}

        //移动一次光标,只要有数据,就代表登陆成功
        if(resultSet.next()){
            System.out.println("登陆成功!");
        }else{
            System.out.println("登陆失败");
        }

        //6.关闭资源
        resultSet.close();
        statement.close();
        connection.close();

    }
}

SQL语句需要字符串拼接，比较麻烦
只能拼接字符串类型，其他的数据库类型无法处理
可能发生注入攻击，动态值充当了SQL语句结构影响原有的查询结果

密码输入时注入攻击 ‘ or ‘1’ = ‘1

三、statement的优化

statement

创建statement
拼接SQL语句
发送SQL语句，并且获取返回结果

preparedstatement

编写SQL语句结构不包含动态值的语句，动态值使用占位符？替代
创建PreparedStatement，并传入动态值
动态值占位符赋值？单独赋值即可
发送SQL语句即可，并获取返回结果

package com.plume.api.preparedstatement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

/*
* TODO:防止注入攻击,演示预编译statement*/
public class PSUserLoginPart {
    public static void main(String[] args) throws Exception {
        //1.获取用户输入信息
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入账号:");
        String account = scanner.nextLine();
        System.out.println("请输入密码:");
        String password = scanner.nextLine();

        //2.ps的数据库流程
        //1.注册流程
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取链接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///pass?user=root&password=123456");

        //3.编写SQL语句结果
        String sql = "SELECT * FROM information WHERE name = ? AND password = ? ;";

        //4.创建预编译statement并且设置SQL语句结果
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //5.单独的占位符进行赋值
        /*
        * 参数1:index 占位符的位置 从左向右数 从1开始
        * 参数2:object 占位符的值,可以设置任何类型的数据
        * */
        preparedStatement.setObject(1,account);
        preparedStatement.setObject(2,password);

        //6.发送SQL语句,获取返回结果
        //statement.executeUpdate | executeQuery(String sql);
        //preparedStatement.executeUpdate | executeQuery();TODO:因为他已经知道语句,知道语句的动态值
        ResultSet resultSet = preparedStatement.executeQuery();

        //7.结果集解析
        if(resultSet.next()){
            System.out.println("登陆成功!");
        }else {
            System.out.println("登陆失败!");
        }

        //6.关闭资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

四、基于preparedStatement的curd

package com.plume.api.preparedstatement;

import org.junit.Test;

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

public class PSCURDPart {

    //测试方法需要导入junit的测试包
    @Test
    public void testInsert() throws ClassNotFoundException, SQLException {

        /*
        * information插入一条数据
        *   name        test
        *   password    test
        *   phone       12345678
        * */

        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取链接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///pass", "root", "123456");
        //3.编写SQL语句结果,动态值使用?代替
        String sql = "INSERT into information(name,password,phone) values(?,?,?)";
        //4.创建preparedStatement 并且传入SQL语句结果
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //5.占位符赋值
        preparedStatement.setObject(1,"test");
        preparedStatement.setObject(2,"test");
        preparedStatement.setObject(3,"12345678");
        //6.发送SQL语句
        //DML类型
        int rows = preparedStatement.executeUpdate();
        //7.输出结果
        if (rows > 0){
            System.out.println("数据插入成功!");
        }else{
            System.out.println("数据插入失败!");
        }
        //8.关闭资源
        preparedStatement.close();
        connection.close();

    }

    @Test
    public void testUpdate() throws ClassNotFoundException, SQLException {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取链接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///pass", "root", "123456");
        //3.编写SQL语句结果,动态值使用?代替
        String sql = "UPDATE information SET name =? WHERE id =?";
        //4.创建preparedStatement 并且传入SQL语句结果
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //5.占位符赋值
        preparedStatement.setObject(1,"灰羽");
        preparedStatement.setObject(2,1234);
        //6.发送SQL语句
        int update = preparedStatement.executeUpdate();
        //7.输出结果
        if(update > 0){
            System.out.println("修改成功!");
        }else{
            System.out.println("修改失败!");
        }
        //8.关闭资源
        preparedStatement.close();
        connection.close();

    }

    @Test
    public void testDelete() throws ClassNotFoundException, SQLException {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取链接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///pass", "root", "123456");
        //3.编写SQL语句结果,动态值使用?代替
        String sql = "DELETE FROM information WHERE id =?";
        //4.创建preparedStatement 并且传入SQL语句结果
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //5.占位符赋值
        preparedStatement.setObject(1,349314787);
        //6.发送SQL语句
        int update = preparedStatement.executeUpdate();
        //7.输出结果
        if(update > 0){
            System.out.println("删除成功!");
        }else{
            System.out.println("删除失败!");
        }
        //8.关闭资源
        preparedStatement.close();
        connection.close();

    }

    /*
    * 目标:查询所有用户数据，并且封装到一个List<Map> list集合中!
    *
    * 实现思路:
    *   遍历行数据,一行对应一个map! 获取一行的列名和对应的列的属性,装配即可
    *   将map撞到一个集合就可以了
    * 难点:
    *   如何获取列的名称?
    * */
    @Test
    public void testSelect() throws ClassNotFoundException, SQLException {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取链接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///pass", "root", "123456");
        //3.编写SQL语句结果,动态值使用?代替
        String sql = "SELECT id,name,password,phone FROM information";
        //4.创建preparedStatement 并且传入SQL语句结果
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //5.占位符赋值
        //无
        //6.发送SQL语句
        ResultSet resultSet = preparedStatement.executeQuery();
        //7.输出结果
        /*
        * TODO:回顾
        *  resultSet:有行有列,内部有一个游标,指向数据的前一行,我们可以用
        *            next()方法移动游标
        * */
        List<Map> list = new ArrayList<>();

        //获取列的信息对象
        //TODO:metaData 装的是当前结果集列的信息对象(可以获取列的名称和数量)
        ResultSetMetaData metaData = resultSet.getMetaData();
        //获取列的数量,可以水平遍历
        int columnCount = metaData.getColumnCount();

        while(resultSet.next()){
            Map map = new HashMap();
            //一行数据对应一个map

            //纯手动取值
//            map.put("id",resultSet.getInt("id"));
//            map.put("name",resultSet.getString("name"));
//            map.put("password",resultSet.getString("password"));
//            map.put("phone",resultSet.getString("phone"));


            //自动遍历获取列的数据
            for (int i = 1; i <= columnCount; i++) {
                //获取指定下角标的值
                Object value = resultSet.getObject(i);
                //获取指定列下角标的列的名称

                //getColumnLabel:会获取别名,如果没有别名才是列的名称
                //getColumnName:只会获取列的名称
                String columnLabel = metaData.getColumnLabel(i);
                map.put(columnLabel, value);
            }



            //一行数据的所有列全部存到了map中!
            //将map存储到集合中
            list.add(map);
        }
        System.out.println("list = "+ list);
        //8.关闭资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

五、jdbc的扩展

5.1 自增长主键回显实现

1
2

1.创建prepareStatement时,告知携带回数据库自增长的主键 (sql,Statement.RETURN_GENERATED_KEYS)
2.获取装主键值的结果集对象,一行一列,获取对应的数据即可 ResultSet resultSet = preparedStatement.getGeneratedKeys();

5.2 批量数据插入性能提升

1.路径后面添加 ?rewriteBatchedStatements=true 运行批量插入
2.insert into values[必须写] 语句不能添加;结束
3.不是执行语句每条,是批量添加 addBatch();
4.遍历添加完毕以后,统一执行 executeBatch()

5.3 jdbc中数据库事务实现

1
2
3

1.事务添加是在业务的方法中
2.利用try catch代码块,开启事务和提交事务,和事务回滚
3.将connection传入dao层即可 dao层只负责使用,不要close

六、Druid连接池技术的使用

连接池节约了创建和销毁连接的性能消耗,提升了时间的响应,提高了效率.

java.sql.DataSource接口

规范了连接池获取连接的方法

规范了连接池回收连接的方法

DataSource=第三方连接池的实现

七、jdbc使用优化以及工具类封装

7.1 工具类封装 v1.0

我们封装一个工具类内部包含连接池对象同时对外提供连接池的方法和回收连接的方法!

package com.plume.api.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/**
 * v1.0版本工具类
 *   内部包含一个连接池对象，对外提供获取连接和回收连接的方法
 * tip：
 *   工具类的方法，推荐写成静态，外部调用会更加方便
 *
 * 实现：
 *   属性 连接池对象 [实例化一次]
 *       单例模式
 *       static{
 *           全局调用一次
 *       }
 *   方法
 *     对外提供连接的方法
 *     回收外部传入的连接
 */

public class JdbcUtils {

    private static DataSource dataSource = null;//连接池对象

    static {
        //初始化连接池对象
        Properties properties = new Properties();
        InputStream ips = JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            properties.load(ips);
        } catch (IOException e) {
            e.printStackTrace();
        }

        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 对外提供来链接的方法
     * @return
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    public static void freeConnection(Connection connection) throws SQLException {
        connection.close();//连接池的连接,调用close就是回收!
    }
}

7.2 jdbc工具类封装 v2.0

优化工具类v1.0,考虑事务的情况下,如何一个线程的不同方法获取同一个连接!

ThreadLocal可以为同一个线程存储共享变量

package com.plume.api.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/**
 * v1.0版本工具类
 *   内部包含一个连接池对象，对外提供获取连接和回收连接的方法
 * tip：
 *   工具类的方法，推荐写成静态，外部调用会更加方便
 *
 * 实现：
 *   属性 连接池对象 [实例化一次]
 *       单例模式
 *       static{
 *           全局调用一次
 *       }
 *   方法
 *     对外提供连接的方法
 *     回收外部传入的连接
 *
 * TODO:
 *   利用线程本地变量,存储连接信息 确保一个线程的多个方法可以获取同一个connection!
 *   优势:事务操作时 service 和 dao 属于同一个线程,不用再传递参数了!
 *       大家都可以调用getConnection,自动获取的是相同的连接池!
 *
 */

public class JdbcUtilsV2 {

    private static DataSource dataSource = null;//连接池对象
    private static ThreadLocal<Connection> tl = new ThreadLocal<>();

    static {
        //初始化连接池对象
        Properties properties = new Properties();
        InputStream ips = JdbcUtilsV2.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            properties.load(ips);
        } catch (IOException e) {
            e.printStackTrace();
        }

        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 对外提供来链接的方法
     * @return
     */
    public static Connection getConnection() throws SQLException {

        //线程本地变量中是否存在
        Connection connection = tl.get();
        //第一次没有
        if(connection == null){
            //线程本地变量没有,连接池获取
            connection = dataSource.getConnection();
            tl.set(connection);
        }

        return connection;
    }

    public static void freeConnection() throws SQLException {
        Connection connection = tl.get();
        if (connection != null) {
            tl.remove();//清空线程本地变量数据
            connection.setAutoCommit(true);//事务状态回归 false
            connection.close();
        }
    }
}

6.3 高级应用层封装 BaseDao

基本上每一个数据表都应该有一个对应Dao接口的实现类,因为所有对表的的增删改查的代码重复度很高,所以可以抽取公共代码,给这些DAO的实现类可以抽取一个公共的父类,我们称之为BaseDao