连接数据库并实现增、删、改、查

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

posted @   呓语-MSHK  阅读(10)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示