JDBC工具类

电玩女神 2022-10-16 06:27 196阅读 0赞

IRowMapper接口：

package com.jd.util.db;
    
    import java.sql.ResultSet;
    
    @FunctionalInterface
    public interface IRowMapper{
        void mapRow(ResultSet resultSet);
    }

PropertiesUtil类：

package com.jd.util;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.Properties;
    
    public class PropertiesUtil {
    
        private static Properties properties = new Properties();
    
        static{
            InputStream inputStream = PropertiesUtil.class.getClassLoader().getResourceAsStream("db.properties");
            try {
                properties.load(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    
        public static String value(String key) {
            return properties.getProperty(key);
        }
    }

`DBUtil类：`

package com.jd.util.db;
    
    import com.jd.util.PropertiesUtil;
    
    import java.sql.*;
    
    public class DBUtil {
    
        static{
            //1、加载驱动类
            try {
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
    
        /**
         * 获取MySQL连接
         *
         * @author GaoHuanjie
         */
        private static Connection getConnection() throws SQLException {
            String url = PropertiesUtil.value("url");
            String userName=PropertiesUtil.value("userName");
            String password=PropertiesUtil.value("password");
            return DriverManager.getConnection(url,userName,password);
        }
    
        public static boolean exist(String sql){
            Connection connection =null;
            Statement statement =null;
            ResultSet resultSet =null;
            try {
                //2、获取连接
                connection = getConnection();
                //3、创建语句
                statement = connection.createStatement();
                //4、执行SQL
                resultSet = statement.executeQuery(sql);
                //5、处理结果
                return resultSet.next();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                //6、释放资源
                close(resultSet,statement,connection);
            }
            return false;
        }
    
        public static boolean exist(String sql,Object...params){
            Connection connection =null;
            PreparedStatement preparedStatement =null;
            ResultSet resultSet =null;
            try {
                //2、获取连接
                connection = getConnection();
                //3、创建语句,预编译
                preparedStatement = connection.prepareStatement(sql);
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i+1,params[i]);
                }
                //4、执行SQL
                resultSet = preparedStatement.executeQuery();
                //5、处理结果
                return resultSet.next();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                //6、释放资源
                close(resultSet,preparedStatement,connection);
            }
            return false;
        }
    
        public static void select(String sql,IRowMapper rowMapper){
            Connection connection =null;
            Statement statement =null;
            ResultSet resultSet =null;
            try {
                //2、获取MySQL连接
                connection = getConnection();
                //3、创建语句
                statement = connection.createStatement();
                //4、执行SQL语句
                resultSet = statement.executeQuery(sql);
                //5、处理结果
                rowMapper.mapRow(resultSet);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                //6、释放资源
                close(resultSet,statement,connection);
            }
        }
    
        public static void select(String sql,IRowMapper rowMapper,Object...params){
            Connection connection =null;
            PreparedStatement preparedStatement =null;
            ResultSet resultSet =null;
            try {
                //2、获取连接
                connection = getConnection();
                //3、创建语句,预编译
                preparedStatement = connection.prepareStatement(sql);
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject((i+1),params[i]);
                }
                //4、执行SQL
                resultSet = preparedStatement.executeQuery();
                //5、处理结果
                rowMapper.mapRow(resultSet);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                //6、释放资源
                close(resultSet,preparedStatement,connection);
            }
        }
    
        public static boolean update(String sql){
            Connection connection =null;
            Statement statement =null;
            try {
                //2、连接MySQL数据库
                connection = getConnection();
                //3、创建SQL语句
                statement = connection.createStatement();
                //4、执行SQL
                int effect = statement.executeUpdate(sql);
                //5、处理结果
                return effect>0;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }  finally {
                //6、释放资源
                close(statement,connection);
            }
            return false;
        }
    
        public static boolean update(String sql,Object...params){
            Connection connection =null;
            PreparedStatement preparedStatement =null;
            try {
                //2、连接MySQL数据库
                connection = getConnection();
                //3、创建SQL语句
                preparedStatement = connection.prepareStatement(sql);
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i+1,params[i]);
                }
                //4、执行SQL
                int effect = preparedStatement.executeUpdate();
                //5、处理结果
                return effect>0;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }  finally {
                //6、释放资源
                close(preparedStatement,connection);
            }
            return false;
        }
    
        private static void close(Statement statement,Connection connection){
            if(statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    
        private static void close(ResultSet resultSet,Statement statement,Connection connection){
            if(resultSet!=null) {
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            close(statement,connection);
        }
    }