JDBC连接数据库进行增删改查

ゝ一纸荒年。 2024-04-01 12:27 114阅读 0赞

什么是JDBC？

> **JDBC的全称是Java数据库连接(Java Database connect)，它是一套用于执行SQL语句的Java API。应用程序可通过这套API连接到关系数据库，并使用SQL语句来完成对数据库中数据的查询、更新和删除等操作。**

![0d3b1810fe87491a9c295635bef8b159.jpeg][]

MySQL代码：

# 创建一个数据库
    create database school;
    # 使用这个数据库
    use school;
    # 如果student这个表存在就删除这个表
    drop table if exists student;
    # 创建一个student表
    create table student(
    	stuId int primary key auto_increment,
    	stuName varchar(16) not null,
    	stuSex varchar(8) not null,
    	stuAddr varchar(16) not null
    )
    # 向student这个表添加数据
    insert into student values(1,'剑圣','男','召唤师峡谷')
    insert into student(stuName,stuSex,stuAddr) values('小红','女','China')
    insert into student(stuName,stuSex,stuAddr) values('D.va','女','OW')
    insert into student(stuName,stuSex,stuAddr) values('美羊羊','女','羊村')

JDBC操作数据库的步骤

1.首先在根目录创建一个lib文件夹，放入JDBC驱动程序，然后Add As Library

![e32d648f884741558119c47f98a39231.png][]![522e3985d0ac47809ae3c8765b4783a7.png][]

2.加载数据库驱动，使用反射加载

Class.forName("com.mysql.cj.jdbc.Driver");// 注意！如果JDBC驱动程序是版本5的 Driver的地址为：com.mysql.jdbc.Driver

3.使用驱动管理器来获得连接——获得一个数据库连接对象Connection

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC", username, password);// username为你的数据库用户名，password为你的数据库密码

4.使用Connection创建PreparedStatement预处理对象，使用PreparedStatement来执行sql语句

String sql = "select * from student";
    
    PreparedStatement ps = con.prepareStatement(sql);

5.操作判断——增删改返回的是影响的行数（返回值是int），只有查询获得结果集（返回值是ResultSet）

// 增删改的操作判断
     int a = ps.executeUpdate();
     if (a > 0) {
        System.out.println("成功");
     } else {
        System.out.println("失败");
     }

// 查询的操作判断
    ResultSet re = ps.executeQuery();
    while (re.next()) {
      int stuId = re.getInt(1);
      String stuName = re.getString(2);
      String stuSex = re.getString(3);
      String stuAddr = re.getString(4);
      System.out.println(stuId + "----" + stuName + "----" + stuSex + "----" + stuAddr);
    
     }

6.回收资源

if (re != null) {
      re.close();
     }
    if (ps != null) {
      ps.close();
     }
    if (con != null) {
      con.close();
     }

全部代码：

import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.util.Scanner;
    
    public class Main {
        private static String driver = "com.mysql.cj.jdbc.Driver";
        private static String url = "jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC";
        private static String username = "root";
        private static String password = "123456";
    
        public static void main(String[] args) throws Exception {
            Scanner scanner = new Scanner(System.in);
            System.out.println("1--查询表");
            System.out.println("2--删除数据");
            System.out.println("3--修改数据");
            System.out.println("4--增加数据");
            int a = scanner.nextInt();
            if (a == 1) {
                test();
            } else if (a == 2) {
                testDelete();
            } else if (a == 3) {
                testUpdate();
            } else if (a == 4) {
                testAdd();
            } else {
                System.out.println("请输入正确的数字");
            }
        }
    
        /*
         * 查询
         * */
    
        //    @Test
        public static void test() throws Exception {
    
            Class.forName(driver);
    
            Connection con = DriverManager.getConnection(url, username, password);
    
            String sql = "select * from student";
    
            PreparedStatement ps = con.prepareStatement(sql);
    
            ResultSet re = ps.executeQuery();
    
            while (re.next()) {
    
                int stuId = re.getInt(1);
    
                String stuName = re.getString(2);
    
                String stuSex = re.getString(3);
                
                String stuAddr = re.getString(4);
    
                System.out.println(stuId + "----" + stuName + "----" + stuSex + "----" + stuAddr);
    
            }
            if (re != null) {
                re.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (con != null) {
                con.close();
            }
    
        }
    
        /*
         * 添加
         * */
    
        //    @Test
        public static void testAdd() throws Exception {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url, username, password);
            String sql = "insert into student(stuName,stuSex,stuAddr) values (?,?,?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, "Jack");
            ps.setString(2, "男");
            ps.setString(3, "漂亮国");
            int a = ps.executeUpdate();
            if (a > 0) {
                System.out.println("添加成功，已添加Jack");
            } else {
                System.out.println("添加失败，表中已有Jack");
            }
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    
        /*
         * 删除
         * */
    //    @Test
        public static void testDelete() throws Exception {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url, username, password);
            String sql = "delete from student where stuId = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, 4);
            int a = ps.executeUpdate();
            if (a > 0) {
                System.out.println("删除成功，已删除编号为4的同学");
            } else {
                System.out.println("删除失败，表中没有编号为4的同学");
            }
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    
        /*
         * 修改
         * */
    //    @Test
        public static void testUpdate() throws Exception {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url, username, password);
            String sql = "update student set stuName = ? where stuName = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, "Michael");
            ps.setString(2, "Jack");
            int a = ps.executeUpdate();
            if (a > 0) {
                System.out.println("修改成功，已将Jack改为Michael");
            } else {
                System.out.println("修改失败");
            }
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    }

[0d3b1810fe87491a9c295635bef8b159.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/02c257e2c17d43da90facc44a438bd3c.jpeg
[e32d648f884741558119c47f98a39231.png]: https://img-blog.csdnimg.cn/e32d648f884741558119c47f98a39231.png
[522e3985d0ac47809ae3c8765b4783a7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/05c3aba100914646bdd8ad0ddc924383.png