Java封装JDBC工具类utils

1.JDBC封装代码

package com.mallprogram.utils;



import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * @Author: W
 * @Date: 2023-10-17 11:32
 */
public class JDBCPlus {

    private static Connection conn;
    private static DataSource ds;
    private static Properties prop;

    //静态代码块,随着类的加载而加载，并且只加载一次
    static {
        try {
            prop=new Properties();
            FileInputStream fis=new FileInputStream("D:\\druid.properties");
            prop.load(fis);
             ds= DruidDataSourceFactory.createDataSource(prop);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection(){
        //创建连接
        try {
            conn =  ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static int execute(String sql,Object...params){
        Connection conn=getConnection();
        PreparedStatement pst=null;
        int result=0;
        try {
            //创建了一个预编译语句对象；pst
            pst= conn.prepareStatement(sql);
            //给占位符赋值;
            if(params!=null){
                for (int i = 0; i < params.length; i++) {
                    //将数组对应的元素，赋值给？占位符;
                    pst.setObject(i+1,params[i]);
                }
            }
            //受影响的行数：result
            result = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(null,pst,conn);
        }
        return result;
    }


    public static ResultSet executeQuery(String sql,Object...params){
        Connection conn=getConnection();
        PreparedStatement pst=null;
        ResultSet result=null;
        try {
            //创建了一个预编译语句对象；pst
            pst= conn.prepareStatement(sql);
            //给占位符赋值;
            if(params!=null){
                for (int i = 0; i < params.length; i++) {
                    //将数组对应的元素，赋值给？占位符;
                    pst.setObject(i+1,params[i]);
                }
            }
            //受影响的行数：result
            result = pst.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            // close(result,pst,conn);
        }
        return result;
    }
    //关闭方法
    public static void close(ResultSet rs , Statement statm, Connection conn){
        try {
            if(rs != null){
                rs.close();
                rs =null;//关闭连接，给赋值null,gc垃圾回收回机制会优先处理这些对象
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(statm != null){
                statm.close();
                statm=null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn != null){
                conn.close();
                conn = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2.数据库连接配置文件 druid.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mall
username=root
password=root
initialSize=5
maxActive=50
maxWait=3000

3.使用工具类

package A商城项目.mallprogram.service;

import A商城项目.mallprogram.entity.Good;
import A商城项目.mallprogram.utils.JDBCPlus;


import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * @Author: W
 * @Date: 2023-09-21 15:45
 */
/*商品库存管理操作*/
public class MallStore{
    static Scanner sc=new Scanner(System.in);
    //添加
    public static  void addGood() throws Exception {
        System.out.println("请输入id");
        int id=sc.nextInt();
        System.out.println("请输入商品名");
        String name = sc.next();
        System.out.println("请输入价格");
        double price= sc.nextDouble();
        System.out.println("请输入数量");
        int num= sc.nextInt();
        Object[]prams={id,name,price,num};
        int rs = JDBCPlus.execute("insert goodstore(id,name,price,num) values(?,?,?,?)",prams);
        if(rs>0){
            System.out.println("成功");
        }else {
            System.out.println("失败");
        }
    }
    //删除
    public static void deleteGood() throws Exception {
        System.out.println("请输入删除的商品id");
        int id=sc.nextInt();
        Object[]prams={id};
        int rs = JDBCPlus.execute("delete from goodstore where id=?",prams);
        if(rs>0){
            System.out.println("成功");
        }else {
            System.out.println("失败");
        }
    }
    //修改
    public static void updateGood() throws Exception {
        System.out.println("请输入想要修改的商品id");
        int id=sc.nextInt();
        System.out.println("请输入商品名");
        String name = sc.next();
        System.out.println("请输入价格");
        double price= sc.nextDouble();
        System.out.println("请输入数量");
        int num= sc.nextInt();
        Object[]prams={name,price,num,id};
        int rs = JDBCPlus.execute("update goodstore set name=?,price=?,num=? where id=?",prams);
        if(rs>0){
            System.out.println("成功");
        }else {
            System.out.println("失败");
        }
    }
    public static void updateGoodNum(int num,int id) throws Exception {
        Object[]prams={num,id};
        int rs = JDBCPlus.execute("update goodstore set num=? where id=?",prams);
        if(rs>0){
            System.out.println("成功");
        }else {
            System.out.println("失败");
        }
    }
    //查询
    public static List<Good> selectGood() throws Exception {
        List<Good> goodstore=new ArrayList<>();
        ResultSet rs = JDBCPlus.executeQuery("select * from goodstore");
        while (rs.next()){
            Good good=new Good();
            good.id=rs.getInt(1);
            good.name=rs.getString(2);
            good.price=rs.getDouble(3);
            good.num=rs.getInt(4);
            goodstore.add(good);
        }
        return goodstore;
    }
    public static Good selectGoodByname(String name) throws Exception {
        Object[] prams={name};
        ResultSet rs = JDBCPlus.executeQuery("select * from goodstore where name=?",prams);
        Good good=new Good();
        while (rs.next()){
            good.id=rs.getInt(1);
            good.name=rs.getString(2);
            good.price=rs.getDouble(3);
            good.num=rs.getInt(4);
        }
        return good;
    }
}