JDBC 单例模式 封装 使用预编译（PreparedStatement接口）

使用预编译的PreparedStatement 类
使用上的区别？
private PreparedStatement preparedStatement;
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(int parameterIndex, Object x);
preparedStatement.executeUpdate();
预编译PreparedStatement需要实现将SQL语句和语句中 的占位符相对应的value存入

If arbitrary parameter type conversions are required, the method setObject should be used with a target SQL type.

In the following example of setting a parameter, con represents an active connection:
使用范例：


PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
SET SALARY = ? WHERE ID = ?");
pstmt.setBigDecimal(1, 153833.00)
pstmt.setInt(2, 110592)


接口PreparedStatement接口中用到的方法
1. 方法 void setObject​(int parameterIndex, Object x)
API：
Parameters:
parameterIndex - the first parameter is 1, the second is 2, ...
x - the object containing the input parameter value

2.方法int executeUpdate()
API:
Returns:
either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

3.方法 ResultSet executeQuery()
Returns:
a ResultSet object that contains the data produced by the query; never null

ResultSetMetaData接口

ResultSetMetaData metaData =resultSet.getMetaData();
方法：
1. String getColumnName​(int column) —–>Get the designated column's name.
Parameters:
column - the first column is 1, the second is 2, ...
Returns: column name
2. int getColumnCount()—->
Returns the number of columns in this ResultSet object.

mport java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBmanager {
   //数据库的用户名
    private final String USER="root";
    //密码
    private final String PASSWORD="19970909";
    //数据库的访问连接
    private final String URL="jdbc:mysql://localhost:3306/mydatabase?characterEncoding=utf8&useSSL=false";
    //数据库驱动
    private final String DRIVER="com.mysql.cj.jdbc.Driver";
    //获取单例
    private static DBmanager instance;
    //获取连接
    private Connection connection;


    //预编译的SQL执行空间
    private PreparedStatement preparedStatement;


    //单例模式，将构造方法设置为私有
    private DBmanager(){
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 单例模式，获取实例的方法
     * @return
     */
    public static DBmanager getInstance(){
        if(instance==null){
            instance = new DBmanager();
        }
        return instance;
    }

    /**
     * 获取数据库连接的方法
     * @return
     * @throws SQLException
     */

    public Connection getConnection()throws SQLException{
        connection = DriverManager.getConnection(URL,USER,PASSWORD);
        return connection;
    }

    /**
     * 可执行 delete update insert 语句
     * @param sql SQL语句
     * @param params 装载value的list
     * @return
     * @throws SQLException
     */

    public int executeUpdateBySQL(String sql,List<Object> params)throws SQLException{
        preparedStatement = connection.prepareStatement(sql);
        int index =1;//占位符的序号
        if(params!=null && !params.isEmpty()){
            for (int i = 0; i <params.size() ; i++) {
                preparedStatement.setObject(index++,params.get(i));
            }
        }
        return preparedStatement.executeUpdate();
    }


    /**
     *
     * @param sql   SQL语句
     * @param params 字段值的数组
     * @param cls  Class 的 引用
     * @param <T> 返回值是按照传入类的类型决定的
     * @return
     * @throws SQLException
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws NoSuchFieldException
     */

    public <T> T executeSingleObjectBySQL(String sql,List<Object> params,Class<?> cls) throws SQLException, IllegalAccessException, InstantiationException, NoSuchFieldException {
        T result_object = null;
        preparedStatement = connection.prepareStatement(sql);
        int index=1;
        if(params!=null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(index++, params.get(i));
            }
        }
        Object target_object = cls.newInstance();

        //提取列的属性
        ResultSet resultSet =preparedStatement.executeQuery();
        //从结果集中取出元数据
        ResultSetMetaData metaData =resultSet.getMetaData();
        int numberOfColumn = metaData.getColumnCount();

        //行数循环读取
        while(resultSet.next()){
            //读取每行中的列数据
            for(int i=0;i<numberOfColumn;i++){
                String propertyName = metaData.getColumnName(i+1);//i是从0开始的，所以要加1
                //获取列名，对应着对象的属性名
                Field field = cls.getDeclaredField(propertyName);
                field.setAccessible(true);
                //获取属性名的属性值
                Object value = resultSet.getObject(propertyName);
                //若属性值为空，把value设置为空
                if(value == null){
                    value = "";
                }
                //将value存入对应的对象中
                field.set(target_object,value);

            }
        }
        return (T) target_object;
    }

    /**
     *
     * @param sql SQL语句
     * @param params 字段值的数组
     * @param cls Class 的 引用
     * @param <T> 返回值为泛型 T 的List<T>
     * @return
     * @throws SQLException
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws NoSuchFieldException
     */

    public <T> List<T> executeMutiObjectBySQL(String sql,List<Object> params,Class<?> cls) throws SQLException, IllegalAccessException, InstantiationException, NoSuchFieldException {
        List<T> list = new ArrayList<>();
        preparedStatement=connection.prepareStatement(sql);
        int index = 1;
        if(params!=null&& !params.isEmpty()){
            for (int i = 0; i <params.size() ; i++) {
                preparedStatement.setObject(index++,params.get(i));
            }
        }
        ResultSet resultSet =preparedStatement.executeQuery();
        ResultSetMetaData metaData =resultSet.getMetaData();
        int numberOfcolumn= metaData.getColumnCount();
        while(resultSet.next()){
            Object target = cls.newInstance();
            for(int i =0;i<numberOfcolumn;i++){
                String propertyName = metaData.getColumnName(i+1);
                Field field = cls.getDeclaredField(propertyName);
                field.setAccessible(true);
                Object value = resultSet.getObject(propertyName);
                if(value ==null){
                    value = "";
                }
                field.set(target,value);
            }
            list.add((T) target);
        }
        return list;
    }

    /**
     * 查询数据库返回单行记录，一般都是按照主键查询
     * <p>
     *     select * from student where id =?
     *
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public Map<String,Object> executeSingleMapBySQL(String sql,List<Object> params) throws SQLException {
        Map<String,Object> map = new HashMap<>();
        preparedStatement = connection.prepareStatement(sql);
        int index =1;
        if(params!=null&&!params.isEmpty()){
            for (int i = 0; i <params.size() ; i++) {
                preparedStatement.setObject(index++,params.get(i));
            }
        }
        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSetMetaData metaData =resultSet.getMetaData();
        int numberOfColumns=metaData.getColumnCount();
        while(resultSet.next()) {
            for (int i = 0; i < numberOfColumns; i++) {
                String properName = metaData.getColumnName(i + 1);
                Object value = resultSet.getObject(properName);
                if (value==null){
                    value ="";
                }
                map.put(properName,value);
            }
        }
        return map;
    }

    public List<Map<String,Object>> executeMultiMapBySQL(String sql,List<Object> params) throws SQLException {
        List<Map<String,Object>> list = new ArrayList<>();
        preparedStatement = connection.prepareStatement(sql);
        int index = 1;
        if(params != null && !params.isEmpty()){
            for (int i = 0; i <params.size() ; i++) {
                preparedStatement.setObject(index++,params.get(i));
            }
        }
        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSetMetaData metaData =resultSet.getMetaData();
        int numberOfColumn = metaData.getColumnCount();
        while(resultSet.next()){
            Map<String,Object> map = new HashMap<>();
            for (int i = 0; i <numberOfColumn ; i++) {
                String property = metaData.getCatalogName(i+1);
                Object value=resultSet.getObject(property);
                if(value == null){
                    value="";
                }
                map.put(property,value);
            }
            list.add(map);
        }
        return list;
    }


//    /**
//     *insert into teach(name,birthday,info,pic)value(?,?,?,?)l
//     * @param sql
//     * @return
//     * @throws SQLException
//     */
//    public int executeUpdateBySQL(String sql, String name, Date birthday, Reader info, InputStream pic)throws  SQLException{
//
//            preparedStatement = connection.prepareStatement(sql);
//            preparedStatement.setString(1,name);
//            preparedStatement.setDate(2,birthday);
//            preparedStatement.setClob(3,info);
//            preparedStatement.setBlob(4,pic);
//           return preparedStatement.executeUpdate();
//
//    }
//
//    /**
//     * 查询一条记录
//     * @param sql
//     * @param id
//     * @return
//     * @throws SQLException
//     */
//    public ResultSet queryOne(String sql,int id)throws SQLException{
//        ResultSet resultSet =null;
//        preparedStatement = connection.prepareStatement(sql);
//        preparedStatement.setInt(1,id);
//        resultSet = preparedStatement.executeQuery();
//        return resultSet;
//    }
    public void closeAll(){
        try {
            if(preparedStatement!=null) {
                preparedStatement.close();
            }
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}