JDBC复习

JDBC复习

什么是JDBC：

• java datebase connection 即java连接数据库

创建数据库表

CREATE TABLE users(
	id INT PRIMARY KEY,
	`name` VARCHAR(40),
	`password` VARCHAR(40),
	email VARCHAR(60),
	birthday DATE
);

INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(1,'张三','123456','30666@qq.com','2001-01-01')
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(2,'李四','123456','30666@qq.com','2001-01-02')
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(3,'王五','123456','30666@qq.com','2001-01-03')
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(4,'小刘','123456','30666@qq.com','2001-01-04')


SELECT * FROM users;

导入数据库依赖

<!-- 连接数据库  mysql的驱动-->
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.47</version>
</dependency>

IDEA连接数据库

• datebase

JDBC固定步骤

import java.sql.*;

public class TestJdbc {
    public static void main(String[] args) throws Exception {
        //1、配置信息
        String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8";
        String username = "root";
        String password = "123456";

        //2、加载驱动
        Class.forName("com.mysql.jdbc.Driver");

        //3、连接数据库
        Connection connection = DriverManager.getConnection(url,username,password);

        //4、向数据库发送sql的对象
        Statement statement = connection.createStatement();
        //5、编写sql
        String sql = "select * from users";

        //6、执行查询sql，返回一个结果集
        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"));
        }
        //7、关闭连接
        rs.close();
        statement.close();
        connection.commit();
    }
}

sql预编译：

import java.sql.*;

public class TestJdbc2 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //配置信息
        String url = "jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf8";
        String username = "root";
        String password = "123456";
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //连接数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        //向数据库发送sql的对象
        Statement statement = connection.createStatement();

        //编写SQL
        String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";

        //预编译
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setInt(1,5);
        preparedStatement.setString(2,"小七");
        preparedStatement.setString(3,"123456");
        preparedStatement.setString(4,"30666@qq.com");
        preparedStatement.setString(5, String.valueOf(new Date(new java.util.Date().getTime())));

        int i = preparedStatement.executeUpdate();

        if (i>0){
            System.out.println("插入成功");
        }
        //关闭连接

        statement.close();
        connection.commit();
    }
}

事务

事务

要么都成功，要么都失败 ！

ACID原则，保证数据的安全。

1. 开启事务
2. 事务提交 commi ()
3. 事务回滚 rollback ()
4. 关闭事务

Juit单元测试

依赖

<!--单元测试-->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
    <scope>test</scope>
</dependency>

简单使用

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

package com.kk;

import org.junit.Test;

public class TestJDBC3 {

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