14、JDBC

什么是jdbc：java连接数据库

需要jar包的支持：

• java.sql
• javax.sql
• mysql-connecter-java…

实现环境搭建：

package com.xiao.test;

import java.sql.*;

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

        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.连接数据库  代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        //3.向数据库发送sql的对象statement
        Statement statement = connection.createStatement();
        //4.编写sql
        String sql = "select * from users";
        //5.执行查询sql  返回一个ResultSet ：结果集
        ResultSet rs = statement.executeQuery(sql);

        while (rs.next()){
            System.out.println("id" + rs.getObject("id"));
            System.out.println("name" + rs.getObject("name"));
            System.out.println("password" + rs.getObject("password"));
            System.out.println("email" + rs.getObject("email"));
            System.out.println("birthday" + rs.getObject("birthday"));
        }
        //6.关闭连接释放资源
        rs.close();
        statement.close();
        connection.close();
    }
}

导入数据库依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

idea中连接数据库

jdbc固定步骤：

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

事务：

要么都成功，要么都失败

ACID原则：保证数据的安全

开启事务
事务提交 commit()
事务回滚 rollback()
关闭事务

Junit单元测试

依赖

<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
    <scope>test</scope>
</dependency>

简单使用

@Test只在方法上有效，只要加了这个注解的方法，就能直接运行！

@Test
public void test(){
    System.out.println("你好");
}

package com.xiao.test;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class TestJDBC1 {

    @Test
    public void test() throws Exception {
        String url = "jdbc:mysql://localhost:3306/jdbc?useUnicode=true&charaterEncoding=utf-8";
        String username ="root";
        String password = "12345678";

        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url, username, password);

        //通知数据库开启事务
        connection.setAutoCommit(false);//开启
        String sql = "update accout set money = money - 100 where name = 'a'";
        connection.prepareStatement(sql).executeUpdate();

        //制造错误
        //int i  = 1/0;
        String sql2 = "update accout set money = money + 100 where name = 'b'";
        connection.prepareStatement(sql2).executeUpdate();
        connection.commit();//以上两条sql都执行成功了，就提交事务
    }
}

start transaction; #开启事务
update accout set money = money - 100 where name = 'a';
update accout set money = money + 100 where name = 'b';

commit ;