[Java知识库]Java从零码起11-通过JDBC实现数据库的增删改查操作

需求：通过jdbc完成品牌数据库的增删改查操作

1.在数据库中添加一张Brand表，保存品牌的信息

-- 删除tb_brand表 如果存在
DROP TABLE IF EXISTS tb_brand;
-- 创建tb_brand表
CREATE TABLE tb_brand(
-- id主键
id INT PRIMARY KEY auto_increment,
-- 品牌名称
brand_name VARCHAR(32),
-- 企业名称
company_name VARCHAR(32),
-- 排序字段
ordered INT,
-- 描述信息
decription VARCHAR(64),
--  状态 ：0-禁用 1-启用
statu tinyint
);

-- 添加数据
INSERT INTO tb_brand (brand_name,company_name,ordered,decription,status) 
VALUES('三只松鼠','三只松鼠有限公司',5,'好吃不上火',0),
	  ('华为','华为技术有限公司',100,'华为致力于把数字世界带入每个人、每个家庭、每个组织，构建万物互联的智能世界',1),
	  ('小米','小米科技有限公司',100,'are you ok',1);

-- 查询表的数据
SELECT * FROM tb_brand;

2.创建Brand类，用于获取并创建品牌对象

/*
品牌信息
在类中建议使用包装类型，默认值为null
 */
public class Brand {
    private Integer id;
    private String brandName;
    private String companyName;
    private String description;
    private Integer ordered;
    private Integer status;
    public Brand(){}
    public Brand(Integer id, String brandName, String companyName, String description, Integer ordered, Integer status) {
        this.id = id;
        this.brandName = brandName;
        this.companyName = companyName;
        this.description = description;
        this.ordered = ordered;
        this.status = status;
    }

    public Integer getId() {
        return id;
    }

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

    public String getBrandName() {
        return brandName;
    }

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

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public String getDescription() {
        return description;
    }

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

    public Integer getOrdered() {
        return ordered;
    }

    public void setOrdered(Integer ordered) {
        this.ordered = ordered;
    }

    public Integer getStatus() {
        return status;
    }

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

    @Override
    public String toString() {
        return "id:" + id + ", 品牌名称:" + brandName + ", 企业全称:" + companyName + ", 企业描述:" + description + ", 企业地位:" + ordered + ", 经营状态:" + status+'\n';
    }
}

3.创建Demo类，实现对brand表增删改查的操作

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.Scanner;

/*
练习：完成品牌数据的增删改查操作
 */
public class Demo {
    public static void main(String[] args) throws Exception {
//        连接数据库
        String url="jdbc:mysql:///panda?useSSL=false&useServerPrepStmts=true";
        String user="root";
        String password="1234";
        Connection conn = DriverManager.getConnection(url, user, password);
//        执行sql
        Scanner sc = new Scanner(System.in);
        boolean flog=true;
        while (flog) {
            System.out.println("1.查询数据库所有数据,2.添加一条数据,3.修改一条数据,4.删除一条数据,0.退出");
            switch (sc.nextInt()) {
                case 1:
                    selectAll(conn);
                    break;
                case 2:
                    addBrand(conn);
                    break;
                case 3:
                    modify(conn);
                    break;
                case 4:
                    delete(conn);
                    break;
                case 0:
                    flog=false;
                    break;
                default:
                    System.out.println("error");
            }
        }
//        释放资源
        conn.close();
    }

/*
查询所有数据：
1.sql:select * from tb_brand
2.参数：null
3.结果：List<Brand>
*/
    private static void selectAll(Connection conn) throws SQLException {
        //定义sql
        String sql = "select * from tb_brand";
        //创建PreparedStatement对象
        PreparedStatement ps = conn.prepareStatement(sql);
        //设置参数

        //执行sql
        ResultSet res = ps.executeQuery();
        //处理结果
        List<Brand> brands = new ArrayList<>();
        while (res.next()) {
            //获取结果
            int id = res.getInt("id");
            String brandName = res.getString("brand_name");
            String companyName = res.getString("company_name");
            int ordered = res.getInt("ordered");
            String description = res.getString("description");
            int status = res.getInt("status");
            //封装Brand对象
            Brand brand = new Brand(id, brandName, companyName, description, ordered, status);
            //写入集合
            brands.add(brand);
        }
        //输出集合
        System.out.println(brands);
        //释放资源
        ps.close();
    }

/*
增加一条数据
sql:insert into brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?);
参数:需要除了id以外的所有信息
结果:boolean
     */
    private static void addBrand(Connection conn) throws SQLException {
        //定义sql语句
        String sql = "insert into tb_brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?)";
        //创建PreparedStatement对象
        PreparedStatement ps = conn.prepareStatement(sql);
        //设置参数,从键盘获取
        Scanner sc = new Scanner(System.in);
        System.out.println("输入品牌名称:");
        ps.setString(1, sc.next());
        System.out.println("输入企业全称:");
        ps.setString(2, sc.next());
        System.out.println("输入企业排序:");
        ps.setInt(3, sc.nextInt());
        System.out.println("输入企业描述:");
        ps.setString(4, sc.next());
        System.out.println("输入企业状态:");
        ps.setInt(5, sc.nextInt());
        //执行sql
        int count = ps.executeUpdate();
        //输出结果
        System.out.println(count > 0);
        //释放资源
        ps.close();
    }

/*
修改一条数据
sql:update tb_brand set brand_name=? ,company_name=?,ordered=? ,description=? ,status=? where id=? ;
参数:所有数据
返回值:boolean
*/
    public static void modify(Connection conn) throws SQLException {
        //定义sql语句
        String sql = "update tb_brand set brand_name=? ,company_name=?,ordered=? ,description=? ,status=? where id=?";
        //创建PreparedStatement
        PreparedStatement ps = conn.prepareStatement(sql);
        //设置参数,键盘输入
        Scanner sc = new Scanner(System.in);
        System.out.println("输入要修改的ID:");
        ps.setInt(6, sc.nextInt());
        System.out.println("输入品牌名称:");
        ps.setString(1, sc.next());
        System.out.println("输入企业全称:");
        ps.setString(2, sc.next());
        System.out.println("输入企业排序:");
        ps.setInt(3, sc.nextInt());
        System.out.println("输入企业描述:");
        ps.setString(4, sc.next());
        System.out.println("输入企业状态:");
        ps.setInt(5, sc.nextInt());
        //执行sql
        int count = ps.executeUpdate();
        System.out.println(count > 0);
        //释放资源
        ps.close();
    }

/*
删除一条数据
sql:delete from tb_brand where id=?
参数:id
结果:boolean
*/
    public static void delete(Connection conn) throws SQLException {
        //定义sql
        String sql = "delete from tb_brand where id= ?";
        //创建PreparedStatement
        PreparedStatement ps = conn.prepareStatement(sql);
        //设置参数
        Scanner sc = new Scanner(System.in);
        System.out.println("输入要删除的id:");
        ps.setInt(1, sc.nextInt());
        //执行sql
        int count = ps.executeUpdate();
        //处理结果
        System.out.println(count > 0);
        //释放资源
        ps.close();
    }
}