JDBC的使用

一、JDBC概述

1.1 JDBC概述

JDBC：Java Database Connectivity，它是代表一组独立于任何数据库管理系统（DBMS）的API，声明在java.sql与javax.sql包中，是SUN（现在的Oracle）提供的一组接口规范。由各个数据库厂商来提供实现类，这些实现类的集合构成了数据库驱动jar。

请添加图片描述

即JDBC技术包含两个部分：

（1）java.sql包和javax.sql包中的API

因为为了项目代码的可移植性，可维护性，SUN公司从最初的就制定了Java程序连接各种数据库的统一接口规范。这样的话，不管连接哪一种DBMS软件，Java代码可以保持一致性。

（2）各个数据库厂商提供的jar

因为各个数据库厂商的DBMS软件各有不同，那么内部如何通过SQL实现增、删、改、查等管理数据，只有这个数据库厂商自己清楚，因此把接口规范的实现交给各个数据库厂商自己实现。

1.2 JDBC使用步骤

1.2.1 注册驱动

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

1.2.2 获取Connection连接对象

// 2. 获得连接
        // jdbc:mysql://服务器的主机地址:端口号/day04?characterEncoding=utf8
        // 如果数据库主机地址是localhost，并且端口号是3306，那么可以省略localhost:3306
        String url = "jdbc:mysql://localhost:3306/day04?characterEncoding=utf8";
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, user, password);

1.2.3 执行SQL并处理结果

        // 3. 创建statement对象
        Statement statement = connection.createStatement();

        // 4. 使用statement对象执行SQL语句
        // 增删改：调用executeUpda方法
        // 查询：调用executeQuery方法
        String sql = "SELECT * FROM USER";
        // 执行查询数据的SQL语句，获得查询到的结果集
        ResultSet rst = statement.executeQuery(sql);

        // 处理结果
        // 增删改：返回的是整数值，表示受到影响的数据条数
        // 查询：返回ResultSet结果
        // 5. 遍历啊rst，从中获取到查询
        while(rst.next()){
            // 每次调用next()就是将游标移动到结果集的下一行
            // 获取当前行的每列数据，根据列名获取
            int id = (int)rst.getObject("id");
            String username = (String) rst.getObject("username");
            String pwd = (String) rst.getObject("password");
            String nickname = (String) rst.getObject("nickname");

            System.out.println(id + ":" + username + ":" + pwd + ":" + nickname);
            System.out.println("------------------------------------------------------------------------");
        }

1.2.4 释放资源

原则是后创建的资源先关闭。

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

二、JDBC的练习

2.1 执行添加的SQL语句

public static void testInsert()throws Exception {

        // 目标：增加新用户
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2. 获得连接
        // jdbc:mysql://服务器的主机地址:端口号/day04?characterEncoding=utf8
        // 如果数据库主机地址是localhost，并且端口号是3306，那么可以省略localhost:3306
        String url = "jdbc:mysql://localhost:3306/day04?characterEncoding=utf8";
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, user, password);

        // 3. 创建statement对象
        Statement statement = connection.createStatement();

        // 4. 使用statement对象执行SQL语句
        String sql = "insert into user (username,password,nickname) values ('tq',77777,'田七')";
        // 执行查询数据的SQL语句，获得收到影响的数据行数
        int i = statement.executeUpdate(sql);

        System.out.println(i);

        statement.close();
        connection.close();
    }

2.2 执行删除的SQL语句

 public static void testDelete() throws Exception{
        // 目标：删除id为2的用户
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2. 获得连接
        // jdbc:mysql://服务器的主机地址:端口号/day04?characterEncoding=utf8
        // 如果数据库主机地址是localhost，并且端口号是3306，那么可以省略localhost:3306
        String url = "jdbc:mysql://localhost:3306/day04?characterEncoding=utf8";
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, user, password);

        // 3. 创建statement对象
        Statement statement = connection.createStatement();

        // 4. 使用statement对象执行SQL语句
        String sql = "delete from user where id = 2 ";
        // 执行查询数据的SQL语句，获得收到影响的数据行数
        int i = statement.executeUpdate(sql);

        System.out.println(i);

        statement.close();
        connection.close();
    }

2.3 执行修改的SQL语句

public static void testUpdate() throws  Exception{
        // 目标：更新id为6的用户的密码为88888888
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2. 获得连接
        // jdbc:mysql://服务器的主机地址:端口号/day04?characterEncoding=utf8
        // 如果数据库主机地址是localhost，并且端口号是3306，那么可以省略localhost:3306
        String url = "jdbc:mysql://localhost:3306/day04?characterEncoding=utf8";
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, user, password);

        // 3. 创建statement对象
        Statement statement = connection.createStatement();

        // 4. 使用statement对象执行SQL语句
        String sql = "update user set password = '88888888' where id = 6 ";
        // 执行查询数据的SQL语句，获得收到影响的数据行数
        int i = statement.executeUpdate(sql);

        System.out.println(i);

        statement.close();
        connection.close();
    }

2.4 执行查询单行数据的SQL语句

public static void testFindById() throws Exception{
        // 目标：查询id为1的用户的信息
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2. 获得连接
        // jdbc:mysql://服务器的主机地址:端口号/day04?characterEncoding=utf8
        // 如果数据库主机地址是localhost，并且端口号是3306，那么可以省略localhost:3306
        String url = "jdbc:mysql://localhost:3306/day04?characterEncoding=utf8";
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, user, password);

        // 3. 创建statement对象
        Statement statement = connection.createStatement();

        // 4. 使用statement对象执行SQL语句
        String sql = "select * from user where id = 1";
        // 执行查询数据的SQL语句，获得收到影响的数据行数
        ResultSet rst = statement.executeQuery(sql);

        // 遍历结果集
        while(rst.next()){
            // 获取当前行的每列数据
            int id = rst.getInt("id");
            String username = rst.getString("username");
            String pwd = rst.getString("password");
            String nickname = rst.getString("nickname");

            System.out.println(id + ":" + username + ":" + pwd + ":" + nickname);
            System.out.println("------------------------------------------------------------------------");

            // 从数据库查询数据的目的是：为了在Java代码的内存中操作查询出来的数据
            // 将查询出来的一行数据作为一个整体：就将查询出来的这行数据中的各列数据存储到一个Map中或者User对象中
//            Map<String,Object> userMap = new HashMap<>();
//            userMap.put("id",id);
//            userMap.put("username",username);
//            userMap.put("password",password);
//            userMap.put("nickname",nickname);

            User user1 = new User(id, username, password, nickname);

            System.out.println(user1);
        }
        statement.close();
        connection.close();
    }

三、使用PreparedStatement处理增删改查

3.1 Statement存在的问题

  1. 每次执行一个SQL语句都需要先编译
  2. SQL语句拼接
  3. SQL注入
public static void testError() throws Exception{

        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2. 获得连接
        // jdbc:mysql://服务器的主机地址:端口号/day04?characterEncoding=utf8
        // 如果数据库主机地址是localhost，并且端口号是3306，那么可以省略localhost:3306
        String url = "jdbc:mysql://localhost:3306/day04?characterEncoding=utf8";
        String user = "root";
        String pwd = "root";
        Connection connection = DriverManager.getConnection(url, user, pwd);

        // 3. 创建statement对象
        Statement statement = connection.createStatement();


        // SQL注入：由于SQL语句中的字符串拼接，导致SQL语句的格式发生了变化而引发的问题
        // 演示SQL注入的问题
        String username = "hahah' or '1'='1";
        String password = "";

        // 根据username和password查询用户，其实就是模拟登录
        String sql = "select * from user where username = '"+username+"' and password = '"+password+"'";

        ResultSet rst = statement.executeQuery(sql);

        // 只需要判断rst里有没有数据
        if(rst.next()){
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }

    }

3.2 PreparedStatement解决问题

3.2.1 预编译

PreparedStatement会先对参数化的SQL语句进行预编译，执行SQL语句的时候不会再进行编译。

// 3.1 编写参数化的SQL语句，需要传入参数的地方使用?占位
        String sql = "select * from user where username=? and password=?";

        // 3.2 预编译SQL语句：可以确定SQL语句的结构，那么预编译之后就无法再通过SQL注入改变SQL语句的结构
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

3.2.2 避免SQL拼接

 // 3. 预编译参数化的SQL语句
        String username = "zs";
        String password = "123456";
        // 3.1 编写参数化的SQL语句，需要传入参数的地方使用?占位
        String sql = "select * from user where username=? and password=?";

        // 3.2 预编译SQL语句：可以确定SQL语句的结构，那么预编译之后就无法再通过SQL注入改变SQL语句的结构
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        // 4. 给?占位符传入对应的参数
        preparedStatement.setObject(1,username);
        preparedStatement.setObject(2,password);

        // 5. 执行SQL语句，此时不要再传入SQL语句了，因为再预编译的时候已经传过了
        ResultSet rst = preparedStatement.executeQuery();

        if (rst.next()){
            System.out.println("登陆成功");
        }else{
            System.out.println("登陆失败");
        }

3.2.3防止SQL注入

PreparedStatement在进行预编译的时候，就已经确定好了SQL语句的格式，不会再因为SQL语句的拼接改变SQL语句的格式。

   public static void testLogin() throws Exception{
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2. 获取连接
        String url = "jdbc:mysql://localhost:3306/day04?characterEncoding=utf8";
        String user = "root";
        String pwd = "root";
        Connection connection = DriverManager.getConnection(url, user, pwd);

        // 3. 预编译参数化的SQL语句
        String username = "zs";
        String password = "123456";
        // 3.1 编写参数化的SQL语句，需要传入参数的地方使用?占位
        String sql = "select * from user where username=? and password=?";

        // 3.2 预编译SQL语句：可以确定SQL语句的结构，那么预编译之后就无法再通过SQL注入改变SQL语句的结构
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        // 4. 给?占位符传入对应的参数
        preparedStatement.setObject(1,username);
        preparedStatement.setObject(2,password);

        // 5. 执行SQL语句，此时不要再传入SQL语句了，因为再预编译的时候已经传过了
        ResultSet rst = preparedStatement.executeQuery();

        if (rst.next()){
            System.out.println("登陆成功");
        }else{
            System.out.println("登陆失败");
        }

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

3.3 获取自增长键值

主要使用在添加完主表的一条数据之后，要获取到这条数据的主键值，然后将该值添加进从表的外键字段的场景。

3.3.1 获取自增长键值的步骤

  1. 在预编译的时候，指定要返回自增长的key
  2. 在执行完添加数据的SQL语句之后，通过PreparedStatement的对象调用getGeneratedKeys()方法来获取自增长键值，遍历结果集。
  3. 遍历获取自增长的键值。
 public static void testObtainPrimaryKeyAfterInsert() throws Exception{
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2. 获取连接
        String url = "jdbc:mysql://localhost:3306/day04?characterEncoding=utf8";
        String user = "root";
        String pwd = "root";
        Connection connection = DriverManager.getConnection(url, user, pwd);

        // 测试在添加数据之后获取主键值
        String sql = "insert into user(username,password,nickname) values (?,?,?)";

        // 预编译的时候，要指定，不仅要预编译，还需要获取自增长的主键值
        PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

        // 设置SQL语句的参数
        preparedStatement.setObject(1,"aobama");
        preparedStatement.setObject(2,"111111");
        preparedStatement.setObject(3,"圣枪游侠");

        // 执行SQL语句
        int i = preparedStatement.executeUpdate();
        System.out.println(i);

        // 单独获取自增长的主键值
        ResultSet rst = preparedStatement.getGeneratedKeys();

        // 因为主键值只有一个，可以不遍历
        if (rst.next()) {
            int id = rst.getInt(1);
            System.out.println(id);
        }

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

3.4 批处理

批处理相比较单独一条条执行SQL语句来说，其效率高很多。批处理一般会使用在批量添加多条数据和批量修改多条数据。

3.4.1 批处理的具体操作步骤

  1. 在url中要添加一个参数rewriteBatchedStatements=true
  2. 在完成所有参数设置之后，调用PreparedStatement的addBatch()方法，添加到批处理中
  3. 最后执行PreparedStatement的executeBatch()方法执行批处理语句
 // 测试批量添加
    public static void testBatchedInsert() throws Exception{
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2. 获取连接
        String url = "jdbc:mysql://localhost:3306/day04?characterEncoding=utf8&rewriteBatchedStatements=true";
        String user = "root";
        String pwd = "root";
        Connection connection = DriverManager.getConnection(url, user, pwd);

        // 预编译SQL语句
        String sql = "insert into user (username,password,nickname) values (?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        // 设置参数
        for (int i = 0; i < 1000; i++) {
            preparedStatement.setObject(1,"Bob-"+i);
            preparedStatement.setObject(2,"11111"+i);
            preparedStatement.setObject(3,"饱不？-"+i);

            // 添加到批量操作中
            preparedStatement.addBatch();
        }

        // 执行批量操作
        preparedStatement.executeBatch();

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

    }

3.5 事务

3.5.1 事务操作的步骤

  1. 执行逻辑单元之前先开启事务
  2. 逻辑单元执行完毕，没有出现异常则提交事务
  3. 逻辑单元执行过程中出现异常，则回滚事务

3.5.2 事务相关API

Connection中与事务有关的方法 说明
setAutoCommit(boolean autoCommit) 参数是true或false，如果设置为false，表示关闭自动提交，相当于开启事务；类似SQL里面的start transaction
void commit() 提交事务；类似SQL里面的commit
void rollback() 回滚事务；类似SQL里面的rollback

3.5.3 使用JDBC的事务完成转账案例


    /**
     * 事务相关的API：
     * 1. 开启事务 connection.setAutoCommit(false);
     * 2. 提交事务 connection.commit();
     * 3. 回滚事务 connection.rollback();
     * 4. 事务结束之后，要将此次连接的autoCommit还原成true
     */
    public static void testTransaction() throws Exception{

        // 测试转账
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2. 获取连接
        String url = "jdbc:mysql://localhost:3306/day04?characterEncoding=utf8";
        String user = "root";
        String pwd = "root";
        Connection connection = DriverManager.getConnection(url, user, pwd);

        // 3. 预编译SQL语句
        String sql = "update account set money=money+? where name=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        // 开启事务
        connection.setAutoCommit(false);

        try {
            // 3.1 zs扣款500
            preparedStatement.setObject(1,-500);
            preparedStatement.setObject(2,"zs");
            // 执行zs扣款的SQL语句
            preparedStatement.executeUpdate();

            // 异常
//            int num = 10/0;

            // 3.2 ls收款500
            preparedStatement.setObject(1,500);
            preparedStatement.setObject(2,"ls");
            // 执行ls收款的SQL语句
            preparedStatement.executeUpdate();

            // 提交事务
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
            connection.rollback();
        }finally {
            // 还原connection的AutoCommit为TRUE
            connection.setAutoCommit(true);
        }

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

学海无涯苦作舟

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,185评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,445评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,684评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,564评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,681评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,874评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,025评论 3赞 408
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,761评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,217评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,545评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,694评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,351评论 4赞 332
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,988评论 3赞 315
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,778评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,007评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,427评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,580评论 2赞 349

推荐阅读更多精彩内容