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