Javaweb数据库通用查询BaseDao

连接数据库工具类：DBUtil.java

package util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/*
 * @param null:
 * jdbc连接工具类
 * @return null
 * @author liu
 * @description TODO
 * @date 2024/5/25 17:05
 */
public class DBUtil {
    private static Connection con;
    private static final String driver;
    private static String url;
    private static String username;
    private static String password;
    static{
        // 读取数据库连接配置文件  反射机制加载类下资源
        InputStream is = DBUtil.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        try {
            // 加载输入流文件内容
            properties.load(is);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password =  properties.getProperty("password");
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }
    // 连接方法
    public static Connection getConnection(){
        try {
            if(con == null || con.isClosed()){
                con =  DriverManager.getConnection(url,username,password);
                System.out.println(con+"数据库连接成功");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return con;
    }


    /**
     * 释放资源
     * @param rs    结果集对象
     * @param pre   预处理对象
     * @param conn  连接数据库对象
     */
    public static void close(ResultSet rs, PreparedStatement pre, Connection conn){
        try {
            if (rs != null){
                rs.close();
            }
            if (pre != null){
                pre.close();
            }
            if (conn != null){
                conn.close();
            }
            System.out.println("资源已全部释放");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

}

通用查询接口：BaseDao.java

package dao;

import pojo.User;

import java.util.List;

// 定义基本的增、删、改、查
public interface BaseDao {
    /*
     * @param String sql,Object args:
     * 执行通用的增、删、改 功能
     * @return row      受影响的行数
     */
    int executeUpdate(String sql, Object... args);

    /*
     * @param String sql:
     *  查询表中所有数据并保存到集合中
     */
    List<User> executeSelect(String sql,Object... args);
}

通用查询接口实现类

package dao.impl;

import dao.BaseDao;
import pojo.User;
import util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

// 定义基本的增、删、改、查
public class BaseDaoImpl implements BaseDao {
    private static Connection con = null;
    private static PreparedStatement ptmt = null;
    private static ResultSet rs = null;

    /*
     * @param String sql,Objectargs:
     * 执行通用的增、删、改 功能
     * @return row      受影响的行数
     * @author liu
     * @description TODO
     * @date 2024/5/25 18:13
     */
    @Override
    public int executeUpdate(String sql, Object... args){
        int row = 0;
        con = DBUtil.getConnection();
        try {
            // 开启事务
            con.setAutoCommit(false);
            // 预编译
            ptmt = con.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ptmt.setObject(i+1,args[i]);
            }
            row = ptmt.executeUpdate();
            if(row > 0){
                // 提交事务
                con.commit();
            }else{
                // 回滚事务
                con.rollback();
            }
            System.out.println("受影响的行数为："+row);
        } catch (SQLException e) {
            // 发生异常，回滚事务
            try {
                con.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        }finally {
            // 释放资源
            DBUtil.close(rs,ptmt,con);
        }
        return row;
    }

    /*
     * @param String sql:
     *  查询表中所有数据并保存到集合中
     * @return List
     * @author liu
     * @description TODO
     * @date 2024/5/25 18:24
     */
    @Override
    public List<User> executeSelect(String sql,Object... args){
        System.out.println("===查询操作===");
        con = DBUtil.getConnection();
        List users = new ArrayList();
        try {
            ptmt = con.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ptmt.setObject(i+1,args[i]);
            }
            rs = ptmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt(1);
                String username = rs.getString(2);
                String password = rs.getString(3);
                User user = new User(id,username,password);
                users.add(user);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            // 释放资源
            DBUtil.close(rs,ptmt,con);
        }
        return users;
    }

}