連線資料庫並實現增、刪、改、查

呓语-MSHK發表於2024-11-05
資料庫

1、建立資料庫表

2、建立實體類物件
package pojo;

/*
品牌
*/
public class Brand {
private int id; //id 主鍵（非空且唯一）
private String brandName; //品牌名稱
private String company; //公司名稱
private int order; //排序欄位
private String description; //描述資訊
private int status; //狀態： 0：禁用 1：啟用

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public String getBrandName() {
    return brandName;
}

public void setBrandName(String brandName) {
    this.brandName = brandName;
}

public String getCompany() {
    return company;
}

public void setCompany(String company) {
    this.company = company;
}

public int getOrder() {
    return order;
}

public void setOrder(int order) {
    this.order = order;
}

public String getDescription() {
    return description;
}

public void setDescription(String description) {
    this.description = description;
}

public int getStatus() {
    return status;
}

public void setStatus(int status) {
    this.status = status;
}

@Override
public String toString() {
    return "Brand{" +
            "id=" + id +
            ", brandName='" + brandName + '\'' +
            ", company='" + company + '\'' +
            ", order=" + order +
            ", description='" + description + '\'' +
            ", status=" + status +
            '}';
}

}

3-1、查詢操作

package com.itheima.example;

import pojo.Brand;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/*
品牌資料的增刪改查工作
*/
public class BrandTest {

public static void main(String[]args) throws Exception {
    //1、註冊驅動
    Class.forName("com.mysql.cj.jdbc.Driver");

    //2、獲取連結
    String username="root";
    String url="jdbc:mysql://localhost:3306/data?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8";
    String password="Lhw123456";
    Connection conn= DriverManager.getConnection(url,username,password);

    //3、定義sql
    String sql="SELECT * FROM tb_brand;";

    //4、獲取執行物件
    PreparedStatement pstmt=conn.prepareStatement(sql);

    //5、執行sql
    ResultSet rs=pstmt.executeQuery();

    //6、處理結果List<Brand>，封裝Brand物件，裝在List集合
    Brand brand=null;
    List<Brand> brands=new ArrayList<>();
    while(rs.next()){
        //獲取資料
        int id=rs.getInt("id");
        String brandName=rs.getString("brand_name");
        String company=rs.getString("company");
        int ordered=rs.getInt("ordered");
        int status=rs.getInt("status");
        String description=rs.getString("description");

        //封裝Brand
        brand=new Brand();
        brand.setId(id);
        brand.setBrandName(brandName);
        brand.setCompany(company);
        brand.setOrder(ordered);
        brand.setStatus(status);
        brand.setDescription(description);

        //裝載集合
        brands.add(brand);
    }
    System.out.println(brands);

    //7、釋放資源
    rs.close();
    pstmt.close();
    conn.close();
}

}

3-2、增加操作（這裡面id是資料庫自動生成的，所以在建立資料庫時需要把id設定成“自增欄位（auto-increment）”）

package com.itheima.example;

import pojo.Brand;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class BrandAdd {
public static void main(String[]args) throws Exception {
//接收頁面提交引數
String brandName="香飄飄";
String company="香飄飄";
int ordered=1;
int status=1;
String description="繞地球一圈";

    //1、註冊驅動
    Class.forName("com.mysql.cj.jdbc.Driver");

    //2、獲取連結
    String username="root";
    String url="jdbc:mysql://localhost:3306/data?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8";
    String password="Lhw123456";
    Connection conn= DriverManager.getConnection(url,username,password);

    //3、定義sql
    String sql="INSERT into tb_brand(brand_name,company,ordered,status,description) values(?,?,?,?,?);";

    //4、獲取執行物件
    PreparedStatement pstmt=conn.prepareStatement(sql);

    //5、設定引數
    pstmt.setString(1,brandName);
    pstmt.setString(2,company);
    pstmt.setInt(3,ordered);
    pstmt.setInt(4,status);
    pstmt.setString(5,description);

    //6、執行sql
    int count=pstmt.executeUpdate();//影響行數

    //7、處理結果
    System.out.println(count>0);

    //8、釋放資源
    pstmt.close();
    conn.close();
}

}

3-3、修改操作

public static void main(String[]args) throws Exception {
    //接收頁面提交引數
    String brandName="香飄飄";
    String company="香飄飄";
    int ordered=1000;
    int status=1;
    String description="繞地球三圈";
    int id=4;


    //1、註冊驅動
    Class.forName("com.mysql.cj.jdbc.Driver");

    //2、獲取連結
    String username="root";
    String url="jdbc:mysql://localhost:3306/data?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8";
    String password="Lhw123456";
    Connection conn= DriverManager.getConnection(url,username,password);

    //3、定義sql
    String sql="UPDATE tb_brand\n" +
            "  set brand_name=?,\n" +
            "  company=       ?,\n" +
            "  ordered=       ?,\n" +
            "  status=        ?,\n" +
            "  description=   ?\n" +
            "  where id=?";

    //4、獲取執行物件
    PreparedStatement pstmt=conn.prepareStatement(sql);

    //5、設定引數
    pstmt.setString(1,brandName);
    pstmt.setString(2,company);
    pstmt.setInt(3,ordered);
    pstmt.setInt(4,status);
    pstmt.setString(5,description);
    pstmt.setInt(6,id);

    //6、執行sql
    int count=pstmt.executeUpdate();//影響行數

    //7、處理結果
    System.out.println(count>0);

    //8、釋放資源
    pstmt.close();
    conn.close();
}

3-4、刪除操作

public static void main(String[]args) throws Exception {
//接收頁面提交引數
int id=4;

    //1、註冊驅動
    Class.forName("com.mysql.cj.jdbc.Driver");

    //2、獲取連結
    String username="root";
    String url="jdbc:mysql://localhost:3306/data?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8";
    String password="Lhw123456";
    Connection conn= DriverManager.getConnection(url,username,password);

    //3、定義sql
    String sql="delete from tb_brand where id=?";

    //4、獲取執行物件
    PreparedStatement pstmt=conn.prepareStatement(sql);

    //5、設定引數
    pstmt.setInt(1,id);

    //6、執行sql
    int count=pstmt.executeUpdate();//影響行數

    //7、處理結果
    System.out.println(count>0);

    //8、釋放資源
    pstmt.close();
    conn.close();
}

相關文章