JDBC第2天(MySQL第8天)中的demo小练习

1.JDBC中预编译的sql执行对象(以变量的形式插入数据)

    Scanner sc = new Scanner(System.in);
    System.out.println("请输入姓名:");
    String name = sc.nextLine();
    System.out.println("请输入年龄:");
    int age = sc.nextInt();
    try (Connection conn = DBUtils.getConn()) {
        // 创建sql语句
        String sql = "insert into person values(null,?,?)";
        // 创建预编译的sql执行对象
        PreparedStatement stat = conn.prepareStatement(sql);
        // 替换?为真正的变量
        stat.setString(1, name);
        stat.setInt(2, age);
        // 执行sql语句
        stat.executeUpdate();
        System.out.println("保存成功!");
    } catch (Exception e) {
        e.printStackTrace();
    }
    sc.close();

2.JDBC中sql注入

    Scanner sc = new Scanner(System.in);
    System.out.println("请输入用户名:");
    String username = sc.nextLine();
    System.out.println("请输入密码:");
    String password = sc.nextLine();
    sc.close();
    try (Connection conn = DBUtils.getConn()) {// JDK1.7之后推出的流自动关闭特性
        // 创建sql执行对象
        Statement stat = conn.createStatement();
        // 执行sql语句
        String sql = "select count(*) from user where username='" + username + "' and password='" + password + "'";
        System.out.println(sql);
        /**
         * 
         * 请输入用户名: jsjgsjgjs 
         * 请输入密码: 
         * ' or '1'='1//这里注入了sql语句 
         * select count(*) from user where username='jsjgsjgjs' and password='' or '1'='1' 
         * 登录成功!
         */
        ResultSet rs = stat.executeQuery(sql);
        while (rs.next()) {
            int count = rs.getInt("count(*)");
            if (count > 0) {
                System.out.println("登录成功!");
            } else {
                System.out.println("登录失败!");
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

3.JDBC批量操作sql语句

   try (Connection conn = DBUtils.getConn()) {
        String sql = "insert into person values(null,?,?)";
        PreparedStatement stat = conn.prepareStatement(sql);
        // 批量插入100条数据 name1-100 年龄1-100
        for (int i = 0; i <= 100; i++) {
            stat.setString(1, "name" + i);
            stat.setInt(2, i);
            // 添加到批量操作
            stat.addBatch();
            // 避免内存溢出
            if (i % 20 == 0) {// 每隔20次执行一次
                stat.executeBatch();// 执行批量操作
            }
        }
        stat.executeBatch();// 执行批量操作
        System.out.println("数据插入成功!");
    } catch (Exception e) {
        e.printStackTrace();
    }

4.JDBC分页查询

    Scanner sc = new Scanner(System.in);
    System.out.println("请输入页数:");
    int page = sc.nextInt();
    System.out.println("请输入条数:");
    int count = sc.nextInt();
    try (Connection conn = DBUtils.getConn()) {
        String sql = "select name from person limit ?,?";
        PreparedStatement stat = conn.prepareStatement(sql);
        stat.setInt(1, (page - 1) * count);
        stat.setInt(2, count);
        ResultSet rs = stat.executeQuery();
        while (rs.next()) {
            String name = rs.getString("name");
            System.out.println(name);
        }
        sc.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

5.JDBC事务处理

   try (Connection conn = DBUtils.getConn()) {
        // 创建sql执行对象
        Statement stat = conn.createStatement();
        // 开启事务,关闭自动提交
        conn.setAutoCommit(false);
        // 执行sql语句
        String sql1 = "update hero set money=money-2000 where id=2";
        String sql2 = "update hero set money=money+2000 where id=1";
        stat.executeUpdate(sql1);
        stat.executeUpdate(sql2);
        // 查询剩余的钱是否大于等于0
        String sql3 = "select money from hero where id=2";
        ResultSet rs = stat.executeQuery(sql3);
        while (rs.next()) {
            int money = rs.getInt("money");
            if (money >= 0) {// 说明钱够
                conn.commit();// 提交事务
                System.out.println("转账成功!");
            } else {// 钱不够
                conn.rollback();// 回滚
                System.out.println("转账失败!");
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

6.JDBC获取自增主键的值

    Scanner sc = new Scanner(System.in);
    System.out.println("请输入球队名称:");
    String teamName = sc.nextLine();
    System.out.println("请输入球员名称:");
    String playerName = sc.nextLine();
    try (Connection conn = DBUtils.getConn()) {
        // 先查询是否存在这个球队
        String sql = "select id from team where name=?";
        PreparedStatement stat = conn.prepareStatement(sql);
        stat.setString(1, teamName);// 通过球队名称查询球队id
        ResultSet rs = stat.executeQuery();
        int teamId = -1;// 上来先假设球队不存在,即球队的id不可能为-1
        while (rs.next()) {// 若球队存在
            System.out.println("该球队已存在!");
            teamId = rs.getInt("id");
        }
        if (teamId == -1) {// 不存在这个球队
            System.out.println("该球队不存在!");
            // 保存该球队的数据
            String sql1 = "insert into team values(null,?)";
            PreparedStatement stat1 = conn.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
            stat1.setString(1, teamName);
            stat1.executeUpdate();
            System.out.println("球队添加成功!");
            ResultSet rs1 = stat1.getGeneratedKeys();
            while (rs1.next()) {
                // 得到自增的球队id
                teamId = rs1.getInt(1);
            }
        }
        // 添加球员
        String sql2 = "insert into player values(null,?,?)";
        PreparedStatement stat2 = conn.prepareStatement(sql2);
        stat2.setString(1, playerName);
        stat2.setInt(2, teamId);
        stat2.executeUpdate();
        System.out.println("球员添加成功!");
        sc.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

7.JDBC获取数据库和表及其字段的源数据

   try (Connection conn = DBUtils.getConn()) {
        // 得到数据库的源数据数据
        DatabaseMetaData dbmd = conn.getMetaData();
        System.out.println("数据库版本:" + dbmd.getDriverVersion());
        System.out.println("用户名:" + dbmd.getUserName());
        System.out.println("数据库地址:" + dbmd.getURL());
        System.out.println("数据库厂商:" + dbmd.getDatabaseProductName());

        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("select * from emp");
        // 得到表相关的源数据
        ResultSetMetaData rsmd = rs.getMetaData();
        // 得到表的字段数量
        int count = rsmd.getColumnCount();
        for (int i = 0; i < count; i++) {
            System.out.println("字段名称:" + rsmd.getColumnName(i + 1));
            System.out.println("字段类型:" + rsmd.getColumnTypeName(i + 1));
        }

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