JDBC案例

将来如果完成的话

就代表对JDBC里面的知识点全部融会贯通了

其实就是对数据的增删改查

我们入门做不出来前端的内容

很正常

准备环境

建表

use mybatis;

create table tbl_brand
(
    id int primary key auto_increment,

    brand_name varchar(20),

    company_name varchar(20),

    ordered int ,

    description varchar(100),

    staus int

);

insert into tbl_brand(brand_name, company_name, ordered, description, staus)
values ('三只松鼠','三只松鼠股份有限公司',5,'好吃不上火',0),
       ('华为','华为技术有限公司',100,'华为致力于把数字世界带给每个人','1'),
       ('小米','小米科技有限公司',50,'are you ok',1);

SELECT * from tbl_brand;

创建实体类

封装实体类

package pojo;

public class Brand {
    private Integer id;
    private String brandName;
    private String companyName;
    private String ordered;
    private String description;
    private String status;

    public Brand() {
    }

    public Brand(Integer id, String brandName, String companyName, String ordered, String description, String status) {
        this.id = id;
        this.brandName = brandName;
        this.companyName = companyName;
        this.ordered = ordered;
        this.description = description;
        this.status = status;
    }

    /**
     * 获取
     * @return id
     */
    public Integer getId() {
        return id;
    }

    /**
     * 设置
     * @param id
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * 获取
     * @return brandName
     */
    public String getBrandName() {
        return brandName;
    }

    /**
     * 设置
     * @param brandName
     */
    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }

    /**
     * 获取
     * @return companyName
     */
    public String getCompanyName() {
        return companyName;
    }

    /**
     * 设置
     * @param companyName
     */
    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    /**
     * 获取
     * @return ordered
     */
    public String getOrdered() {
        return ordered;
    }

    /**
     * 设置
     * @param ordered
     */
    public void setOrdered(String ordered) {
        this.ordered = ordered;
    }

    /**
     * 获取
     * @return description
     */
    public String getDescription() {
        return description;
    }

    /**
     * 设置
     * @param description
     */
    public void setDescription(String description) {
        this.description = description;
    }

    /**
     * 获取
     * @return status
     */
    public String getStatus() {
        return status;
    }

    /**
     * 设置
     * @param status
     */
    public void setStatus(String status) {
        this.status = status;
    }

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

在实体类当中

基本数据类型建议使用对应的包装类

写测试用例

我们的测试类是放在example包下的

我们要做的是查询 添加 修改 删除 这四个功能

我们在之前学习到的

我们的JDBC写代码 都是7步

我们首先得写一个配置类

目的是获取数据库的连接

package config;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;

public class GetConnection {
    public static Connection getConnection() throws Exception {

        //加载配置文件
        Properties prop=new Properties();
        prop.load(new FileInputStream("JDBC/druid.properties"));

        //获取连接池对象
        DataSource dataSource=DruidDataSourceFactory.createDataSource(prop);

        //获取数据库连接 Connection
        Connection connection=dataSource.getConnection();

        return connection;

    }
}

但是由于路径的缘故

我们把他写在测试里面

我们首先思考如何书写SQL;语句

然后获取连接

然后执行就行了

package example;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import org.junit.Test;
import pojo.Brand;

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

//品牌数据的增删改查
public class BrandTest {

    @Test
    public void testSelectAll() throws Exception {

        System.out.println(System.getProperty("user.dir"));

        //获取连接
        Properties prop=new Properties();
        prop.load(new FileInputStream("src\\druid.properties"));

        //获取连接池对象
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);

        //获取数据库连接 Connection
        Connection connection=dataSource.getConnection();

        //定义sql语句
        String sql = "select * from tbl_brand";

        //获取PreparedStatement对象
        PreparedStatement pstmt = connection.prepareStatement(sql);

        //设置参数

        //执行sql
        ResultSet rs = pstmt.executeQuery();

        //处理结果 List<Brand> 封装brand对象 装载List集合
        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 stauts = rs.getInt("status");
            //封装Brand对象
            Brand brand=new Brand(id,brandName,companyName,ordered,description,stauts);
            //装载集合
            brands.add(brand);
        }

        //打印集合
        for (Brand brand : brands) {
            System.out.println(brand);
        }

        //释放资源
        rs.close();
        pstmt.close();
        connection.close();

    }

}

测试通过

添加数据

我们查看一下页面原型

我们代码接收一个insert语句

插入数据就行

我们要插入除了id外的所有数据

id为递增且唯一

我们还是用PreparedStatement对象接收

我们以后在开发JDBC程序的时候

要注意三个事情

一个是SQL语句怎么书写

然后是要填入的参数

然后是返回值

例如这边的添加操作

package example;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import org.junit.Test;
import pojo.Brand;

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

//增 添加数据
public class TestAdd {

    @Test
    public void testSelectAll() throws Exception {

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

        //insert into tbl_brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?);
        //参数 需要 除了id之外的所有参数信息
        //返回值 boolean

        System.out.println(System.getProperty("user.dir"));

        //获取连接
        Properties prop=new Properties();
        prop.load(new FileInputStream("src\\druid.properties"));

        //获取连接池对象
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);

        //获取数据库连接 Connection
        Connection connection=dataSource.getConnection();

        //定义sql语句
        String sql = "insert into tbl_brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?);";

        //获取PreparedStatement对象
        PreparedStatement pstmt = connection.prepareStatement(sql);

        //设置参数
        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();  //影响的行数

        //处理结果
        System.out.println(count>0);

        //释放资源
        pstmt.close();
        connection.close();

    }

}

添加成功

修改数据

我们只是换了个SQL数据模型

和参数

即可

代码

package example;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import org.junit.Test;
import pojo.Brand;

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

//改 修改数据
public class TestUpdate {

    @Test
    public void testUpdate() throws Exception {

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

        //update tbl_brand set brand_name = ?,company_name=?,ordered=?,description=?,status=? where id =? ;
        //参数 需要 所有参数信息
        //返回值 boolean

        System.out.println(System.getProperty("user.dir"));

        //获取连接
        Properties prop=new Properties();
        prop.load(new FileInputStream("src\\druid.properties"));

        //获取连接池对象
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);

        //获取数据库连接 Connection
        Connection connection=dataSource.getConnection();

        //定义sql语句
        String sql = "update tbl_brand set brand_name = ?,company_name=?,ordered=?,description=?,status=? where id =? ;";

        //获取PreparedStatement对象
        PreparedStatement pstmt = connection.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();  //影响的行数

        //处理结果
        System.out.println(count>0);

        //释放资源
        pstmt.close();
        connection.close();

    }

}

执行成功

瞅一眼数据库

成功修改

删除数据

不解释了

直接灵魂三问

改一下sql语句和获取PreparedStatement对象的参数即可

执行代码

package example;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import org.junit.Test;
import pojo.Brand;

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

//增 添加数据
public class TestDelete {

    @Test
    public void testDelete() throws Exception {

        //模拟接收页面提交的数据
        int id=1;

        //delete from tbl_brand where id= ?
        //参数 需要id
        //返回值 boolean

        System.out.println(System.getProperty("user.dir"));

        //获取连接
        Properties prop=new Properties();
        prop.load(new FileInputStream("src\\druid.properties"));

        //获取连接池对象
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);

        //获取数据库连接 Connection
        Connection connection=dataSource.getConnection();

        //定义sql语句
        String sql = "delete from tbl_brand where id= ?";

        //获取PreparedStatement对象
        PreparedStatement pstmt = connection.prepareStatement(sql);

        //设置参数
        pstmt.setInt(1,id);

        //执行sql
        int count=pstmt.executeUpdate();  //影响的行数

        //处理结果
        System.out.println(count>0);

        //释放资源
        pstmt.close();
        connection.close();

    }

}

删除成功

数据库中的数据成功删除

个人号推广

博客主页

多多!-CSDN博客

Web后端开发

https://blog.csdn.net/qq_30500575/category_12624592.html?spm=1001.2014.3001.5482

Web前端开发

https://blog.csdn.net/qq_30500575/category_12642989.html?spm=1001.2014.3001.5482

数据库开发

https://blog.csdn.net/qq_30500575/category_12651993.html?spm=1001.2014.3001.5482

项目实战

https://blog.csdn.net/qq_30500575/category_12699801.html?spm=1001.2014.3001.5482

算法与数据结构

https://blog.csdn.net/qq_30500575/category_12630954.html?spm=1001.2014.3001.5482

计算机基础

https://blog.csdn.net/qq_30500575/category_12701605.html?spm=1001.2014.3001.5482

回忆录

https://blog.csdn.net/qq_30500575/category_12620276.html?spm=1001.2014.3001.5482