JDBC 连接数据库实现增删改查

第一步：创建数据库

CREATE DATABASE student;

第二步：创建表并插入数据

use student;
create table student(
	stuId INT PRIMARY KEY AUTO_INCREMENT, 
	stuName VARCHAR(24),
	stuSex VARCHAR(2),
	stuAge INT,
	stuAddr VARCHAR(64)
);

INSERT INTO student(stuName,stuSex,stuAge,stuAddr) VALUES('小妹','女',12,'河南');
INSERT INTO student(stuName,stuSex,stuAge,stuAddr) VALUES('小样','男',13,'郑州');
INSERT INTO student(stuName,stuSex,stuAge,stuAddr) VALUES('小宁','男',14,'信阳');
INSERT INTO student(stuName,stuSex,stuAge,stuAddr) VALUES('小陈','女',15,'不知道');

第三步：用JDEA实现增删改查

package com.hp.bean;

public class Student {

    private int stuId;
    private String stuName;
    private String stuSex;
    private int stuAge;
    private String stuAddr;

    @Override
    public String toString() {
        return "Student{" +
                "stuId=" + stuId +
                ", stuName='" + stuName + '\'' +
                ", stuSex='" + stuSex + '\'' +
                ", stuAge=" + stuAge +
                ", stuAddr='" + stuAddr + '\'' +
                '}';
    }

    public int getStuId() {
        return stuId;
    }

    public void setStuId(int stuId) {
        this.stuId = stuId;
    }

    public String getStuName() {
        return stuName;
    }

    public void setStuName(String stuName) {
        this.stuName = stuName;
    }

    public String getStuSex() {
        return stuSex;
    }

    public void setStuSex(String stuSex) {
        this.stuSex = stuSex;
    }

    public int getStuAge() {
        return stuAge;
    }

    public void setStuAge(int stuAge) {
        this.stuAge = stuAge;
    }

    public String getStuAddr() {
        return stuAddr;
    }

    public void setStuAddr(String stuAddr) {
        this.stuAddr = stuAddr;
    }

    

    public Student(int stuId, String stuName, String stuSex, int stuAge, String stuAddr) {
        this.stuId = stuId;
        this.stuName = stuName;
        this.stuSex = stuSex;
        this.stuAge = stuAge;
        this.stuAddr = stuAddr;
    }

   


}

package com.hp.bean;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Main {
    private String driver = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=UTC";
    private String username = "root";
    private String password = "root";


    @Test

    /**
     * 查
     */
    public void testSelectAll() throws Exception {
        Class.forName(driver);
        Connection con = DriverManager.getConnection(url, username, password);
        String sql = "select *from student";
        PreparedStatement pstm = con.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();

        while (rs.next()) {
            int stuId = rs.getInt(1);
            String stuName = rs.getString(2);
            String stuSex = rs.getString(3);
            int stuAge = rs.getInt(4);
            String stuAddr = rs.getString(5);

            System.out.println(stuId + stuName + stuSex + stuAge + stuAddr);

        }
        rs.close();
        pstm.close();
        con.close();
    }

    @Test
    /**
     * 修
     */
    public void TestADD() throws Exception {
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, username, password);
        String sql = "INSERT INTO  student(stuName,stuSex,stuAge,stuAddr) VALUES(?,?,?,?)";
        PreparedStatement pstm = connection.prepareStatement(sql);

        pstm.setString(1, "狗");
        pstm.setString(2, "女");
        pstm.setInt(3, 17);
        pstm.setString(4, "177");

        int n = pstm.executeUpdate();
        if (n > 0) {
            System.out.println("添加成功");
        } else {
            System.out.println("添加失败");
        }

    }

    @Test
    /**
     * 删
     */
    public void TestDelete() throws Exception {
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, username, password);
        String sql = "delete from student where stuId =?";
        PreparedStatement pstm = connection.prepareStatement(sql);

        pstm.setInt(1, 4);

        int n = pstm.executeUpdate();
        if (n > 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }
        pstm.close();
        connection.close();
    }

    @Test
    /**
     * 改
     */
    public void TestRevise() throws Exception {
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, username, password);
        String sql = "UPDATE  student SET stuName=? WHERE stuSex=?";
        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setString(1, "小妹");
        pstm.setString(2, "男");
        int n = pstm.executeUpdate();
        if (n > 0) {
            System.out.println("修改成功，已将Jack改为Michael");
        } else {
            System.out.println("修改失败");
        }
        if (pstm != null) {
            pstm.close();
        }
        if (connection != null) {
            connection.close();
        }
    }
}