1.20 JDBC（面向接口编程）

1.Java代码操作数据库
步骤

1. 创建工程，导入驱动jar包
   2.注册驱动
   Class.forName("com.mysql.jdbc.Driver");
   3.获取连接
   Connection conn = DriverManager.getConnection(url, username, password);
   4.定义SQL语句
   String sql = “update…” ;
   5.获取执行SQL对象
   Statement stmt = conn.createStatement();
   6.执行SQL
   stmt.executeUpdate(sql);  

具体操作

点击查看代码

public class JDBCDemo {
    public static void main(String[] args) throws
Exception {
        //1. 注册驱动
        //Class.forName("com.mysql.jdbc.Driver");
        //2. 获取连接
        String url =
"jdbc:mysql://localhost:3306/db1";
        String username = "root";
        String password = "1234";
        Connection conn =
DriverManager.getConnection(url, username,
password);
        //3. 定义sql
        String sql = "update account set money =
2000 where id = 1";
        //4. 获取执行sql的对象 Statement
        Statement stmt = conn.createStatement();
        //5. 执行sql
 int count = stmt.executeUpdate(sql);//受影响
的行数
        //6. 处理结果
        System.out.println(count);
        //7. 释放资源
        stmt.close();
        conn.close();
   }
}

练习
完成商品品牌数据的增删改查操作

• 查询：查询所有数据
• 添加：添加品牌
• 修改：根据id修改
• 删除：根据id删除
  1.环境准备

数据库表 tb_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(20),
    -- 企业名称
   company_name varchar(20),
    -- 排序字段
   ordered int,
    -- 描述信息
   description varchar(100),
    -- 状态：0：禁用 1：启用
   status int
);
-- 添加数据
insert into tb_brand (brand_name, company_name,
ordered, description, status)
values ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上
火', 0),
       ('华为', '华为技术有限公司', 100, '华为致力于把
数字世界带入每个人、每个家庭、每个组织，构建万物互联的智能世
界', 1),
       ('小米', '小米科技有限公司', 50, 'are you ok',
1);

在pojo包下实体类 Brand

点击查看代码

package com.itheima.pojo;

public class Brand {
 //id 主键
  private Integer  id              ;
 // 品牌名称
  private String  brandName      ;
 // 企业名称
  private String  companyName    ;
 // 排序字段
  private Integer  ordered         ;
         // 描述信息
  private String  description     ;
 // 状态：0：禁用  1：启用
  private Integer  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 Integer getOrdered() {
  return ordered;
 }

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

 public String getDescription() {
  return description;
 }

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

 public Integer getStatus() {
  return status;
 }

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

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

1.查询所有

点击查看代码

@Test
    public void testSelectAll() throws Exception {
        //获取connection
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource dateSource =DruidDataSourceFactory.createDataSource(prop);
        Connection conn = dateSource.getConnection();

        //定义SQL
        String sql = "select * from tb_brand";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //设置参数
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        //处理结果，封装为brand，再装载集合
        Brand brand = null;
        List<Brand> brands = new ArrayList<>();
        while (rs.next()) {
            //获取数据
            int id = rs.getInt("id");
            String brandName = rs.getString("brand_name");
            String companyName = rs.getString("company_name");
            int ordered = rs.getInt("ordered");
            String description = rs.getString("description");
            int status = rs.getInt("status");

            //封装brand
            brand = new Brand();
            brand.setId(id);
            brand.setBrandName(brandName);
            brand.setCompanyName(companyName);
            brand.setOrdered(ordered);
            brand.setDescription(description);
            brand.setStatus(status);
            //装载集合
            brands.add(brand);
        }
        System.out.println(brands);
        //释放资源
        rs.close();
        pstmt.close();
        conn.close();
    }

2.根据页面接收内容添加

点击查看代码

 @Test
    public void testAdd() throws Exception {

        //接收页面提交的数据
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 1;
        String description = "绕地球一圈";
        int status = 1;

        //获取connection
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource dateSource = DruidDataSourceFactory.createDataSource(prop);
        Connection conn = dateSource.getConnection();

        //定义SQL
        String sql = "insert into tb_brand(brand_name,company_name, ordered, description, status) values(?,?,?,?,?);";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //设置参数,除了ID
        pstmt.setString(1, brandName);
        pstmt.setString(2, companyName);
        pstmt.setInt(3, ordered);
        pstmt.setString(4, description);
        pstmt.setInt(5, status);
        //执行sql
        int count = pstmt.executeUpdate();
        //处理结果,返回bool类型
        System.out.println(count > 0);
        //释放资源
        pstmt.close();
        conn.close();
    }

3.根据ID进行修改

点击查看代码

@Test
    public void testUpdate() throws Exception {

        //接收页面提交的数据
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 1;
        String description = "绕地球三圈";
        int status = 1;
        int id = 4;
        //获取connection
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource dateSource = DruidDataSourceFactory.createDataSource(prop);
        Connection conn = dateSource.getConnection();

        //定义SQL
        String sql = "update tb_brand set brand_name=?,company_name=?,ordered=?,description=?,status=? where id=?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //设置参数
        pstmt.setString(1, brandName);
        pstmt.setString(2, companyName);
        pstmt.setInt(3, ordered);
        pstmt.setString(4, description);
        pstmt.setInt(5, status);
        pstmt.setInt(6, id);
        //执行sql
        int count = pstmt.executeUpdate();
        //处理结果,返回bool类型
        System.out.println(count > 0);
        //释放资源
        pstmt.close();
        conn.close();
    }

4.根据ID进行删除

点击查看代码

@Test
    public void testDelete() throws Exception {

        //接收页面提交的数据

        int id = 4;
        //获取connection
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource dateSource = DruidDataSourceFactory.createDataSource(prop);
        Connection conn = dateSource.getConnection();

        //定义SQL
        String sql = "delete from tb_brand  where id=?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //设置参数

        pstmt.setInt(1, id);
        //执行sql
        int count = pstmt.executeUpdate();
        //处理结果,返回bool类型
        System.out.println(count > 0);
        //释放资源
        pstmt.close();
        conn.close();
    }