java全栈学习之路——jdbc(2)

jdbc

jdbc事务操作

注意：mysql InnoDB支持事务，MyISAM不支持事务

例子：小明向妈妈要5块钱去买冰棍。

测试表：

+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | 小明     |     0 |
|  2 | 李桂芳   |   100 |
+----+----------+-------+

    @Test
    public void test() {
        //题：小明向他妈李桂芳要5块钱买冰棍。

        //1.首先查看李桂芳余额
        String sql = "select money from `text` where name = ?";
        Connection connection = null;
        PreparedStatement p = null;
        ResultSet res = null;
        int count = 0;
        //获取连接对象
        connection = JdbcUtil.getConnection();
        //创建语句对象
        try {
            //取消自动提交改为手动提交(开启事务)
            connection.setAutoCommit(false);
            //查询余额
            p = connection.prepareStatement(sql);
            p.setString(1, "李桂芳");
            res = p.executeQuery();
            if (!res.next()) {
                //事务回滚
                connection.rollback();
                throw new RuntimeException("李桂芳钱不够呀");
            }
            //李桂芳钱-5
            sql = "update `text` set money=money-5 where name = ?";
            p = connection.prepareStatement(sql);
            p.setString(1, "李桂芳");
            count = p.executeUpdate();
            if (count == 0) {
                //事务回滚
                connection.rollback();
                throw new RuntimeException("你的要钱方式有问题,李桂芳有钱但不交出来");
            }
            //模拟中间出错
//            int i = 1 / 0;
            //小明钱+5
            sql = "update `text` set money=money+5 where name = ?";
            p = connection.prepareStatement(sql);
            p.setString(1, "小明");
            count = p.executeUpdate();
            if (count == 0) {
                //事务回滚
                connection.rollback();
                throw new RuntimeException("李桂芳给了，但小明没拿到，why?");
            }
            //提交事务
            connection.commit();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(connection);
        }

    }

success

+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   |     5 |
|  2 | 李桂芳 |    95 |
+----+--------+-------+

jdbc获取自动生成的主键

插入前
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   |     5 |
|  2 | 李桂芳 |    95 |
+----+--------+-------+
插入后
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   |    5  |
|  2 | 李桂芳 |    95 |
|  3 | 小刘   |   100 |
+----+--------+-------+
控制台输出为：3

    @Test
    public void getAutoId() {
        //题：获取自动生成的主键
        String sql = "insert into `text`(`name`,money) values (?,?)";
        Connection connection;
        //获取连接对象
        connection = JdbcUtil.getConnection();
        ResultSet res = null;
        //获取
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, "小刘");
            preparedStatement.setInt(2, 100);
            //受影响行数
            preparedStatement.executeUpdate();
            //结果集
            res = preparedStatement.getGeneratedKeys();
            while (res.next()) {
            // //打印插入后自动生成的主键
                System.out.println(res.getLong(1));
            }
            res.close(); 
            preparedStatement.close();
            JdbcUtil.close(connection);
        } catch (SQLException e) {
            throw new RuntimeException("数据插入失败", e);
        }
    }