【JavaWeb】JavaWeb从入门到实战(13)

前言：

本文内容：JDBC复习、JDBC事务

推荐免费JavaWeb入门到实战视频：【狂神说Java】JavaWeb入门到实战_哔哩哔哩_bilibili

JDBC复习

JDBC： Java连接数据库

需要jar包的支持：

• java.sql
• javax.sql
• mysql-conneter-java(连接驱动,必须导入)

回顾环境搭建

创建数据库

-- 创建jdbc数据库
create database jdbc;
-- 用户表
create table users(
	`id` int primary key,
	`name` varchar(40),
	`pwd` varchar(40),
	`email` varchar(40),
	`birthday` date
);
insert into users(id,`name`,pwd,email,birthday) values(1,'小王','123','123@gmail.com','1999-01-01');
insert into users(id,`name`,pwd,email,birthday) values(2,'张三','123','123@gmail.com','1999-01-01');
insert into users(id,`name`,pwd,email,birthday) values(3,'李四','123','123@gmail.com','1999-01-01');
insert into users(id,`name`,pwd,email,birthday) values(4,'王五','123','123@gmail.com','1999-01-01');
insert into users(id,`name`,pwd,email,birthday) values(5,'小明','123','123@gmail.com','1999-01-01');

导入数据库依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.jokerdig</groupId>
    <artifactId>javaweb-jdbc</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
<!--        MySQL连接驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>
    </dependencies>
</project>

基本的JDBC

package com.jokerdig.test;

import java.sql.*;

/**
 * @author Joker大雄
 * @data 2022/4/12 - 13:36
 **/
public class JDBCTest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 配置
        String url = "jdbc:mysql://localhost:3306/jdbc?useSSL=false&serverTimezone=UTC";
        String username="root";
        String password="123456";
        // 加载启动
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn=DriverManager.getConnection(url,username,password);

        Statement st = conn.createStatement();
        // 编写sql
        String sql="select * from users";
        ResultSet re = st.executeQuery(sql);
        while(re.next()){
            System.out.println("id:"+re.getObject("id"));
            System.out.println("name:"+re.getObject("name"));
            System.out.println("password:"+re.getObject("pwd"));
            System.out.println("email:"+re.getObject("email"));
            System.out.println("birthday:"+re.getObject("birthday"));
        }
        // 释放资源
        re.close();
        st.close();
        conn.close();
    }
}

JDBC固定步骤

• 加载驱动
• 连接数据库，代表数据库
• 向数据库发送SQL的对象Statement：CRUD
• 编写SQL（根据业务和不同的SQL）
• 执行SQL
• 关闭连接

使用PreparedStatement更安全

package com.jokerdig.test;

import java.sql.*;

/**
 * @author Joker大雄
 * @data 2022/4/12 - 17:08
 **/
public class JDBCTest2 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 配置
        String url = "jdbc:mysql://localhost:3306/jdbc?useSSL=false&serverTimezone=UTC";
        String username="root";
        String password="123456";
        // 加载启动
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn= DriverManager.getConnection(url,username,password);
        // 编写sql
        String sql="select * from users where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1,1);
        ResultSet re = ps.executeQuery();
        while(re.next()){
            System.out.println("id:"+re.getObject("id"));
            System.out.println("name:"+re.getObject("name"));
            System.out.println("password:"+re.getObject("pwd"));
            System.out.println("email:"+re.getObject("email"));
            System.out.println("birthday:"+re.getObject("birthday"));
        }
        // 释放资源
        re.close();
        ps.close();
        conn.close();
    }
}

JDBC事务

要么都成功，要么都失败(ACID原则)

开始事务

事务提交 commit()

事务回滚 rollback()

关闭事务

Junit简单了解

IDEA新版运行Junit找不到包，需在设置Bulid Tools>Maven>Runner中勾选第一项Delegate IDE build/run actions to Maven(打开会降低运行速度)

了解：Junit单元测试

public class JDBCTest3 {
    // @Test只有在方法上有效
    @Test
    public void test(){
        System.out.println("Hello");
    }
}

事务解决转账问题

创建事务测试表

-- 新建事务测试表
create table account(
	id int primary key auto_increment,
	`name` varchar(40),
	money float
);
-- 插入测试数据
insert into account(`name`,money)values('A',1000);
insert into account(`name`,money)values('B',1000);
insert into account(`name`,money)values('C',1000);

通过转账测试事务

package com.jokerdig.test;

import java.sql.*;

/**
 * @author Joker大雄
 * @data 2022/4/12 - 17:18
 **/
public class JDBCTest3 {
    public static void main(String[] args) throws SQLException {
        // 配置
        String url = "jdbc:mysql://localhost:3306/jdbc?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "123456";
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            // 加载启动
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
            // 通知数据库开启事务 关闭自动提交
            conn.setAutoCommit(false);
            // 转账
            String sql = "update account set money = money-100 where `name` ='A'";
            ps = conn.prepareStatement(sql);
            ps.executeUpdate();
            // 制造错误
            // int i = 1/0;
            // 转账
            String sql1 ="update account set money = money+100 where `name` ='B'";
            ps = conn.prepareStatement(sql1);
            ps.executeUpdate();
            // 提交事务
            conn.commit();
            System.out.println("转账成功");
            // 开启自动提交
            conn.setAutoCommit(true);
        } catch (Exception e) {
            try {
                // 异常就回滚事务
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            // 释放资源
            ps.close();
            conn.close();
        }
    }
}