模拟封装DBUtil工具类实现mysql数据库增删改查

最新推荐文章于 2023-05-02 20:09:52 发布

原创最新推荐文章于 2023-05-02 20:09:52 发布 · 1.4k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #java

java 同时被 2 个专栏收录

22 篇文章

订阅专栏

idea

1 篇文章

订阅专栏

本文介绍了一个用于简化JDBC操作的DBUtil工具类，通过封装实现了数据源的加载、连接管理和基本的CURD操作。同时，还介绍了QueryRunner类及ResultSetHandler接口的相关实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

模拟DBUtil工具类进行JDBC封装

DBUtilsutil

作为DBUtil的util工具类

作用：

（1）加载配置文件得到数据源dataSource

（2）提供getRunner方法方便获取QueryRunner 不用再重复放入Connection连接

public class DBUtilsutil {
    private static DataSource dataSource;
    static {
        Properties p = new Properties();
        InputStream is = DButilsUtil.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            p.load(is);
            dataSource = DruidDataSourceFactory.createDataSource(p);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }


    public static QueryRunner getRunner(){
        return new QueryRunner(dataSource);
    }
}

QueryRunner

作用：

（1）提供了获取连接和CURD的方法

（2）getConnection方法使用了单例的饿汉模式创建ThreadLocal来保证获取的连接是同一个

（3）update为增删改通用的方法返回处理数据的条数

（4）addkey为增加数据方法返回的是主键（主键回填）

（5）query为通用的查询方法获取到了ResultSet结果集最后将结果集交给ResultSetHandler中的handle方法来处理

public class QueryRunner {

    private static DataSource dataSource ;
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();

    public QueryRunner(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    public QueryRunner(){

    }

    public <T> T query(String sql, ResultSetHandler<T> handler, Object... params) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
            for (int i = 0; i < parameterMetaData.getParameterCount(); i++) {
                preparedStatement.setObject(i+1,params[i]);
            }
            resultSet = preparedStatement.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return handler.handle(resultSet);
    }


    public int update(String sql,Object... params){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int count = 0;
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
            for (int i = 0; i < parameterMetaData.getParameterCount(); i++) {
                preparedStatement.setObject(i+1,params[i]);
            }
             count = preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return count;
    }


    public int addKey(String sql,Object... params){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int k = -1;

        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
            for (int i = 0; i < parameterMetaData.getParameterCount(); i++) {
                preparedStatement.setObject(i+1,params[i]);
            }

            preparedStatement.executeUpdate();
            resultSet = preparedStatement.getGeneratedKeys();
            if (resultSet.next()){
                k = resultSet.getInt(1);
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return k;
    }
    public static Connection getConnection(){

        Connection connection = threadLocal.get();
        if (connection != null){
            return connection;
        }
        try {
            connection = dataSource.getConnection();
            threadLocal.set(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

}

ResultSetHandler接口

作用：

可以创建实现类实现这个接口针对不同的sql语句创建不同的实现类来处理结果集

public interface ResultSetHandler<T> {
    T handle(ResultSet resultSet) throws SQLException;
}

接口实现类

BeanHandler

public class BeanHandler<T> implements ResultSetHandler<T>{
    Class<T> clazz;

    public BeanHandler(Class<T> clazz){
        this.clazz = clazz;
    }
    @Override
    public  T handle(ResultSet resultSet) throws SQLException {
        T t = null;
        if (resultSet.next()){
            try {
                t = clazz.newInstance();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                for (int i = 0; i < columnCount; i++) {
                    String columnLabel = metaData.getColumnLabel(i+1);
                    Object columnValue = resultSet.getObject(columnLabel);

                    BeanUtils.copyProperty(t,columnLabel,columnValue);
                }
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
        return t;
    }
}

BeanListHandler

public class BeanListHandler<T> implements ResultSetHandler <List<T>> {
    Class<T> clazz;

    public BeanListHandler(Class<T> clazz) {
        this.clazz = clazz;
    }


    @Override
    public List<T> handle(ResultSet resultSet) throws SQLException {
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        List<T> list = new ArrayList<>();
        while (resultSet.next()) {
            try {
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    Object columnValue = resultSet.getObject(columnLabel);
                    BeanUtils.copyProperty(t, columnLabel, columnValue);
                    list.add(t);
                }

            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }

        }
        return list;
    }
}

ScalarHandler

public class ScalarHandler<T> implements ResultSetHandler<T> {

    @Override
    public T handle(ResultSet resultSet) throws SQLException {
        if (resultSet.next()){
            return (T)resultSet.getObject(1);
        }
        return null;
    }
}