JDBC-Java封装实现增删查改

  1. 创建表结构,包含编号,名称,简介,作者信息,价格,库存
  2. 完成增加图书操作,部门信息从控制台输入.
  3. 完成删除图书操作,部门信息从控制台输入。
  4. 完成查询操作,根据图书编号查询信息并输出。

一、数据库表book

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '编号',
  `book_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '书籍编号',
  `book_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '书名',
  `book_introduction` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '简介',
  `book_author` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '作者',
  `book_price` decimal(50, 2) NULL DEFAULT NULL COMMENT '价格',
  `book_stock` int NULL DEFAULT NULL COMMENT '库存',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

Diagram 1.png

二、BookEntity实体类

package com.singerw.book.entity;

/**
 * @Author: CodeSleep
 * @Date: 2021-06-09 21:58
 * @Description: //TODO Book实体类 BookEntity
 */
public class BookEntity {
    private int book_id;
    private String book_name;
    private String book_introduction;
    private String book_author;
    private Double book_price;
    private int book_stock;

    public int getBook_id() {
        return book_id;
    }

    public void setBook_id(int book_id) {
        this.book_id = book_id;
    }

    public String getBook_name() {
        return book_name;
    }

    public void setBook_name(String book_name) {
        this.book_name = book_name;
    }

    public String getBook_introduction() {
        return book_introduction;
    }

    public void setBook_introduction(String book_introduction) {
        this.book_introduction = book_introduction;
    }

    public String getBook_author() {
        return book_author;
    }

    public void setBook_author(String book_author) {
        this.book_author = book_author;
    }

    public Double getBook_price() {
        return book_price;
    }

    public void setBook_price(Double book_price) {
        this.book_price = book_price;
    }

    public int getBook_stock() {
        return book_stock;
    }

    public void setBook_stock(int book_stock) {
        this.book_stock = book_stock;
    }

    public BookEntity() {
    }

    public BookEntity(int book_id, String book_name, String book_introduction, String book_author, Double book_price, int book_stock) {
        this.book_id = book_id;
        this.book_name = book_name;
        this.book_introduction = book_introduction;
        this.book_author = book_author;
        this.book_price = book_price;
        this.book_stock = book_stock;
    }

    @Override
    public String toString() {
        return "BookEntity{" +
                "book_id=" + book_id +
                ", book_name='" + book_name + '\'' +
                ", book_introduction='" + book_introduction + '\'' +
                ", book_author='" + book_author + '\'' +
                ", book_price=" + book_price +
                ", book_stock=" + book_stock +
                '}';
    }
}

三、工具类

JdbcDriver

package com.singerw.book.util;

import java.sql.*;

/**
 * @Author: CodeSleep
 * @Date: 2021-06-09 21:46
 * @Description: //TODO JdbcUtil工具类
 */
public  class JdbcDriver {

    /**
     * @return Connection对象
     * @throws ClassNotFoundException
     * @throws SQLException
     * @Author CodeSleep
     * @Date: 2021-06-09 21:52
     * @Description: //TODO 获取连接对象的方法
     */
     final Connection jdbcDriver() throws ClassNotFoundException, SQLException {
        // 1 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 2 获取链接
        String url = "jdbc:mysql://localhost:3306/singerw?serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "795200";
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }


    /**
     * @param rs    结果集ResultSet对象
     * @param pstmt 预处理对象 PreparedStatement
     * @param conn  连接对象 Connection
     * @Author CodeSleep
     * @Date: 2021-06-09 21:54
     * @Description: //TODO 方法描述
     */
    final void closeAll(ResultSet rs, PreparedStatement pstmt, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

ExUpdate

package com.singerw.book.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @Author: CodeSleep
 * @Date: 2021-06-09 21:45
 * @Description: //TODO 通用的增加 删除 和 修改类
 */
public class ExUpdate {

    /**
     * @Author CodeSleep
     * @Date: 2021-06-09 22:13
     * @Description: //TODO 通用的增加 删除和修改方法
     * @param sql
     * @param params
     * @return
     */
    public int exUpdate(String sql, Object...params){
        JdbcDriver driver = new JdbcDriver();
        Connection conn = null;
        PreparedStatement pstmt = null;
        int n = 0;

        try {
            // 获取连接对象
            conn = driver.jdbcDriver();
            // 创建pstmt对象
            pstmt = conn.prepareStatement(sql);
            // params当成数组来处理
            if (params !=null){
                for (int i = 0; i < params.length; i++) {
                    // 设置占位符对应参数值
                    pstmt.setObject(i+1,params[i]);
                }
            }
            // 执行增删改方法
            n = pstmt.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            driver.closeAll(null,pstmt,conn);
        }
        // 返回受影响的行
        return n;
    }
}

ExQuery

package com.singerw.book.util;

import org.apache.commons.beanutils.BeanUtils;

import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: CodeSleep
 * @Date: 2021-06-09 21:45
 * @Description: //TODO 通用查询
 */
public class ExQuery {

    /**
     * @param sql
     * @param cls
     * @param params
     * @return
     * @Author CodeSleep
     * @Date: 2021-06-09 23:04
     * @Description: //TODO 通用查询方法
     */
    public Object exQuery(String sql, Class cls, Object... params) throws IllegalAccessException, InvocationTargetException, InstantiationException {
        JdbcDriver driver = new JdbcDriver();
        // List就是存放查询到的结果集
        List list = new ArrayList();

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            // 获取连接对象
            conn = driver.jdbcDriver();
            // 创建pstmt对象
            pstmt = conn.prepareStatement(sql);
            // parmas当成数组来处理
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    // 设置占位符对应参数值
                    pstmt.setObject(i + 1, params[i]);
                }
            }
            // 执行查询方法
            rs = pstmt.executeQuery();
            // 遍历
            while (rs.next()) {
                // 得到一行数据就调用convert方法将列的数据填充到一个对象中,然后返回
                Object obj = convert(cls, rs);
                list.add(obj);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            driver.closeAll(rs,pstmt,conn);
        }
        // 返回结果
        return list;
    }

    /**
     * @param cls Classs
     * @param rs  结果集
     * @return Object
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws SQLException
     * @throws InvocationTargetException
     * @Author CodeSleep
     * @Date: 2021-06-09 23:04
     * @Description: //TODO 结果集转换为Class对象
     */
    private final Object convert(Class cls, ResultSet rs)
            throws InstantiationException, IllegalAccessException, SQLException, InvocationTargetException {
        Object obj = cls.newInstance();
        // 检索此 ResultSet对象的列的数量,类型和属性。
        ResultSetMetaData rsmd = rs.getMetaData();
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
            String cname = rsmd.getColumnLabel(i);
            // 开源组件:beanutil
            // 1-》对象名
            // 2->属性名
            // 3->属性的值
            BeanUtils.setProperty(obj, cname, rs.getObject(cname));
        }
        return obj;
    }
}

四、测试类

BookInsertTest

package com.singerw.book.test;

import com.singerw.book.util.ExUpdate;

import java.util.Scanner;

/**
 * @Author: CodeSleep
 * @Date: 2021-06-09 21:45
 * @Description: //TODO 完成增加图书操作,部门信息从控制台输入.
 */
public class BookInsertTest {

    public static void main(String[] args) {
        ExUpdate update = new ExUpdate();
        String sql = "insert into book (id,book_id,book_name,book_introduction,book_author,book_price,book_stock)" +
                " value(null,?,?,?,?,?,?)";

        Scanner input = new Scanner(System.in);
        System.out.print("请输入ISBN:");
        int id = input.nextInt();
        System.out.print("请输入书名:");
        String name = input.next();
        System.out.print("请输入简介:");
        String introduction = input.next();
        System.out.print("请输入作者:");
        int author = input.nextInt();
        System.out.print("请输入价格:");
        String price = input.next();
        System.out.print("请输入库存:");
        String stock = input.next();
        input.close();

        // 调用工具类的方法
        int n = update.exUpdate(sql,id,name,introduction,author,price,stock);
        System.out.println(n == 1 ? "操作成功" : "操作失败");
    }
}

BookDeleteTest

package com.singerw.book.test;

import com.singerw.book.util.ExUpdate;

import java.util.Scanner;

/**
 * @Author: CodeSleep
 * @Date: 2021-06-09 21:45
 * @Description: //TODO 完成删除图书操作,部门信息从控制台输入。
 */
public class BookDeleteTest {

    public static void main(String[] args) {

        // 实例化Exupdate对象
        ExUpdate delete = new ExUpdate();

        String sql = "delete from book where book_id = ?";

        Scanner input = new Scanner(System.in);
        System.out.print("请输入要删除书的编号:");
        String id = input.next();
        input.close();

        // 调用工具类的方法
        int n = delete.exUpdate(sql,id);
        System.out.println(n == 1 ? "操作成功" : "操作失败");
    }
}

BookQueryTest

package com.singerw.book.test;

import com.singerw.book.entity.BookEntity;
import com.singerw.book.util.ExQuery;

import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * @Author: CodeSleep
 * @Date: 2021-06-09 23:06
 * @Description: //TODO 完成查询操作,根据图书编号查询信息并输出。
 */
public class BookQueryTest {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        ExQuery query = new ExQuery();
        // 完成查询操作,根据部门编号查询部门信息并输出
        // 操作步骤
        String sql = "select * from book where book_id = ?";
        // 接下来:
        Scanner input = new Scanner(System.in);
        System.out.print("请输入图书ID:");
        int book_id = input.nextInt();
        input.close();

        Object obj = null;
        try {
            obj = query.exQuery(sql, BookEntity.class, book_id);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }
        System.out.println("Book :" + obj);
    }
}
最后修改:2021 年 07 月 28 日 04 : 03 PM
如果觉得我的文章对你有用,请随意赞赏