DBUtil+Druid德鲁伊连接池，实现增删改查

1、引入包，我使用的是Maven导入

数据库用的是Mysql8

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.19</version>
        </dependency>

 <!-- 德鲁伊-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.6</version>
        </dependency>

2、创建配置文件

在resources下面创建配置文件，按照自己实际的配置填写

drivername=com.mysql.cj.jdbc.Driver
url=jdbc:mysql:/localhost:3006/express_station?serverTimezone=UTC
username=root
password=111111

3、创建DBUtil工具类

import com.alibaba.druid.pool.DruidDataSource;

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

/**
 * @author suyiping
 * @create 2021-06-10 21:28
 */
public class DBUtil {
    private static String url;
    private static String username;
    private static String password;
    private static String drivername;

    public Connection conn = null;
    public PreparedStatement state = null;
    public ResultSet rs = null;
    public static DruidDataSource dataSource = new DruidDataSource();

    static {
        ResourceBundle rb = ResourceBundle.getBundle("db");
        url = rb.getString("url");
        username = rb.getString("username");
        password = rb.getString("password");
        drivername = rb.getString("drivername");
        dataSource.setDriverClassName(drivername);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setUrl(url);
    }

    public DBUtil() {

    }

    public Connection getConn() throws SQLException {

        return dataSource.getConnection();
    }

    public void closeAll() {
        try {
            if (conn != null) {
                conn.close();
            }
            if (state != null) {
                state.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public ResultSet executeQuery(String sql, List param) {

        try {
            conn = this.getConn();
            if (conn != null) {
                state = conn.prepareStatement(sql);
            }
            setParam(param);
            if (state != null) {
                rs = state.executeQuery();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    private void setParam(List param) throws SQLException {
        if (param == null) {
            return;
        }
        for (int i = 0; i < param.size(); i++) {
            state.setObject(i + 1, param.get(i));
        }
    }

    public int executeUpdate(String sql, List param) {
        int result = 0;
        try {
            conn = this.getConn();
            if (conn != null) {
                state = conn.prepareStatement(sql);
            }
            setParam(param);
            if (state != null) {
                result = state.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return result;
    }

}

4、创建实体类

package com.syp.flight.bean;

import lombok.Data;

import java.io.Serializable;
import java.util.Date;

/**
 * @author suyiping
 * @create 2021-07-07 20:28
 */
public class Flight implements Serializable {
    //主键
    private Integer id;
    //航班号
    private String flightNo;
    //目的地
    private String destination;
    //起飞时间
    private String startDate;


    @Override
    public String toString() {
        return "Flight{" +
                "id=" + id +
                ", flightNo='" + flightNo + '\'' +
                ", destination='" + destination + '\'' +
                ", startDate=" + startDate +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getFlightNo() {
        return flightNo;
    }

    public void setFlightNo(String flightNo) {
        this.flightNo = flightNo;
    }

    public String getDestination() {
        return destination;
    }

    public void setDestination(String destination) {
        this.destination = destination;
    }

    public String getStartDate() {
        return startDate;
    }

    public void setStartDate(String startDate) {
        this.startDate = startDate;
    }

    public Flight() {
    }

    public Flight(int id, String flightNo, String destination, String startDate) {
        this.id = id;
        this.flightNo = flightNo;
        this.destination = destination;
        this.startDate = startDate;
    }
}

5、实现增删改查

这里的查询用了反射实现

BaseDAO接口

package com.syp.flight.dao;

import java.sql.SQLException;
import java.util.List;

/**
 * @author suyiping
 * @create 2021-07-07 21:40
 */
public interface IBaseDAO<T> {
    List<T> queryByWhere(String where, List params) throws SQLException;

    T queryById(String id) throws SQLException;

    int add(T t);

    int update(T t);

    int deleteById(String id);
}

BaseDAO实现类

package com.syp.flight.dao.impl;

import com.syp.flight.dao.IBaseDAO;
import com.syp.flight.utils.DBUtil;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * @author suyiping
 * @create 2021-07-07 22:48
 */
public abstract class BaseDAOImpl<T> implements IBaseDAO<T> {
    private Class<?> gclass;

    public BaseDAOImpl() {

        ParameterizedType pt = (ParameterizedType)this.getClass().getGenericSuperclass();

        gclass = (Class<?>) pt.getActualTypeArguments()[0];

    }

    @Override
    public List<T> queryByWhere(String where, List params) throws SQLException {
        if (StringUtils.isBlank(where)) {
            where = " 1=1 ";
        }
        String sql = "select * from " + gclass.getSimpleName() + " where " + where;
        DBUtil dbUtil = new DBUtil();
        ResultSet rs = dbUtil.executeQuery(sql, params);
        // 实例化对象
        T t;
        List<T> list = new ArrayList<>();
        try {
            while (rs.next()) {
                t = (T) gclass.getDeclaredConstructor().newInstance();
                Field[] df = gclass.getDeclaredFields();
                for (int i = 0; i < df.length; i++) {
                    Field f = df[i];
                    // 拿到属性的值
                    Object value = rs.getObject(f.getName());
                    // 拿到setter方法
                    String setter = "set" + f.getName().substring(0, 1).toUpperCase() + f.getName().substring(1);
                    // 获得对象的属性
                    Method method = gclass.getMethod(setter, value.getClass());
                    method.invoke(t, value);
                }
                list.add(t);
            }
            dbUtil.closeAll();
            return list;
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (SecurityException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public T queryById(String id) throws SQLException {

        ArrayList<T> result = (ArrayList<T>) this.queryByWhere("id = ?", Collections.singletonList(id));
        if (CollectionUtils.isEmpty(result)) {
            return null;
        }
        return result.get(0);
    }
}

FlightDAO接口

package com.syp.flight.dao;

import com.syp.flight.bean.Flight;

import java.sql.SQLException;

/**
 * @author suyiping
 * @create 2021-07-07 21:19
 */
public interface IFlightDao extends IBaseDAO<Flight> {
    Flight queryByFlightNo(String FlightNo) throws SQLException;
}

FlightDAO实现类

package com.syp.flight.dao.impl;

import com.syp.flight.bean.Flight;
import com.syp.flight.dao.IFlightDao;
import com.syp.flight.utils.DBUtil;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.List;

/**
 * @author suyiping
 * @create 2021-07-07 21:20
 */
public class FlightDaoImpl extends BaseDAOImpl<Flight> implements IFlightDao {
    DBUtil dbUtil = new DBUtil();

    @Override
    public int add(Flight flight) {
        String sql = "INSERT INTO `db1`.`flight`(`flightNo`, `destination`, `startDate`) VALUES (?, ?, ?);";
        ArrayList<Object> param = new ArrayList<>();
        param.add(flight.getFlightNo());
        param.add(flight.getDestination());
        param.add(flight.getStartDate());
        return dbUtil.executeUpdate(sql, param);
    }

    @Override
    public int update(Flight flight) {
        String sql = "UPDATE `db1`.`flight` SET `flightNo` = ?, `destination` = ?, `startDate` = ? WHERE `id` = ? ;";
        ArrayList<Object> param = new ArrayList<>();
        param.add(flight.getFlightNo());
        param.add(flight.getDestination());
        param.add(flight.getStartDate());
        param.add(flight.getId());
        return dbUtil.executeUpdate(sql, param);

    }

    @Override
    public int deleteById(String id) {
        String sql = "delete from flight WHERE `id` = ? ;";
        ArrayList<Object> param = new ArrayList<>();
        param.add(id);
        return dbUtil.executeUpdate(sql, param);
    }

    @Override
    public Flight queryByFlightNo(String FlightNo) throws SQLException {

        List<Flight> flights = queryByWhere("flightno = ?", Collections.singletonList(FlightNo));
        dbUtil.closeAll();
        if (CollectionUtils.isEmpty(flights)) {
            return null;
        } else {
            return flights.get(0);
        }
    }
}