JZTXT

尚硅谷JDBC笔记

发布时间 2023-09-24 00:15:36作者: 迷人的拖拉机

核心api

java.sql和javax.sql

过程

  1. 注册驱动

  2. 创建链接

  3. 创建发送SQL语句对象

  4. 发送SQL语句，获取结果

  5. 结果解析

  6. 释放资源

8.0.25版本驱动，自动识别时区 serverTimeZone=Asia/Shanghai 不用添加

8.0.25之前的版本，添加以下：

t_user表

create database atguigu;

use atguigu;

create table t_user(
     id int primary key auto_increment comment '用户主键',
     account varchar(20) not null unique comment '账号',
     password varchar(64) not null comment '密码',
     nickname varchar(20) not null comment '昵称');

 insert into t_user(account,password,nickname) values('root','123456','经理'),('admin','666666','管理员');

getConnection()

url:jdbc:数据库管理软件名称[nysql,oracle]?/ip地址|主机名:端口号/数据库名?key=value&key=value 可选信息

//2.获取连接
//方式一
Connection connection = DriverManager.getConnection("jdbc:mysql//127.0.0.1:3306/atguigu", "root", "123456");

//方式二
Properties info = new Properties();
info.put("user","root");
info.put("password","123456");

Connection connection1 = DriverManager.getConnection("jdbc:mysql//127.0.0.1:3306/atguigu", info);

//方式三
Connection connection2 = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu?user=root&password=123456");

statement

Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu", "root", "123456");
Statement statement = connection.createStatement();
String sql = "select * from t_user";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
    sout(resultSet.getString("字段名"));
    sout(resultSet.getIndex(1));//根据下标，从1开始
}

close();

preparedstatement

动态值使用占位符替代，防止sql注入攻击

//1.收集用户信息
Scanner scanner = new Scanner(System.in);
System.out.println("请输入账号");
String account = scanner.nextLine();
System.out.println("请输入密码");
String password = scanner.nextLine();

//2.ps的数据库流程

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

//2.2获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu", "root", "123456");

/**
 * statement
 *      1.创建statement
 *      2.拼接SQL语句
 *      3.发送SQL语句，并接收返回结果集
 *
 * preparedstatement
 *      1.编写SQL语句  不包含动态值部分的语句，动态值部分使用占位符?  替代 注意： ?只能替代动态值
 *      2.创建perparedstatment,并且传入动态值
 *      3.动态值  占位符 赋值 ? 单独赋值即可
 *      4.发送SQL语句，并返回结果集
 */

//2.3编写SQL语句结果
String sql = "select * from t_user where account = ? and password = ? ; ";

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

//2.5单独的占位符进行赋值
/**
 * 参数1：index  占位符的位置，从左往右数  从1开始 账号 ? 1
 * 参数2：object  占位符的值  可以设置任何类型的数据，避免了拼接和类型更加丰富
 */
preparedStatement.setObject(1,account);
preparedStatement.setObject(2,password);

//2.6发送sql语句，并获取返回结果
//statement.executeUpdate | statement.executeQuery(Stirng sql)
ResultSet resultSet = preparedStatement.executeQuery();

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

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

主键回滚

@Test
public void returnPrimaryKey() throws Exception {
    /**
     * 插入一条数据，并获取数据库自增长的主键
     * TODO:使用总结
     *  1.创建preparedstatement的时候，告知，携带回自增的主键(sql,Statement.RETURN_GENERATED_KEYS)
     *  2.获取带回的主键结果集对象，一行一列，获取对应的数据即可  ResultSet resultSet = preparedStatement.getGeneratedKeys();
     * */

    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu", "root", "123456");
    String sql = "insert into t_user(account,password,nickname) values(?,?,?);";
    PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

    preparedStatement.setObject(1,"test");
    preparedStatement.setObject(2,"test");
    preparedStatement.setObject(3,"二狗子");

    int rows = preparedStatement.executeUpdate();
    if(rows > 0){
        System.out.println("插入成功");

        //可以获取回显的主键
        ResultSet resultSet = preparedStatement.getGeneratedKeys();
        resultSet.next();
        int id = resultSet.getInt(1);
        System.out.println("id: " + id);
    }
    else{
        System.out.println("插入失败");
    }

}

批量插入数据

普通for循环插入

/**
 * 使用普通方法插入10000条数据
 */
@Test
public void testInsert() throws Exception {
    /**
     * 插入一条数据，并获取数据库自增长的主键
     * TODO:使用总结
     *  1.创建preparedstatement的时候，告知，携带回自增的主键(sql,Statement.RETURN_GENERATED_KEYS)
     *  2.获取带回的主键结果集对象，一行一列，获取对应的数据即可  ResultSet resultSet = preparedStatement.getGeneratedKeys();
     * */

    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu", "root", "123456");
    String sql = "insert into t_user(account,password,nickname) values(?,?,?);";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);

    long start = System.currentTimeMillis();

    for (int i = 0; i < 10000; i++) {
        preparedStatement.setObject(1,"dd" + i);
        preparedStatement.setObject(2,"dd" + i);
        preparedStatement.setObject(3,"二狗子" + i);

        preparedStatement.executeUpdate();
    }

    long end = System.currentTimeMillis();

    System.out.println("执行10000次消耗的时间"+(end-start));//20316

    preparedStatement.close();
    connection.close();
}

优化插入

/**
 * 使用批量方法插入10000条数据
 */
@Test
public void testBatchInsert() throws Exception {
    /**
     * 插入10000条数据
     * TODO:使用总结
     *  1.路径后面添加  ?rewriteBatchedStatements=true  允许批量插入
     *  2.insert into values 语句不能添加分号结束
     *  3.不是执行语句每条，是批量添加  addBatch()
     *  4.遍历添加完毕，统一执行
     * */

    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu?rewriteBatchedStatements=true", "root", "123456");
    String sql = "insert into t_user(account,password,nickname) values(?,?,?)";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);

    long start = System.currentTimeMillis();

    for (int i = 0; i < 10000; i++) {
        preparedStatement.setObject(1,"ddd" + i);
        preparedStatement.setObject(2,"ddd" + i);
        preparedStatement.setObject(3,"二狗子" + i);

        preparedStatement.addBatch();//不执行，追加到values的后面
    }

    //追加完毕，统一执行
    preparedStatement.executeBatch();

    long end = System.currentTimeMillis();

    System.out.println("执行10000次消耗的时间"+(end-start));//254

    preparedStatement.close();
    connection.close();
}

事物

利用try catch开启事物和提交事物

BankService

/**
 * 事物添加是在业务方法中
 * 利用try catch代码块，开启事务和提交事务，和事物回滚
 * 将connection传入dao层即可，dao层只负责使用，不负责关闭连接
 * @throws Exception
 */

@Test
public void start() throws Exception {
    transfer("lvdandan","ergouzi",500);
}

public void transfer(String addAccount,String subAccount,int money) throws Exception{
    BankDao bankDao = new BankDao();

    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu", "root", "123456");

    try{
        //开启事物
        //关闭事物提交
        connection.setAutoCommit(false);

        //执行数据库动作
        //执行数据库动作
        bankDao.add(addAccount,money,connection);
        System.out.println("--------------------------------");
        bankDao.sub(subAccount,money,connection);

        //事物提交
        connection.commit();
    }catch (Exception e){
        //事物回滚
        connection.rollback();

        //抛出
        throw e;
    }finally {
        connection.close();
    }

}

bankDao

public class BankDao {

    /**
     * 加钱的数据库操作方法
     * @param account 加钱的账号
     * @param money 加钱金额
     */
    public void add(String account,int money,Connection connection) throws Exception {

        String sql = "update t_bank set money = money + ? where account = ?;";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setObject(1,money);
        statement.setObject(2,account);

        statement.executeUpdate();

        statement.close();

        System.out.println("加钱成功");
    }

    /**
     * 减钱的数据库操作方法
     * @param account 减钱的账号
     * @param money 减钱的金额
     */
    public void sub(String account,int money,Connection connection) throws Exception {

        String sql = "update t_bank set money = money - ? where account = ?;";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setObject(1,money);
        statement.setObject(2,account);

        statement.executeUpdate();

        statement.close();

        System.out.println("减钱成功");
    }
}

Druid

硬编码，不推荐

/**
 * 直接使用代码设置连接池连接参数方式
 * 1.创建druid连接池对象
 *
 * 2.设置连接池参数[必须  |  非必须]
 *
 * 3.获取连接[通用方法，所有连接池都一样]
 *
 * 4.回收连接[通用]
 */
public void testHard() throws SQLException {
    DruidDataSource dataSource = new DruidDataSource();

    //设置参数
    //必须  连接数据库驱动类的全限定符[注册驱动]  |  url  |  user  | password
    dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/atguigu");
    dataSource.setUsername("root");
    dataSource.setPassword("123456");
    dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");//帮助我们进行注册驱动和获取连接
    //非必须  初始化连接数量  最大连接数量.....
    dataSource.setInitialSize(5);//初始化连接数量
    dataSource.setMaxActive(10);//最大的数量

    //获取连接
    Connection connection = dataSource.getConnection();

    //数据库crud


    //回收连接
    connection.close();//连接池的close()就是回收连接
}

软编码

/**
 * 通过读取外部配置文件的方式，实例化druid连接池对象
 */
public void testSoft() throws Exception {

    //1.读取外部配置文件 Properties
    Properties properties = new Properties();

    //src下的文件可以使用类加载器
    InputStream is = DruidUsePart.class.getClassLoader().getResourceAsStream("druid.preperties");

    properties.load(is);
    //2.使用连接池的工具类的工厂模式，创建连接池
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

    Connection connection = dataSource.getConnection();

    //数据库crud

    connection.close();
}

druid.properties

#key = value => java properties读取
#druid的key必须固定命名
driverClassName=com.mysql.cj.jdbc.Driver
username=root
password=123456
url=jdbc:mysql://127.0.0.1:3306/atguigu

jdbc工具类封装

JdbcUtils v1.0

package com.atguigu.api.utils;

import com.alibaba.druid.pool.DruidDataSource;
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;

/**
 * @author qingya
 * @create 2023-09-23 15:09
 *
 * v1.0工具类
 *  内部包含一个连接池对象，并且对外提供获取连接和回收连接的方法
 *  小建议：
 *        工具类的方法推荐声明为static，外部调用更加方便
 *
 *  实现
 *        属性：连接池对象[只实例化一次]
 *              1.单例模式
 *              2.static{
 *                  全局调用一次
 *              }
 *        方法：
 *              获取对外提供连接的方法
 *              回收外部传入连接的方法
 *
 *
 */
public class JdbcUtils {

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

    static{
        //初始化连接池对象
        Properties properties = new Properties();
        InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");

        try {
            properties.load(is);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    public static void freeConnection(Connection connection) throws SQLException {
        connection.close();//连接池的连接执行回收
    }
}

JdbcUtils v2.0

/** 
 *   //TODO:利用线程本地变量，存储连接信息   确保一个线程的多个方法可以获取同一个connection
 *      优势：事务操作的时候server 和 dao 属于同一个线程，不用再传递参数
 *      大家都可以调用getConnection自动获取的是相同的连接池
 *
 *
 */
public class JdbcUtilsV2 {

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

    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();

    static{
        //初始化连接池对象
        Properties properties = new Properties();
        InputStream is = JdbcUtilsV2.class.getClassLoader().getResourceAsStream("druid.properties");

        try {
            properties.load(is);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static Connection getConnection() throws SQLException {

        //线程本地变量中是否存在
        Connection connection = threadLocal.get();

        if(connection == null){
            //线程本地变量没有,连接池获取
            connection = dataSource.getConnection();
            threadLocal.set(connection);
        }
        return dataSource.getConnection();
    }

    public static void freeConnection() throws SQLException {

        Connection connection = threadLocal.get();
        if(connection != null){
            threadLocal.remove();//清空本地变量数据
            connection.setAutoCommit(true);
            connection.close();//连接池的连接执行回收
        }

    }
}

BaseDao

package com.atguigu.api.utils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author qingya
 * @create 2023-09-23 16:09
 * Description:封装dao层数据库重复代码
 * TODO:封装两个方法
 *      一个简化非DQL
 *      一个简化DQL
 */
public class BaseDao {

    /**
     * 封装简化非DQL语句
     * @param sql  带占位符的SQL语句
     * @param params 占位符的值  注意：传入的占位符的值，必须等于SQL语句?位置的值
     * @return  执行影响的行数
     */
    public int executeUpdate(String sql, Object... params) throws SQLException {

        //获取连接
        Connection connection = JdbcUtilsV2.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //可变参数可当作数组使用
        for (int i = 1; i <= params.length; i++) {
            preparedStatement.setObject(i+1, params[i-1]);
        }

        int rows = preparedStatement.executeUpdate();

        preparedStatement.close();
        //是否回收连接，需要考虑是不是事务
        //开启事务  getAutoCommit  获取的返回值为false
        if (connection.getAutoCommit()) {
            //true  -> 没有开启事物，正常回收连接
            JdbcUtilsV2.freeConnection();
        }
        return rows;
    }


    /**
     * 将查结果封装到一个实体类集合
     * @param clazz 要接值的实体类集合的模板对象
     * @param sql  查询语句，要求类名或者别名等于实体类的属性名  u_id as  uId  =>  uId
     * @param params  占位符的值 要和 ? 位置对象对应
     * @return
     * @param <T>     声明的结果的泛型
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws NoSuchFieldException
     */
    public <T> List<T> executeQuery(Class<T> clazz,String sql,Object... params) throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException {

        //获取连接
        Connection connection = JdbcUtilsV2.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        if(params != null && params.length != 0) {
            for (int i = 1; i <= params.length; i++) {
                preparedStatement.setObject(i,params[i-1]);
            }
        }

        ResultSet resultSet = preparedStatement.executeQuery();

        List<T> list = new ArrayList<>();

        //获取列的信息
        //TODO:metadata装的是列的信息(可以获取列的名称根据下角标，可以获取列的数量)
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        while (resultSet.next()) {

            T t = clazz.newInstance();

            //自动遍历列  注意：要从1开始，等于总列数
            for (int i = 1; i <= columnCount; i++) {
                //获取指定列下角标的值
                Object value = resultSet.getObject(i);
                //获取指定列下角标列的名称

                //getColumnLabel: 会获取别名，如果没有定义别名，获取列名  不要使用getColumnName:只会获取列名
                String key = metaData.getColumnLabel(i);

                //反射，给对象的属性值赋值
                Field field = clazz.getDeclaredField(key);
                field.setAccessible(true);
                field.set(t,value);
            }

            list.add(t);
        }

        resultSet.close();
        preparedStatement.close();
        if(connection.getAutoCommit()){
            JdbcUtilsV2.freeConnection();
        }

        return list;
    }
}

测试代码

public class PSCURDPart extends BaseDao {

    @Test
    public void testInsert() throws Exception {
        /**
         * t_user表插入一条数据
         *      account  test
         *      password test
         *      nickname 二狗子
         */

        String sql = "insert into t_user(account,password,nickname) values(?,?,?)";

        int i = executeUpdate(sql, "测试333", "333", "ergouzi");
        if (i > 0) {
            System.out.println("插入成功");
        } else {
            System.out.println("插入失败");
        }
    }

    @Test
    public void testUpdate() throws Exception {

        String sql = "update t_user set nickname = ? where id = ?;";
        executeUpdate(sql, "新的nickname", "4");

    }

    @Test
    public void testDelete() throws Exception {

        String sql = "delete from t_user where id = ?;";

        executeUpdate(sql, 4);
    }

    @Test
    public void testSelect() throws Exception {
        String sql = "select id,account,money from t_bank;";
        List<Bank> banks = executeQuery(Bank.class, sql);
        for (Bank bank : banks) {
            System.out.println(bank);
        }

    }

    @Test
    public void testSelect1() throws Exception {
        String sql = "select * from t_bank;";
        executeQuery(Bank.class,sql);
    }
}

t_customer表

create table t_customer(
        id int primary key auto_increment comment '客户主键',
        name varchar(20) comment '客户名称',
        gender varchar(4) comment '客户性别',
        age int comment '客户年龄',
        salary double(8,1) comment '客户工资',
        phone varchar(11) comment '客户电话');