JDBC复习

桃扇骨 2023-10-03 14:40 7阅读 0赞

### 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;

**导入数据库依赖**

<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");
        }
    }