Java学习笔记（三）—— JDBC封装

这里提供使用Map返回当行记录的封装

public Map<String, Object> selectSimpleResult(String sql, List<Object> params)

和 返回List<Map<String,Object>> 返回多行记录从而查询多行记录，这里list代表多行记录，每一行当中的都代表一个键值对，从而实现返回多行记录

public List<Map<String, Object>> selectMoreResult(String sql, List<Object> params)

package jdbcTest;

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.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 
 * @author chencong
 * @Time 2017年8月3日 上午9:37:00
 * @TODO 进行封装jdbc方法的增删改查 ， 同时提供反射机制封装
 */
public class JDBCUtils {

    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 JDBCUtils() {
        try {
            Class.forName(DRIVER_NAME);
            System.out.println("连接成功！");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 提供Connection的getConnection()方法获得一个数据库连接
     * 
     * @return 返回数据库连接Connection
     */
    public Connection getConnection() {
        try {
            connection = DriverManager.getConnection(DRIVER_URL, DB_USER, DB_PASSWORD);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 增加、删除、修改
     * <p>
     * sql = "select stuId , stuName form student where stuId = ?"
     * 此时的params中就为这里？的占位符
     * 
     * @param sql
     *            将要执行的的sql语句
     * @param params
     *            sql语句中的参数
     * @return 返回是否执行成功
     * @throws SQLException
     *             抛出sql异常
     */
    public boolean updateByPreparedStatement(String sql, List<Object> params) throws SQLException {
        if (connection == null) {
            return false;
        }
        if (sql == null || params == null || params.isEmpty()) {
            return false;
        }
        int result = -1;
        boolean flag = false;
        int index = 1;
        preparedStatement = connection.prepareStatement(sql);
        // 遍历参数列表
        for (int i = 0; i < params.size(); i++) {
            preparedStatement.setObject(index, params.get(i));
            index++;
        }
        result = preparedStatement.executeUpdate();
        flag = result > 0 ? true : false;
        return flag;
    }

    /**
     * 单条记录查询
     * 
     * @param sql
     *            将要执行的sql语句
     * @param params
     *            该条sql语句当中的参数列表
     * @return 返回单条记录的map集合
     * @throws SQLException
     */
    public Map<String, Object> selectSimpleResult(String sql, List<Object> params) throws SQLException {
        if (connection == null) {
            return null;
        }
        if (sql == null || params == null || params.isEmpty()) {
            return null;
        }
        Map<String, Object> map = new HashMap<>();
        int index = 1;
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < params.size(); i++) {
            preparedStatement.setObject(index, params.get(i));
            index++;
        }
        // 返回查询结果
        resultSet = preparedStatement.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int col_length = metaData.getColumnCount();
        while (resultSet.next()) {
            for (int i = 0; i < col_length; i++) {
                String col_name = metaData.getColumnName(i + 1); // 列名从1开始
                Object col_value = resultSet.getObject(col_name); // 通过列名查询该列的值
                if (col_value == null) {
                    col_value = "";
                }

                map.put(col_name, col_value);
            }
        }
        return map;
    }

    /**
     * 多行记录查询
     * 
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> selectMoreResult(String sql, List<Object> params) throws SQLException {
        if (connection == null) {
            return null;
        }
        if (sql == null || params == null || params.isEmpty()) {
            return null;
        }
        int index = 1;
        List<Map<String, Object>> list = new ArrayList<>();
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < params.size(); i++) {
            preparedStatement.setObject(index, params.get(i));
            index++;
        }
        resultSet = preparedStatement.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int col_length = metaData.getColumnCount();
        while (resultSet.next()) {
            Map<String, Object> map = new HashMap<>();
            for (int i = 0; i < col_length; i++) {
                String col_name = metaData.getColumnName(i + 1);
                Object col_value = resultSet.getObject(col_name);
                if (col_value == null) {
                    col_value = "";
                }
                map.put(col_name, col_value);
            }
            list.add(map);
        }
        return list;
    }

    /**
     * 关闭数据库连接
     * 
     * @param resultSet
     * @param statement
     * @param connection
     * @throws SQLException
     */
    public void closeAll(ResultSet resultSet, Statement statement, Connection connection) throws SQLException {
        if (resultSet != null) {
            resultSet.close();
        }
        if (connection != null) {
            connection.close();
        }
        if (statement != null) {
            statement.close();
        }
    }

    /**
     * 关闭连接
     * 
     * @param resultSet
     * @param statement
     * @throws SQLException
     */
    public void closeAll(ResultSet resultSet, Statement statement) throws SQLException {
        if (resultSet != null) {
            resultSet.close();
        }
        if (statement != null) {
            statement.close();
        }
    }

}