jdbc中查询封装——反射，jdbc封装反射

在使用原生jdbc时候使用查询语句大同小异，因此在这里使用反射将jdbc中查询语句进行封装

查询单条语句
查询多条语句

package src.jdbcTest;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
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 org.apache.log4j.Logger;

/**
 * 
 * @author chencong
 * @Time 2017年8月3日 下午12:37:55
 * @TODO 通过反射返回单条记录和多行记录
 */
public class DBUtils {

    private static final String DRIVER_URL = "jdbc:sqlserver://localhost:1433;DataBaseName=mangerQQSys";
    private static final String DRIVER_NAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private static final String DB_USER = "sa";
    private static final String DB_PASSWORD = "123456";

    private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;

    public DBUtils() {

    }

    {
        try {
            Class.forName(DRIVER_NAME);
            connection = DriverManager.getConnection(DRIVER_URL, DB_USER, DB_PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Connection getConnection() {
        return connection;
    }

    /**
     * 关闭数据库连接
     * 
     * @param connection
     * @param ptStatement
     * @param resultSet
     */
    public void closeAll(Connection connection, PreparedStatement ptStatement, ResultSet resultSet) {
        try {
            if (connection != null) {
                connection.close();
            }
            if (ptStatement != null) {
                ptStatement.close();
            }
            if (resultSet != null) {
                ptStatement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 关闭数据库连接
     * 
     * @param ptStatement
     * @param resultSet
     */
    public void closeAll(PreparedStatement ptStatement, ResultSet resultSet) {
        try {
            if (ptStatement != null) {
                ptStatement.close();
            }
            if (resultSet != null) {
                ptStatement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 反射机制 返回单条记录 T
     * 
     * @param sql
     * @param params
     * @param clazz
     * @return
     * @throws Exception
     */
    public <T> T selectSimpleResult(String sql, List<Object> params, Class<T> clazz) throws Exception {
        if (connection == null) {
            return null;
        }
        if (sql == null || clazz == null) {
            return null;
        }
        T resultObject = null;
        int index = 1;
        preparedStatement = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(index, params.get(i));
                index++;
            }
        }
        resultSet = preparedStatement.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int couLength = metaData.getColumnCount();
        while (resultSet.next()) {
            // 通过反射的到一个泛型对象
            resultObject = clazz.newInstance();
            for (int i = 0; i < couLength; i++) {
                String colName = metaData.getColumnName(i + 1);
                Object colValue = resultSet.getObject(colName);
                if (colValue == null) {
                    colValue = "";
                }
                Field field = clazz.getDeclaredField(colName);
                field.setAccessible(true); // 取消Java检查机制 打开访问权限
                field.set(resultObject, colValue);
            }
        }
        return resultObject;
    }

    /**
     * 查询多条记录
     * 
     * @param sql
     *            将要执行的sql语句
     * @param params
     * @param clazz
     * @return
     * @throws Exception
     */
    public <T> List<T> selectMoreResult(String sql, List<Object> params, Class<T> clazz) throws Exception {
        if (connection == null) {
            return null;
        }
        if (sql == null || clazz == null) {
            return null;
        }
        List<T> list = new ArrayList<T>();
        int index = 1;
        preparedStatement = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                System.out.println(index + ":" + params.get(i));
                preparedStatement.setObject(index, params.get(i));
                index++;
            }
        }
        resultSet = preparedStatement.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int colLength = metaData.getColumnCount();
        while (resultSet.next()) {
            // 通过反射创建实体
            T resultObject = clazz.newInstance();
            for (int i = 0; i < colLength; i++) {
                String colName = metaData.getColumnName(i + 1);
                Object colValue = resultSet.getObject(colName);
                if (colValue == null) {
                    colValue = "";
                }
                Field field = clazz.getDeclaredField(colName);
                field.setAccessible(true);
                field.set(resultObject, colValue);
            }
            list.add(resultObject);
        }
        return list;

    }

}

聪聪的独立博客

聪聪的独立博客 ，一个喜欢技术，喜欢钻研的95后。

• Blog@ccoder’s blog
• CSDN@ccoder
• Github@ccoder
• Email@ccoder or Gmail@ccoder