JDBC基础

1.jdbc的概念

JDBC（Java DataBase Connectivity：java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系型数据库提供统一访问，它是由一组用Java语言编写的类和接口组成的。
JDBC的作用：可以通过java代码操作数据库

2.jdbc的本质

其实就是java官方提供的一套规范(接口)。用于帮助开发人员快速实现不同关系型数据库的连接！

3.jdbc的快速入门程序

导入jar包

注册驱动

Class.forName("com.mysql.jdbc.Driver");

获取连接

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2", "root", "root");

获取执行者对象

Statement stat = conn.createStatement();

执行sql语句，并接收返回结果

String sql = "SELECT * FROM user";
ResultSet rs = stat.executeQuery(sql);

处理结果

while(rs.next()) {
    System.out.println(rs.getInt("id") + "\t" + rs.getString("name"));
}

释放资源
 

import org.junit.Test;
 
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
 
//查询操作
public class StudentText {
    String drive = "com.mysql.cj.jdbc.Driver";
    String url = "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC";
    String nsername = "root";
    String password = "root";
 
    @Test
    public void text() throws ClassNotFoundException, SQLException {
        //JDBC操作数据库步骤
        //1.加载驱动
        Class.forName(drive);
        //2.获取数据库
        Connection con = DriverManager.getConnection(url, nsername, password);
        //3.创建sql语句
        String sql = "select * from student";
        //4.执行sql语句
        PreparedStatement ps = con.prepareStatement(sql);
 
        //5.获取集合
        ResultSet rs = ps.executeQuery();
 
        //6.获取结果
        //让结果集游标不断下移，直到循环结束
        //定义集合，装student对象
        List<Student> studentlist = new ArrayList<>();
        while (rs.next()) {
            //根据字段名称获取表中的数据
 
            int stuId = rs.getInt("stuId");
            String stuName = rs.getString("stuName");
            String stuSex = rs.getString("stuSex");
            int stuAge = rs.getInt("stuAge");
            String stuAddr = rs.getString("stuAddr");
            //把以上数据封装到Student对象中
 
            Student student = new Student(); //一行数据就封装成了一个Student对象
 
            student.setStuId(stuId);
            student.setStuName(stuName);
            student.setStuSex(stuSex);
            student.setStuAge(stuAge);
            student.setStuAddr(stuAddr);
//把当前行封装后的Student对象装载到 List集合中
 
            studentlist.add(student);
        }
        System.out.println(studentlist);
        //关闭资源
        if (rs != null) {
            rs.close();
        }
        if (ps != null) {
            ps.close();
        }
        if (con != null) {
            con.close();
        }
    }
 
    //添加操作
    @Test
    public void testadd() throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName(drive);
        //2.获取数据库
        Connection con = DriverManager.getConnection(url, nsername, password);
        //3.编写sql
        String sql = "insert into student(stuName,stuSex,stuAge,stuAddr) values(?,?,?,?)";
        //4.执行sql语句
        PreparedStatement ps = con.prepareStatement(sql);
        //5.添加数据
        Student student = new Student();
        student.setStuName("王五");
        student.setStuSex("男");
        student.setStuAge(16);
        student.setStuAddr("南阳");
        //6.1传参
 
        ps.setObject(1, student.getStuName());
        ps.setObject(2, student.getStuSex());
        ps.setObject(3, student.getStuAge());
        ps.setObject(4, student.getStuAddr());
        //6.2执行更新
        int n = ps.executeUpdate();
        //7.判断
        if (n > 0) {
            System.out.println("插入数据成功");
        } else {
            System.out.println("插入数据失败");
        }
        //8.关闭资源
 
        if (ps != null) {
            ps.close();
        }
        if (con != null) {
            con.close();
        }
    }
 
    //删除操作
    @Test
    public void testdel() throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName(drive);
        //2.获取数据库
        Connection con = DriverManager.getConnection(url, nsername, password);
        //3.编写sql
        String sql = "delete from student where stuId=?";
        //4.执行sql语句
        PreparedStatement ps = con.prepareStatement(sql);
        //5.1传参
        int stuId = 3;
        ps.setObject(1, stuId);
        //5.2执行更新
        int n = ps.executeUpdate();
        //6.判断
        if (n > 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }
        //7.关闭资源
 
        if (ps != null) {
            ps.close();
        }
        if (con != null) {
            con.close();
        }
    }
}