eip_manage/Hos-Resource-Cloud/src/main/java/com.yihu.ehr/util/db/DBSessionFactory.java

package com.yihu.ehr.util.db;

import com.fasterxml.jackson.databind.node.ObjectNode;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.dom4j.Document;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;


/**
 * ���ݿ�������,�򻯲���
 *
 * @created Air on 2015/6/2
 */
public class DBSessionFactory {
    public static final String HOS = "hos-mysql";
    private final static HashMap<String, DataSourcePool> dataSourcePool = new HashMap<>();
    private DataSource dataSource;
    private QueryRunner queryRunner;
    private Connection connection;
    private String driver;

    public DBSessionFactory() {
        DataSourcePool dataSourcePool = DBSessionFactory.dataSourcePool.get(HOS);
        if (dataSourcePool != null) {
            dataSource = dataSourcePool.getDataSource();
            driver = dataSourcePool.getDriver();
        }
        queryRunner = new QueryRunner();
    }

    /**
     * @param dsName ���ݿ����ñ���,���ڱ�ʶ��������ݿ�����
     */
    public DBSessionFactory(String dsName) throws SQLException {
        DataSourcePool dataSourcePool = DBSessionFactory.dataSourcePool.get(dsName);
        if (dataSourcePool != null) {
            dataSource = dataSourcePool.getDataSource();
            connection = dataSource.getConnection();
            driver = dataSourcePool.getDriver();
        }
        queryRunner = new QueryRunner();
    }

    /**
     * ������ݿ�����
     *
     * @param dsName ���ݿ����ñ���,���ڱ�ʶ��������ݿ�����
     * @param uri    ���ݿ����Ӵ�
     */
    public static synchronized void addDataSource(String dsName, String uri) {
        DataSourcePool dataSourcePool = DBSessionFactory.dataSourcePool.get(dsName);
        if (dataSourcePool == null) {
            DBSessionFactory.dataSourcePool.put(dsName, new DataSourcePool(uri));
        }
    }

    /**
     * �ر�Connection
     *
     * @throws SQLException
     */
    public void close() throws SQLException {
        if (connection != null) {
            connection.close();
        }
    }

    /**
     * ʹ��Json��ʽ
     *
     * @param jsonRoot
     * @param arrayList
     * @param sql       SQL�﷨
     * @param params    �����б�
     * @return JSONObject
     * @throws SQLException
     */
    public ObjectNode queryForJson(String jsonRoot, ArrayList<String> arrayList, String sql, Object... params) throws SQLException {
        JsonHandler jsonHandler = new JsonHandler(jsonRoot);
        jsonHandler.setTagList(arrayList);
        return this.query(sql, jsonHandler, params);
    }

    public ObjectNode queryForJson(String jsonRoot, String sql, Object... params) throws SQLException {
        JsonHandler jsonHandler = new JsonHandler(jsonRoot);
        return this.query(sql, jsonHandler, params);
    }

    /**
     * ʹ��XML��ʽ
     *
     * @param sql    SQL�﷨
     * @param params �����б�
     * @return Document XML
     * @throws SQLException
     */
    public Document queryForXml(String sql, Object... params) throws SQLException {
        return null;
    }

    /**
     * �����鷽ʽ���ؽ��
     *
     * @param sql    SQL�﷨
     * @param params �����б�
     * @return Object[]����
     * @throws SQLException
     */
    public Object[] queryForArray(String sql, Object... params) throws SQLException {
        ArrayHandler arrayHandler = new ArrayHandler();
        return this.query(sql, arrayHandler, params);
    }

    /**
     * �����鷽ʽ�б��ؽ��
     *
     * @param sql    SQL�﷨
     * @param params �����б�
     * @return Object[]�б�
     * @throws SQLException
     */
    public List<Object[]> queryForArrayList(String sql, Object... params) throws SQLException {
        ArrayListHandler arrayListHandler = new ArrayListHandler();
        return this.query(sql, arrayListHandler, params);
    }

    /**
     * ��Bean��ʽ�б��ؽ��
     *
     * @param cls    ���ص�������
     * @param sql    SQL�﷨
     * @param params �����б�
     * @param <T>    ���ص���
     * @return T����
     * @throws SQLException
     */
    public <T> T queryForBean(Class<T> cls, String sql, Object... params) throws SQLException {
        BeanHandler<T> beanHandler = new BeanHandler<T>(cls);
        return this.query(sql, beanHandler, params);
    }

    /**
     * ��Bean�б�ʽ�б��ؽ��
     *
     * @param <T>           ���ص���
     * @param cls           ���ص�������
     * @param beanProcessor
     * @param sql           SQL�﷨
     * @param params        �����б�   @return T�����б�   @throws SQLException
     */
    public <T> List<T> queryForBeanList(Class<T> cls, BeanProcessor beanProcessor, String sql, Object... params) throws SQLException {
        BeanListHandler<T> beanListHandler = null;

        if (beanProcessor == null) {
            beanListHandler = new BeanListHandler<T>(cls);
        } else {
            BasicRowProcessor rowProcessor = new BasicRowProcessor(beanProcessor);
            beanListHandler = new BeanListHandler<T>(cls, rowProcessor);
        }


        return this.query(sql, beanListHandler, params);
    }

    /**
     * �����б�ʽ�б��ؽ��
     *
     * @param cls    ���ص�������
     * @param sql    SQL�﷨
     * @param params �����б�
     * @param <T>    ���ص���
     * @return T�����б�
     * @throws SQLException
     */
    public <T> List<T> queryForColumnList(Class<T> cls, String sql, Object... params) throws SQLException {
        ColumnListHandler<T> columnListHandler = new ColumnListHandler<T>(String.valueOf(cls));
        return this.query(sql, columnListHandler, params);
    }

    /**
     * ��Map��ʽ���ضԽ��
     *
     * @param sql    SQL�﷨
     * @param params �����б�
     * @return Map
     * @throws SQLException
     */
    public Map<String, Object> queryForMap(String sql, Object... params) throws SQLException {
        MapHandler mapHandler = new MapHandler();
        return this.query(sql, mapHandler, params);
    }

    /**
     * ��Map�б�ʽ���ضԽ��
     *
     * @param sql    SQL�﷨
     * @param params �����б�
     * @return Map
     * @throws SQLException
     */
    public List<Map<String, Object>> queryForMapList(String sql, Object... params) throws SQLException {
        MapListHandler mapListHandler = new MapListHandler();
        return this.query(sql, mapListHandler, params);
    }

    /**
     * ���ݽ������ʽ��ѯ����
     *
     * @param sql    SQL�﷨
     * @param rsh    ���������
     * @param params �����б�
     * @param <T>    ���ص���
     * @return T����
     * @throws SQLException
     */
    public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        checkConnection();
        return queryRunner.query(connection, sql, rsh, params);
    }

    /**
     * ���ݽ������ʽ��ѯ����
     *
     * @param sql SQL�﷨
     * @param rsh ���������
     * @param <T> ���ص���
     * @return T����
     * @throws SQLException
     */
    public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException {
        checkConnection();
        setTransactionReadCommitted();

        return queryRunner.query(connection, sql, rsh, (Object[]) null);
    }

    public int update(String sql) throws SQLException {
        checkConnection();
        setTransactionReadCommitted();

        return queryRunner.update(connection, sql, (Object[]) null);
    }

    public int update(String sql, Object param) throws SQLException {
        checkConnection();
        setTransactionReadCommitted();

        return queryRunner.update(connection, sql, new Object[]{param});
    }

    public int update(String sql, Object... params) throws SQLException {
        checkConnection();
        setTransactionReadCommitted();

        return queryRunner.update(connection, sql, params);
    }

    public int insert(String sql, Object... params) throws SQLException {
        checkConnection();
        setTransactionReadCommitted();

        PreparedStatement stmt = null;

        try {
            stmt = connection.prepareStatement(sql, 1);
            queryRunner.fillStatement(stmt, params);
            int result = stmt.executeUpdate();
            return result;
        } catch (SQLException e) {
            throw e;
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
    }

    public <T> T insert(String sql, ResultSetHandler<T> rsh) throws SQLException {
        checkConnection();
        setTransactionReadCommitted();

        return queryRunner.insert(connection, sql, rsh, (Object[]) null);
    }

    public <T> T insert(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        checkConnection();
        setTransactionReadCommitted();

        return queryRunner.insert(connection, sql, rsh, params);
    }


    /**
     * @param sql    SQL�﷨
     * @param rsh    ���������
     * @param params �����б�
     * @param <T>    ���ص���
     * @return T����
     * @throws SQLException
     */
    public <T> T insertBatch(String sql, ResultSetHandler<T> rsh, Object[][] params) throws SQLException {
        checkConnection();
        setTransactionReadCommitted();

        return queryRunner.insertBatch(connection, sql, rsh, params);
    }

    /**
     * ����ִ��,insert,update,delete
     *
     * @param sql    SQL�﷨
     * @param params �����б�
     * @return ��Ӱ������
     * @throws SQLException
     */
    public int[] batch(String sql, Object[][] params) throws SQLException {
        checkConnection();
        setTransactionReadCommitted();

        return queryRunner.batch(connection, sql, params);
    }


    /**
     * 时间获取，不使用BeanProcessorEx方法时，只能获取到可能无法获取到时间部分。
     *
     * @return
     * @throws SQLException
     */
    public Date getSystemDateTime() throws SQLException {
        String sql = null;
        if (driver.equals(DataSourcePool.ORACLE_JDBC_DRIVER_ORACLE_DRIVER)) {
            sql = "SELECT sysdate DATE_TIME  FROM dual";
        } else if (driver.equals(DataSourcePool.COM_MICROSOFT_JDBC_SQLSERVER_DRIVER)) {
            sql = "SELECT getdate() AS DATE_TIME";
        } else if (driver.equals(DataSourcePool.COM_MYSQL_JDBC_DRIVER)) {
            sql = "SELECT now() AS DATE_TIME";
        }

        Map<String, String> propertyMap = new HashMap<>();
        propertyMap.put("DATE_TIME", "date");
        BeanProcessorEx beanProcessorEx = new BeanProcessorEx(propertyMap);
        List<SystemDataTime> systemDataTimes = this.queryForBeanList(SystemDataTime.class, beanProcessorEx, sql);
        if (systemDataTimes != null && systemDataTimes.size() != 0) {
            return systemDataTimes.get(0).getDate();
        }

        throw new SQLException("获取数据库系统时间错误.");
    }

    /**
     * �����������
     *
     * @throws SQLException
     */
    private void checkConnection() throws SQLException {
        if (dataSource == null) {
            throw new SQLException("invalid DataSource.");
        }

        if (connection == null || connection.isClosed()) {
            connection = dataSource.getConnection();
        }

        if (connection == null) {
            throw new SQLException("invalid Connection.");
        }
    }

    /**
     * �����������ȼ�Ϊ���ύ�Ķ�ȡ
     *
     * @throws SQLException
     */
    private void setTransactionReadCommitted() throws SQLException {
        connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    }
}