JDBC

JDBC: java database connection

需要jar包的支持:

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

步骤

普通步骤

导入数据库依赖

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.19</version>
</dependency>

加载驱动
连接数据库, 代表数据库
向数据库发送SQL的对象statement: CRUD
执行查询, 返回一个result
关闭连接

public class TestJDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //配置信息
        String url = "jdbc:mysql://localhost:3306/book";
        String username = "root";
        String password = "root";

        // 1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2. 连接数据库, 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //3. 向数据库发送SQL的对象Statement:CRUD
        Statement statement = connection.createStatement();
        //4. 编写SQL
        String sql = "select * from t_user;";
        //5. 执行SQL, 返回结果集
        ResultSet resultSet = statement.executeQuery(sql);

        while (resultSet.next()){
            System.out.println("id="+resultSet.getInt("id"));
            System.out.println("username="+resultSet.getString("username"));
            System.out.println("password="+resultSet.getString("password"));
            System.out.println("email="+resultSet.getString("email"));
        }

        //6. 关闭连接, 释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

预编译

public class TestJDBC2 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //配置信息
        String url = "jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=UTF8";
//        String url = "jdbc:mysql://localhost:3306/book";
        String username = "root";
        String password = "root";

        // 1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2. 连接数据库, 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //3. 编写SQL
        String sql = "insert into t_user(id, username, password, email) VALUES (?, ?, ?, ?);";
        //4. 预编译
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, 8);
        preparedStatement.setString(2, "刘潇博");
        preparedStatement.setString(3, "asdff");
        preparedStatement.setString(4, "zsdff@xx.com");
        // 5. 执行
        int i = preparedStatement.executeUpdate();
        if (i>0){
            System.out.println("插入成功");
        }
        //6. 关闭连接, 释放资源
        preparedStatement.close();
        connection.close();
    }
}

这里需要注意, 插入中文字符时可能会变成问号, 先查看并修改数据库的字符集:

SHOW CREATE DATABASE book; //查看库的字符集
SHOW CREATE TABLE book.t_user;//查看表的字符集
SHOW FULL COLUMNS FROM book.t_user; //查看字段编码

ALTER DATABASE book DEFAULT CHARACTER SET utf8; //修改库的字符集
ALTER TABLE book.t_user CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; //修改表以及字符字段的字符集

事物

要么都成功, 要么都失败

ACID原则: 保证数据的安全

开启事物
事物提交
事物回滚
关闭事物

创建一个表, 模拟转账:

create table account
(
    id    int auto_increment
        primary key,
    name  varchar(40) null,
    money float       null
);

插入值:

1
2
3

insert into account(name, money) VALUES ('A', 1000);
insert into account(name, money) VALUES ('B', 1000);
insert into account(name, money) VALUES ('C', 1000);

执行:

start transaction; # 开启事物

update account
set money=money - 100
where name = 'A';

结果为:

没有变化的原因是没有提交事物.

添加提交语句后:

start transaction; # 开启事物

update account
set money=money - 100
where name = 'A';

commit ;

结果达到预期, 不再贴出

在commit前加上rollback 就能保证无论出现什么错误或者做了什么操作都能回滚到开始事物时的状态.

在JDBC中编写如下:

@Test
public void test() {
    //配置信息
    String url = "jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=UTF8";
    //        String url = "jdbc:mysql://localhost:3306/book";
    String username = "root";
    String password = "root";
    Connection connection = null;

    try {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2. 连接数据库, 代表数据库
        connection = DriverManager.getConnection(url, username, password);
        //3. 通知数据库开启事物, 默认是自动开启的, 给个false就手动开启了
        connection.setAutoCommit(false);

        String sql = "update account set money=money - 100 where name = 'A'";
        connection.prepareStatement(sql).executeUpdate();

        //4. 制造错误
        int i = 1/0;
        String sql2 = "update account set money=money+100 where name = 'B'";
        connection.prepareStatement(sql2).executeUpdate();

        connection.commit();    //以上两条SQL都执行成功了, 就提交事物
        System.out.println("success");
    } catch (Exception e) {
        try {
            // 如果出现异常, 就通知数据库执行回滚操作
            connection.rollback();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        e.printStackTrace();
    }finally {
        //6. 关闭连接, 释放资源
        try {
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}