使用jdbc实现crud实战

使用jdbc执行sql增、删、改、查语句是最基础的操作，话不多说直接上代码：

添加pom依赖：

<!--数据库相关的依赖-->
<!--MyBatis-Plus的依赖-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.0</version>
</dependency>
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.4.0</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.11</version>
</dependency>
<!-- MySQL 连接驱动依赖 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.39</version>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13</version>
    <scope>compile</scope>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

实体类

/**
 * 品牌表
 * */
@Data
@ToString
@Builder
@NoArgsConstructor
@AllArgsConstructor
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 class DBUtils {
    /**
     * 获取数据连接
     * 从配置文件读取数据库连接信息
     * @author kaikaiking
     * */
    public static Connection getConnection() throws Exception {
        //获取定义在src/main/resources文件夹中的配置文件
        ClassPathResource resource = new ClassPathResource("druid.properties");
        //加载配置文件
        Properties prop = new Properties();
        prop.load(resource.getStream());
        //获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //获取数据库连接 Connection
        return dataSource.getConnection();
    }

    /**
     * 关闭连接资源
     *
     * @param rs
     * @param state
     * @param conn
     * @author kaikaiking
     */
    public static void close(ResultSet rs, Statement state, Connection conn) throws Exception {
        if (Objects.nonNull(rs)) {
            rs.close();
        }
        if (Objects.nonNull(state)) {
            state.close();
        }
        if (Objects.nonNull(conn)) {
            conn.close();
        }
    }

}

数据库连接信息配置

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db1?useSSL=false&useServerPrepStmts=true&characterEncoding=utf8
username=root
password=
initialSize=5
maxActive=10
maxWait=3000

增删查改代码实现

package com.lzj.demo.jdbc;


import org.junit.Test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class CrudByJdbc {
    @Test
    public void test()throws Exception {
        //新增
        doInsert();
        //查询
        doQuery();
        //修改
        doUpdate();
        //删除
        doDelete();
    }

    /***
     * 插入
     */
    public void doInsert()throws Exception{
        //模拟接收页面提交的参数
        int id = 1;
        String brandName = "王者荣耀";
        String companyName = "腾讯天美游戏工作室";
        String description = "Moba类推塔游戏";
        int ordered = 1;
        int status = 1;

        //定义insert sql语句
        String insertSql = "insert into tb_brand(id,brand_name,company_name,description,ordered,status)"
                +" values('"+ id + "','"
                + brandName + "','" +companyName + "','" + description + "','" + ordered + "','" + status +"')";
        System.out.println("insertSql: "+insertSql);
        Connection conn = DBUtils.getConnection();
        Statement statement = conn.createStatement();
        //执行SQL,并返回被影响的行数
        int count = statement.executeUpdate(insertSql);
        if(count > 0){
            System.out.println("操作结果:成功新增 "+count+" 行数据");
        }
        DBUtils.close(null,statement,conn);
    }

    /***
     * 查询
     */
    public void doQuery()throws Exception{
        //定义select sql语句
        String selectSql = "select * from tb_brand;";
        //获取Statement对象
        Connection conn = DBUtils.getConnection();
        Statement statement = conn.createStatement();
        //设置参数,执行SQL
        ResultSet rs = statement.executeQuery(selectSql);
        //装载brand对象
        List<Brand> list = 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 brand = Brand.builder()
                    .id(id)
                    .brandName(brandName)
                    .companyName(companyName)
                    .ordered(ordered)
                    .description(description)
                    .status(status)
                    .build();
            list.add(brand);
        }
        //在控制台打印结果
        System.out.println("查询结果集:"+list);
        DBUtils.close(rs,statement,conn);
    }

    /***
     * 修改
     */
    public void doUpdate()throws Exception{
        //定义update sql语句
        String updateSql = "update tb_brand set ordered = 2 where id = 1 ";
        //获取执行sql的对象Statement
         Connection conn = DBUtils.getConnection();
        Statement statement = conn.createStatement();
        //执行sql并返回受影响行数
        int count = statement.executeUpdate(updateSql);
        if(count > 0){
            System.out.println("操作结果:成功修改 "+count+" 行数据");
        }
        DBUtils.close(null,statement,conn);
    }

    /***
     * 删除
     */
    public void doDelete()throws Exception{
        //定义 delete sql语句
        String deleteSql = "delete from tb_brand where id = 1";
        Connection conn = DBUtils.getConnection();
        Statement statement = conn.createStatement();
        int count = statement.executeUpdate(deleteSql);
        if(count > 0){
            System.out.println("操作结果:成功删除 "+count+" 行数据");
        }
        DBUtils.close(null,statement,conn);
    }
}