1 package com.bdqn.utils;
  2 /**
  3  * 数据库工具类
  4  * @author JY
  5  *
  6  */
  7 
  8 import java.io.IOException;
  9 import java.io.InputStream;
 10 import java.sql.Connection;
 11 import java.sql.DriverManager;
 12 import java.sql.PreparedStatement;
 13 import java.sql.ResultSet;
 14 import java.sql.SQLException;
 15 import java.util.Optional;
 16 import java.util.Properties;
 17 import java.util.function.Consumer;
 18 import java.util.function.Function;
 19 import java.util.function.Supplier;
 20 
 21 
 22 public class DBUtil {
 23     //定义静态变量
 24     private static Properties properties;
 25     
 26     //准备静态块,价值数据到配置对象中
 27     static {
 28         //使用消费型接口,接受配置文件的名称,消费型接口:有参数,无返回值
 29         Consumer<String> c=(fileName) ->{
 30             //使用供给型接口,加载文件到输入流中;供给型接口;无参数,有返回值
 31             Supplier<InputStream> s=() ->DBUtil.class.getClassLoader().getResourceAsStream(fileName);
 32             //得到properties配置对象
 33             properties = new Properties();
 34             try {
 35             //将输入流通过properties配置对象的load()方法
 36                 properties.load(s.get());
 37             } catch (IOException e) {
 38                 // TODO Auto-generated catch block
 39                 e.printStackTrace();
 40             }
 41         };
 42         c.accept("db.properties");
 43     }
 44     /**
 45      * 得到数据库连接
 46      * @return
 47      */
 48     public static Connection getConnection() {
 49         Function<Properties, Connection> f = (properties) ->{
 50             String driverName=properties.getProperty("driverName");
 51             String url=properties.getProperty("url");
 52             String username=properties.getProperty("username");
 53             String password=properties.getProperty("password");
 54             
 55             try {
 56                 Class.forName(driverName);
 57                 return DriverManager.getConnection(url,username,password);
 58             } catch (ClassNotFoundException e) {
 59                 e.printStackTrace();
 60             } catch (SQLException e) {
 61                 // TODO Auto-generated catch block
 62                 e.printStackTrace();
 63             }
 64             
 65             return null;
 66         };
 67         //返回函数类型接口中的返回值
 68         return f.apply(properties);
 69     }
 70     /**
 71      * 关闭资源
 72      * Optional:处理空指针异常
 73      * 当数据可能为空,但之后一定会使用该数据的方法或属性时
 74      * @param resultSet
 75      * @param preparedStatement
 76      * @param connection
 77      */
 78     public static void close(ResultSet resultSet,PreparedStatement preparedStatement,Connection connection) {
 79         Optional.ofNullable(resultSet).ifPresent((r)->{
 80             try {
 81                 r.close();
 82             } catch (SQLException e) {
 83                 e.printStackTrace();
 84             }
 85         });
 86         Optional.ofNullable(preparedStatement).ifPresent((p)->{
 87             try {
 88                 p.close();
 89             } catch (SQLException e) {
 90                 e.printStackTrace();
 91             }
 92         });
 93         Optional.ofNullable(connection).ifPresent((c)->{
 94             try {
 95                 c.close();
 96             } catch (SQLException e) {
 97                 e.printStackTrace();
 98             }
 99         });
100     }
101     
102 }

BaseDao

package com.bdqn.dao;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.IntStream;

import com.bdqn.utils.DBUtil;

/**
 * 数据库工具类
 * 
 * @author JY
 *
 */
public class BaseDao {
    /**
     * 更新操作
     * @param sql
     * @param params
     * @return
     */
    public static int executeUpdate(String sql,List<Object> params) {
        int row=0;
        //在括号里声明要关闭的资源;定义在try里面的变量会被final关键字修饰,不能重复赋值
        try(Connection connection=DBUtil.getConnection();
                PreparedStatement preparedStatement = getPreparedStatement(sql,params,connection);
                ){
            //执行更新,返回受影响行数
            row =preparedStatement.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
        }
        
        return row;
    
    }
    /**
     * 查询某一个字段(常用查询总数count)
     * @param sql
     * @param params
     * @return
     */
    public static Object findSingleValue(String sql,List<Object> params) {
        Object object=null;
        try(Connection connection=DBUtil.getConnection();
                PreparedStatement preparedStatement = getPreparedStatement(sql,params,connection);
                ResultSet resultSet=preparedStatement.executeQuery();
                ){
            //执行更新,返回受影响行数
            if (resultSet.next()) {
                object=resultSet.getObject(1);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return object;
    }
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public static List queryRows(String sql,List<Object> params,Class cls) {
        List list=new ArrayList();
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            connection =DBUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (params != null && params.size() >0) {
                for (int i = 0; i < params.size(); i++) {
                    preparedStatement.setObject(i+1, params.get(i));
                }
            }
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData=resultSet.getMetaData();
            int fiedNum= metaData.getColumnCount();
            while (resultSet.next()) {
                Object object=cls.getDeclaredConstructor().newInstance();
                for (int i = 1; i <= fiedNum; i++) {
                    String columnName=metaData.getColumnLabel(i);
                    Field field=cls.getDeclaredField(columnName);
                    String methodName="set"+columnName.substring(0,1).toUpperCase()+columnName.substring(1);
                    Method method=cls.getDeclaredMethod(methodName, field.getType());
                    method.invoke(object, resultSet.getObject(columnName));
                }
                list.add(object);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }
    /**
     * 查询对象
     *     从集合中获取第一个对象即可
     * @param sql
     * @param params
     * @param cls
     * @return
     */
    @SuppressWarnings("rawtypes")
    public static Object queryRow(String sql,List<Object> params,Class cls) {
        Object object=null;
        List list =queryRows(sql, params, cls);
        if (list !=null && list.size()>0) {
            object = list.get(0);
        }
        return object;
     }
    
    
    /**
     * 完成预编译操作
     * @param sql
     * @param params
     * @param connection
     * @return
     * @throws SQLException
     */
    public static PreparedStatement getPreparedStatement(String sql,List<Object> params,Connection connection) throws SQLException {
        //预编译
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        if (params !=null && params.size() >0) {
            IntStream.range(0, params.size()).forEach((i) ->{
                try {
                    preparedStatement.setObject(i+1, params.get(i));
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            });
        }
        return preparedStatement;
    }
    
}

配置文件

Jav数据库访问工具DBUtil和BasDao (泛型)_数据库访问工具