• Home
• com.landawn
• abacus-android
• 1.7.2
• source code
• SQLExecutor.java

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

com.landawn.abacus.util.SQLExecutor Maven / Gradle / Ivy

/*
 * Copyright (C) 2015 HaiYang Li
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except
 * in compliance with the License. You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software distributed under the License
 * is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
 * or implied. See the License for the specific language governing permissions and limitations under
 * the License.
 */

package com.landawn.abacus.util;

import java.io.Closeable;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
import java.util.Objects;
import java.util.Set;
import java.util.concurrent.Callable;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.TimeUnit;

import com.landawn.abacus.DataSet;
import com.landawn.abacus.DataSource;
import com.landawn.abacus.DataSourceManager;
import com.landawn.abacus.DataSourceSelector;
import com.landawn.abacus.DirtyMarker;
import com.landawn.abacus.IsolationLevel;
import com.landawn.abacus.annotation.Beta;
import com.landawn.abacus.annotation.Id;
import com.landawn.abacus.annotation.ReadOnlyId;
import com.landawn.abacus.condition.And;
import com.landawn.abacus.condition.Condition;
import com.landawn.abacus.condition.ConditionFactory.L;
import com.landawn.abacus.condition.Equal;
import com.landawn.abacus.dataSource.SQLDataSource;
import com.landawn.abacus.exception.AbacusException;
import com.landawn.abacus.exception.DuplicatedResultException;
import com.landawn.abacus.exception.UncheckedSQLException;
import com.landawn.abacus.logging.Logger;
import com.landawn.abacus.logging.LoggerFactory;
import com.landawn.abacus.type.Type;
import com.landawn.abacus.util.Fn.FN;
import com.landawn.abacus.util.Fn.Suppliers;
import com.landawn.abacus.util.JdbcUtil.BiRecordGetter;
import com.landawn.abacus.util.JdbcUtil.PreparedQuery;
import com.landawn.abacus.util.SQLBuilder.NE;
import com.landawn.abacus.util.SQLBuilder.NE2;
import com.landawn.abacus.util.SQLBuilder.NE3;
import com.landawn.abacus.util.SQLBuilder.SP;
import com.landawn.abacus.util.StringUtil.Strings;
import com.landawn.abacus.util.u.Nullable;
import com.landawn.abacus.util.u.Optional;
import com.landawn.abacus.util.u.OptionalBoolean;
import com.landawn.abacus.util.u.OptionalByte;
import com.landawn.abacus.util.u.OptionalChar;
import com.landawn.abacus.util.u.OptionalDouble;
import com.landawn.abacus.util.u.OptionalFloat;
import com.landawn.abacus.util.u.OptionalInt;
import com.landawn.abacus.util.u.OptionalLong;
import com.landawn.abacus.util.u.OptionalShort;
import com.landawn.abacus.util.function.BiConsumer;
import com.landawn.abacus.util.function.Function;
import com.landawn.abacus.util.function.Supplier;
import com.landawn.abacus.util.stream.Collector;
import com.landawn.abacus.util.stream.ObjIteratorEx;
import com.landawn.abacus.util.stream.Stream;

/**
 * SQLExecutor is a simple sql/jdbc utility class. SQL is supported with different format: 

 * 
 * 
 * 
 * 
 INSERT INTO account (first_name, last_name, gui, last_update_time, create_time) VALUES (?,  ?,  ?,  ?,  ?)
 * 
 INSERT INTO account (first_name, last_name, gui, last_update_time, create_time) VALUES (#{firstName}, #{lastName}, #{gui}, #{lastUpdateTime}, #{createTime})
 * 
 INSERT INTO account (first_name, last_name, gui, last_update_time, create_time) VALUES (:firstName, :lastName, :gui, :lastUpdateTime, :createTime)
 * 
 * All these kinds of SQLs can be generated by SQLBuilder conveniently. Parameters with format of Array/List parameters are supported for parameterized sql with '?'. 
 * Parameters with format of Array/List/Map/Entity are supported for parameterized SQL with named parameters.
 * 
 *  
 * Here is sample of CRUD(create/read/update/delete):
 * 
========================================================================
 * 
 * 
        static final DataSource dataSource = JdbcUtil.createDataSource(...);
        static final SQLExecutor sqlExecutor = new SQLExecutor(dataSource);
        ...
        Account account = createAccount();

        // create
        String sql_insert = NE.insert(GUI, FIRST_NAME, LAST_NAME, LAST_UPDATE_TIME, CREATE_TIME).into(Account.class).sql();
        N.println(sql_insert);
        sqlExecutor.insert(sql_insert, account);

        // read
        String sql_selectByGUI = NE.selectFrom(Account.class, N.asSet(DEVICES)).where(L.eq(GUI, L.QME)).sql();
        N.println(sql_selectByGUI);
        Account dbAccount = sqlExecutor.findFirst(Account.class, sql_selectByGUI, account);
        assertEquals(account.getFirstName(), dbAccount.getFirstName());

        // update
        String sql_updateByLastName = NE.update(Account.class).set(FIRST_NAME).where(L.eq(LAST_NAME, L.QME)).sql();
        N.println(sql_updateByLastName);
        dbAccount.setFirstName("newFirstName");
        sqlExecutor.update(sql_updateByLastName, dbAccount);

        // delete
        String sql_deleteByFirstName = NE.deleteFrom(Account.class).where(L.eq(FIRST_NAME, L.QME)).sql();
        N.println(sql_deleteByFirstName);
        sqlExecutor.update(sql_deleteByFirstName, dbAccount);

        dbAccount = sqlExecutor.findFirst(Account.class, sql_selectByGUI, account);
        assertNull(dbAccount);
 * 
 * 
 * ========================================================================
 * 

 * 

 * If {@code conn} argument is null or not specified, {@code SQLExecutor} is responsible to get the connection from the
 * internal {@code DataSource}, start and commit/roll back transaction for batch operations if needed, and close the
 * connection finally. otherwise it's user's responsibility to do such jobs if {@code conn} is specified and not null. 

 * 

 * 
 * The general programming way with SQLExeucte is to execute sql scripts(generated by SQLBuilder) with array/list/map/entity by calling (batch)insert/update/delete/query/... methods.
 * if Transaction is required. it can be started:
 * 
 * 
 *      final SQLTransaction tran = sqlExecutor.beginTransaction(IsolationLevel.READ_COMMITTED);
        boolean noException = false;
        try {
            // sqlExecutor.insert(...);
            // sqlExecutor.update(...);
            // sqlExecutor.query(...);

            noException = true;
        } finally {
            // The connection will be automatically closed after the transaction is committed or rolled back.            
            if (noException) {
                tran.commit();
            } else {
                tran.rollback();
            }
        }
 * 
 * 
 * 
 * SQLExecutor is tread-safe.


 * 
 * @since 0.8
 * 
 * @author Haiyang Li
 * 
 * @see JdbcUtil
 * 
 * @see {@link com.landawn.abacus.annotation.ReadOnly}
 * @see {@link com.landawn.abacus.annotation.ReadOnlyId}
 * @see {@link com.landawn.abacus.annotation.NonUpdatable}
 * @see {@link com.landawn.abacus.annotation.Transient}
 * @see {@link com.landawn.abacus.annotation.Table}
 * @see {@link com.landawn.abacus.annotation.Column}
 * 
 * @see http://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html
 * @see http://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html
 * @see http://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html
 * @see http://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html
 */
public class SQLExecutor implements Closeable {
    private static final Logger logger = LoggerFactory.getLogger(SQLExecutor.class);

    static final String ID = "id";

    static final String QUERY_WITH_DATA_SOURCE = "queryWithDataSource";

    private static final ResultExtractor EXISTS_RESULT_SET_EXTRACTOR = new ResultExtractor() {
        @Override
        public Boolean extractData(final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            JdbcUtil.skip(rs, jdbcSettings.getOffset());

            return rs.next();
        }
    };

    private static final ResultExtractor COUNT_RESULT_SET_EXTRACTOR = new ResultExtractor() {
        @Override
        public Integer extractData(final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            JdbcUtil.skip(rs, jdbcSettings.getOffset());

            int cnt = 0;

            while (rs.next()) {
                cnt++;
            }

            return cnt;
        }
    };

    private static final ResultExtractor SINGLE_BOOLEAN_EXTRACTOR = new ResultExtractor() {
        @Override
        public OptionalBoolean extractData(final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            JdbcUtil.skip(rs, jdbcSettings.getOffset());

            if (rs.next()) {
                return OptionalBoolean.of(rs.getBoolean(1));
            }

            return OptionalBoolean.empty();
        }
    };

    private static final ResultExtractor SINGLE_CHAR_EXTRACTOR = new ResultExtractor() {
        @Override
        public OptionalChar extractData(final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            JdbcUtil.skip(rs, jdbcSettings.getOffset());

            if (rs.next()) {
                final String str = rs.getString(1);

                return OptionalChar.of(str == null || str.length() == 0 ? N.CHAR_0 : str.charAt(0));
            }

            return OptionalChar.empty();
        }
    };

    private static final ResultExtractor SINGLE_BYTE_EXTRACTOR = new ResultExtractor() {
        @Override
        public OptionalByte extractData(final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            JdbcUtil.skip(rs, jdbcSettings.getOffset());

            if (rs.next()) {
                return OptionalByte.of(rs.getByte(1));
            }

            return OptionalByte.empty();
        }
    };

    private static final ResultExtractor SINGLE_SHORT_EXTRACTOR = new ResultExtractor() {
        @Override
        public OptionalShort extractData(final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            JdbcUtil.skip(rs, jdbcSettings.getOffset());

            if (rs.next()) {
                return OptionalShort.of(rs.getShort(1));
            }

            return OptionalShort.empty();
        }
    };

    private static final ResultExtractor SINGLE_INT_EXTRACTOR = new ResultExtractor() {
        @Override
        public OptionalInt extractData(final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            JdbcUtil.skip(rs, jdbcSettings.getOffset());

            if (rs.next()) {
                return OptionalInt.of(rs.getInt(1));
            }

            return OptionalInt.empty();
        }
    };

    private static final ResultExtractor SINGLE_LONG_EXTRACTOR = new ResultExtractor() {
        @Override
        public OptionalLong extractData(final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            JdbcUtil.skip(rs, jdbcSettings.getOffset());

            if (rs.next()) {
                return OptionalLong.of(rs.getLong(1));
            }

            return OptionalLong.empty();
        }
    };

    private static final ResultExtractor SINGLE_FLOAT_EXTRACTOR = new ResultExtractor() {
        @Override
        public OptionalFloat extractData(final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            JdbcUtil.skip(rs, jdbcSettings.getOffset());

            if (rs.next()) {
                return OptionalFloat.of(rs.getFloat(1));
            }

            return OptionalFloat.empty();
        }
    };

    private static final ResultExtractor SINGLE_DOUBLE_EXTRACTOR = new ResultExtractor() {
        @Override
        public OptionalDouble extractData(final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            JdbcUtil.skip(rs, jdbcSettings.getOffset());

            if (rs.next()) {
                return OptionalDouble.of(rs.getDouble(1));
            }

            return OptionalDouble.empty();
        }
    };

    private static final ResultExtractor> SINGLE_BIG_DECIMAL_EXTRACTOR = new ResultExtractor>() {
        @Override
        public Nullable extractData(final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            JdbcUtil.skip(rs, jdbcSettings.getOffset());

            if (rs.next()) {
                return Nullable.of(rs.getBigDecimal(1));
            }

            return Nullable.empty();
        }
    };

    private static final ResultExtractor> SINGLE_STRING_EXTRACTOR = new ResultExtractor>() {
        @Override
        public Nullable extractData(final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            JdbcUtil.skip(rs, jdbcSettings.getOffset());

            if (rs.next()) {
                return Nullable.of(rs.getString(1));
            }

            return Nullable.empty();
        }
    };

    private static final ResultExtractor> SINGLE_DATE_EXTRACTOR = new ResultExtractor>() {
        @Override
        public Nullable extractData(final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            JdbcUtil.skip(rs, jdbcSettings.getOffset());

            if (rs.next()) {
                return Nullable.of(rs.getDate(1));
            }

            return Nullable.empty();
        }
    };

    private static final ResultExtractor> SINGLE_TIME_EXTRACTOR = new ResultExtractor>() {
        @Override
        public Nullable extractData(final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            JdbcUtil.skip(rs, jdbcSettings.getOffset());

            if (rs.next()) {
                return Nullable.of(rs.getTime(1));
            }

            return Nullable.empty();
        }
    };

    private static final ResultExtractor> SINGLE_TIMESTAMP_EXTRACTOR = new ResultExtractor>() {
        @Override
        public Nullable extractData(final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            JdbcUtil.skip(rs, jdbcSettings.getOffset());

            if (rs.next()) {
                return Nullable.of(rs.getTimestamp(1));
            }

            return Nullable.empty();
        }
    };

    private static final ResultExtractor RESULT_SET_EXTRACTOR = new ResultExtractor() {
        @Override
        public ResultSet extractData(ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            return rs;
        }
    };

    private static final int factor = Math.min(Math.max(1, IOUtil.MAX_MEMORY_IN_MB / 1024), 8);
    private static final int CACHED_SQL_LENGTH = 1024 * factor;
    private static final int SQL_CACHE_SIZE = 1000 * factor;

    private static final Map> _sqlColumnLabelPool = new ConcurrentHashMap<>();
    private final Map> _tableColumnNamePool = new ConcurrentHashMap<>();

    private final DataSource _ds;
    private final DataSourceManager _dsm;
    private final DataSourceSelector _dss;
    private final JdbcSettings _jdbcSettings;
    private final SQLMapper _sqlMapper;
    private final NamingPolicy _namingPolicy;
    private final AsyncExecutor _asyncExecutor;
    private final boolean _isReadOnly;
    private final String _dbProudctName;
    private final String _dbProudctVersion;
    private final DBVersion _dbVersion;
    private final IsolationLevel _defaultIsolationLevel;
    private final AsyncSQLExecutor _asyncSQLExecutor;

    private final Map, Mapper> mapperPool = new ConcurrentHashMap<>();

    /**
     * 
     * @param dataSource
     * @see JdbcUtil#createDataSource(String)
     * @see JdbcUtil#createDataSource(java.io.InputStream)
     */
    public SQLExecutor(final javax.sql.DataSource dataSource) {
        this(dataSource, null);
    }

    /**
     * 
     * @param dataSource
     * @param jdbcSettings
     * @see JdbcUtil#createDataSource(String)
     * @see JdbcUtil#createDataSource(java.io.InputStream)
     */
    public SQLExecutor(final javax.sql.DataSource dataSource, final JdbcSettings jdbcSettings) {
        this(dataSource, jdbcSettings, null);
    }

    /**
     * 
     * @param dataSource
     * @param jdbcSettings
     * @param sqlMapper
     * @see JdbcUtil#createDataSource(String)
     * @see JdbcUtil#createDataSource(java.io.InputStream)
     */
    public SQLExecutor(final javax.sql.DataSource dataSource, final JdbcSettings jdbcSettings, final SQLMapper sqlMapper) {
        this(dataSource, jdbcSettings, sqlMapper, null);
    }

    /**
     * 
     * @param dataSource
     * @param jdbcSettings
     * @param sqlMapper
     * @param namingPolicy
     * @see JdbcUtil#createDataSourceManager(String)
     * @see JdbcUtil#createDataSourceManager(java.io.InputStream)
     */
    public SQLExecutor(final javax.sql.DataSource dataSource, final JdbcSettings jdbcSettings, final SQLMapper sqlMapper, final NamingPolicy namingPolicy) {
        this(dataSource, jdbcSettings, sqlMapper, namingPolicy, null);
    }

    /**
     * 
     * @param dataSource
     * @param jdbcSettings
     * @param sqlMapper
     * @param asyncExecutor
     * @see JdbcUtil#createDataSource(String)
     * @see JdbcUtil#createDataSource(java.io.InputStream)
     */
    public SQLExecutor(final javax.sql.DataSource dataSource, final JdbcSettings jdbcSettings, final SQLMapper sqlMapper, final NamingPolicy namingPolicy,
            final AsyncExecutor asyncExecutor) {
        this(null, JdbcUtil.wrap(dataSource), jdbcSettings, sqlMapper, namingPolicy, asyncExecutor, false);
    }

    /**
     * 
     * @param dataSourceManager
     * @see JdbcUtil#createDataSourceManager(String)
     * @see JdbcUtil#createDataSourceManager(java.io.InputStream)
     */
    public SQLExecutor(final DataSourceManager dataSourceManager) {
        this(dataSourceManager, null);
    }

    /**
     * 
     * @param dataSourceManager
     * @param jdbcSettings
     * @see JdbcUtil#createDataSourceManager(String)
     * @see JdbcUtil#createDataSourceManager(java.io.InputStream)
     */
    public SQLExecutor(final DataSourceManager dataSourceManager, final JdbcSettings jdbcSettings) {
        this(dataSourceManager, jdbcSettings, null);
    }

    /**
     * 
     * @param dataSourceManager
     * @param jdbcSettings
     * @param sqlMapper
     * @see JdbcUtil#createDataSourceManager(String)
     * @see JdbcUtil#createDataSourceManager(java.io.InputStream)
     */
    public SQLExecutor(final DataSourceManager dataSourceManager, final JdbcSettings jdbcSettings, final SQLMapper sqlMapper) {
        this(dataSourceManager, jdbcSettings, sqlMapper, null);
    }

    /**
     * 
     * @param dataSourceManager
     * @param jdbcSettings
     * @param sqlMapper
     * @param namingPolicy
     * @see JdbcUtil#createDataSourceManager(String)
     * @see JdbcUtil#createDataSourceManager(java.io.InputStream)
     */
    public SQLExecutor(final DataSourceManager dataSourceManager, final JdbcSettings jdbcSettings, final SQLMapper sqlMapper, final NamingPolicy namingPolicy) {
        this(dataSourceManager, jdbcSettings, sqlMapper, namingPolicy, null);
    }

    /**
     * 
     * @param dataSourceManager
     * @param jdbcSettings
     * @param sqlMapper
     * @param asyncExecutor
     * @see JdbcUtil#createDataSourceManager(String)
     * @see JdbcUtil#createDataSourceManager(java.io.InputStream)
     */
    public SQLExecutor(final DataSourceManager dataSourceManager, final JdbcSettings jdbcSettings, final SQLMapper sqlMapper, final NamingPolicy namingPolicy,
            final AsyncExecutor asyncExecutor) {
        this(dataSourceManager, null, jdbcSettings, sqlMapper, namingPolicy, asyncExecutor, false);
    }

    protected SQLExecutor(final DataSourceManager dataSourceManager, final DataSource dataSource, final JdbcSettings jdbcSettings, final SQLMapper sqlMapper,
            final NamingPolicy namingPolicy, final AsyncExecutor asyncExecutor, final boolean isReadOnly) {

        if (dataSourceManager == null) {
            this._ds = dataSource;
            this._dsm = null;
            this._dss = null;
        } else {
            this._ds = dataSourceManager.getPrimaryDataSource();
            this._dsm = dataSourceManager;
            this._dss = dataSourceManager.getDataSourceSelector();
        }

        this._jdbcSettings = (jdbcSettings == null) ? JdbcSettings.create() : jdbcSettings.copy();

        if (_jdbcSettings.getBatchSize() == 0) {
            _jdbcSettings.setBatchSize(JdbcSettings.DEFAULT_BATCH_SIZE);
        }

        _jdbcSettings.freeze();

        this._sqlMapper = sqlMapper == null ? new SQLMapper() : sqlMapper;
        this._namingPolicy = namingPolicy == null ? NamingPolicy.LOWER_CASE_WITH_UNDERSCORE : namingPolicy;
        this._asyncExecutor = asyncExecutor == null ? new AsyncExecutor(64, 300, TimeUnit.SECONDS) : asyncExecutor;
        this._isReadOnly = isReadOnly;

        int originalIsolationLevel;
        Connection conn = getConnection();

        try {
            _dbProudctName = conn.getMetaData().getDatabaseProductName();
            _dbProudctVersion = conn.getMetaData().getDatabaseProductVersion();
            _dbVersion = JdbcUtil.getDBVersion(conn);
            originalIsolationLevel = conn.getTransactionIsolation();
        } catch (SQLException e) {
            throw new UncheckedSQLException(e);
        } finally {
            closeQuietly(conn);
        }

        final IsolationLevel tmp = this._ds instanceof SQLDataSource ? ((SQLDataSource) this._ds).getDefaultIsolationLevel() : IsolationLevel.DEFAULT;
        _defaultIsolationLevel = tmp == IsolationLevel.DEFAULT ? IsolationLevel.valueOf(originalIsolationLevel) : tmp;

        this._asyncSQLExecutor = new AsyncSQLExecutor(this, _asyncExecutor);
    }

    //    public static SQLExecutor create(final String dataSourceFile) {
    //        return new SQLExecutor(JdbcUtil.createDataSourceManager(dataSourceFile));
    //    }
    //
    //    public static SQLExecutor create(final InputStream dataSourceInputStream) {
    //        return new SQLExecutor(JdbcUtil.createDataSourceManager(dataSourceInputStream));
    //    }
    //
    //    public static SQLExecutor create(final String url, final String user, final String password) {
    //        return new SQLExecutor(JdbcUtil.createDataSource(url, user, password));
    //    }
    //
    //    public static SQLExecutor create(final String driver, final String url, final String user, final String password) {
    //        return new SQLExecutor(JdbcUtil.createDataSource(driver, url, user, password));
    //    }
    //
    //    public static SQLExecutor create(final Class driverClass, final String url, final String user, final String password) {
    //        return new SQLExecutor(JdbcUtil.createDataSource(driverClass, url, user, password));
    //    }
    //
    //    /**
    //     * 
    //     * @param props refer to Connection.xsd for the supported properties.
    //     * @return
    //     */
    //    public static SQLExecutor create(final Map props) {
    //        return new SQLExecutor(JdbcUtil.createDataSource(props));
    //    }
    //
    //    public static SQLExecutor create(final javax.sql.DataSource sqlDataSource) {
    //        return new SQLExecutor(JdbcUtil.wrap(sqlDataSource));
    //    }

    //
    //    public SQLMapper sqlMapper() {
    //        return _sqlMapper;
    //    }

    @Beta
    public static SQLExecutor w(final String url, final String user, final String password) {
        return new SQLExecutor(JdbcUtil.createDataSource(url, user, password));
    }

    @Beta
    public static SQLExecutor w(final String driver, final String url, final String user, final String password) {
        return new SQLExecutor(JdbcUtil.createDataSource(driver, url, user, password));
    }

    @Beta
    public static SQLExecutor w(final Class driverClass, final String url, final String user, final String password) {
        return new SQLExecutor(JdbcUtil.createDataSource(driverClass, url, user, password));
    }

    public  Mapper mapper(final Class targetClass) {
        Mapper mapper = (Mapper) mapperPool.get(targetClass);

        if (mapper == null) {
            N.checkArgument(N.isEntity(targetClass), ClassUtil.getCanonicalClassName(targetClass) + " is not an entity class with getter/setter methods");

            SQLBuilder.registerEntityPropColumnNameMap(targetClass);

            mapper = new Mapper(targetClass, this, this._namingPolicy);
            mapperPool.put(targetClass, mapper);
        }

        return mapper;
    }

    //    /**
    //     * Create a Mapper which has the same life cycle as the specified Connection.
    //     * To start transaction for a Mapper:
    //     * 
    //     * 
    //     * final Transaction tran = sqlExecutor.beginTransaction(isolationLevel);
    //     * final ExMapper mapper = sqlExecutor.mapper(targetClass, tran.connection());
    //     * boolean isOk = false;
    //     * try {
    //     *     // Do something with sqlExecutor and mapper
    //     *     isOk = true;
    //     * } finally {
    //     *     if (isOk) {
    //     *         tran.commit();
    //     *     } else {
    //     *          tran.rollback();
    //     *     }  
    //     * }
    //     * 
    //     * 
    //     * 
    //     * @param targetClass
    //     * @param conn
    //     * @return
    //     */
    //    public  ExMapper mapper(final Class targetClass, final Connection conn) {
    //        if (conn == null) {
    //            return mapper(targetClass);
    //        }
    //
    //        return new ExMapper(conn, targetClass, this, this._namingPolicy);
    //    }

    public AsyncSQLExecutor async() {
        return _asyncSQLExecutor;
    }

    public DataSource dataSource() {
        return _ds;
    }

    public JdbcSettings jdbcSettings() {
        return _jdbcSettings;
    }

    public String dbProudctName() {
        return _dbProudctName;
    }

    public String dbProudctVersion() {
        return _dbProudctVersion;
    }

    public DBVersion dbVersion() {
        return _dbVersion;
    }

    @SafeVarargs
    public final  T insert(final String sql, final Object... parameters) throws UncheckedSQLException {
        return insert(sql, StatementSetter.DEFAULT, parameters);
    }

    @SafeVarargs
    public final  T insert(final String sql, final StatementSetter statementSetter, final Object... parameters) throws UncheckedSQLException {
        return insert(sql, statementSetter, null, parameters);
    }

    @SafeVarargs
    public final  T insert(final String sql, final JdbcSettings jdbcSettings, final Object... parameters) throws UncheckedSQLException {
        return insert(sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  T insert(final String sql, final StatementSetter statementSetter, final JdbcSettings jdbcSettings, final Object... parameters)
            throws UncheckedSQLException {
        return insert(null, sql, statementSetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  T insert(final Connection conn, final String sql, final Object... parameters) throws UncheckedSQLException {
        return insert(conn, sql, StatementSetter.DEFAULT, parameters);
    }

    @SafeVarargs
    public final  T insert(final Connection conn, final String sql, final StatementSetter statementSetter, final Object... parameters)
            throws UncheckedSQLException {
        return insert(conn, sql, statementSetter, null, parameters);
    }

    public final  T insert(final Connection conn, final String sql, final JdbcSettings jdbcSettings, final Object... parameters)
            throws UncheckedSQLException {
        return insert(conn, sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    /**
     * @see #batchInsert(Connection, String, StatementSetter, JdbcSettings, String, Object[])
     */
    @SuppressWarnings({ "unchecked", "deprecation" })
    @SafeVarargs
    public final  T insert(final Connection conn, final String sql, StatementSetter statementSetter, JdbcSettings jdbcSettings, final Object... parameters)
            throws UncheckedSQLException {
        final NamedSQL namedSQL = getNamedSQL(sql);
        statementSetter = checkStatementSetter(namedSQL, statementSetter);
        jdbcSettings = checkJdbcSettings(jdbcSettings, namedSQL, _sqlMapper.getAttrs(sql));

        String idPropName = checkGeneratedIdPropName(jdbcSettings);
        DataSource ds = null;
        Connection localConn = null;
        Object result = null;
        PreparedStatement stmt = null;

        try {
            ds = getDataSource(namedSQL.getPureSQL(), parameters, jdbcSettings);

            localConn = getConnection(conn, ds, jdbcSettings, SQLOperation.INSERT);

            stmt = prepareStatement(ds, localConn, namedSQL, statementSetter, jdbcSettings, true, false, parameters);

            result = executeInsert(namedSQL, stmt);
        } catch (SQLException e) {
            String msg = AbacusException.getErrorMsg(e) + ". [SQL] " + namedSQL.getNamedSQL();
            logger.error(msg);
            throw new UncheckedSQLException(msg, e);
        } finally {
            closeQuietly(stmt);
            closeQuietly(localConn, conn, ds);
        }

        if ((result != null) && isEntityOrMapParameter(namedSQL, parameters)) {
            Object parameter_0 = parameters[0];

            if (parameter_0 instanceof Map) {
                // // don't update input map ?
                // Map m = (Map) parameter_0;
                // Object idPropValue = m.get(generatedIdPropName);
                //
                // if ((idPropValue == null)
                // || (idPropValue instanceof Number
                // && (((Number) idPropValue).longValue() == 0))) {
                // m.put(generatedIdPropName, result);
                // }
            } else {
                final Object entity = parameter_0;

                try {
                    Method idGetMethod = ClassUtil.getPropGetMethod(entity.getClass(), idPropName);
                    Method idSetMethod = ClassUtil.getPropSetMethod(entity.getClass(), idPropName);

                    if ((idGetMethod != null) && (idSetMethod != null)) {
                        Object idPropValue = ClassUtil.getPropValue(entity, idGetMethod);

                        if ((idPropValue == null) || (idPropValue instanceof Number && (((Number) idPropValue).longValue() == 0))) {
                            ClassUtil.setPropValue(entity, idSetMethod, result);
                        }
                    } else {
                        if (logger.isWarnEnabled()) {
                            logger.warn("Failed to set the returned id property to entity. no get/set method for id property (" + idPropName + ") found. ");
                        }
                    }
                } catch (Exception e) {
                    logger.error("Failed to set the returned id property to entity", e);
                }

                if (entity instanceof DirtyMarker) {
                    ((DirtyMarker) entity).dirtyPropNames().clear();
                }
            }
        }

        return (T) result;
    }

    protected Object executeInsert(final NamedSQL namedSQL, final PreparedStatement stmt) throws SQLException {
        if (_isReadOnly) {
            throw new AbacusException("This SQL Executor is configured for read-only");
        }

        stmt.executeUpdate();

        Object id = null;
        ResultSet rs = null;

        try {
            rs = stmt.getGeneratedKeys();

            if (rs.next()) {
                id = JdbcUtil.getColumnValue(rs, 1);
            }
        } catch (SQLException e) {
            logger.error("Failed to retrieve the auto-generated Ids", e);
        } finally {
            closeQuietly(rs);
        }

        return id;
    }

    public  List batchInsert(final String sql, final List parametersList) throws UncheckedSQLException {
        return batchInsert(sql, StatementSetter.DEFAULT, parametersList);
    }

    public  List batchInsert(final String sql, final StatementSetter statementSetter, final List parametersList) throws UncheckedSQLException {
        return batchInsert(sql, statementSetter, null, parametersList);
    }

    public  List batchInsert(final String sql, final JdbcSettings jdbcSettings, final List parametersList) throws UncheckedSQLException {
        return batchInsert(sql, StatementSetter.DEFAULT, jdbcSettings, parametersList);
    }

    public  List batchInsert(final String sql, final StatementSetter statementSetter, final JdbcSettings jdbcSettings, final List parametersList)
            throws UncheckedSQLException {
        return batchInsert(null, sql, statementSetter, jdbcSettings, parametersList);
    }

    public  List batchInsert(final Connection conn, final String sql, final List parametersList) throws UncheckedSQLException {
        return batchInsert(conn, sql, StatementSetter.DEFAULT, parametersList);
    }

    public  List batchInsert(final Connection conn, final String sql, final StatementSetter statementSetter, final List parametersList)
            throws UncheckedSQLException {
        return batchInsert(conn, sql, statementSetter, null, parametersList);
    }

    public  List batchInsert(final Connection conn, final String sql, final JdbcSettings jdbcSettings, final List parametersList)
            throws UncheckedSQLException {
        return batchInsert(conn, sql, StatementSetter.DEFAULT, jdbcSettings, parametersList);
    }

    /**
     *
     * @see #batchInsert(Connection, String, StatementSetter, JdbcSettings, Object[])
     */
    @SuppressWarnings("deprecation")
    public  List batchInsert(final Connection conn, final String sql, StatementSetter statementSetter, JdbcSettings jdbcSettings,
            final List parametersList) throws UncheckedSQLException {
        final NamedSQL namedSQL = getNamedSQL(sql);
        statementSetter = checkStatementSetter(namedSQL, statementSetter);
        jdbcSettings = checkJdbcSettings(jdbcSettings, namedSQL, _sqlMapper.getAttrs(sql));

        String idPropName = checkGeneratedIdPropName(jdbcSettings);

        final int len = parametersList.size();
        final int batchSize = getBatchSize(jdbcSettings);

        List resultIdList = new ArrayList<>(len);

        DataSource ds = null;
        Connection localConn = null;
        PreparedStatement stmt = null;
        int originalIsolationLevel = 0;
        boolean autoCommit = true;

        try {
            ds = getDataSource(namedSQL.getPureSQL(), parametersList, jdbcSettings);

            localConn = getConnection(conn, ds, jdbcSettings, SQLOperation.INSERT);

            try {
                originalIsolationLevel = localConn.getTransactionIsolation();
                autoCommit = localConn.getAutoCommit();
            } catch (SQLException e) {
                closeQuietly(localConn, conn, ds);
                throw new UncheckedSQLException(e);
            }

            if ((conn == null) && (len > batchSize)) {
                localConn.setAutoCommit(false);

                setIsolationLevel(jdbcSettings, localConn);
            }

            stmt = prepareStatement(ds, localConn, namedSQL, statementSetter, jdbcSettings, true, true, parametersList);

            if (len <= batchSize) {
                for (int i = 0; i < len; i++) {
                    statementSetter.setParameters(namedSQL, stmt, N.asArray(parametersList.get(i)));
                    stmt.addBatch();
                }

                executeBatchInsert(resultIdList, namedSQL, stmt);
            } else {
                int num = 0;

                for (int i = 0; i < len; i++) {
                    statementSetter.setParameters(namedSQL, stmt, N.asArray(parametersList.get(i)));
                    stmt.addBatch();
                    num++;

                    if ((num % batchSize) == 0) {
                        executeBatchInsert(resultIdList, namedSQL, stmt);
                    }
                }

                if ((num % batchSize) > 0) {
                    executeBatchInsert(resultIdList, namedSQL, stmt);
                }
            }

            if ((conn == null) && (len > batchSize) && autoCommit == true) {
                localConn.commit();
            }
        } catch (SQLException e) {
            if ((conn == null) && (len > batchSize) && autoCommit == true) {
                if (logger.isWarnEnabled()) {
                    logger.warn("Trying to roll back ...");
                }

                try {
                    localConn.rollback();

                    if (logger.isWarnEnabled()) {
                        logger.warn("succeeded to roll back");
                    }
                } catch (SQLException e1) {
                    logger.error("Failed to roll back", e1);
                }
            }

            String msg = AbacusException.getErrorMsg(e) + ". [SQL] " + namedSQL.getNamedSQL();
            logger.error(msg);
            throw new UncheckedSQLException(msg, e);
        } finally {
            if ((conn == null) && (len > batchSize)) {
                try {
                    localConn.setAutoCommit(autoCommit);
                    localConn.setTransactionIsolation(originalIsolationLevel);
                } catch (SQLException e) {
                    logger.error("Failed to reset AutoCommit", e);
                }
            }

            closeQuietly(stmt);
            closeQuietly(localConn, conn, ds);
        }

        if (N.notNullOrEmpty(resultIdList)) {
            if (isEntityOrMapParameter(namedSQL, parametersList.get(0))) {
                if (resultIdList.size() == len) {
                    Object parameter_0 = parametersList.get(0);

                    if (parameter_0 instanceof Map) {
                        // // don't update input map ?
                        // Map m = null;
                        // Object idPropValue = null;
                        //
                        // for (int i = 0; i < parameters.length; i++) {
                        // m = (Map) (isTypedParameter
                        // ? ((TypedParameters) parameters[i]).parameters[0] :
                        // parameters[i]);
                        // idPropValue = m.get(generatedIdPropName);
                        //
                        // if ((idPropValue == null)
                        // || (idPropValue instanceof Number
                        // && (((Number) idPropValue).longValue() == 0))) {
                        // m.put(generatedIdPropName, resultList.get(i));
                        // }
                        // }
                    } else {
                        try {
                            Method idGetMethod = ClassUtil.getPropGetMethod(parameter_0.getClass(), idPropName);
                            Method idSetMethod = ClassUtil.getPropSetMethod(parameter_0.getClass(), idPropName);

                            if ((idGetMethod != null) && (idSetMethod != null)) {
                                Object entity = null;
                                Object idPropValue = null;

                                for (int i = 0; i < len; i++) {
                                    entity = parametersList.get(i);
                                    idPropValue = ClassUtil.invokeMethod(entity, idGetMethod);

                                    if ((idPropValue == null) || (idPropValue instanceof Number && (((Number) idPropValue).longValue() == 0))) {
                                        ClassUtil.setPropValue(entity, idSetMethod, resultIdList.get(i));
                                    }

                                    if (entity instanceof DirtyMarker) {
                                        ((DirtyMarker) entity).dirtyPropNames().clear();
                                    }
                                }
                            } else {
                                if (logger.isWarnEnabled()) {
                                    logger.warn(
                                            "Failed to set the returned id property to entity. no get/set method for id property (" + idPropName + ") found. ");
                                }
                            }
                        } catch (Exception e) {
                            logger.error("Failed to set the returned id property to entity", e);
                        }
                    }
                } else {
                    if (logger.isWarnEnabled()) {
                        logger.warn(
                                "Failed to set the returned id property to entity/map. because the size of returned key not equals the lenght of the input arrray");
                    }
                }
            }
        }

        return resultIdList;
    }

    private void setIsolationLevel(JdbcSettings jdbcSettings, Connection localConn) throws SQLException {
        final int isolationLevel = jdbcSettings.getIsolationLevel() == null || jdbcSettings.getIsolationLevel() == IsolationLevel.DEFAULT
                ? _defaultIsolationLevel.intValue() : jdbcSettings.getIsolationLevel().intValue();

        if (isolationLevel == localConn.getTransactionIsolation()) {
            // ignore.
        } else {
            localConn.setTransactionIsolation(isolationLevel);
        }
    }

    protected  void executeBatchInsert(final List resultIdList, final NamedSQL namedSQL, final PreparedStatement stmt) throws SQLException {
        if (_isReadOnly) {
            throw new AbacusException("This SQL Executor is configured for read-only");
        }

        stmt.executeBatch();

        ResultSet rs = null;

        try {
            rs = stmt.getGeneratedKeys();

            while (rs.next()) {
                resultIdList.add((T) JdbcUtil.getColumnValue(rs, 1));
            }
        } catch (SQLException e) {
            logger.error("Failed to retrieve the auto-generated Ids", e);
        } finally {
            closeQuietly(rs);
        }

        stmt.clearBatch();
    }

    @SafeVarargs
    public final int update(final String sql, final Object... parameters) throws UncheckedSQLException {
        return update(sql, StatementSetter.DEFAULT, parameters);
    }

    @SafeVarargs
    public final int update(final String sql, final StatementSetter statementSetter, final Object... parameters) throws UncheckedSQLException {
        return update(sql, statementSetter, null, parameters);
    }

    @SafeVarargs
    public final int update(final String sql, final JdbcSettings jdbcSettings, final Object... parameters) throws UncheckedSQLException {
        return update(sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final int update(final String sql, final StatementSetter statementSetter, final JdbcSettings jdbcSettings, final Object... parameters)
            throws UncheckedSQLException {
        return update(null, sql, statementSetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final int update(final Connection conn, final String sql, final Object... parameters) throws UncheckedSQLException {
        return update(conn, sql, StatementSetter.DEFAULT, parameters);
    }

    @SafeVarargs
    public final int update(final Connection conn, final String sql, final StatementSetter statementSetter, final Object... parameters)
            throws UncheckedSQLException {
        return update(conn, sql, statementSetter, null, parameters);
    }

    @SafeVarargs
    public final int update(final Connection conn, final String sql, final JdbcSettings jdbcSettings, final Object... parameters) throws UncheckedSQLException {
        return update(conn, sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    /**
     * @see #batchUpdate(Connection, String, StatementSetter, JdbcSettings, Object[])
     */
    @SafeVarargs
    public final int update(final Connection conn, final String sql, StatementSetter statementSetter, JdbcSettings jdbcSettings, final Object... parameters)
            throws UncheckedSQLException {
        final NamedSQL namedSQL = getNamedSQL(sql);
        statementSetter = checkStatementSetter(namedSQL, statementSetter);
        jdbcSettings = checkJdbcSettings(jdbcSettings, namedSQL, _sqlMapper.getAttrs(sql));

        DataSource ds = null;
        Connection localConn = null;
        PreparedStatement stmt = null;

        try {
            ds = getDataSource(namedSQL.getPureSQL(), parameters, jdbcSettings);

            localConn = getConnection(conn, ds, jdbcSettings, SQLOperation.UPDATE);

            stmt = prepareStatement(ds, localConn, namedSQL, statementSetter, jdbcSettings, false, false, parameters);

            return executeUpdate(namedSQL, stmt);
        } catch (SQLException e) {
            String msg = AbacusException.getErrorMsg(e) + ". [SQL] " + namedSQL.getNamedSQL();
            logger.error(msg);
            throw new UncheckedSQLException(msg, e);
        } finally {
            closeQuietly(stmt);
            closeQuietly(localConn, conn, ds);
        }
    }

    protected int executeUpdate(final NamedSQL namedSQL, final PreparedStatement stmt) throws SQLException {
        if (_isReadOnly) {
            throw new AbacusException("This SQL Executor is configured for read-only");
        }

        return stmt.executeUpdate();
    }

    public int batchUpdate(final String sql, final List parametersList) throws UncheckedSQLException {
        return batchUpdate(sql, StatementSetter.DEFAULT, parametersList);
    }

    public int batchUpdate(final String sql, final StatementSetter statementSetter, final List parametersList) throws UncheckedSQLException {
        return batchUpdate(sql, statementSetter, null, parametersList);
    }

    public int batchUpdate(final String sql, final JdbcSettings jdbcSettings, final List parametersList) throws UncheckedSQLException {
        return batchUpdate(sql, StatementSetter.DEFAULT, jdbcSettings, parametersList);
    }

    public int batchUpdate(final String sql, final StatementSetter statementSetter, final JdbcSettings jdbcSettings, final List parametersList)
            throws UncheckedSQLException {
        return batchUpdate(null, sql, statementSetter, jdbcSettings, parametersList);
    }

    public int batchUpdate(final Connection conn, final String sql, final List parametersList) throws UncheckedSQLException {
        return batchUpdate(conn, sql, StatementSetter.DEFAULT, parametersList);
    }

    public int batchUpdate(final Connection conn, final String sql, final StatementSetter statementSetter, final List parametersList)
            throws UncheckedSQLException {
        return batchUpdate(conn, sql, statementSetter, null, parametersList);
    }

    public int batchUpdate(final Connection conn, final String sql, final JdbcSettings jdbcSettings, final List parametersList)
            throws UncheckedSQLException {
        return batchUpdate(conn, sql, StatementSetter.DEFAULT, jdbcSettings, parametersList);
    }

    /**
     * @see #batchUpdate(Connection, String, StatementSetter, JdbcSettings, Object[])
     */
    public int batchUpdate(final Connection conn, final String sql, StatementSetter statementSetter, JdbcSettings jdbcSettings, final List parametersList)
            throws UncheckedSQLException {
        final NamedSQL namedSQL = getNamedSQL(sql);
        statementSetter = checkStatementSetter(namedSQL, statementSetter);
        jdbcSettings = checkJdbcSettings(jdbcSettings, namedSQL, _sqlMapper.getAttrs(sql));

        final int len = parametersList.size();
        final int batchSize = getBatchSize(jdbcSettings);

        DataSource ds = null;
        Connection localConn = null;
        PreparedStatement stmt = null;
        int originalIsolationLevel = 0;
        boolean autoCommit = true;

        try {
            ds = getDataSource(namedSQL.getPureSQL(), parametersList, jdbcSettings);

            localConn = getConnection(conn, ds, jdbcSettings, SQLOperation.UPDATE);

            try {
                originalIsolationLevel = localConn.getTransactionIsolation();
                autoCommit = localConn.getAutoCommit();
            } catch (SQLException e) {
                closeQuietly(localConn, conn, ds);
                throw new UncheckedSQLException(e);
            }

            if ((conn == null) && (len > batchSize)) {
                localConn.setAutoCommit(false);

                setIsolationLevel(jdbcSettings, localConn);
            }

            stmt = prepareStatement(ds, localConn, namedSQL, statementSetter, jdbcSettings, false, true, parametersList);

            int result = 0;

            if (len <= batchSize) {
                for (int i = 0; i < len; i++) {
                    statementSetter.setParameters(namedSQL, stmt, N.asArray(parametersList.get(i)));
                    stmt.addBatch();
                }

                result += executeBatchUpdate(namedSQL, stmt);
            } else {
                int num = 0;

                for (int i = 0; i < len; i++) {
                    statementSetter.setParameters(namedSQL, stmt, N.asArray(parametersList.get(i)));
                    stmt.addBatch();
                    num++;

                    if ((num % batchSize) == 0) {
                        result += executeBatchUpdate(namedSQL, stmt);
                    }
                }

                if ((num % batchSize) > 0) {
                    result += executeBatchUpdate(namedSQL, stmt);
                }
            }

            if ((conn == null) && (len > batchSize) && autoCommit == true) {
                localConn.commit();
            }

            return result;
        } catch (SQLException e) {
            if ((conn == null) && (len > batchSize) && autoCommit == true) {
                if (logger.isWarnEnabled()) {
                    logger.warn("Trying to roll back ...");
                }

                try {
                    localConn.rollback();

                    if (logger.isWarnEnabled()) {
                        logger.warn("succeeded to roll back");
                    }
                } catch (SQLException e1) {
                    logger.error("Failed to roll back", e1);
                }
            }

            String msg = AbacusException.getErrorMsg(e) + ". [SQL] " + namedSQL.getNamedSQL();
            logger.error(msg);
            throw new UncheckedSQLException(msg, e);
        } finally {
            if ((conn == null) && (len > batchSize)) {
                try {
                    localConn.setAutoCommit(autoCommit);
                    localConn.setTransactionIsolation(originalIsolationLevel);
                } catch (SQLException e) {
                    logger.error("Failed to reset AutoCommit", e);
                }
            }

            closeQuietly(stmt);
            closeQuietly(localConn, conn, ds);
        }
    }

    protected int executeBatchUpdate(final NamedSQL namedSQL, final PreparedStatement stmt) throws SQLException {
        if (_isReadOnly) {
            throw new AbacusException("This SQL Executor is configured for read-only");
        }

        final int[] results = stmt.executeBatch();
        stmt.clearBatch();

        if ((results == null) || (results.length == 0)) {
            return 0;
        }

        int sum = 0;

        for (int i = 0; i < results.length; i++) {
            sum += results[i];
        }

        return sum;
    }

    //    // mess up. To uncomment this method, also need to modify getNamingPolicy/setNamingPolicy in JdbcSettings.
    //    int update(final EntityId entityId, final Map props) {
    //        return update(null, entityId, props);
    //    }
    //
    //    // mess up. To uncomment this method, also need to modify getNamingPolicy/setNamingPolicy in JdbcSettings.
    //    int update(final Connection conn, final EntityId entityId, final Map props) {
    //        final Pair2 pair = generateUpdateSQL(entityId, props);
    //
    //        return update(conn, pair.sql, pair.parameters);
    //    }
    //
    //    private Pair2 generateUpdateSQL(final EntityId entityId, final Map props) {
    //        final Condition cond = EntityManagerUtil.entityId2Condition(entityId);
    //        final NamingPolicy namingPolicy = _jdbcSettings.getNamingPolicy();
    //
    //        if (namingPolicy == null) {
    //            return NE.update(entityId.entityName()).set(props).where(cond).pair();
    //        }
    //
    //        switch (namingPolicy) {
    //            case LOWER_CASE_WITH_UNDERSCORE: {
    //                return NE.update(entityId.entityName()).set(props).where(cond).pair();
    //            }
    //
    //            case UPPER_CASE_WITH_UNDERSCORE: {
    //                return NE2.update(entityId.entityName()).set(props).where(cond).pair();
    //            }
    //
    //            case CAMEL_CASE: {
    //                return NE3.update(entityId.entityName()).set(props).where(cond).pair();
    //            }
    //
    //            default:
    //                throw new IllegalArgumentException("Unsupported naming policy");
    //        }
    //    }
    //
    //    // mess up. To uncomment this method, also need to modify getNamingPolicy/setNamingPolicy in JdbcSettings.
    //    int delete(final EntityId entityId) {
    //        return delete(null, entityId);
    //    }
    //
    //    // mess up. To uncomment this method, also need to modify getNamingPolicy/setNamingPolicy in JdbcSettings.
    //    int delete(final Connection conn, final EntityId entityId) {
    //        final Pair2 pair = generateDeleteSQL(entityId);
    //
    //        return update(conn, pair.sql, pair.parameters);
    //    }
    //
    //    private Pair2 generateDeleteSQL(final EntityId entityId) {
    //        final Condition cond = EntityManagerUtil.entityId2Condition(entityId);
    //        final NamingPolicy namingPolicy = _jdbcSettings.getNamingPolicy();
    //
    //        if (namingPolicy == null) {
    //            return NE.deleteFrom(entityId.entityName()).where(cond).pair();
    //        }
    //
    //        switch (namingPolicy) {
    //            case LOWER_CASE_WITH_UNDERSCORE: {
    //                return NE.deleteFrom(entityId.entityName()).where(cond).pair();
    //            }
    //
    //            case UPPER_CASE_WITH_UNDERSCORE: {
    //                return NE2.deleteFrom(entityId.entityName()).where(cond).pair();
    //            }
    //
    //            case CAMEL_CASE: {
    //                return NE3.deleteFrom(entityId.entityName()).where(cond).pair();
    //            }
    //
    //            default:
    //                throw new IllegalArgumentException("Unsupported naming policy");
    //        }
    //    }
    //
    //    // mess up. To uncomment this method, also need to modify getNamingPolicy/setNamingPolicy in JdbcSettings.
    //    boolean exists(final EntityId entityId) {
    //        return exists(null, entityId);
    //    }
    //
    //    // mess up. To uncomment this method, also need to modify getNamingPolicy/setNamingPolicy in JdbcSettings.
    //    boolean exists(final Connection conn, final EntityId entityId) {
    //        final Pair2 pair = generateQuerySQL(entityId, NE._1_list);
    //
    //        return query(conn, pair.sql, StatementSetter.DEFAULT, EXISTS_RESULT_SET_EXTRACTOR, null, pair.parameters);
    //    }

    @SafeVarargs
    public final boolean exists(final String sql, final Object... parameters) {
        return exists(null, sql, parameters);
    }

    @SafeVarargs
    public final boolean exists(final Connection conn, final String sql, final Object... parameters) {
        return query(conn, sql, StatementSetter.DEFAULT, EXISTS_RESULT_SET_EXTRACTOR, null, parameters);
    }

    @SafeVarargs
    public final int count(final String sql, final Object... parameters) {
        return count(null, sql, parameters);
    }

    @SafeVarargs
    public final int count(final Connection conn, final String sql, final Object... parameters) {
        return query(conn, sql, StatementSetter.DEFAULT, COUNT_RESULT_SET_EXTRACTOR, null, parameters);
    }

    /**
     * 
     * @param targetClass
     * @param sql
     * @param parameters
     * @return
     * @throws DuplicatedResultException if two or more records are found.
     */
    @SafeVarargs
    public final  Optional get(final Class targetClass, final String sql, final Object... parameters) {
        return Optional.ofNullable(gett(targetClass, sql, parameters));
    }

    @SafeVarargs
    public final  Optional get(final Class targetClass, final String sql, final StatementSetter statementSetter, final Object... parameters) {
        return Optional.ofNullable(gett(targetClass, sql, statementSetter, parameters));
    }

    @SafeVarargs
    public final  Optional get(final Class targetClass, final String sql, final JdbcSettings jdbcSettings, final Object... parameters) {
        return Optional.ofNullable(gett(targetClass, sql, jdbcSettings, parameters));
    }

    /**
     * 
     * @param targetClass
     * @param sql
     * @param statementSetter
     * @param jdbcSettings
     * @param parameters
     * @return
     * @throws DuplicatedResultException if two or more records are found.
     */
    @SafeVarargs
    public final  Optional get(final Class targetClass, final String sql, final StatementSetter statementSetter, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return Optional.ofNullable(gett(targetClass, sql, statementSetter, jdbcSettings, parameters));
    }

    /**
     * 
     * @param targetClass
     * @param conn
     * @param sql
     * @param parameters
     * @return
     * @throws DuplicatedResultException if two or more records are found.
     */
    @SafeVarargs
    public final  Optional get(final Class targetClass, final Connection conn, final String sql, final Object... parameters) {
        return Optional.ofNullable(gett(targetClass, conn, sql, parameters));
    }

    @SafeVarargs
    public final  Optional get(final Class targetClass, final Connection conn, final String sql, final StatementSetter statementSetter,
            final Object... parameters) {
        return Optional.ofNullable(gett(targetClass, conn, sql, statementSetter, parameters));
    }

    @SafeVarargs
    public final  Optional get(final Class targetClass, final Connection conn, final String sql, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return Optional.ofNullable(gett(targetClass, conn, sql, jdbcSettings, parameters));
    }

    /**
     * 
     * @param targetClass
     * @param conn
     * @param sql
     * @param statementSetter
     * @param jdbcSettings
     * @param parameters
     * @return
     * @throws DuplicatedResultException if two or more records are found.
     */
    @SafeVarargs
    public final  Optional get(final Class targetClass, final Connection conn, final String sql, final StatementSetter statementSetter,
            JdbcSettings jdbcSettings, final Object... parameters) {
        return Optional.ofNullable(gett(targetClass, conn, sql, statementSetter, jdbcSettings, parameters));
    }

    /**
     * v
     * @param sql
     * @param recordGetter
     * @param parameters
     * @return
     * @throws DuplicatedResultException if two or more records are found.
     */
    @SafeVarargs
    public final  Optional get(final String sql, final JdbcUtil.RecordGetter recordGetter, final Object... parameters) {
        return Optional.ofNullable(gett(sql, recordGetter, parameters));
    }

    public final  Optional get(final String sql, final StatementSetter statementSetter, final JdbcUtil.RecordGetter recordGetter,
            final Object... parameters) {
        return Optional.ofNullable(gett(sql, statementSetter, recordGetter, parameters));
    }

    public final  Optional get(final String sql, final JdbcUtil.RecordGetter recordGetter, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return Optional.ofNullable(gett(sql, recordGetter, jdbcSettings, parameters));
    }

    /**
     *  
     * @param sql
     * @param statementSetter
     * @param recordGetter
     * @param jdbcSettings
     * @param parameters
     * @return
     * @throws DuplicatedResultException if two or more records are found.
     */
    @SafeVarargs
    public final  Optional get(final String sql, final StatementSetter statementSetter, final JdbcUtil.RecordGetter recordGetter,
            final JdbcSettings jdbcSettings, final Object... parameters) {
        return Optional.ofNullable(gett(sql, statementSetter, recordGetter, jdbcSettings, parameters));
    }

    /**
     *  
     * @param conn
     * @param sql
     * @param recordGetter
     * @param parameters
     * @return
     * @throws DuplicatedResultException if two or more records are found.
     */
    @SafeVarargs
    public final  Optional get(final Connection conn, final String sql, final JdbcUtil.RecordGetter recordGetter,
            final Object... parameters) {
        return Optional.ofNullable(gett(conn, sql, recordGetter, parameters));
    }

    @SafeVarargs
    public final  Optional get(final Connection conn, final String sql, final StatementSetter statementSetter,
            final JdbcUtil.RecordGetter recordGetter, final Object... parameters) {
        return Optional.ofNullable(gett(conn, sql, statementSetter, recordGetter, parameters));
    }

    @SafeVarargs
    public final  Optional get(final Connection conn, final String sql, final JdbcUtil.RecordGetter recordGetter,
            JdbcSettings jdbcSettings, final Object... parameters) {
        return Optional.ofNullable(gett(conn, sql, recordGetter, jdbcSettings, parameters));
    }

    /**
     *  
     * @param conn
     * @param sql
     * @param statementSetter
     * @param recordGetter
     * @param jdbcSettings
     * @param parameters
     * @return
     * @throws DuplicatedResultException if two or more records are found.
     */
    @SafeVarargs
    public final  Optional get(final Connection conn, final String sql, final StatementSetter statementSetter,
            final JdbcUtil.RecordGetter recordGetter, final JdbcSettings jdbcSettings, final Object... parameters) {
        return Optional.ofNullable(gett(conn, sql, statementSetter, recordGetter, jdbcSettings, parameters));
    }

    @SafeVarargs
    public final  T gett(final Class targetClass, final String sql, final Object... parameters) {
        return gett(targetClass, sql, StatementSetter.DEFAULT, parameters);
    }

    @SafeVarargs
    public final  T gett(final Class targetClass, final String sql, final StatementSetter statementSetter, final Object... parameters) {
        return gett(targetClass, sql, statementSetter, null, parameters);
    }

    @SafeVarargs
    public final  T gett(final Class targetClass, final String sql, final JdbcSettings jdbcSettings, final Object... parameters) {
        return gett(targetClass, sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  T gett(final Class targetClass, final String sql, final StatementSetter statementSetter, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return gett(targetClass, null, sql, statementSetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  T gett(final Class targetClass, final Connection conn, final String sql, final Object... parameters) {
        return gett(targetClass, conn, sql, StatementSetter.DEFAULT, parameters);
    }

    @SafeVarargs
    public final  T gett(final Class targetClass, final Connection conn, final String sql, final StatementSetter statementSetter,
            final Object... parameters) {
        return gett(targetClass, conn, sql, statementSetter, null, parameters);
    }

    @SafeVarargs
    public final  T gett(final Class targetClass, final Connection conn, final String sql, final JdbcSettings jdbcSettings, final Object... parameters) {
        return gett(targetClass, conn, sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    /**
     *
     * @param targetClass
     * @param conn
     * @param sql
     * @param statementSetter
     * @param jdbcSettings
     * @param parameters
     * @return
     * @throws DuplicatedResultException if two or more records are found.
     */
    @SuppressWarnings("unchecked")
    @SafeVarargs
    public final  T gett(final Class targetClass, final Connection conn, final String sql, final StatementSetter statementSetter,
            JdbcSettings jdbcSettings, final Object... parameters) {
        N.checkArgNotNull(targetClass, "targetClass");

        final JdbcUtil.RecordGetter recordGetter = new JdbcUtil.RecordGetter() {
            private final BiRecordGetter biRecordGetter = BiRecordGetter.to(targetClass);

            @Override
            public T apply(ResultSet rs) throws SQLException {
                return biRecordGetter.apply(rs, JdbcUtil.getColumnLabelList(rs));
            }
        };

        return gett(conn, sql, statementSetter, recordGetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  T gett(final String sql, final JdbcUtil.RecordGetter recordGetter, final Object... parameters) {
        return gett(sql, StatementSetter.DEFAULT, recordGetter, parameters);
    }

    @SafeVarargs
    public final  T gett(final String sql, final StatementSetter statementSetter, final JdbcUtil.RecordGetter recordGetter,
            final Object... parameters) {
        return gett(sql, statementSetter, recordGetter, null, parameters);
    }

    @SafeVarargs
    public final  T gett(final String sql, final JdbcUtil.RecordGetter recordGetter, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return gett(sql, StatementSetter.DEFAULT, recordGetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  T gett(final String sql, final StatementSetter statementSetter, final JdbcUtil.RecordGetter recordGetter,
            final JdbcSettings jdbcSettings, final Object... parameters) {
        return gett(null, sql, statementSetter, recordGetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  T gett(final Connection conn, final String sql, final JdbcUtil.RecordGetter recordGetter,
            final Object... parameters) {
        return gett(conn, sql, StatementSetter.DEFAULT, recordGetter, parameters);
    }

    public final  T gett(final Connection conn, final String sql, final StatementSetter statementSetter,
            final JdbcUtil.RecordGetter recordGetter, final Object... parameters) {
        return gett(conn, sql, statementSetter, recordGetter, null, parameters);
    }

    public final  T gett(final Connection conn, final String sql, final JdbcUtil.RecordGetter recordGetter, JdbcSettings jdbcSettings,
            final Object... parameters) {
        return gett(conn, sql, StatementSetter.DEFAULT, recordGetter, jdbcSettings, parameters);
    }

    /**
     *v
     * @param conn
     * @param sql
     * @param statementSetter
     * @param recordGetter
     * @param jdbcSettings
     * @param parameters
     * @return
     * @throws DuplicatedResultException if two or more records are found.
     */
    @SuppressWarnings("unchecked")
    @SafeVarargs
    public final  T gett(final Connection conn, final String sql, final StatementSetter statementSetter,
            final JdbcUtil.RecordGetter recordGetter, JdbcSettings jdbcSettings, final Object... parameters) {
        N.checkArgNotNull(recordGetter, "recordGetter");

        final ResultExtractor resultExtractor = new ResultExtractor() {
            @Override
            public T extractData(ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
                int offset = jdbcSettings.getOffset();

                if (offset > 0) {
                    JdbcUtil.skip(rs, offset);
                }

                T result = null;

                if (rs.next()) {
                    result = Objects.requireNonNull(recordGetter.apply(rs));

                    if (rs.next()) {
                        throw new DuplicatedResultException("More than one records found by sql: " + sql);
                    }
                }

                return result;
            }
        };

        return query(conn, sql, statementSetter, resultExtractor, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  Optional findFirst(final Class targetClass, final String sql, final Object... parameters) {
        return findFirst(targetClass, sql, StatementSetter.DEFAULT, parameters);
    }

    @SafeVarargs
    public final  Optional findFirst(final Class targetClass, final String sql, final StatementSetter statementSetter, final Object... parameters) {
        return findFirst(targetClass, sql, statementSetter, null, parameters);
    }

    @SafeVarargs
    public final  Optional findFirst(final Class targetClass, final String sql, final JdbcSettings jdbcSettings, final Object... parameters) {
        return findFirst(targetClass, sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  Optional findFirst(final Class targetClass, final String sql, final StatementSetter statementSetter, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return findFirst(targetClass, null, sql, statementSetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  Optional findFirst(final Class targetClass, final Connection conn, final String sql, final Object... parameters) {
        return findFirst(targetClass, conn, sql, StatementSetter.DEFAULT, parameters);
    }

    public final  Optional findFirst(final Class targetClass, final Connection conn, final String sql, final StatementSetter statementSetter,
            final Object... parameters) {
        return findFirst(targetClass, conn, sql, statementSetter, null, parameters);
    }

    public final  Optional findFirst(final Class targetClass, final Connection conn, final String sql, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return findFirst(targetClass, conn, sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    /**
     * Just fetch the result in the 1st row. {@code null} is returned if no result is found. This method will try to
     * convert the column value to the type of mapping entity property if the mapping entity property is not assignable
     * from column value.
     *
     * Remember to add {@code limit} condition if big result will be returned by the query.
     *
     * @param targetClass
     * @param conn
     * @param sql
     * @param statementSetter
     * @param jdbcSettings
     * @param parameters
     * @return
     */
    @SuppressWarnings("unchecked")
    @SafeVarargs
    public final  Optional findFirst(final Class targetClass, final Connection conn, final String sql, final StatementSetter statementSetter,
            final JdbcSettings jdbcSettings, final Object... parameters) {
        N.checkArgNotNull(targetClass, "targetClass");

        final JdbcUtil.RecordGetter recordGetter = new JdbcUtil.RecordGetter() {
            private final BiRecordGetter biRecordGetter = BiRecordGetter.to(targetClass);

            @Override
            public T apply(ResultSet rs) throws SQLException {
                return biRecordGetter.apply(rs, JdbcUtil.getColumnLabelList(rs));
            }
        };

        return findFirst(conn, sql, statementSetter, recordGetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  Optional findFirst(final String sql, final JdbcUtil.RecordGetter recordGetter, final Object... parameters) {
        return findFirst(sql, StatementSetter.DEFAULT, recordGetter, parameters);
    }

    @SafeVarargs
    public final  Optional findFirst(final String sql, final StatementSetter statementSetter,
            final JdbcUtil.RecordGetter recordGetter, final Object... parameters) {
        return findFirst(sql, statementSetter, recordGetter, null, parameters);
    }

    @SafeVarargs
    public final  Optional findFirst(final String sql, final JdbcUtil.RecordGetter recordGetter, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return findFirst(sql, StatementSetter.DEFAULT, recordGetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  Optional findFirst(final String sql, final StatementSetter statementSetter,
            final JdbcUtil.RecordGetter recordGetter, final JdbcSettings jdbcSettings, final Object... parameters) {
        return findFirst(null, sql, statementSetter, recordGetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  Optional findFirst(final Connection conn, final String sql, final JdbcUtil.RecordGetter recordGetter,
            final Object... parameters) {
        return findFirst(conn, sql, StatementSetter.DEFAULT, recordGetter, parameters);
    }

    public final  Optional findFirst(final Connection conn, final String sql, final StatementSetter statementSetter,
            final JdbcUtil.RecordGetter recordGetter, final Object... parameters) {
        return findFirst(conn, sql, statementSetter, recordGetter, null, parameters);
    }

    public final  Optional findFirst(final Connection conn, final String sql, final JdbcUtil.RecordGetter recordGetter,
            final JdbcSettings jdbcSettings, final Object... parameters) {
        return findFirst(conn, sql, StatementSetter.DEFAULT, recordGetter, jdbcSettings, parameters);
    }

    /**
     *
     * Remember to add {@code limit} condition if big result will be returned by the query.
     *
     * @param conn
     * @param sql
     * @param statementSetter
     * @param recordGetter
     * @param jdbcSettings
     * @param parameters
     * @return
     */
    @SuppressWarnings("unchecked")
    @SafeVarargs
    public final  Optional findFirst(final Connection conn, final String sql, final StatementSetter statementSetter,
            final JdbcUtil.RecordGetter recordGetter, final JdbcSettings jdbcSettings, final Object... parameters) {
        N.checkArgNotNull(recordGetter, "recordGetter");

        final ResultExtractor resultExtractor = new ResultExtractor() {
            @Override
            public T extractData(ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
                int offset = jdbcSettings.getOffset();

                if (offset > 0) {
                    JdbcUtil.skip(rs, offset);
                }

                return rs.next() ? Objects.requireNonNull(recordGetter.apply(rs)) : null;
            }
        };

        return Optional.ofNullable(query(conn, sql, statementSetter, resultExtractor, jdbcSettings, parameters));
    }

    @SafeVarargs
    public final  List list(final Class targetClass, final String sql, final Object... parameters) {
        return list(targetClass, sql, StatementSetter.DEFAULT, parameters);
    }

    @SafeVarargs
    public final  List list(final Class targetClass, final String sql, final StatementSetter statementSetter, final Object... parameters) {
        return list(targetClass, sql, statementSetter, null, parameters);
    }

    @SafeVarargs
    public final  List list(final Class targetClass, final String sql, final JdbcSettings jdbcSettings, final Object... parameters) {
        return list(targetClass, sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  List list(final Class targetClass, final String sql, final StatementSetter statementSetter, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return list(targetClass, null, sql, statementSetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  List list(final Class targetClass, final Connection conn, final String sql, final Object... parameters) {
        return list(targetClass, conn, sql, StatementSetter.DEFAULT, parameters);
    }

    public final  List list(final Class targetClass, final Connection conn, final String sql, final StatementSetter statementSetter,
            final Object... parameters) {
        return list(targetClass, conn, sql, statementSetter, null, parameters);
    }

    public final  List list(final Class targetClass, final Connection conn, final String sql, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return list(targetClass, conn, sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    /**
     *
     * @param targetClass
     * @param conn
     * @param sql
     * @param statementSetter
     * @param jdbcSettings
     * @param parameters
     * @return
     */
    @SuppressWarnings("unchecked")
    @SafeVarargs
    public final  List list(final Class targetClass, final Connection conn, final String sql, final StatementSetter statementSetter,
            final JdbcSettings jdbcSettings, final Object... parameters) {
        final JdbcUtil.BiRecordGetter biRecordGetter = BiRecordGetter.to(targetClass);

        return list(conn, sql, statementSetter, biRecordGetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  List list(final String sql, final JdbcUtil.BiRecordGetter recordGetter, final Object... parameters) {
        return list(sql, StatementSetter.DEFAULT, recordGetter, parameters);
    }

    @SafeVarargs
    public final  List list(final String sql, final StatementSetter statementSetter, final JdbcUtil.BiRecordGetter recordGetter,
            final Object... parameters) {
        return list(sql, statementSetter, recordGetter, null, parameters);
    }

    @SafeVarargs
    public final  List list(final String sql, final JdbcUtil.BiRecordGetter recordGetter, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return list(sql, StatementSetter.DEFAULT, recordGetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  List list(final String sql, final StatementSetter statementSetter, final JdbcUtil.BiRecordGetter recordGetter,
            final JdbcSettings jdbcSettings, final Object... parameters) {
        return list(null, sql, statementSetter, recordGetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  List list(final Connection conn, final String sql, final JdbcUtil.BiRecordGetter recordGetter,
            final Object... parameters) {
        return list(conn, sql, StatementSetter.DEFAULT, recordGetter, parameters);
    }

    public final  List list(final Connection conn, final String sql, final StatementSetter statementSetter,
            final JdbcUtil.BiRecordGetter recordGetter, final Object... parameters) {
        return list(conn, sql, statementSetter, recordGetter, null, parameters);
    }

    public final  List list(final Connection conn, final String sql, final JdbcUtil.BiRecordGetter recordGetter,
            final JdbcSettings jdbcSettings, final Object... parameters) {
        return list(conn, sql, StatementSetter.DEFAULT, recordGetter, jdbcSettings, parameters);
    }

    /**
     *
     * @param conn
     * @param sql
     * @param statementSetter
     * @param recordGetter
     * @param jdbcSettings
     * @param parameters
     * @return
     */
    @SuppressWarnings("unchecked")
    @SafeVarargs
    public final  List list(final Connection conn, final String sql, final StatementSetter statementSetter,
            final JdbcUtil.BiRecordGetter recordGetter, final JdbcSettings jdbcSettings, final Object... parameters) {
        N.checkArgNotNull(recordGetter);

        final ResultExtractor> resultExtractor = new ResultExtractor>() {
            @Override
            public List extractData(ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
                int offset = jdbcSettings.getOffset();
                int count = jdbcSettings.getCount();

                if (offset > 0) {
                    JdbcUtil.skip(rs, offset);
                }

                final List result = new ArrayList<>(N.min(count, 16));
                final List columnLabels = JdbcUtil.getColumnLabelList(rs);

                while (count-- > 0 && rs.next()) {
                    result.add(recordGetter.apply(rs, columnLabels));
                }

                return result;
            }
        };

        return query(conn, sql, statementSetter, resultExtractor, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  List listAll(final Class targetClass, final String sql, final JdbcSettings jdbcSettings, final Object... parameters) {
        return listAll(targetClass, sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  List listAll(final Class targetClass, final String sql, final StatementSetter statementSetter, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        final JdbcUtil.BiRecordGetter biRecordGetter = BiRecordGetter.to(targetClass);

        return listAll(sql, statementSetter, biRecordGetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  List listAll(final Class targetClass, final List sqls, final JdbcSettings jdbcSettings, final Object... parameters) {
        return listAll(targetClass, sqls, null, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  List listAll(final Class targetClass, final List sqls, final StatementSetter statementSetter,
            final JdbcSettings jdbcSettings, final Object... parameters) {
        final JdbcUtil.BiRecordGetter biRecordGetter = BiRecordGetter.to(targetClass);

        return listAll(sqls, statementSetter, biRecordGetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  List listAll(final String sql, final JdbcUtil.BiRecordGetter recordGetter, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return listAll(sql, StatementSetter.DEFAULT, recordGetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  List listAll(final String sql, final StatementSetter statementSetter, final JdbcUtil.BiRecordGetter recordGetter,
            final JdbcSettings jdbcSettings, final Object... parameters) {
        checkJdbcSettingsForAllQuery(jdbcSettings);

        if (jdbcSettings == null || N.isNullOrEmpty(jdbcSettings.getQueryWithDataSources())) {
            return list(sql, statementSetter, recordGetter, jdbcSettings, parameters);
        }

        final Collection dss = jdbcSettings.getQueryWithDataSources();
        List> resultList = null;

        if (jdbcSettings.isQueryInParallel()) {
            resultList = Stream.of(dss).map(new Function() {
                @Override
                public JdbcSettings apply(String ds) {
                    final JdbcSettings newJdbcSettings = jdbcSettings.copy();
                    newJdbcSettings.setQueryWithDataSources(null);
                    newJdbcSettings.setQueryWithDataSource(ds);
                    return newJdbcSettings;
                }
            }).parallel(dss.size()).map(new Function>() {
                @Override
                public List apply(JdbcSettings newJdbcSettings) {
                    return list(sql, statementSetter, recordGetter, newJdbcSettings, parameters);
                }
            }).toList();
        } else {
            final JdbcSettings newJdbcSettings = jdbcSettings.copy();
            newJdbcSettings.setQueryWithDataSources(null);
            resultList = new ArrayList<>(dss.size());

            for (String ds : dss) {
                newJdbcSettings.setQueryWithDataSource(ds);
                resultList.add(list(sql, statementSetter, recordGetter, newJdbcSettings, parameters));
            }
        }

        return N.concat(resultList);
    }

    private void checkJdbcSettingsForAllQuery(JdbcSettings jdbcSettings) {
        if (jdbcSettings != null && (jdbcSettings.getOffset() != 0 || jdbcSettings.getCount() != Integer.MAX_VALUE)) {
            throw new IllegalArgumentException("Can't set 'offset' or 'count' for findAll/queryAll/streamAll methods");
        }
    }

    @SafeVarargs
    public final  List listAll(final List sqls, final JdbcUtil.BiRecordGetter recordGetter, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return listAll(sqls, null, recordGetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  List listAll(final List sqls, final StatementSetter statementSetter,
            final JdbcUtil.BiRecordGetter recordGetter, final JdbcSettings jdbcSettings, final Object... parameters) {
        if (sqls.size() == 1) {
            return listAll(sqls.get(0), statementSetter, recordGetter, jdbcSettings, parameters);
        }

        List> resultList = null;

        if (jdbcSettings != null && jdbcSettings.isQueryInParallel()) {
            resultList = Stream.of(sqls).parallel(sqls.size()).map(new Function>() {
                @Override
                public List apply(String sql) {
                    return listAll(sql, statementSetter, recordGetter, jdbcSettings, parameters);
                }
            }).toList();
        } else {
            resultList = new ArrayList<>(sqls.size());

            for (String sql : sqls) {
                resultList.add(listAll(sql, statementSetter, recordGetter, jdbcSettings, parameters));
            }
        }

        return N.concat(resultList);
    }

    /**
     * @see SQLExecutor#queryForSingleResult(Class, Connection, String, Object...).
     */
    @SafeVarargs
    public final OptionalBoolean queryForBoolean(final String sql, final Object... parameters) {
        return query(sql, StatementSetter.DEFAULT, SINGLE_BOOLEAN_EXTRACTOR, null, parameters);
    }

    /**
     *
     * @see SQLExecutor#queryForSingleResult(Class, Connection, String, Object...).
     */
    @SafeVarargs
    public final OptionalChar queryForChar(final String sql, final Object... parameters) {
        return query(sql, StatementSetter.DEFAULT, SINGLE_CHAR_EXTRACTOR, null, parameters);
    }

    /**
     * @see SQLExecutor#queryForSingleResult(Class, Connection, String, Object...).
     */
    @SafeVarargs
    public final OptionalByte queryForByte(final String sql, final Object... parameters) {
        return query(sql, StatementSetter.DEFAULT, SINGLE_BYTE_EXTRACTOR, null, parameters);
    }

    /**
     * @see SQLExecutor#queryForSingleResult(Class, Connection, String, Object...).
     */
    @SafeVarargs
    public final OptionalShort queryForShort(final String sql, final Object... parameters) {
        return query(sql, StatementSetter.DEFAULT, SINGLE_SHORT_EXTRACTOR, null, parameters);
    }

    /**
     * @see SQLExecutor#queryForSingleResult(Class, Connection, String, Object...).
     */
    @SafeVarargs
    public final OptionalInt queryForInt(final String sql, final Object... parameters) {
        return query(sql, StatementSetter.DEFAULT, SINGLE_INT_EXTRACTOR, null, parameters);
    }

    /**
     * @see SQLExecutor#queryForSingleResult(Class, Connection, String, Object...).
     */
    @SafeVarargs
    public final OptionalLong queryForLong(final String sql, final Object... parameters) {
        return query(sql, StatementSetter.DEFAULT, SINGLE_LONG_EXTRACTOR, null, parameters);
    }

    /**
     * @see SQLExecutor#queryForSingleResult(Class, Connection, String, Object...).
     */
    @SafeVarargs
    public final OptionalFloat queryForFloat(final String sql, final Object... parameters) {
        return query(sql, StatementSetter.DEFAULT, SINGLE_FLOAT_EXTRACTOR, null, parameters);
    }

    /**
     * @see SQLExecutor#queryForSingleResult(Class, Connection, String, Object...).
     */
    @SafeVarargs
    public final OptionalDouble queryForDouble(final String sql, final Object... parameters) {
        return query(sql, StatementSetter.DEFAULT, SINGLE_DOUBLE_EXTRACTOR, null, parameters);
    }

    /**
     * @see SQLExecutor#queryForSingleResult(Class, Connection, String, Object...).
     */
    @SafeVarargs
    public final Nullable queryForBigDecimal(final String sql, final Object... parameters) {
        return query(sql, StatementSetter.DEFAULT, SINGLE_BIG_DECIMAL_EXTRACTOR, null, parameters);
    }

    /**
     * @see SQLExecutor#queryForSingleResult(Class, Connection, String, Object...).
     */
    @SafeVarargs
    public final Nullable queryForString(final String sql, final Object... parameters) {
        return query(sql, StatementSetter.DEFAULT, SINGLE_STRING_EXTRACTOR, null, parameters);
    }

    /**
     * @see SQLExecutor#queryForSingleResult(Class, String, Object...).
     */
    @SafeVarargs
    public final Nullable queryForDate(final String sql, final Object... parameters) {
        return query(sql, StatementSetter.DEFAULT, SINGLE_DATE_EXTRACTOR, null, parameters);
    }

    /**
     * @see SQLExecutor#queryForSingleResult(Class, String, Object...).
     */
    @SafeVarargs
    public final Nullable queryForTime(final String sql, final Object... parameters) {
        return query(sql, StatementSetter.DEFAULT, SINGLE_TIME_EXTRACTOR, null, parameters);
    }

    /**
    * @see SQLExecutor#queryForSingleResult(Class, String, Object...).
    */
    @SafeVarargs
    public final Nullable queryForTimestamp(final String sql, final Object... parameters) {
        return query(sql, StatementSetter.DEFAULT, SINGLE_TIMESTAMP_EXTRACTOR, null, parameters);
    }

    @SafeVarargs
    public final  Nullable queryForSingleResult(final Class targetClass, final String sql, final Object... parameters) {
        return queryForSingleResult(targetClass, sql, StatementSetter.DEFAULT, parameters);
    }

    @SafeVarargs
    public final  Nullable queryForSingleResult(final Class targetClass, final String sql, final StatementSetter statementSetter,
            final Object... parameters) {
        return queryForSingleResult(targetClass, sql, statementSetter, null, parameters);
    }

    @SafeVarargs
    public final  Nullable queryForSingleResult(final Class targetClass, final String sql, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return queryForSingleResult(targetClass, sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  Nullable queryForSingleResult(final Class targetClass, final String sql, final StatementSetter statementSetter,
            final JdbcSettings jdbcSettings, final Object... parameters) {
        return queryForSingleResult(targetClass, null, sql, statementSetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  Nullable queryForSingleResult(final Class targetClass, final Connection conn, final String sql, final Object... parameters) {
        return queryForSingleResult(targetClass, conn, sql, StatementSetter.DEFAULT, parameters);
    }

    public final  Nullable queryForSingleResult(final Class targetClass, final Connection conn, final String sql,
            final StatementSetter statementSetter, final Object... parameters) {
        return queryForSingleResult(targetClass, conn, sql, statementSetter, null, parameters);
    }

    public final  Nullable queryForSingleResult(final Class targetClass, final Connection conn, final String sql, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return queryForSingleResult(targetClass, conn, sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    /**
     * Returns a {@code Nullable} describing the value in the first row/column if it exists, otherwise return an empty {@code Nullable}.
     * 

     *
     * Special note for type conversion for {@code boolean} or {@code Boolean} type: {@code true} is returned if the
     * {@code String} value of the target column is {@code "true"}, case insensitive. or it's an integer with value > 0.
     * Otherwise, {@code false} is returned.
     *
     * Remember to add {@code limit} condition if big result will be returned by the query.
     *
     * @param targetClass
     *            set result type to avoid the NullPointerException if result is null and T is primitive type
     *            "int, long. short ... char, boolean..".
     * @param conn
     * @param sql
     * @param statementSetter
     * @param jdbcSettings
     * @param parameters
     */
    @SuppressWarnings("unchecked")
    @SafeVarargs
    public final  Nullable queryForSingleResult(final Class targetClass, final Connection conn, final String sql,
            final StatementSetter statementSetter, final JdbcSettings jdbcSettings, final Object... parameters) {
        return query(conn, sql, statementSetter, createSingleResultExtractor(targetClass), jdbcSettings, parameters);
    }

    private final ObjectPool, ResultExtractor>> singleResultExtractorPool = new ObjectPool<>(64);

    private  ResultExtractor> createSingleResultExtractor(final Class targetClass) {
        @SuppressWarnings("rawtypes")
        ResultExtractor result = singleResultExtractorPool.get(targetClass);

        if (result == null) {
            result = new ResultExtractor>() {
                @Override
                public Nullable extractData(final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
                    JdbcUtil.skip(rs, jdbcSettings.getOffset());

                    if (rs.next()) {
                        return Nullable.of(N.convert(JdbcUtil.getColumnValue(rs, 1), targetClass));
                    }

                    return Nullable.empty();
                }
            };

            singleResultExtractorPool.put(targetClass, result);
        }

        return result;
    }

    @SafeVarargs
    public final  Nullable queryForUniqueResult(final Class targetClass, final String sql, final Object... parameters)
            throws DuplicatedResultException {
        return queryForUniqueResult(targetClass, sql, StatementSetter.DEFAULT, parameters);
    }

    @SafeVarargs
    public final  Nullable queryForUniqueResult(final Class targetClass, final String sql, final StatementSetter statementSetter,
            final Object... parameters) throws DuplicatedResultException {
        return queryForUniqueResult(targetClass, sql, statementSetter, null, parameters);
    }

    @SafeVarargs
    public final  Nullable queryForUniqueResult(final Class targetClass, final String sql, final JdbcSettings jdbcSettings, final Object... parameters)
            throws DuplicatedResultException {
        return queryForUniqueResult(targetClass, sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  Nullable queryForUniqueResult(final Class targetClass, final String sql, final StatementSetter statementSetter,
            final JdbcSettings jdbcSettings, final Object... parameters) throws DuplicatedResultException {
        return queryForUniqueResult(targetClass, null, sql, statementSetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  Nullable queryForUniqueResult(final Class targetClass, final Connection conn, final String sql, final Object... parameters)
            throws DuplicatedResultException {
        return queryForUniqueResult(targetClass, conn, sql, StatementSetter.DEFAULT, parameters);
    }

    public final  Nullable queryForUniqueResult(final Class targetClass, final Connection conn, final String sql,
            final StatementSetter statementSetter, final Object... parameters) throws DuplicatedResultException {
        return queryForUniqueResult(targetClass, conn, sql, statementSetter, null, parameters);
    }

    public final  Nullable queryForUniqueResult(final Class targetClass, final Connection conn, final String sql, final JdbcSettings jdbcSettings,
            final Object... parameters) throws DuplicatedResultException {
        return queryForUniqueResult(targetClass, conn, sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    /**
     * Returns a {@code Nullable} describing the value in the first row/column if it exists, otherwise return an empty {@code Nullable}.
     * And throws {@code DuplicatedResultException} if more than one record found.
     * 

     *
     * Special note for type conversion for {@code boolean} or {@code Boolean} type: {@code true} is returned if the
     * {@code String} value of the target column is {@code "true"}, case insensitive. or it's an integer with value > 0.
     * Otherwise, {@code false} is returned.
     *
     * Remember to add {@code limit} condition if big result will be returned by the query.
     *
     * @param targetClass
     *            set result type to avoid the NullPointerException if result is null and T is primitive type
     *            "int, long. short ... char, boolean..".
     * @param conn
     * @param sql
     * @param statementSetter
     * @param jdbcSettings
     * @param parameters
     * @throws DuplicatedResultException if more than one record found.
     */
    @SuppressWarnings("unchecked")
    @SafeVarargs
    public final  Nullable queryForUniqueResult(final Class targetClass, final Connection conn, final String sql,
            final StatementSetter statementSetter, final JdbcSettings jdbcSettings, final Object... parameters) throws DuplicatedResultException {
        return query(conn, sql, statementSetter, createUniqueResultExtractor(targetClass), jdbcSettings, parameters);
    }

    private final ObjectPool, ResultExtractor>> uniqueResultExtractorPool = new ObjectPool<>(64);

    private  ResultExtractor> createUniqueResultExtractor(final Class targetClass) {
        @SuppressWarnings("rawtypes")
        ResultExtractor result = uniqueResultExtractorPool.get(targetClass);

        if (result == null) {
            result = new ResultExtractor>() {
                @Override
                public Nullable extractData(final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
                    JdbcUtil.skip(rs, jdbcSettings.getOffset());

                    if (rs.next()) {
                        final Nullable result = Nullable.of(N.convert(JdbcUtil.getColumnValue(rs, 1), targetClass));

                        if (result.isPresent() && rs.next()) {
                            throw new DuplicatedResultException("At least two results found: "
                                    + Strings.concat(result.get(), ", ", N.convert(JdbcUtil.getColumnValue(rs, 1), targetClass)));
                        }

                        return result;
                    }

                    return Nullable.empty();
                }
            };

            uniqueResultExtractorPool.put(targetClass, result);
        }

        return result;
    }

    @SafeVarargs
    public final DataSet query(final String sql, final Object... parameters) {
        return query(sql, StatementSetter.DEFAULT, parameters);
    }

    @SafeVarargs
    public final DataSet query(final String sql, final StatementSetter statementSetter, final Object... parameters) {
        return query(sql, statementSetter, (JdbcSettings) null, parameters);
    }

    @SafeVarargs
    public final DataSet query(final String sql, final JdbcSettings jdbcSettings, final Object... parameters) {
        return query(sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final DataSet query(final String sql, final StatementSetter statementSetter, final JdbcSettings jdbcSettings, final Object... parameters) {
        return query(sql, statementSetter, ResultExtractor.DATA_SET, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  T query(final String sql, final ResultExtractor resultExtractor, final Object... parameters) {
        return query(sql, StatementSetter.DEFAULT, resultExtractor, parameters);
    }

    @SafeVarargs
    public final  T query(final String sql, final StatementSetter statementSetter, final ResultExtractor resultExtractor, final Object... parameters) {
        return query(sql, statementSetter, resultExtractor, null, parameters);
    }

    @SafeVarargs
    public final  T query(final String sql, final ResultExtractor resultExtractor, final JdbcSettings jdbcSettings, final Object... parameters) {
        return query(sql, StatementSetter.DEFAULT, resultExtractor, jdbcSettings, parameters);
    }

    /** 
     * Remember to close the ResultSet, Statement and Connection if the return type T is ResultSet or RowIterator.
     * 
     * If T is RowIterator, call rowIterator.close() to close ResultSet, Statement and Connection.
     * 


     * If T is ResultSet, call below codes to close ResultSet, Statement and Connection.
     * 
     * 
     * 
     * Connection conn = null;
     * Statement stmt = null;
     * 
     * try {
     *     stmt = rs.getStatement();
     *     conn = stmt.getConnection();
     * } catch (SQLException e) {
     *     // TODO.
     * } finally {
     *     JdbcUtil.closeQuietly(rs, stmt, conn);
     * }
     * 
     * 
     * 
     * @param sql
     * @param statementSetter
     * @param resultExtractor
     * @param jdbcSettings
     * @param parameters
     * @return
     */
    @SafeVarargs
    public final  T query(final String sql, final StatementSetter statementSetter, final ResultExtractor resultExtractor, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return query(null, sql, statementSetter, resultExtractor, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final DataSet query(final Connection conn, final String sql, final Object... parameters) {
        return query(conn, sql, StatementSetter.DEFAULT, parameters);
    }

    @SafeVarargs
    public final DataSet query(final Connection conn, final String sql, final StatementSetter statementSetter, final Object... parameters) {
        return query(conn, sql, statementSetter, (JdbcSettings) null, parameters);
    }

    @SafeVarargs
    public final DataSet query(final Connection conn, final String sql, final JdbcSettings jdbcSettings, final Object... parameters) {
        return query(conn, sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final DataSet query(final Connection conn, final String sql, final StatementSetter statementSetter, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return query(conn, sql, statementSetter, ResultExtractor.DATA_SET, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  T query(final Connection conn, final String sql, final ResultExtractor resultExtractor, final Object... parameters) {
        return query(conn, sql, StatementSetter.DEFAULT, resultExtractor, parameters);
    }

    @SafeVarargs
    public final  T query(final Connection conn, final String sql, final StatementSetter statementSetter, final ResultExtractor resultExtractor,
            final Object... parameters) {
        return query(conn, sql, statementSetter, resultExtractor, null, parameters);
    }

    @SafeVarargs
    public final  T query(final Connection conn, final String sql, final ResultExtractor resultExtractor, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return query(conn, sql, StatementSetter.DEFAULT, resultExtractor, jdbcSettings, parameters);
    }

    /**
     * Remember to close the ResultSet, Statement and Connection if the return type T is ResultSet or RowIterator.
     * 
     * If T is RowIterator, call rowIterator.close() to close ResultSet, Statement and Connection.
     * 


     * If T is ResultSet, call below codes to close ResultSet, Statement and Connection.
     * 
     * 
     * 
     * Connection conn = null;
     * Statement stmt = null;
     * 
     * try {
     *     stmt = rs.getStatement();
     *     conn = stmt.getConnection();
     * } catch (SQLException e) {
     *     // TODO.
     * } finally {
     *     JdbcUtil.closeQuietly(rs, stmt, conn);
     * }
     * 
     * 
     *  
     * @param conn
     * @param sql
     * @param statementSetter
     * @param resultExtractor
     * @param jdbcSettings
     * @param parameters
     * @return
     */
    @SafeVarargs
    public final  T query(final Connection conn, final String sql, final StatementSetter statementSetter, final ResultExtractor resultExtractor,
            final JdbcSettings jdbcSettings, final Object... parameters) {
        return query(null, conn, sql, statementSetter, new ResultSetExtractor() {
            @Override
            public T extractData(Class targetClass, NamedSQL namedSQL, ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
                return resultExtractor.extractData(rs, jdbcSettings);
            }

        }, jdbcSettings, parameters);
    }

    protected  T query(final Class targetClass, final Connection conn, final String sql, StatementSetter statementSetter,
            ResultSetExtractor resultExtractor, JdbcSettings jdbcSettings, final Object... parameters) {
        final NamedSQL namedSQL = getNamedSQL(sql);
        statementSetter = checkStatementSetter(namedSQL, statementSetter);
        resultExtractor = checkResultSetExtractor(namedSQL, resultExtractor);
        jdbcSettings = checkJdbcSettings(jdbcSettings, namedSQL, _sqlMapper.getAttrs(sql));

        T result = null;

        DataSource ds = null;
        Connection localConn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            ds = getDataSource(namedSQL.getPureSQL(), parameters, jdbcSettings);

            localConn = getConnection(conn, ds, jdbcSettings, SQLOperation.SELECT);

            stmt = prepareStatement(ds, localConn, namedSQL, statementSetter, jdbcSettings, false, false, parameters);

            stmt.setFetchDirection(ResultSet.FETCH_FORWARD);

            rs = stmt.executeQuery();

            result = resultExtractor.extractData(targetClass, namedSQL, rs, jdbcSettings);
        } catch (SQLException e) {
            String msg = AbacusException.getErrorMsg(e) + ". [SQL] " + namedSQL.getNamedSQL();
            logger.error(msg);
            throw new UncheckedSQLException(msg, e);
        } finally {
            if (result instanceof ResultSet) {
                // delay.
            } else {
                closeQuietly(rs, stmt);
                closeQuietly(localConn, conn, ds);
            }
        }

        return result;
    }

    @SafeVarargs
    public final DataSet queryAll(final String sql, final JdbcSettings jdbcSettings, final Object... parameters) {
        return queryAll(sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final DataSet queryAll(final String sql, final StatementSetter statementSetter, final JdbcSettings jdbcSettings, final Object... parameters) {
        checkJdbcSettingsForAllQuery(jdbcSettings);

        if (jdbcSettings == null || N.isNullOrEmpty(jdbcSettings.getQueryWithDataSources())) {
            return query(sql, statementSetter, jdbcSettings, parameters);
        }

        final Collection dss = jdbcSettings.getQueryWithDataSources();

        if (jdbcSettings.isQueryInParallel()) {
            final List resultList = Stream.of(dss).map(new Function() {
                @Override
                public JdbcSettings apply(String ds) {
                    final JdbcSettings newJdbcSettings = jdbcSettings.copy();
                    newJdbcSettings.setQueryWithDataSources(null);
                    newJdbcSettings.setQueryWithDataSource(ds);
                    return newJdbcSettings;
                }
            }).parallel(dss.size()).map(new Function() {
                @Override
                public DataSet apply(JdbcSettings newJdbcSettings) {
                    return query(sql, statementSetter, newJdbcSettings, parameters);
                }
            }).toList();

            return DataSetUtil.merge(resultList);
        } else {
            final JdbcSettings newJdbcSettings = jdbcSettings.copy();
            newJdbcSettings.setQueryWithDataSources(null);
            final List resultList = new ArrayList<>(dss.size());

            for (String ds : dss) {
                newJdbcSettings.setQueryWithDataSource(ds);

                resultList.add(query(sql, statementSetter, newJdbcSettings, parameters));
            }

            return DataSetUtil.merge(resultList);
        }
    }

    @SafeVarargs
    public final DataSet queryAll(final List sqls, final JdbcSettings jdbcSettings, final Object... parameters) {
        return queryAll(sqls, null, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final DataSet queryAll(final List sqls, final StatementSetter statementSetter, final JdbcSettings jdbcSettings, final Object... parameters) {
        if (sqls.size() == 1) {
            return queryAll(sqls.get(0), statementSetter, jdbcSettings, parameters);
        }

        if (jdbcSettings != null && jdbcSettings.isQueryInParallel()) {
            final List resultList = Stream.of(sqls).parallel(sqls.size()).map(new Function() {
                @Override
                public DataSet apply(String sql) {
                    return queryAll(sql, statementSetter, jdbcSettings, parameters);
                }
            }).toList();

            return DataSetUtil.merge(resultList);
        } else {
            final List resultList = new ArrayList<>(sqls.size());

            for (String sql : sqls) {
                resultList.add(queryAll(sql, statementSetter, jdbcSettings, parameters));
            }

            return DataSetUtil.merge(resultList);
        }
    }

    @SafeVarargs
    public final  Stream stream(final Class targetClass, final String sql, final Object... parameters) {
        return stream(targetClass, sql, StatementSetter.DEFAULT, parameters);
    }

    @SafeVarargs
    public final  Stream stream(final Class targetClass, final String sql, final StatementSetter statementSetter, final Object... parameters) {
        return stream(targetClass, sql, statementSetter, null, parameters);
    }

    public final  Stream stream(final Class targetClass, final String sql, final JdbcSettings jdbcSettings, final Object... parameters) {
        return stream(targetClass, sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    /**
     * Remember to close the returned Stream to close the underlying ResultSet.
     * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
     * 
     * @param sql
     * @param statementSetter
     * @param jdbcSettings
     * @param parameters
     * @return
     */
    @SafeVarargs
    public final  Stream stream(final Class targetClass, final String sql, final StatementSetter statementSetter, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        final JdbcUtil.BiRecordGetter biRecordGetter = BiRecordGetter.to(targetClass);

        return stream(sql, statementSetter, biRecordGetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  Stream stream(final String sql, final JdbcUtil.BiRecordGetter recordGetter, final Object... parameters) {
        return stream(sql, StatementSetter.DEFAULT, recordGetter, parameters);
    }

    @SafeVarargs
    public final  Stream stream(final String sql, final StatementSetter statementSetter, final JdbcUtil.BiRecordGetter recordGetter,
            final Object... parameters) {
        return stream(sql, statementSetter, recordGetter, null, parameters);
    }

    @SafeVarargs
    public final  Stream stream(final String sql, final JdbcUtil.BiRecordGetter recordGetter, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return stream(sql, StatementSetter.DEFAULT, recordGetter, jdbcSettings, parameters);
    }

    /**
     * Remember to close the returned Stream to close the underlying ResultSet.
     * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
     * 
     * @param sql
     * @param statementSetter
     * @param recordGetter
     * @param jdbcSettings
     * @param parameters
     * @return
     */
    @SafeVarargs
    public final  Stream stream(final String sql, final StatementSetter statementSetter, final JdbcUtil.BiRecordGetter recordGetter,
            final JdbcSettings jdbcSettings, final Object... parameters) {

        final ObjIteratorEx lazyIter = ObjIteratorEx.of(new Supplier>() {
            private ObjIteratorEx internalIter = null;

            @Override
            public ObjIteratorEx get() {
                if (internalIter == null) {
                    final JdbcSettings newJdbcSettings = jdbcSettings == null ? _jdbcSettings.copy() : jdbcSettings.copy();
                    final int offset = newJdbcSettings.getOffset();
                    final int count = newJdbcSettings.getCount();
                    newJdbcSettings.setOffset(0);
                    newJdbcSettings.setCount(Integer.MAX_VALUE);

                    final ResultSet rs = SQLExecutor.this.query(sql, statementSetter, RESULT_SET_EXTRACTOR, newJdbcSettings, parameters);

                    internalIter = new ObjIteratorEx() {
                        private boolean skipped = false;
                        private boolean hasNext = false;
                        private int cnt = 0;
                        private List columnLabels = null;

                        @Override
                        public boolean hasNext() {
                            if (skipped == false) {
                                skip();
                            }

                            if (hasNext == false) {
                                try {
                                    if (cnt++ < count && rs.next()) {
                                        hasNext = true;
                                    }
                                } catch (SQLException e) {
                                    throw new UncheckedSQLException(e);
                                }
                            }

                            return hasNext;
                        }

                        @Override
                        public T next() {
                            if (hasNext() == false) {
                                throw new NoSuchElementException();
                            }

                            try {
                                final T result = recordGetter.apply(rs, columnLabels);
                                hasNext = false;
                                return result;
                            } catch (SQLException e) {
                                throw new UncheckedSQLException(e);
                            }
                        }

                        @Override
                        public void skip(long n) {
                            N.checkArgNotNegative(n, "n");

                            if (skipped == false) {
                                skip();
                            }

                            final long m = hasNext ? n - 1 : n;
                            hasNext = false;

                            try {
                                JdbcUtil.skip(rs, Math.min(m, count - cnt));
                            } catch (SQLException e) {
                                throw new UncheckedSQLException(e);
                            }
                        }

                        @Override
                        public long count() {
                            if (skipped == false) {
                                skip();
                            }

                            long result = hasNext ? 1 : 0;
                            hasNext = false;

                            try {
                                while (cnt++ < count && rs.next()) {
                                    result++;
                                }
                            } catch (SQLException e) {
                                throw new UncheckedSQLException(e);
                            }

                            return result;
                        }

                        @Override
                        public void close() {
                            JdbcUtil.closeQuietly(rs, true, true);
                        }

                        private void skip() {
                            if (skipped == false) {
                                skipped = true;

                                try {
                                    columnLabels = JdbcUtil.getColumnLabelList(rs);

                                    if (offset > 0) {
                                        JdbcUtil.skip(rs, offset);
                                    }
                                } catch (SQLException e) {
                                    throw new UncheckedSQLException(e);
                                }
                            }
                        }
                    };
                }

                return internalIter;
            }
        });

        return Stream.of(lazyIter).onClose(new Runnable() {
            @Override
            public void run() {
                lazyIter.close();
            }
        });
    }

    @SafeVarargs
    public final  Stream streamAll(final Class targetClass, final String sql, final JdbcSettings jdbcSettings, final Object... parameters) {
        return streamAll(targetClass, sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    /**
     * Remember to close the returned Stream list to close the underlying ResultSet list.
     * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
     * 
     * @param sql
     * @param statementSetter
     * @param jdbcSettings
     * @param parameters
     * @return
     */
    @SafeVarargs
    public final  Stream streamAll(final Class targetClass, final String sql, final StatementSetter statementSetter, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        final JdbcUtil.BiRecordGetter biRecordGetter = BiRecordGetter.to(targetClass);

        return streamAll(sql, statementSetter, biRecordGetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  Stream streamAll(final String sql, final JdbcUtil.BiRecordGetter recordGetter, final JdbcSettings jdbcSettings,
            final Object... parameters) {
        return streamAll(sql, StatementSetter.DEFAULT, recordGetter, jdbcSettings, parameters);
    }

    /**
     * Remember to close the returned Stream list to close the underlying ResultSet list.
     * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
     * 
     * @param sql
     * @param statementSetter
     * @param jdbcSettings
     * @param parameters
     * @return
     */
    @SafeVarargs
    public final  Stream streamAll(final String sql, final StatementSetter statementSetter,
            final JdbcUtil.BiRecordGetter recordGetter, final JdbcSettings jdbcSettings, final Object... parameters) {
        checkJdbcSettingsForAllQuery(jdbcSettings);

        if (jdbcSettings == null || N.isNullOrEmpty(jdbcSettings.getQueryWithDataSources())) {
            return stream(sql, statementSetter, recordGetter, jdbcSettings, parameters);
        }

        final Collection dss = jdbcSettings.getQueryWithDataSources();

        return Stream.of(dss).map(new Function() {
            @Override
            public JdbcSettings apply(String ds) {
                return jdbcSettings.copy().setQueryWithDataSources(null).setQueryWithDataSource(ds);
            }
        }).__(new Function, Stream>() {
            @Override
            public Stream apply(Stream s) {
                return jdbcSettings.isQueryInParallel() ? s.parallel(dss.size()) : s;
            }
        }).flatMap(new Function>() {
            @Override
            public Stream apply(JdbcSettings newJdbcSettings) {
                return stream(sql, statementSetter, recordGetter, newJdbcSettings, parameters);
            }
        });
    }

    @SafeVarargs
    public final  Stream streamAll(final Class targetClass, final List sqls, final JdbcSettings jdbcSettings, final Object... parameters) {
        return streamAll(targetClass, sqls, null, jdbcSettings, parameters);
    }

    /**
     * Remember to close the returned Stream list to close the underlying ResultSet list.
     * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
     * 
     * @param sqls
     * @param statementSetter
     * @param jdbcSettings
     * @param parameters
     * @return
     */
    @SafeVarargs
    public final  Stream streamAll(final Class targetClass, final List sqls, final StatementSetter statementSetter,
            final JdbcSettings jdbcSettings, final Object... parameters) {
        final JdbcUtil.BiRecordGetter biRecordGetter = BiRecordGetter.to(targetClass);

        return streamAll(sqls, statementSetter, biRecordGetter, jdbcSettings, parameters);
    }

    @SafeVarargs
    public final  Stream streamAll(final List sqls, final JdbcUtil.BiRecordGetter recordGetter,
            final JdbcSettings jdbcSettings, final Object... parameters) {
        return streamAll(sqls, null, recordGetter, jdbcSettings, parameters);
    }

    /**
     * Remember to close the returned Stream list to close the underlying ResultSet list.
     * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
     * 
     * @param sqls
     * @param statementSetter
     * @param recordGetter
     * @param jdbcSettings
     * @param parameters
     * @return
     */
    @SafeVarargs
    public final  Stream streamAll(final List sqls, final StatementSetter statementSetter,
            final JdbcUtil.BiRecordGetter recordGetter, final JdbcSettings jdbcSettings, final Object... parameters) {
        if (sqls.size() == 1) {
            return streamAll(sqls.get(0), statementSetter, recordGetter, jdbcSettings, parameters);
        }

        final boolean isQueryInParallel = jdbcSettings != null && jdbcSettings.isQueryInParallel();

        return Stream.of(sqls).__(new Function, Stream>() {
            @Override
            public Stream apply(Stream s) {
                return isQueryInParallel ? s.parallel(sqls.size()) : s;
            }
        }).flatMap(new Function>() {
            @Override
            public Stream apply(String sql) {
                return streamAll(sql, statementSetter, recordGetter, jdbcSettings, parameters);
            }
        });
    }

    public PreparedQuery prepareQuery(final String sql, final Object... parameters) throws UncheckedSQLException {
        return prepareQuery(sql, StatementSetter.DEFAULT, parameters);
    }

    public PreparedQuery prepareQuery(final String sql, final StatementSetter statementSetter, final Object... parameters) throws UncheckedSQLException {
        return prepareQuery(sql, statementSetter, (JdbcSettings) null, parameters);
    }

    public PreparedQuery prepareQuery(final String sql, final JdbcSettings jdbcSettings, final Object... parameters) throws UncheckedSQLException {
        return prepareQuery(sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    public PreparedQuery prepareQuery(final String sql, final StatementSetter statementSetter, final JdbcSettings jdbcSettings, final Object... parameters)
            throws UncheckedSQLException {
        return prepareQuery(null, sql, statementSetter, jdbcSettings, parameters);
    }

    public PreparedQuery prepareQuery(final Connection conn, final String sql, final Object... parameters) throws UncheckedSQLException {
        return prepareQuery(conn, sql, StatementSetter.DEFAULT, parameters);
    }

    public PreparedQuery prepareQuery(final Connection conn, final String sql, final StatementSetter statementSetter, final Object... parameters)
            throws UncheckedSQLException {
        return prepareQuery(conn, sql, statementSetter, (JdbcSettings) null, parameters);
    }

    public PreparedQuery prepareQuery(final Connection conn, final String sql, final JdbcSettings jdbcSettings, final Object... parameters)
            throws UncheckedSQLException {
        return prepareQuery(conn, sql, StatementSetter.DEFAULT, jdbcSettings, parameters);
    }

    @SuppressWarnings("resource")
    public PreparedQuery prepareQuery(final Connection conn, final String sql, StatementSetter statementSetter, JdbcSettings jdbcSettings,
            final Object... parameters) throws UncheckedSQLException {
        final NamedSQL namedSQL = getNamedSQL(sql);
        statementSetter = statementSetter == null ? StatementSetter.DEFAULT : statementSetter;
        jdbcSettings = checkJdbcSettings(jdbcSettings, namedSQL, _sqlMapper.getAttrs(sql));

        final DataSource ds = getDataSource(namedSQL.getPureSQL(), parameters, jdbcSettings);
        final Connection localConn = getConnection(conn, ds, jdbcSettings, SQLOperation.SELECT);
        PreparedQuery result = null;

        try {
            final PreparedStatement stmt = prepareStatement(ds, localConn, namedSQL, statementSetter, jdbcSettings, false, false, parameters);

            result = new PreparedQuery(stmt, _asyncExecutor).onClose(new Runnable() {
                @Override
                public void run() {
                    closeQuietly(localConn, conn, ds);
                }
            });
        } catch (SQLException e) {
            throw new UncheckedSQLException(e);
        } finally {
            if (result == null && conn == null) {
                closeQuietly(localConn, conn, ds);
            }
        }

        return result;
    }

    //    public PreparedCallableQuery prepareCallableQuery(final String sql) throws UncheckedSQLException {
    //        return prepareCallableQuery(sql, null);
    //    }
    //
    //    public PreparedCallableQuery prepareCallableQuery(final String sql, final JdbcSettings jdbcSettings) throws UncheckedSQLException {
    //        return prepareCallableQuery(null, sql, jdbcSettings);
    //    }
    //
    //    public PreparedCallableQuery prepareCallableQuery(final Connection conn, final String sql) throws UncheckedSQLException {
    //        return prepareCallableQuery(conn, sql, null);
    //    }
    //
    //    @SuppressWarnings("resource")
    //    public PreparedCallableQuery prepareCallableQuery(final Connection conn, final String sql, JdbcSettings jdbcSettings) throws UncheckedSQLException {
    //        jdbcSettings = checkJdbcSettings(jdbcSettings, null);
    //
    //        final DataSource ds = getDataSource(sql, N.EMPTY_OBJECT_ARRAY, jdbcSettings);
    //        final Connection localConn = getConnection(conn, ds, jdbcSettings, SQLOperation.SELECT);
    //        PreparedCallableQuery result = null;
    //
    //        try {
    //            logSQL(sql, jdbcSettings, N.EMPTY_OBJECT_ARRAY);
    //
    //            final CallableStatement stmt = prepareCallableStatement(localConn, sql, jdbcSettings);
    //
    //            result = new PreparedCallableQuery(stmt).onClose(conn == null ? localConn : null);
    //        } catch (SQLException e) {
    //            throw new UncheckedSQLException(e);
    //        } finally {
    //            if (result == null && conn == null) {
    //                closeQuietly(localConn, conn, ds, jdbcSettings);
    //            }
    //        }
    //
    //        return result;
    //    }

    public final void execute(final String sql, final Object... parameters) throws UncheckedSQLException {
        execute(null, sql, parameters);
    }

    /**
     * Execute the sql with the specified parameters.
     * 
     * @param conn
     * @param sql
     * @param parameters
     * 
     * @see java.sql.PreparedStatement#execute()
     */
    @SafeVarargs
    public final void execute(final Connection conn, final String sql, final Object... parameters) throws UncheckedSQLException {
        final NamedSQL namedSQL = getNamedSQL(sql);
        final StatementSetter statementSetter = checkStatementSetter(namedSQL, null);
        final JdbcSettings jdbcSettings = checkJdbcSettings(null, namedSQL, _sqlMapper.getAttrs(sql));

        final SQLOperation op = StringUtil.startsWithIgnoreCase(namedSQL.getPureSQL().trim(), "select") ? SQLOperation.SELECT : SQLOperation.UPDATE;
        DataSource ds = null;
        Connection localConn = null;
        PreparedStatement stmt = null;

        try {
            ds = getDataSource(namedSQL.getPureSQL(), parameters, jdbcSettings);

            localConn = getConnection(conn, ds, jdbcSettings, op);

            stmt = prepareStatement(ds, localConn, namedSQL, statementSetter, jdbcSettings, false, false, parameters);

            stmt.execute();
        } catch (SQLException e) {
            String msg = AbacusException.getErrorMsg(e) + ". [SQL] " + namedSQL.getNamedSQL();
            logger.error(msg);
            throw new UncheckedSQLException(msg, e);
        } finally {
            closeQuietly(stmt);
            closeQuietly(localConn, conn, ds);
        }
    }

    /** 
     * The connection opened in the transaction will be automatically closed after the transaction is committed or rolled back.
     * DON'T close it again by calling the close method.
     * 
     * @return
     */
    public SQLTransaction beginTransaction() {
        return beginTransaction(IsolationLevel.DEFAULT);
    }

    /** 
     * The connection opened in the transaction will be automatically closed after the transaction is committed or rolled back.
     * DON'T close it again by calling the close method.
     * 
     * @param isolationLevel
     * @return
     */
    public SQLTransaction beginTransaction(IsolationLevel isolationLevel) {
        return beginTransaction(isolationLevel, false);
    }

    /** 
     * The connection opened in the transaction will be automatically closed after the transaction is committed or rolled back.
     * DON'T close it again by calling the close method.
     * 
     * @param forUpdateOnly
     * @return
     */
    public SQLTransaction beginTransaction(boolean forUpdateOnly) {
        return beginTransaction(IsolationLevel.DEFAULT, forUpdateOnly);
    }

    /**
     * The connection opened in the transaction will be automatically closed after the transaction is committed or rolled back.
     * DON'T close it again by calling the close method.
     * 
     * @param isolationLevel
     * @param forUpdateOnly
     * @return
     */
    public SQLTransaction beginTransaction(IsolationLevel isolationLevel, boolean forUpdateOnly) {
        N.checkArgNotNull(isolationLevel, "isolationLevel");

        isolationLevel = isolationLevel == IsolationLevel.DEFAULT ? _defaultIsolationLevel : isolationLevel;

        final String ttid = getTransactionThreadId();
        SQLTransaction transaction = threadTransactionMap.get(ttid);

        if (transaction == null) {
            transaction = new SQLTransaction(this, isolationLevel);
            threadTransactionMap.put(ttid, transaction);

            logger.info("Creating a new transaction(id={})", transaction.id());

        } else {
            logger.info("Reusing existing transaction(id={})", transaction.id());
        }

        logger.debug("Current active transactions: {}", threadTransactionMap.values());

        transaction.incrementAndGetRef(isolationLevel, forUpdateOnly);

        return transaction;
    }

    final Map threadTransactionMap = new ConcurrentHashMap<>();

    static String getTransactionThreadId() {
        final Thread currentThread = Thread.currentThread();

        return currentThread.getName() + "_" + currentThread.getId();
    }

    public DBSequence getDBSequence(final String tableName, final String seqName) {
        return new DBSequence(this, tableName, seqName, 0, 1000);
    }

    /**
     * Supports global sequence by db table.
     * 
     * @param tableName
     * @param seqName
     * @param startVal
     * @param seqBufferSize the numbers to allocate/reserve from database table when cached numbers are used up.
     * @return
     */
    public DBSequence getDBSequence(final String tableName, final String seqName, final long startVal, final int seqBufferSize) {
        return new DBSequence(this, tableName, seqName, startVal, seqBufferSize);
    }

    /**
     * Supports global lock by db table
     * 
     * @param tableName
     * @return
     */
    public DBLock getDBLock(final String tableName) {
        return new DBLock(this, tableName);
    }

    public boolean doesTableExist(final String tableName) {
        Connection conn = getConnection();

        try {
            return JdbcUtil.doesTableExist(conn, tableName);
        } finally {
            closeQuietly(conn);
        }
    }

    /**
     * Returns {@code true} if succeed to create table, otherwise {@code false} is returned.
     *
     * @param tableName
     * @param schema
     * @return
     */
    public boolean createTableIfNotExists(final String tableName, final String schema) {
        Connection conn = getConnection();

        try {
            return JdbcUtil.createTableIfNotExists(conn, tableName, schema);
        } finally {
            closeQuietly(conn);
        }
    }

    /**
     * Returns {@code true} if succeed to drop table, otherwise {@code false} is returned.
     *
     * @param tableName
     * @return
     */
    public boolean dropTableIfExists(final String tableName) {
        Connection conn = getConnection();

        try {
            return JdbcUtil.dropTableIfExists(conn, tableName);
        } finally {
            closeQuietly(conn);
        }
    }

    /**
     *
     * @param tableName
     * @return
     */
    public List getColumnNameList(final String tableName) {
        List columnNameList = _tableColumnNamePool.get(tableName);

        if (columnNameList == null) {
            Connection conn = getConnection();

            try {
                columnNameList = ImmutableList.of(JdbcUtil.getColumnNameList(conn, tableName));
                _tableColumnNamePool.put(tableName, columnNameList);
            } catch (SQLException e) {
                throw new UncheckedSQLException(e);
            } finally {
                closeQuietly(conn);
            }
        }

        return columnNameList;
    }

    public Connection getConnection() {
        return _ds.getConnection();
    }

    protected DataSource getDataSource(final String sql, final Object[] parameters, final JdbcSettings jdbcSettings) {
        if (_dsm == null || _dss == null) {
            if ((jdbcSettings != null) && (jdbcSettings.getQueryWithDataSource() != null || N.notNullOrEmpty(jdbcSettings.getQueryWithDataSources()))) {
                throw new IllegalArgumentException("No data source is available with name: " + (jdbcSettings.getQueryWithDataSource() != null
                        ? jdbcSettings.getQueryWithDataSource() : N.toString(jdbcSettings.getQueryWithDataSources())));
            }

            return _ds;
        } else {
            if ((jdbcSettings == null) || (jdbcSettings.getQueryWithDataSource() == null)) {
                return _dss.select(_dsm, null, sql, parameters, null);
            } else {
                return _dss.select(_dsm, null, sql, parameters, N.asProps(QUERY_WITH_DATA_SOURCE, jdbcSettings.getQueryWithDataSource()));
            }
        }
    }

    protected DataSource getDataSource(final String sql, final List parametersList, final JdbcSettings jdbcSettings) {
        if (_dsm == null || _dss == null) {
            if ((jdbcSettings != null) && (jdbcSettings.getQueryWithDataSource() != null || N.notNullOrEmpty(jdbcSettings.getQueryWithDataSources()))) {
                throw new IllegalArgumentException("No data source is available with name: " + (jdbcSettings.getQueryWithDataSource() != null
                        ? jdbcSettings.getQueryWithDataSource() : N.toString(jdbcSettings.getQueryWithDataSources())));
            }

            return _ds;
        } else {
            if ((jdbcSettings == null) || (jdbcSettings.getQueryWithDataSource() == null)) {
                return _dss.select(_dsm, null, sql, parametersList, null);
            } else {
                return _dss.select(_dsm, null, sql, parametersList, N.asProps(QUERY_WITH_DATA_SOURCE, jdbcSettings.getQueryWithDataSource()));
            }
        }
    }

    protected Connection getConnection(final Connection conn, final DataSource ds, final JdbcSettings jdbcSettings, final SQLOperation op) {
        if (conn != null) {
            return conn;
        }

        final SQLTransaction tran = threadTransactionMap.get(getTransactionThreadId());

        if (tran != null && (tran.isForUpdateOnly() == false || op != SQLOperation.SELECT)) {
            if (ds != _ds) {
                throw new IllegalArgumentException("Target DataSource is different from the DataSource where transaction is started");
            }

            return tran.connection();
        }

        return getConnection(ds);
    }

    protected Connection getConnection(final DataSource ds) {
        return ds.getConnection();
    }

    protected PreparedStatement prepareStatement(final DataSource ds, final Connection localConn, final NamedSQL namedSQL,
            final StatementSetter statementSetter, final JdbcSettings jdbcSettings, final boolean autoGeneratedKeys, final boolean isBatch,
            final Object... parameters) throws SQLException {
        String sql = namedSQL.getPureSQL();

        if (isBatch) {
            sql = ds.getSliceSelector().select(null, sql, (List) parameters[0], null);
        } else {
            sql = ds.getSliceSelector().select(null, sql, parameters, null);
        }

        logSQL(sql, jdbcSettings, parameters);

        final PreparedStatement stmt = prepareStatement(localConn, sql, autoGeneratedKeys, jdbcSettings);

        setParameters(namedSQL, stmt, statementSetter, isBatch, parameters);

        return stmt;
    }

    protected PreparedStatement prepareStatement(final Connection conn, String sql, final boolean autoGeneratedKeys, final JdbcSettings jdbcSettings)
            throws SQLException {
        PreparedStatement stmt = null;

        if (jdbcSettings == null) {
            stmt = conn.prepareStatement(sql);
        } else {
            if (N.notNullOrEmpty(jdbcSettings.getReturnedColumnIndexes())) {
                if (jdbcSettings.getReturnedColumnIndexes().length != 1) {
                    throw new IllegalArgumentException("only 1 generated key is supported At present");
                }

                stmt = conn.prepareStatement(sql, jdbcSettings.getReturnedColumnIndexes());
            } else if (N.notNullOrEmpty(jdbcSettings.getReturnedColumnNames())) {
                if (jdbcSettings.getReturnedColumnNames().length != 1) {
                    throw new IllegalArgumentException("only 1 generated key is supported At present");
                }

                stmt = conn.prepareStatement(sql, jdbcSettings.getReturnedColumnNames());
            } else if (jdbcSettings.isAutoGeneratedKeys() || autoGeneratedKeys) {
                stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            } else if ((jdbcSettings.getResultSetType() != -1) || (jdbcSettings.getResultSetConcurrency() != -1)
                    || (jdbcSettings.getResultSetHoldability() != -1)) {
                int resultSetType = (jdbcSettings.getResultSetType() == -1) ? JdbcSettings.DEFAULT_RESULT_SET_TYPE : jdbcSettings.getResultSetType();

                int resultSetConcurrency = (jdbcSettings.getResultSetConcurrency() == -1) ? JdbcSettings.DEFAULT_RESULT_SET_CONCURRENCY
                        : jdbcSettings.getResultSetConcurrency();

                if (jdbcSettings.getResultSetHoldability() != -1) {
                    stmt = conn.prepareStatement(sql, resultSetType, resultSetConcurrency, jdbcSettings.getResultSetHoldability());
                } else {
                    stmt = conn.prepareStatement(sql, resultSetType, resultSetConcurrency);
                }
            } else {
                stmt = conn.prepareStatement(sql);
            }

            if (jdbcSettings.getFetchSize() != -1) {
                stmt.setFetchSize(jdbcSettings.getFetchSize());
            }

            if (jdbcSettings.getMaxRows() != -1) {
                stmt.setMaxRows(jdbcSettings.getMaxRows());
            }

            if (jdbcSettings.getMaxFieldSize() != -1) {
                stmt.setMaxFieldSize(jdbcSettings.getMaxFieldSize());
            }

            if (jdbcSettings.getFetchDirection() != -1) {
                stmt.setFetchDirection(jdbcSettings.getFetchDirection());
            }

            if (jdbcSettings.getQueryTimeout() != -1) {
                stmt.setQueryTimeout(jdbcSettings.getQueryTimeout());
            }
        }
        return stmt;
    }

    //    protected CallableStatement prepareCallableStatement(final DataSource ds, final Connection localConn, final NamedSQL namedSQL,
    //            final StatementSetter statementSetter, final JdbcSettings jdbcSettings, final boolean autoGeneratedKeys, final boolean isBatch,
    //            final Object... parameters) throws SQLException {
    //        String sql = namedSQL.getPureSQL();
    //
    //        if (isBatch) {
    //            sql = ds.getSliceSelector().select(null, sql, (List) parameters[0], null);
    //        } else {
    //            sql = ds.getSliceSelector().select(null, sql, parameters, null);
    //        }
    //
    //        logSQL(sql, jdbcSettings, parameters);
    //
    //        final CallableStatement stmt = prepareCallableStatement(localConn, sql, jdbcSettings);
    //
    //        setParameters(namedSQL, stmt, statementSetter, isBatch, parameters);
    //
    //        return stmt;
    //    }
    //
    //    protected CallableStatement prepareCallableStatement(final Connection conn, String sql, final JdbcSettings jdbcSettings) throws SQLException {
    //        CallableStatement stmt = null;
    //
    //        if (jdbcSettings == null) {
    //            stmt = conn.prepareCall(sql);
    //        } else {
    //            if ((jdbcSettings.getResultSetType() != -1) || (jdbcSettings.getResultSetConcurrency() != -1) || (jdbcSettings.getResultSetHoldability() != -1)) {
    //                int resultSetType = (jdbcSettings.getResultSetType() == -1) ? JdbcSettings.DEFAULT_RESULT_SET_TYPE : jdbcSettings.getResultSetType();
    //
    //                int resultSetConcurrency = (jdbcSettings.getResultSetConcurrency() == -1) ? JdbcSettings.DEFAULT_RESULT_SET_CONCURRENCY
    //                        : jdbcSettings.getResultSetConcurrency();
    //
    //                if (jdbcSettings.getResultSetHoldability() != -1) {
    //                    stmt = conn.prepareCall(sql, resultSetType, resultSetConcurrency, jdbcSettings.getResultSetHoldability());
    //                } else {
    //                    stmt = conn.prepareCall(sql, resultSetType, resultSetConcurrency);
    //                }
    //            } else {
    //                stmt = conn.prepareCall(sql);
    //            }
    //
    //            if (jdbcSettings.getFetchSize() != -1) {
    //                stmt.setFetchSize(jdbcSettings.getFetchSize());
    //            }
    //
    //            if (jdbcSettings.getMaxRows() != -1) {
    //                stmt.setMaxRows(jdbcSettings.getMaxRows());
    //            }
    //
    //            if (jdbcSettings.getMaxFieldSize() != -1) {
    //                stmt.setMaxFieldSize(jdbcSettings.getMaxFieldSize());
    //            }
    //
    //            if (jdbcSettings.getFetchDirection() != -1) {
    //                stmt.setFetchDirection(jdbcSettings.getFetchDirection());
    //            }
    //
    //            if (jdbcSettings.getQueryTimeout() != -1) {
    //                stmt.setQueryTimeout(jdbcSettings.getQueryTimeout());
    //            }
    //        }
    //        return stmt;
    //    }

    protected void setParameters(final NamedSQL namedSQL, final PreparedStatement stmt, final StatementSetter statementSetter, final boolean isBatch,
            final Object... parameters) throws SQLException {
        if (isBatch || N.isNullOrEmpty(parameters)) {
            // ignore
        } else {
            statementSetter.setParameters(namedSQL, stmt, parameters);
        }
    }

    protected void logSQL(String sql, final JdbcSettings jdbcSettings, final Object... parameters) {
        if ((jdbcSettings != null) && (jdbcSettings.isLogSQL() || jdbcSettings.isLogSQLWithParameters()) && logger.isInfoEnabled()) {
            if (jdbcSettings.isLogSQLWithParameters()) {
                logger.info(sql + " {" + StringUtil.join(parameters, ", ") + "}");
            } else {
                logger.info(sql);
            }
        }
    }

    protected void closeQuietly(final ResultSet rs) {
        JdbcUtil.closeQuietly(rs);
    }

    protected void closeQuietly(final PreparedStatement stmt) {
        JdbcUtil.closeQuietly(stmt);
    }

    protected void closeQuietly(final Connection conn) {
        JdbcUtil.closeQuietly(conn);
    }

    protected void closeQuietly(final ResultSet rs, final PreparedStatement stmt) {
        JdbcUtil.closeQuietly(rs, stmt);
    }

    protected void closeQuietly(final Connection localConn, final Connection inputConn, final DataSource ds) {
        if (inputConn == null) {
            closeQuietly(localConn, ds);
        }
    }

    protected void closeQuietly(final Connection conn, final DataSource ds) {
        JdbcUtil.closeQuietly(conn);
    }

    /**
     * Close the underline data source
     */
    @Override
    public void close() throws IOException {
        try {
            if (_ds != null && _ds.isClosed() == false) {
                _ds.close();
            }
        } finally {
            if (_dsm != null && _dsm.isClosed() == false) {
                _dsm.close();
            }
        }
    }

    protected int getBatchSize(final JdbcSettings jdbcSettings) {
        return ((jdbcSettings == null) || (jdbcSettings.getBatchSize() < 0)) ? JdbcSettings.DEFAULT_BATCH_SIZE : jdbcSettings.getBatchSize();
    }

    protected StatementSetter checkStatementSetter(final NamedSQL namedSQL, StatementSetter statementSetter) {
        if (statementSetter == null) {
            statementSetter = StatementSetter.DEFAULT;
        }

        return statementSetter;
    }

    @SuppressWarnings("unchecked")
    protected  ResultSetExtractor checkResultSetExtractor(final NamedSQL namedSQL, ResultSetExtractor resultExtractor) {
        if (resultExtractor == null) {
            resultExtractor = (ResultSetExtractor) ResultExtractor.DATA_SET;
        }

        return resultExtractor;
    }

    protected JdbcSettings checkJdbcSettings(final JdbcSettings jdbcSettings, final NamedSQL namedSQL, final Map attrs) {
        JdbcSettings newJdbcSettings = null;

        if (jdbcSettings == null) {
            newJdbcSettings = setJdbcSettingsForNamedSQL(_jdbcSettings, namedSQL, attrs);
        } else {
            newJdbcSettings = setJdbcSettingsForNamedSQL(jdbcSettings, namedSQL, attrs);
        }

        if ((newJdbcSettings.getOffset() < 0) || (newJdbcSettings.getCount() < 0)) {
            throw new IllegalArgumentException("offset or count can't be less than 0: " + newJdbcSettings.getOffset() + ", " + newJdbcSettings.getCount());
        }

        return newJdbcSettings;
    }

    protected JdbcSettings setJdbcSettingsForNamedSQL(JdbcSettings jdbcSettings, final NamedSQL namedSQL, final Map attrs) {
        if ((namedSQL == null) || N.isNullOrEmpty(attrs)) {
            return jdbcSettings;
        } else {
            jdbcSettings = jdbcSettings.copy();

            String attr = attrs.get(SQLMapper.BATCH_SIZE);
            if (attr != null) {
                jdbcSettings.setBatchSize(N.parseInt(attr));
            }

            attr = attrs.get(SQLMapper.FETCH_SIZE);
            if (attr != null) {
                jdbcSettings.setFetchSize(N.parseInt(attr));
            }

            attr = attrs.get(SQLMapper.RESULT_SET_TYPE);
            if (attr != null) {
                Integer resultSetType = SQLMapper.RESULT_SET_TYPE_MAP.get(attr);

                if (resultSetType == null) {
                    throw new IllegalArgumentException("Result set type: '" + attr + "' is not supported");
                }

                jdbcSettings.setResultSetType(resultSetType);
            }

            attr = attrs.get(SQLMapper.TIMEOUT);
            if (attr != null) {
                jdbcSettings.setQueryTimeout(N.parseInt(attr));
            }

            return jdbcSettings;
        }
    }

    protected String checkGeneratedIdPropName(final JdbcSettings jdbcSettings) {
        return ((jdbcSettings == null) || (jdbcSettings.getGeneratedIdPropName() == null)) ? JdbcSettings.DEFAULT_GENERATED_ID_PROP_NAME
                : jdbcSettings.getGeneratedIdPropName();
    }

    protected NamedSQL getNamedSQL(final String sql) {
        N.checkArgNotNull(sql, "sql");

        NamedSQL namedSQL = null;

        if (_sqlMapper != null) {
            namedSQL = _sqlMapper.get(sql);
        }

        if (namedSQL == null) {
            namedSQL = NamedSQL.parse(sql);
        }

        return namedSQL;
    }

    /**
     * 
     * @param sql should be prepared sql because it will be cached.
     * @param rs
     * @return
     * @throws SQLException
     */
    protected static List getColumnLabelList(final String sql, final ResultSet rs) throws SQLException {
        List labelList = N.notNullOrEmpty(sql) ? _sqlColumnLabelPool.get(sql) : null;

        if (labelList == null) {
            labelList = ImmutableList.of(JdbcUtil.getColumnLabelList(rs));

            if (N.notNullOrEmpty(sql) && sql.length() <= CACHED_SQL_LENGTH) {
                if (_sqlColumnLabelPool.size() >= SQL_CACHE_SIZE) {
                    final List tmp = new ArrayList<>(_sqlColumnLabelPool.keySet());
                    Maps.removeKeys(_sqlColumnLabelPool, tmp.subList(0, (int) (tmp.size() * 0.25)));
                }

                _sqlColumnLabelPool.put(sql, labelList);
            }
        }

        return labelList;
    }

    protected static boolean isEntityOrMapParameter(final NamedSQL namedSQL, final Object... parameters) {
        if (N.isNullOrEmpty(namedSQL.getNamedParameters())) {
            return false;
        }

        if (N.isNullOrEmpty(parameters) || (parameters.length != 1) || (parameters[0] == null)) {
            return false;
        }

        if (parameters[0] instanceof Map || (N.isEntity(parameters[0].getClass()))) {
            return true;
        }

        return false;
    }

    /**
     * 
     * @author haiyang li
     *
     * @param 
     * 
     * @see {@link com.landawn.abacus.annotation.ReadOnly}
     * @see {@link com.landawn.abacus.annotation.ReadOnlyId}
     * @see {@link com.landawn.abacus.annotation.NonUpdatable}
     * @see {@link com.landawn.abacus.annotation.Transient}
     * @see {@link com.landawn.abacus.annotation.Table}
     * @see {@link com.landawn.abacus.annotation.Column} 
     * 
     * @see http://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html
     * @see http://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html
     * @see http://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html
     * @see http://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html
     */
    public static final class Mapper {

        static final List EXISTS_SELECT_PROP_NAMES = ImmutableList.of(NE._1);
        static final List COUNT_SELECT_PROP_NAMES = ImmutableList.of(NE.COUNT_ALL);

        private final Class targetClass;
        private final Type targetType;
        private final List propNameList;
        private final Set propNameSet;
        private final List defaultSelectPropNameList;
        private final String idPropName;
        private final List idPropNameList;
        private final Set idPropNameSet;
        private final SQLExecutor sqlExecutor;
        private final NamingPolicy namingPolicy;
        private final Condition idCond;
        private final String sql_exists_by_id;
        private final String sql_get_by_id;
        private final String sql_delete_by_id;
        private final AsyncMapper asyncMapper;
        // TODO cache more sqls to improve performance.

        Mapper(final Class targetClass, final SQLExecutor sqlExecutor, final NamingPolicy namingPolicy) {
            this.sqlExecutor = sqlExecutor;
            this.namingPolicy = namingPolicy;

            final Set idPropNames = new LinkedHashSet<>();

            final Set allFields = new HashSet<>();

            for (Class superClass : ClassUtil.getAllSuperclasses(targetClass)) {
                allFields.addAll(Array.asList(superClass.getDeclaredFields()));
            }

            allFields.addAll(Array.asList(targetClass.getDeclaredFields()));

            for (Field field : allFields) {
                if (ClassUtil.getPropGetMethod(targetClass, field.getName()) == null
                        && ClassUtil.getPropGetMethod(targetClass, ClassUtil.formalizePropName(field.getName())) == null) {
                    continue;
                }

                if (field.isAnnotationPresent(Id.class) || field.isAnnotationPresent(ReadOnlyId.class)) {
                    idPropNames.add(field.getName());
                }
            }

            if (targetClass.isAnnotationPresent(Id.class)) {
                String[] values = targetClass.getAnnotation(Id.class).value();
                N.checkArgNotNullOrEmpty(values, "values for annotation @Id on Type/Class can't be null or empty");
                idPropNames.addAll(Arrays.asList(values));
            }

            N.checkArgNotNullOrEmpty(idPropNames, "Target class: " + ClassUtil.getCanonicalClassName(targetClass)
                    + " must at least has one id property annotated by @Id or @ReadOnlyId on field/method or class");

            this.targetClass = targetClass;
            this.targetType = N.typeOf(targetClass);
            this.propNameList = ImmutableList.copyOf(ClassUtil.getPropGetMethodList(targetClass).keySet());
            this.propNameSet = ImmutableSet.of(N.newLinkedHashSet(ClassUtil.getPropGetMethodList(targetClass).keySet()));

            this.idPropName = idPropNames.iterator().next();
            this.idPropNameList = ImmutableList.copyOf(idPropNames);
            this.idPropNameSet = ImmutableSet.copyOf(idPropNames);
            this.defaultSelectPropNameList = ImmutableList.copyOf(SQLBuilder.getSelectPropNamesByClass(targetClass, false, null));

            Condition cond = null;

            if (idPropNameList.size() == 1) {
                cond = L.eq(idPropName);
            } else {
                final And and = L.and();

                for (String idName : idPropNameList) {
                    and.add(L.eq(idName));
                }

                cond = and;
            }

            this.idCond = cond;
            this.sql_exists_by_id = this.prepareQuery(SQLBuilder._1_list, idCond).sql;
            this.sql_get_by_id = this.prepareQuery(defaultSelectPropNameList, idCond).sql;
            this.sql_delete_by_id = this.prepareDelete(idCond).sql;
            this.asyncMapper = new AsyncMapper(this, sqlExecutor._asyncExecutor);
        }

        public Class targetClass() {
            return targetClass;
        }

        public Type targetType() {
            return targetType;
        }

        public List idPropNameList() {
            return idPropNameList;
        }

        public Set idPropNameSet() {
            return idPropNameSet;
        }

        public List propNameList() {
            return propNameList;
        }

        public Set propNameSet() {
            return propNameSet;
        }

        public AsyncMapper async() {
            return asyncMapper;
        }

        private void checkId(final Object id) {
            N.checkArgument(idPropNameList.size() > 1 || !(id instanceof Map || isEntity(id)), "Input 'id' can not be Map or entity for single id ");
        }

        private boolean isEntity(Object obj) {
            return obj != null && N.isEntity(obj.getClass());
        }

        private Condition id2Cond(final Object id, boolean isIdOrEntity) {
            if (isIdOrEntity == false) {
                checkId(id);
            }

            if (idPropNameList.size() == 1) {
                if (id instanceof Map) {
                    return L.eq(idPropName, ((Map) id).get(idPropName));
                } else if (isEntity(id)) {
                    return L.eq(idPropName, ClassUtil.getPropValue(id, idPropName));
                } else {
                    return L.eq(idPropName, id);
                }
            }

            if (id instanceof Map) {
                return L.eqAnd((Map) id);
            } else if (isEntity(id)) {
                return L.eqAnd(id, idPropNameList);
            } else {
                throw new IllegalArgumentException("Not supported id type: " + (id == null ? "null" : ClassUtil.getClassName(id.getClass())));
            }
        }

        /**
         * 
         * @param id which could be {@code Number}/{@code String}... or {@code Entity}/{@code Map} for composed id.
         * @return
         */
        public boolean exists(final Object id) {
            checkId(id);

            return sqlExecutor.queryForInt(sql_exists_by_id, id).orElse(0) > 0;
        }

        public boolean exists(final Condition whereCause) {
            return exists(null, whereCause);
        }

        public boolean exists(final Connection conn, final Object id) {
            checkId(id);

            return sqlExecutor.queryForSingleResult(int.class, conn, sql_exists_by_id, id).orElse(0) > 0;
        }

        public boolean exists(final Connection conn, final Condition whereCause) {
            final SP pair = prepareQuery(EXISTS_SELECT_PROP_NAMES, whereCause, 1);

            return sqlExecutor.exists(conn, pair.sql, pair.parameters.toArray());
        }

        public int count(final Condition whereCause) {
            return count(null, whereCause);
        }

        public int count(final Connection conn, final Condition whereCause) {
            final SP pair = prepareQuery(COUNT_SELECT_PROP_NAMES, whereCause);

            return sqlExecutor.count(conn, pair.sql, pair.parameters.toArray());
        }

        /**
         * 
         * @param id which could be {@code Number}/{@code String}... or {@code Entity}/{@code Map} for composed id.
         * @return
         */
        public Optional get(final Object id) {
            return Optional.ofNullable(gett(id));
        }

        //    /**
        //     * 
        //     * @param id which could be {@code Number}/{@code String}... or {@code Entity}/{@code Map} for composed id.
        //     * @param selectPropNames
        //     * @return
        //     * @deprecated replaced by {@code get(id, Arrays.asList(selectPropNames)}
        //     */
        //    @Deprecated
        //    @SafeVarargs
        //    public final Optional get(final Object id, final String... selectPropNames) {
        //        return Optional.ofNullable(gett(id, selectPropNames));
        //    }

        /**
         * 
         * @param id which could be {@code Number}/{@code String}... or {@code Entity}/{@code Map} for composed id.
         * @param selectPropNames
         * @return
         */
        public Optional get(final Object id, final Collection selectPropNames) {
            return Optional.ofNullable(gett(id, selectPropNames));
        }

        /**
         * 
         * @param conn
         * @param id which could be {@code Number}/{@code String}... or {@code Entity}/{@code Map} for composed id.
         * @param selectPropNames
         * @return
         */
        public Optional get(final Connection conn, final Object id, final Collection selectPropNames) {
            return Optional.ofNullable(gett(conn, id, selectPropNames));
        }

        /**
         * 
         * @param id which could be {@code Number}/{@code String}... or {@code Entity}/{@code Map} for composed id.
         * @return
         */
        public T gett(final Object id) {
            return gett(id, (Collection) null);
        }

        //    /**
        //     * 
        //     * @param id which could be {@code Number}/{@code String}... or {@code Entity}/{@code Map} for composed id.
        //     * @param selectPropNames
        //     * @return
        //     * @deprecated replaced by {@code gett(id, Arrays.asList(selectPropNames)}
        //     */
        //    @Deprecated
        //    @SafeVarargs
        //    public final T gett(final Object id, final String... selectPropNames) {
        //        return gett(id, Arrays.asList(selectPropNames));
        //    }

        /**
         * 
         * @param id which could be {@code Number}/{@code String}... or {@code Entity}/{@code Map} for composed id.
         * @param selectPropNames
         * @return
         */
        public T gett(final Object id, final Collection selectPropNames) {
            return gett(null, id, selectPropNames);
        }

        /**
         * 
         * @param conn
         * @param id which could be {@code Number}/{@code String}... or {@code Entity}/{@code Map} for composed id.
         * @param selectPropNames
         * @return
         */
        public T gett(final Connection conn, final Object id, final Collection selectPropNames) {
            checkId(id);

            if (N.isNullOrEmpty(selectPropNames)) {
                return sqlExecutor.gett(targetClass, conn, sql_get_by_id, id);
            } else {
                final SP pair = prepareQuery(selectPropNames, idCond);
                return sqlExecutor.gett(targetClass, conn, pair.sql, id);
            }
        }

        /** 
         * 
         * @param ids
         * @return 
         */
        public List batchGet(final List ids) {
            return batchGet(ids, (Collection) null);
        }

        //    /** 
        //     * 
        //     * @param ids
        //     * @param selectPropNames
        //     * @return
        //     * @deprecated replaced by {@code batchGet(ids, Arrays.asList(selectPropNames)} 
        //     */
        //    @Deprecated
        //    @SafeVarargs
        //    public final List batchGet(final List ids, final String... selectPropNames) {
        //        return batchGet(ids, Arrays.asList(selectPropNames));
        //    }

        /** 
         * 
         * @param ids
         * @param selectPropNames
         * @return 
         */
        public List batchGet(final List ids, final Collection selectPropNames) {
            return batchGet(ids, selectPropNames, JdbcSettings.DEFAULT_BATCH_SIZE);
        }

        /**
         * 
         * @param ids
         * @param selectPropNames
         * @param batchSize
         * @return
         */
        public List batchGet(final List ids, final Collection selectPropNames, final int batchSize) {
            return batchGet(null, ids, selectPropNames, batchSize);
        }

        /** 
         * 
         * @param conn
         * @param ids
         * @param selectPropNames
         * @param batchSize
         * @return 
         */
        public List batchGet(final Connection conn, final List ids, final Collection selectPropNames, final int batchSize) {
            N.checkArgPositive(batchSize, "batchSize");

            if (N.isNullOrEmpty(ids)) {
                return new ArrayList<>();
            }

            N.checkArgument(idPropNameList.size() > 1 || !(ids.get(0) instanceof Map || isEntity(ids.get(0))),
                    "Input 'ids' can not be Maps or entities for single id ");

            final List entities = new ArrayList<>(ids.size());

            if (idPropNameList.size() == 1) {
                String sql = prepareQuery(selectPropNames, idCond).sql;
                sql = sql.substring(0, sql.lastIndexOf('=')) + "IN ";

                if (ids.size() >= batchSize) {
                    final Joiner joiner = Joiner.with(", ", "(", ")").reuseCachedBuffer(true);

                    for (int i = 0; i < batchSize; i++) {
                        joiner.append('?');
                    }

                    String inSQL = sql + joiner.toString();

                    for (int i = 0, to = ids.size() - batchSize; i <= to; i += batchSize) {
                        entities.addAll(sqlExecutor.list(targetClass, conn, inSQL, null, null, ids.subList(i, i + batchSize).toArray()));
                    }
                }

                if (ids.size() % batchSize != 0) {
                    final int remaining = ids.size() % batchSize;
                    final Joiner joiner = Joiner.with(", ", "(", ")").reuseCachedBuffer(true);

                    for (int i = 0; i < remaining; i++) {
                        joiner.append('?');
                    }

                    String inSQL = sql + joiner.toString();
                    entities.addAll(sqlExecutor.list(targetClass, conn, inSQL, null, null, ids.subList(ids.size() - remaining, ids.size()).toArray()));
                }

            } else {
                final boolean isMap = ids.get(0) instanceof Map;

                if (ids.size() >= batchSize) {
                    for (int i = 0, to = ids.size() - batchSize; i <= to; i += batchSize) {
                        if (isMap) {
                            entities.addAll(list(L.eqAndOr((List>) ids.subList(i, i + batchSize))));
                        } else {
                            entities.addAll(list(L.eqAndOr(ids.subList(i, i + batchSize), idPropNameList)));
                        }
                    }
                }

                if (ids.size() % batchSize != 0) {
                    final int remaining = ids.size() % batchSize;

                    if (isMap) {
                        entities.addAll(list(L.eqAndOr((List>) ids.subList(ids.size() - remaining, ids.size()))));
                    } else {
                        entities.addAll(list(L.eqAndOr(ids.subList(ids.size() - remaining, ids.size()), idPropNameList)));
                    }
                }
            }

            return entities;
        }

        public Optional findFirst(final Condition whereCause) {
            return findFirst((Collection) null, whereCause);
        }

        public Optional findFirst(final Collection selectPropNames, final Condition whereCause) {
            return findFirst(selectPropNames, whereCause, null);
        }

        public Optional findFirst(final Collection selectPropNames, final Condition whereCause, final JdbcSettings jdbcSettings) {
            return findFirst(null, selectPropNames, whereCause, jdbcSettings);
        }

        public Optional findFirst(final Connection conn, final Condition whereCause) {
            return findFirst(conn, null, whereCause);
        }

        public Optional findFirst(final Connection conn, final Collection selectPropNames, final Condition whereCause) {
            return findFirst(conn, selectPropNames, whereCause, null);
        }

        public Optional findFirst(final Connection conn, final Collection selectPropNames, final Condition whereCause,
                final JdbcSettings jdbcSettings) {
            final SP pair = prepareQuery(selectPropNames, whereCause);

            return sqlExecutor.findFirst(targetClass, conn, pair.sql, StatementSetter.DEFAULT, jdbcSettings, pair.parameters.toArray());
        }

        public  Optional findFirst(String selectPropName, final JdbcUtil.RecordGetter recordGetter, final Condition whereCause) {
            return findFirst(selectPropName, recordGetter, whereCause, null);
        }

        public  Optional findFirst(String selectPropName, final JdbcUtil.RecordGetter recordGetter, final Condition whereCause,
                final JdbcSettings jdbcSettings) {
            return findFirst(null, selectPropName, recordGetter, whereCause, jdbcSettings);
        }

        public  Optional findFirst(final Connection conn, String selectPropName, final JdbcUtil.RecordGetter recordGetter,
                final Condition whereCause) {
            return findFirst(conn, selectPropName, recordGetter, whereCause, null);
        }

        public  Optional findFirst(final Connection conn, String selectPropName, final JdbcUtil.RecordGetter recordGetter,
                final Condition whereCause, final JdbcSettings jdbcSettings) {
            return findFirst(conn, Arrays.asList(selectPropName), recordGetter, whereCause, jdbcSettings);
        }

        public  Optional findFirst(String selectPropName, final JdbcUtil.BiRecordGetter recordGetter, final Condition whereCause) {
            return findFirst(selectPropName, recordGetter, whereCause, null);
        }

        public  Optional findFirst(String selectPropName, final JdbcUtil.BiRecordGetter recordGetter, final Condition whereCause,
                final JdbcSettings jdbcSettings) {
            return findFirst(null, selectPropName, recordGetter, whereCause, jdbcSettings);
        }

        public  Optional findFirst(final Connection conn, String selectPropName, final JdbcUtil.BiRecordGetter recordGetter,
                final Condition whereCause) {
            return findFirst(conn, selectPropName, recordGetter, whereCause, null);
        }

        public  Optional findFirst(final Connection conn, String selectPropName, final JdbcUtil.BiRecordGetter recordGetter,
                final Condition whereCause, final JdbcSettings jdbcSettings) {
            return findFirst(conn, Arrays.asList(selectPropName), recordGetter, whereCause, jdbcSettings);
        }

        public  Optional findFirst(Collection selectPropNames, final JdbcUtil.RecordGetter recordGetter,
                final Condition whereCause) {
            return findFirst(selectPropNames, recordGetter, whereCause, null);
        }

        public  Optional findFirst(Collection selectPropNames, final JdbcUtil.RecordGetter recordGetter,
                final Condition whereCause, final JdbcSettings jdbcSettings) {
            return findFirst(null, selectPropNames, recordGetter, whereCause, jdbcSettings);
        }

        public  Optional findFirst(final Connection conn, Collection selectPropNames,
                final JdbcUtil.RecordGetter recordGetter, final Condition whereCause) {
            return findFirst(conn, selectPropNames, recordGetter, whereCause, null);
        }

        public  Optional findFirst(final Connection conn, Collection selectPropNames,
                final JdbcUtil.RecordGetter recordGetter, final Condition whereCause, final JdbcSettings jdbcSettings) {
            final SP pair = prepareQuery(selectPropNames, whereCause);

            return sqlExecutor.findFirst(conn, pair.sql, StatementSetter.DEFAULT, recordGetter, jdbcSettings, pair.parameters.toArray());
        }

        public  Optional findFirst(Collection selectPropNames, final JdbcUtil.BiRecordGetter recordGetter,
                final Condition whereCause) {
            return findFirst(selectPropNames, recordGetter, whereCause, null);
        }

        public  Optional findFirst(Collection selectPropNames, final JdbcUtil.BiRecordGetter recordGetter,
                final Condition whereCause, final JdbcSettings jdbcSettings) {
            return findFirst(null, selectPropNames, recordGetter, whereCause, jdbcSettings);
        }

        public  Optional findFirst(final Connection conn, Collection selectPropNames,
                final JdbcUtil.BiRecordGetter recordGetter, final Condition whereCause) {
            return findFirst(conn, selectPropNames, recordGetter, whereCause, null);
        }

        public  Optional findFirst(final Connection conn, Collection selectPropNames,
                final JdbcUtil.BiRecordGetter recordGetter, final Condition whereCause, final JdbcSettings jdbcSettings) {
            final SP pair = prepareQuery(selectPropNames, whereCause);

            final ResultExtractor resultExtractor = new ResultExtractor() {
                @Override
                public R extractData(ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
                    int offset = jdbcSettings.getOffset();

                    if (offset > 0) {
                        JdbcUtil.skip(rs, offset);
                    }

                    return rs.next() ? Objects.requireNonNull(recordGetter.apply(rs, JdbcUtil.getColumnLabelList(rs))) : null;
                }
            };

            return Optional.ofNullable(sqlExecutor.query(conn, pair.sql, StatementSetter.DEFAULT, resultExtractor, jdbcSettings, pair.parameters.toArray()));
        }

        public List list(final Condition whereCause) {
            return list((Collection) null, whereCause);
        }

        public List list(final Collection selectPropNames, final Condition whereCause) {
            return list(selectPropNames, whereCause, null);
        }

        public List list(final Collection selectPropNames, final Condition whereCause, final JdbcSettings jdbcSettings) {
            return list(null, selectPropNames, whereCause, jdbcSettings);
        }

        public List list(final Connection conn, final Condition whereCause) {
            return list(conn, null, whereCause);
        }

        public List list(final Connection conn, final Collection selectPropNames, final Condition whereCause) {
            return list(conn, selectPropNames, whereCause, null);
        }

        public List list(final Connection conn, final Collection selectPropNames, final Condition whereCause, final JdbcSettings jdbcSettings) {
            final SP pair = prepareQuery(selectPropNames, whereCause);

            return sqlExecutor.list(targetClass, conn, pair.sql, StatementSetter.DEFAULT, jdbcSettings, pair.parameters.toArray());
        }

        public  List list(String selectPropName, final JdbcUtil.RecordGetter recordGetter, final Condition whereCause) {
            return list(selectPropName, recordGetter, whereCause, null);
        }

        public  List list(String selectPropName, final JdbcUtil.RecordGetter recordGetter, final Condition whereCause,
                final JdbcSettings jdbcSettings) {
            return list(null, selectPropName, recordGetter, whereCause, jdbcSettings);
        }

        public  List list(final Connection conn, String selectPropName, final JdbcUtil.RecordGetter recordGetter,
                final Condition whereCause) {
            return list(conn, selectPropName, recordGetter, whereCause, null);
        }

        public  List list(final Connection conn, String selectPropName, final JdbcUtil.RecordGetter recordGetter,
                final Condition whereCause, final JdbcSettings jdbcSettings) {
            return list(conn, Arrays.asList(selectPropName), recordGetter, whereCause, jdbcSettings);
        }

        public  List list(String selectPropName, final JdbcUtil.BiRecordGetter recordGetter, final Condition whereCause) {
            return list(selectPropName, recordGetter, whereCause, null);
        }

        public  List list(String selectPropName, final JdbcUtil.BiRecordGetter recordGetter, final Condition whereCause,
                final JdbcSettings jdbcSettings) {
            return list(null, selectPropName, recordGetter, whereCause, jdbcSettings);
        }

        public  List list(final Connection conn, String selectPropName, final JdbcUtil.BiRecordGetter recordGetter,
                final Condition whereCause) {
            return list(conn, selectPropName, recordGetter, whereCause, null);
        }

        public  List list(final Connection conn, String selectPropName, final JdbcUtil.BiRecordGetter recordGetter,
                final Condition whereCause, final JdbcSettings jdbcSettings) {
            return list(conn, Arrays.asList(selectPropName), recordGetter, whereCause, jdbcSettings);
        }

        public  List list(Collection selectPropNames, final JdbcUtil.RecordGetter recordGetter, final Condition whereCause) {
            return list(selectPropNames, recordGetter, whereCause, null);
        }

        public  List list(Collection selectPropNames, final JdbcUtil.RecordGetter recordGetter, final Condition whereCause,
                final JdbcSettings jdbcSettings) {
            return list(null, selectPropNames, recordGetter, whereCause, jdbcSettings);
        }

        public  List list(final Connection conn, Collection selectPropNames, final JdbcUtil.RecordGetter recordGetter,
                final Condition whereCause) {
            return list(conn, selectPropNames, recordGetter, whereCause, null);
        }

        public  List list(final Connection conn, Collection selectPropNames, final JdbcUtil.RecordGetter recordGetter,
                final Condition whereCause, final JdbcSettings jdbcSettings) {
            N.checkArgNotNull(recordGetter);

            final JdbcUtil.BiRecordGetter biRecordGetter = new JdbcUtil.BiRecordGetter() {
                @Override
                public R apply(final ResultSet rs, final List columnLabels) throws SQLException {
                    return recordGetter.apply(rs);
                }
            };

            return list(conn, selectPropNames, biRecordGetter, whereCause, jdbcSettings);
        }

        public  List list(Collection selectPropNames, final JdbcUtil.BiRecordGetter recordGetter,
                final Condition whereCause) {
            return list(selectPropNames, recordGetter, whereCause, null);
        }

        public  List list(Collection selectPropNames, final JdbcUtil.BiRecordGetter recordGetter, final Condition whereCause,
                final JdbcSettings jdbcSettings) {
            return list(null, selectPropNames, recordGetter, whereCause, jdbcSettings);
        }

        public  List list(final Connection conn, Collection selectPropNames, final JdbcUtil.BiRecordGetter recordGetter,
                final Condition whereCause) {
            return list(conn, selectPropNames, recordGetter, whereCause, null);
        }

        public  List list(final Connection conn, Collection selectPropNames, final JdbcUtil.BiRecordGetter recordGetter,
                final Condition whereCause, final JdbcSettings jdbcSettings) {
            final SP pair = prepareQuery(selectPropNames, whereCause);

            return sqlExecutor.list(conn, pair.sql, StatementSetter.DEFAULT, recordGetter, jdbcSettings, pair.parameters.toArray());
        }

        /**
         * Query from multiple data sources specified in {@code JdbcSettings}.
         * 
         * @param whereCause
         * @param jdbcSettings
         * @return
         * @see SQLExecutor#listAll(Class, String, StatementSetter, JdbcSettings, Object...)
         */
        public List listAll(final Condition whereCause, final JdbcSettings jdbcSettings) {
            return listAll(null, whereCause, jdbcSettings);
        }

        /**
         * Query from multiple data sources specified in {@code JdbcSettings}.
         * 
         * @param selectPropNames
         * @param whereCause
         * @param jdbcSettings
         * @return
         * @see SQLExecutor#listAll(Class, String, StatementSetter, JdbcSettings, Object...)
         */
        public List listAll(final Collection selectPropNames, final Condition whereCause, final JdbcSettings jdbcSettings) {
            final SP pair = prepareQuery(selectPropNames, whereCause);

            return sqlExecutor.listAll(targetClass, pair.sql, StatementSetter.DEFAULT, jdbcSettings, pair.parameters.toArray());
        }

        public  List listAll(String selectPropName, final JdbcUtil.RecordGetter recordGetter, final Condition whereCause,
                final JdbcSettings jdbcSettings) {
            return listAll(Arrays.asList(selectPropName), recordGetter, whereCause, jdbcSettings);
        }

        public  List listAll(String selectPropName, final JdbcUtil.BiRecordGetter recordGetter, final Condition whereCause,
                final JdbcSettings jdbcSettings) {
            return listAll(Arrays.asList(selectPropName), recordGetter, whereCause, jdbcSettings);
        }

        public  List listAll(Collection selectPropNames, final JdbcUtil.RecordGetter recordGetter,
                final Condition whereCause, final JdbcSettings jdbcSettings) {
            N.checkArgNotNull(recordGetter);

            final JdbcUtil.BiRecordGetter biRecordGetter = new JdbcUtil.BiRecordGetter() {
                @Override
                public R apply(final ResultSet rs, final List columnLabels) throws SQLException {
                    return recordGetter.apply(rs);
                }
            };

            return listAll(selectPropNames, biRecordGetter, whereCause, jdbcSettings);
        }

        public  List listAll(Collection selectPropNames, final JdbcUtil.BiRecordGetter recordGetter,
                final Condition whereCause, final JdbcSettings jdbcSettings) {
            final SP pair = prepareQuery(selectPropNames, whereCause);

            return sqlExecutor.listAll(pair.sql, StatementSetter.DEFAULT, recordGetter, jdbcSettings, pair.parameters.toArray());
        }

        /**
         * Remember to close the returned Stream to close the underlying ResultSet.
         * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
         * 
         * 
         * @param whereCause
         * @return
         */
        public Stream stream(final Condition whereCause) {
            return stream((Collection) null, whereCause);
        }

        /**
         * Remember to close the returned Stream to close the underlying ResultSet.
         * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
         * 
         * 
         * @param selectPropNames
         * @param whereCause
         * @return
         */
        public Stream stream(final Collection selectPropNames, final Condition whereCause) {
            return stream(selectPropNames, whereCause, null);
        }

        /**
         * Remember to close the returned Stream to close the underlying ResultSet.
         * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
         * 
         * 
         * @param selectPropNames
         * @param whereCause
         * @param jdbcSettings
         * @return
         */
        public Stream stream(final Collection selectPropNames, final Condition whereCause, final JdbcSettings jdbcSettings) {
            final SP pair = prepareQuery(selectPropNames, whereCause);

            return sqlExecutor.stream(targetClass, pair.sql, StatementSetter.DEFAULT, jdbcSettings, pair.parameters.toArray());
        }

        /**
         * Remember to close the returned Stream to close the underlying ResultSet.
         * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
         * 
         * 
         * @param selectPropName
         * @param recordGetter
         * @param whereCause
         * @return
         */
        public  Stream stream(String selectPropName, final JdbcUtil.RecordGetter recordGetter, final Condition whereCause) {
            return stream(selectPropName, recordGetter, whereCause, null);
        }

        /**
         * Remember to close the returned Stream to close the underlying ResultSet.
         * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
         * 
         * 
         * @param selectPropName
         * @param recordGetter
         * @param whereCause
         * @param jdbcSettings
         * @return
         */
        public  Stream stream(String selectPropName, final JdbcUtil.RecordGetter recordGetter, final Condition whereCause,
                final JdbcSettings jdbcSettings) {
            return stream(Arrays.asList(selectPropName), recordGetter, whereCause, jdbcSettings);
        }

        /**
         * Remember to close the returned Stream to close the underlying ResultSet.
         * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
         * 
         * 
         * @param selectPropName
         * @param recordGetter
         * @param whereCause
         * @return
         */
        public  Stream stream(String selectPropName, final JdbcUtil.BiRecordGetter recordGetter, final Condition whereCause) {
            return stream(selectPropName, recordGetter, whereCause, null);
        }

        /**
         * Remember to close the returned Stream to close the underlying ResultSet.
         * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
         * 
         * 
         * @param selectPropName
         * @param recordGetter
         * @param whereCause
         * @param jdbcSettings
         * @return
         */
        public  Stream stream(String selectPropName, final JdbcUtil.BiRecordGetter recordGetter, final Condition whereCause,
                final JdbcSettings jdbcSettings) {
            return stream(Arrays.asList(selectPropName), recordGetter, whereCause, jdbcSettings);
        }

        /**
         * Remember to close the returned Stream to close the underlying ResultSet.
         * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
         * 
         * 
         * @param selectPropNames
         * @param recordGetter
         * @param whereCause
         * @return
         */
        public  Stream stream(Collection selectPropNames, final JdbcUtil.RecordGetter recordGetter,
                final Condition whereCause) {
            return stream(selectPropNames, recordGetter, whereCause, null);
        }

        /**
         * Remember to close the returned Stream to close the underlying ResultSet.
         * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
         * 
         * 
         * @param selectPropNames
         * @param recordGetter
         * @param whereCause
         * @param jdbcSettings
         * @return
         */
        public  Stream stream(Collection selectPropNames, final JdbcUtil.RecordGetter recordGetter,
                final Condition whereCause, final JdbcSettings jdbcSettings) {
            N.checkArgNotNull(recordGetter);

            final JdbcUtil.BiRecordGetter biRecordGetter = new JdbcUtil.BiRecordGetter() {
                @Override
                public R apply(final ResultSet rs, final List columnLabels) throws SQLException {
                    return recordGetter.apply(rs);
                }
            };

            return stream(selectPropNames, biRecordGetter, whereCause, jdbcSettings);
        }

        /**
         * Remember to close the returned Stream to close the underlying ResultSet.
         * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
         * 
         * 
         * @param selectPropNames
         * @param recordGetter
         * @param whereCause
         * @return
         */
        public  Stream stream(Collection selectPropNames, final JdbcUtil.BiRecordGetter recordGetter,
                final Condition whereCause) {
            return stream(selectPropNames, recordGetter, whereCause, null);
        }

        /**
         * Remember to close the returned Stream to close the underlying ResultSet.
         * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
         * 
         * 
         * @param selectPropNames
         * @param recordGetter
         * @param whereCause
         * @param jdbcSettings
         * @return
         */
        public  Stream stream(Collection selectPropNames, final JdbcUtil.BiRecordGetter recordGetter,
                final Condition whereCause, final JdbcSettings jdbcSettings) {
            final SP pair = prepareQuery(selectPropNames, whereCause);

            return sqlExecutor.stream(pair.sql, StatementSetter.DEFAULT, recordGetter, jdbcSettings, pair.parameters.toArray());
        }

        /**
         * Query from multiple data sources specified in {@code JdbcSettings}.
         * Remember to close the returned Stream to close the underlying ResultSet.
         * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
         *  
         * 
         * @param whereCause
         * @param jdbcSettings
         * @return
         * @see SQLExecutor#streamAll(Class, String, StatementSetter, JdbcSettings, Object...)
         */
        public Stream streamAll(final Condition whereCause, final JdbcSettings jdbcSettings) {
            return streamAll(null, whereCause, jdbcSettings);
        }

        /**
         * Query from multiple data sources specified in {@code JdbcSettings}.
         * Remember to close the returned Stream to close the underlying ResultSet.
         * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
         *  
         * 
         * @param selectPropNames
         * @param whereCause
         * @param jdbcSettings
         * @return
         * @see SQLExecutor#streamAll(Class, String, StatementSetter, JdbcSettings, Object...)
         */
        public Stream streamAll(final Collection selectPropNames, final Condition whereCause, final JdbcSettings jdbcSettings) {
            final SP pair = prepareQuery(selectPropNames, whereCause);

            return sqlExecutor.streamAll(targetClass, pair.sql, StatementSetter.DEFAULT, jdbcSettings, pair.parameters.toArray());
        }

        /**
         * Query from multiple data sources specified in {@code JdbcSettings}.
         * Remember to close the returned Stream to close the underlying ResultSet.
         * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
         *  
         * 
         * @param selectPropName
         * @param recordGetter
         * @param whereCause
         * @param jdbcSettings
         * @return
         */
        public  Stream streamAll(String selectPropName, final JdbcUtil.RecordGetter recordGetter, final Condition whereCause,
                final JdbcSettings jdbcSettings) {
            return streamAll(Arrays.asList(selectPropName), recordGetter, whereCause, jdbcSettings);
        }

        /**
         * Query from multiple data sources specified in {@code JdbcSettings}.
         * Remember to close the returned Stream to close the underlying ResultSet.
         * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
         *  
         * 
         * @param selectPropName
         * @param recordGetter
         * @param whereCause
         * @param jdbcSettings
         * @return
         */
        public  Stream streamAll(String selectPropName, final JdbcUtil.BiRecordGetter recordGetter, final Condition whereCause,
                final JdbcSettings jdbcSettings) {
            return streamAll(Arrays.asList(selectPropName), recordGetter, whereCause, jdbcSettings);
        }

        /**
         * Query from multiple data sources specified in {@code JdbcSettings}.
         * Remember to close the returned Stream to close the underlying ResultSet.
         * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
         *  
         * 
         * @param selectPropNames
         * @param recordGetter
         * @param whereCause
         * @param jdbcSettings
         * @return
         */
        public  Stream streamAll(Collection selectPropNames, final JdbcUtil.RecordGetter recordGetter,
                final Condition whereCause, final JdbcSettings jdbcSettings) {
            N.checkArgNotNull(recordGetter);

            final JdbcUtil.BiRecordGetter biRecordGetter = new JdbcUtil.BiRecordGetter() {
                @Override
                public R apply(final ResultSet rs, final List columnLabels) throws SQLException {
                    return recordGetter.apply(rs);
                }
            };

            return streamAll(selectPropNames, biRecordGetter, whereCause, jdbcSettings);
        }

        /**
         * Query from multiple data sources specified in {@code JdbcSettings}.
         * Remember to close the returned Stream to close the underlying ResultSet.
         * {@code stream} operation won't be part of transaction or use the connection created by {@code Transaction} even it's in transaction block/range.
         *  
         *  
         * @param selectPropNames
         * @param recordGetter
         * @param whereCause
         * @param jdbcSettings
         * @return
         */
        public  Stream streamAll(Collection selectPropNames, final JdbcUtil.BiRecordGetter recordGetter,
                final Condition whereCause, final JdbcSettings jdbcSettings) {
            final SP pair = prepareQuery(selectPropNames, whereCause);

            return sqlExecutor.streamAll(pair.sql, StatementSetter.DEFAULT, recordGetter, jdbcSettings, pair.parameters.toArray());
        }

        public DataSet query(final Condition whereCause) {
            return query((Collection) null, whereCause);
        }

        public DataSet query(final Collection selectPropNames, final Condition whereCause) {
            return query(selectPropNames, whereCause, null);
        }

        public DataSet query(final Collection selectPropNames, final Condition whereCause, final JdbcSettings jdbcSettings) {
            return query(null, selectPropNames, whereCause, jdbcSettings);
        }

        public DataSet query(final Connection conn, final Condition whereCause) {
            return query(conn, null, whereCause);
        }

        public DataSet query(final Connection conn, final Collection selectPropNames, final Condition whereCause) {
            return query(conn, selectPropNames, whereCause, null);
        }

        public DataSet query(final Connection conn, final Collection selectPropNames, final Condition whereCause, final JdbcSettings jdbcSettings) {
            final SP pair = prepareQuery(selectPropNames, whereCause);

            return sqlExecutor.query(conn, pair.sql, StatementSetter.DEFAULT, null, jdbcSettings, pair.parameters.toArray());
        }

        /**
         * Query from multiple data sources specified in {@code JdbcSettings}.
         * 
         * @param whereCause
         * @param jdbcSettings
         * @return
         * @see SQLExecutor#queryAll(String, StatementSetter, JdbcSettings, Object...)
         */
        public DataSet queryAll(final Condition whereCause, final JdbcSettings jdbcSettings) {
            return queryAll(null, whereCause, jdbcSettings);
        }

        /**
         * Query from multiple data sources specified in {@code JdbcSettings}.
         * 
         * @param selectPropNames
         * @param whereCause
         * @param jdbcSettings
         * @return
         * @see SQLExecutor#queryAll(String, StatementSetter, JdbcSettings, Object...)
         */
        public DataSet queryAll(final Collection selectPropNames, final Condition whereCause, final JdbcSettings jdbcSettings) {
            final SP pair = prepareQuery(selectPropNames, whereCause);

            return sqlExecutor.queryAll(pair.sql, StatementSetter.DEFAULT, jdbcSettings, pair.parameters.toArray());
        }

        /**
         * 
         * @param selectPropName
         * @param whereCause
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public OptionalBoolean queryForBoolean(final String selectPropName, final Condition whereCause) {
            final SP pair = prepareQuery(Arrays.asList(selectPropName), whereCause, 1);

            return sqlExecutor.queryForBoolean(pair.sql, pair.parameters.toArray());
        }

        /**
         * 
         * @param selectPropName
         * @param whereCause
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public OptionalChar queryForChar(final String selectPropName, final Condition whereCause) {
            final SP pair = prepareQuery(Arrays.asList(selectPropName), whereCause, 1);

            return sqlExecutor.queryForChar(pair.sql, pair.parameters.toArray());
        }

        /**
         * 
         * @param selectPropName
         * @param whereCause
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public OptionalByte queryForByte(final String selectPropName, final Condition whereCause) {
            final SP pair = prepareQuery(Arrays.asList(selectPropName), whereCause, 1);

            return sqlExecutor.queryForByte(pair.sql, pair.parameters.toArray());
        }

        /**
         * 
         * @param selectPropName
         * @param whereCause
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public OptionalShort queryForShort(final String selectPropName, final Condition whereCause) {
            final SP pair = prepareQuery(Arrays.asList(selectPropName), whereCause, 1);

            return sqlExecutor.queryForShort(pair.sql, pair.parameters.toArray());
        }

        /**
         * 
         * @param selectPropName
         * @param whereCause
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public OptionalInt queryForInt(final String selectPropName, final Condition whereCause) {
            final SP pair = prepareQuery(Arrays.asList(selectPropName), whereCause, 1);

            return sqlExecutor.queryForInt(pair.sql, pair.parameters.toArray());
        }

        /**
         * 
         * @param selectPropName
         * @param whereCause
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public OptionalLong queryForLong(final String selectPropName, final Condition whereCause) {
            final SP pair = prepareQuery(Arrays.asList(selectPropName), whereCause, 1);

            return sqlExecutor.queryForLong(pair.sql, pair.parameters.toArray());
        }

        /**
         * 
         * @param selectPropName
         * @param whereCause
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public OptionalFloat queryForFloat(final String selectPropName, final Condition whereCause) {
            final SP pair = prepareQuery(Arrays.asList(selectPropName), whereCause, 1);

            return sqlExecutor.queryForFloat(pair.sql, pair.parameters.toArray());
        }

        /**
         * 
         * @param selectPropName
         * @param whereCause
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public OptionalDouble queryForDouble(final String selectPropName, final Condition whereCause) {
            final SP pair = prepareQuery(Arrays.asList(selectPropName), whereCause, 1);

            return sqlExecutor.queryForDouble(pair.sql, pair.parameters.toArray());
        }

        /**
         * 
         * @param selectPropName
         * @param whereCause
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public Nullable queryForBigDecimal(final String selectPropName, final Condition whereCause) {
            final SP pair = prepareQuery(Arrays.asList(selectPropName), whereCause, 1);

            return sqlExecutor.queryForBigDecimal(pair.sql, pair.parameters.toArray());
        }

        /**
         * 
         * @param selectPropName
         * @param whereCause
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public Nullable queryForString(final String selectPropName, final Condition whereCause) {
            final SP pair = prepareQuery(Arrays.asList(selectPropName), whereCause, 1);

            return sqlExecutor.queryForString(pair.sql, pair.parameters.toArray());
        }

        /**
         * 
         * @param selectPropName
         * @param whereCause
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public Nullable queryForDate(final String selectPropName, final Condition whereCause) {
            final SP pair = prepareQuery(Arrays.asList(selectPropName), whereCause, 1);

            return sqlExecutor.queryForDate(pair.sql, pair.parameters.toArray());
        }

        /**
         * 
         * @param selectPropName
         * @param whereCause
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public Nullable queryForTime(final String selectPropName, final Condition whereCause) {
            final SP pair = prepareQuery(Arrays.asList(selectPropName), whereCause, 1);

            return sqlExecutor.queryForTime(pair.sql, pair.parameters.toArray());
        }

        /**
         * 
         * @param selectPropName
         * @param whereCause
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public Nullable queryForTimestamp(final String selectPropName, final Condition whereCause) {
            final SP pair = prepareQuery(Arrays.asList(selectPropName), whereCause, 1);

            return sqlExecutor.queryForTimestamp(pair.sql, pair.parameters.toArray());
        }

        /**
         * 
         * @param targetValueClass
         * @param selectPropName
         * @param id
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public  Nullable queryForSingleResult(final Class targetValueClass, final String selectPropName, final Object id) {
            return queryForSingleResult(targetValueClass, selectPropName, id2Cond(id, false));
        }

        /**
         * 
         * @param targetValueClass
         * @param selectPropName
         * @param whereCause
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public  Nullable queryForSingleResult(final Class targetValueClass, final String selectPropName, final Condition whereCause) {
            return queryForSingleResult(targetValueClass, selectPropName, whereCause, null);
        }

        /**
         * 
         * @param targetValueClass
         * @param selectPropName
         * @param whereCause
         * @param jdbcSettings
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public  Nullable queryForSingleResult(final Class targetValueClass, final String selectPropName, final Condition whereCause,
                final JdbcSettings jdbcSettings) {
            return queryForSingleResult(targetValueClass, null, selectPropName, whereCause, jdbcSettings);
        }

        /**
         * 
         * @param targetValueClass
         * @param conn
         * @param selectPropName
         * @param id
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public  Nullable queryForSingleResult(final Class targetValueClass, final Connection conn, final String selectPropName, final Object id) {
            return queryForSingleResult(targetValueClass, conn, selectPropName, id2Cond(id, false));
        }

        /**
         * 
         * @param targetValueClass
         * @param conn
         * @param selectPropName
         * @param whereCause
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public  Nullable queryForSingleResult(final Class targetValueClass, final Connection conn, final String selectPropName,
                final Condition whereCause) {
            return queryForSingleResult(targetValueClass, conn, selectPropName, whereCause, null);
        }

        /**
         * Returns a {@code Nullable} describing the value in the first row/column if it exists, otherwise return an empty {@code Nullable}.
         * 
         * @param targetValueClass
         * @param conn
         * @param selectPropName
         * @param whereCause
         * @param jdbcSettings
         * @return
         * @see Mapper#queryForSingleResult(Class, Connection, String, Condition, JdbcSettings)
         */
        public  Nullable queryForSingleResult(final Class targetValueClass, final Connection conn, final String selectPropName,
                final Condition whereCause, final JdbcSettings jdbcSettings) {
            final SP pair = prepareQuery(Arrays.asList(selectPropName), whereCause, 1);

            return sqlExecutor.queryForSingleResult(targetValueClass, conn, pair.sql, StatementSetter.DEFAULT, jdbcSettings, pair.parameters.toArray());
        }

        public  Nullable queryForUniqueResult(final Class targetValueClass, final String selectPropName, final Object id)
                throws DuplicatedResultException {
            return queryForUniqueResult(targetValueClass, selectPropName, id2Cond(id, false));
        }

        public  Nullable queryForUniqueResult(final Class targetValueClass, final String selectPropName, final Condition whereCause)
                throws DuplicatedResultException {
            return queryForUniqueResult(targetValueClass, selectPropName, whereCause, null);
        }

        public  Nullable queryForUniqueResult(final Class targetValueClass, final String selectPropName, final Condition whereCause,
                final JdbcSettings jdbcSettings) throws DuplicatedResultException {
            return queryForUniqueResult(targetValueClass, null, selectPropName, whereCause, jdbcSettings);
        }

        public  Nullable queryForUniqueResult(final Class targetValueClass, final Connection conn, final String selectPropName, final Object id)
                throws DuplicatedResultException {
            return queryForUniqueResult(targetValueClass, conn, selectPropName, id2Cond(id, false));
        }

        public  Nullable queryForUniqueResult(final Class targetValueClass, final Connection conn, final String selectPropName,
                final Condition whereCause) throws DuplicatedResultException {
            return queryForUniqueResult(targetValueClass, conn, selectPropName, whereCause, null);
        }

        public  Nullable queryForUniqueResult(final Class targetValueClass, final Connection conn, final String selectPropName,
                final Condition whereCause, final JdbcSettings jdbcSettings) throws DuplicatedResultException {
            final SP pair = prepareQuery(Arrays.asList(selectPropName), whereCause, 1);

            return sqlExecutor.queryForUniqueResult(targetValueClass, conn, pair.sql, StatementSetter.DEFAULT, jdbcSettings, pair.parameters.toArray());
        }

        private SP prepareQuery(final Collection selectPropNames, final Condition whereCause) {
            return prepareQuery(selectPropNames, whereCause, 0);
        }

        private SP prepareQuery(Collection selectPropNames, final Condition whereCause, final int count) {
            if (N.isNullOrEmpty(selectPropNames)) {
                selectPropNames = defaultSelectPropNameList;
            }

            SQLBuilder sqlBuilder = null;

            switch (namingPolicy) {
                case LOWER_CASE_WITH_UNDERSCORE:
                    if (N.isNullOrEmpty(selectPropNames)) {
                        sqlBuilder = NE.selectFrom(targetClass).where(whereCause);
                    } else {
                        sqlBuilder = NE.select(selectPropNames).from(targetClass).where(whereCause);
                    }

                    break;

                case UPPER_CASE_WITH_UNDERSCORE:
                    if (N.isNullOrEmpty(selectPropNames)) {
                        sqlBuilder = NE2.selectFrom(targetClass).where(whereCause);
                    } else {
                        sqlBuilder = NE2.select(selectPropNames).from(targetClass).where(whereCause);
                    }

                    break;

                case LOWER_CAMEL_CASE:
                    if (N.isNullOrEmpty(selectPropNames)) {
                        sqlBuilder = NE3.selectFrom(targetClass).where(whereCause);
                    } else {
                        sqlBuilder = NE3.select(selectPropNames).from(targetClass).where(whereCause);
                    }

                    break;

                default:
                    throw new RuntimeException("Unsupported naming policy: " + namingPolicy);
            }

            if (count > 0 && count < Integer.MAX_VALUE) {
                switch (sqlExecutor.dbVersion()) {
                    case ORACLE:
                    case SQL_SERVER:
                        // Do nothing because limit is not supported.

                        break;

                    default:
                        sqlBuilder.limit(count);
                }
            }

            return sqlBuilder.pair();
        }

        /**
         * Insert the specified entity into data store, and set back the auto-generated id to the specified entity if there is the auto-generated id.
         * 
         * @param entity
         * @return the auto-generated id or null if there is no auto-generated id.
         */
        public  ID add(final T entity) {
            return add(null, entity);
        }

        public  ID add(final T entity, final Collection insertPropNames) {
            return add(null, entity, insertPropNames);
        }

        /**
         * 
         * @param props
         * @return the auto-generated id or null if there is no auto-generated id.
         */
        public  ID add(final Map props) {
            return add(null, props);
        }

        public  ID add(final Connection conn, final T entity) {
            return add(conn, entity, null);
        }

        @SuppressWarnings("deprecation")
        public  ID add(final Connection conn, final T entity, final Collection insertPropNames) {
            N.checkArgNotNull(entity);

            if (entity instanceof DirtyMarker && N.isNullOrEmpty(((DirtyMarker) entity).signedPropNames())) {
                throw new IllegalArgumentException("No property value is signed to the specified entity");
            }

            if (insertPropNames == null) {
                final SP pair = prepareAdd(entity);

                final ID id = sqlExecutor.insert(conn, pair.sql, pair.parameters.toArray());

                postAdd(entity, id);

                return id;
            } else {
                final SP pair = prepareAdd(insertPropNames);

                final ID id = sqlExecutor.insert(conn, pair.sql, entity);

                postAdd(entity, id);

                return id;
            }
        }

        /**
         * @param conn
         * @param props
         * @return the auto-generated id or null if there is no auto-generated id.
         */
        public  ID add(final Connection conn, final Map props) {
            N.checkArgNotNull(props);

            final SP pair = prepareAdd(props);

            return sqlExecutor.insert(conn, pair.sql, pair.parameters.toArray());
        }

        //        /**
        //         * Insert All the records one by one in transaction. And set back auto-generated ids to the specified entities if there are the auto-generated ids.
        //         * 
        //         * @param entities
        //         * @return a list with the auto-generated id or null element if there is no auto-generated id.
        //         * @deprecated replaced by {@code #batchAdd(Collection)}
        //         */
        //        @Deprecated
        //        public  List addAll(final Collection entities) {
        //            return addAll(entities, IsolationLevel.DEFAULT);
        //        }
        //
        //        /**
        //         * Insert All the records one by one in transaction.
        //         * 
        //         * @param entities
        //         * @param isolationLevel
        //         * @return a list with the auto-generated id or null element if there is no auto-generated id.
        //         * @deprecated replaced by {@code #batchAdd(Collection)}
        //         */
        //        @Deprecated
        //        public  List addAll(final Collection entities, final IsolationLevel isolationLevel) {
        //            if (N.isNullOrEmpty(entities)) {
        //                return new ArrayList<>();
        //            }
        //
        //            final SQLTransaction tran = sqlExecutor.beginTransaction(isolationLevel);
        //            final List result = new ArrayList<>(entities.size());
        //            boolean isOk = false;
        //
        //            try {
        //                for (T entity : entities) {
        //                    result.add((ID) add(entity));
        //                }
        //
        //                isOk = true;
        //            } finally {
        //                if (tran != null) {
        //                    if (isOk) {
        //                        tran.commit();
        //                    } else {
        //                        tran.rollback();
        //                    }
        //                }
        //            }
        //
        //            return result;
        //        }
        //
        //        /**
        //         * Insert All the records one by one with specified {@code Connection}.
        //         * 
        //         * @param conn
        //         * @param entities
        //         * @return
        //         * @deprecated replaced by {@code #batchAdd(Collection)}
        //         */
        //        @Deprecated
        //        public  List addAll(final Connection conn, final Collection entities) {
        //            if (N.isNullOrEmpty(entities)) {
        //                return new ArrayList<>();
        //            }
        //
        //            if (conn == null) {
        //                return addAll(entities);
        //            }
        //
        //            final List result = new ArrayList<>(entities.size());
        //
        //            for (T entity : entities) {
        //                result.add((ID) add(conn, entity));
        //            }
        //
        //            return result;
        //        }

        /**
         * Insert All the records by batch operation. And set back auto-generated ids to the specified entities if there are the auto-generated ids.
         * 
         * @param entities which must have the same updated properties set.
         * @return the auto-generated id list or an empty list if there is no auto-generated id.
         */
        public  List batchAdd(final Collection entities) {
            return batchAdd(entities, JdbcSettings.DEFAULT_BATCH_SIZE);
        }

        public  List batchAdd(final Collection entities, final int batchSize) {
            return batchAdd(entities, batchSize, IsolationLevel.DEFAULT);
        }

        public  List batchAdd(final Collection entities, final int batchSize, final IsolationLevel isolationLevel) {
            return batchAdd(null, entities, batchSize, isolationLevel);
        }

        public  List batchAdd(final Connection conn, final Collection entities) {
            return batchAdd(conn, entities, JdbcSettings.DEFAULT_BATCH_SIZE);
        }

        /**
         * Insert All the records by batch operation. And set back auto-generated ids to the specified entities if there are the auto-generated ids.
         * 
         * @param entities which must have the same updated properties set.
         * @param batchSize Default value is 200.
         * @return the auto-generated id list or an empty list if there is no auto-generated id.
         */
        public  List batchAdd(final Connection conn, final Collection entities, final int batchSize) {
            return batchAdd(conn, entities, batchSize, IsolationLevel.DEFAULT);
        }

        private  List batchAdd(final Connection conn, final Collection entities, final int batchSize,
                final IsolationLevel isolationLevel) {
            N.checkArgPositive(batchSize, "batchSize");

            if (N.isNullOrEmpty(entities)) {
                return new ArrayList<>();
            }

            final SP pair = prepareAdd(entities.iterator().next());
            final JdbcSettings jdbcSettings = JdbcSettings.create().setBatchSize(batchSize).setIsolationLevel(isolationLevel);
            final List parametersList = entities instanceof List ? (List) entities : new ArrayList<>(entities);

            final List ids = sqlExecutor.batchInsert(conn, pair.sql, StatementSetter.DEFAULT, jdbcSettings, parametersList);

            if (N.notNullOrEmpty(ids) && ids.size() == batchSize) {
                int idx = 0;

                for (T entity : entities) {
                    postAdd(entity, ids.get(idx++));
                }
            }

            return ids;
        }

        private SP prepareAdd(final Collection insertPropNames) {
            SP pair = null;

            switch (namingPolicy) {
                case LOWER_CASE_WITH_UNDERSCORE:
                    pair = NE.insert(insertPropNames).into(targetClass).pair();
                    break;

                case UPPER_CASE_WITH_UNDERSCORE:
                    pair = NE2.insert(insertPropNames).into(targetClass).pair();
                    break;

                case LOWER_CAMEL_CASE:
                    pair = NE3.insert(insertPropNames).into(targetClass).pair();
                    break;

                default:
                    throw new RuntimeException("Unsupported naming policy: " + namingPolicy);
            }
            return pair;
        }

        private SP prepareAdd(final Object entity) {
            checkEntity(entity);

            if (entity instanceof Map) {
                return prepareAdd((Map) entity);
            } else if (entity instanceof DirtyMarker) {
                @SuppressWarnings("deprecation")
                final Set signedPropNames = ((DirtyMarker) entity).signedPropNames();
                final Map props = new HashMap<>(N.initHashCapacity(signedPropNames.size()));

                for (String propName : signedPropNames) {
                    props.put(propName, ClassUtil.getPropValue(entity, propName));
                }

                return prepareAdd(props);
            } else {
                switch (namingPolicy) {
                    case LOWER_CASE_WITH_UNDERSCORE:
                        return NE.insert(entity).into(targetClass).pair();

                    case UPPER_CASE_WITH_UNDERSCORE:
                        return NE2.insert(entity).into(targetClass).pair();

                    case LOWER_CAMEL_CASE:
                        return NE3.insert(entity).into(targetClass).pair();

                    default:
                        throw new RuntimeException("Unsupported naming policy: " + namingPolicy);
                }
            }
        }

        private SP prepareAdd(final Map props) {
            N.checkArgument(N.notNullOrEmpty(props), "The specified 'props' can't be null or empty");

            switch (namingPolicy) {
                case LOWER_CASE_WITH_UNDERSCORE:
                    return NE.insert(props).into(targetClass).pair();

                case UPPER_CASE_WITH_UNDERSCORE:
                    return NE2.insert(props).into(targetClass).pair();

                case LOWER_CAMEL_CASE:
                    return NE3.insert(props).into(targetClass).pair();

                default:
                    throw new RuntimeException("Unsupported naming policy: " + namingPolicy);
            }
        }

        @SuppressWarnings("deprecation")
        private  void postAdd(final T entity, final E idVal) {
            if (idVal != null && isEntity(entity)) {
                ClassUtil.setPropValue(entity, idPropName, idVal);
            }

            if (entity instanceof DirtyMarker) {
                ((DirtyMarker) entity).markDirty(false);
            }
        }

        /**
         * Execute {@code add} and return the added entity if the record doesn't, otherwise, {@code update} is executed and updated db record is returned. 
         * 
         * @param entity
         * @return  
         */
        public T addOrUpdate(final T entity) {
            final T dbEntity = idPropNameList.size() == 1 ? gett(ClassUtil.getPropValue(entity, idPropName)) : gett(entity);

            if (dbEntity == null) {
                add(entity);
                return entity;
            } else {
                N.merge(entity, dbEntity);
                update(dbEntity);
                return dbEntity;
            }
        }

        /**
         * Execute {@code add} and return the added entity if the record doesn't, otherwise, {@code update} is executed and updated db record is returned. 
         * 
         * @param entity
         * @param whereCause to verify if the record exists or not.
         * @return
         */
        public T addOrUpdate(final T entity, final Condition whereCause) {
            N.checkArgNotNull(whereCause, "whereCause");

            final T dbEntity = findFirst(whereCause).orNull();

            if (dbEntity == null) {
                add(entity);
                return entity;
            } else {
                N.merge(entity, dbEntity, false, idPropNameSet);
                update(dbEntity);
                return dbEntity;
            }
        }

        @SuppressWarnings("deprecation")
        public boolean refresh(final T entity) {
            final Collection refreshPropNames = entity instanceof DirtyMarker ? ((DirtyMarker) entity).signedPropNames() : defaultSelectPropNameList;

            return refresh(entity, refreshPropNames);
        }

        /**
         * 
         * @param entity
         * @param refreshPropNames
         * @return {@code false} if no record found by the ids in the specified {@code entity}.
         */
        @SuppressWarnings("deprecation")
        public boolean refresh(final T entity, Collection refreshPropNames) {
            if (N.isNullOrEmpty(refreshPropNames)) {
                return idPropNameList.size() == 1 ? exists(ClassUtil.getPropValue(entity, idPropName)) : exists(entity);
            }

            final T dbEntity = idPropNameList.size() == 1 ? gett(ClassUtil.getPropValue(entity, idPropName), refreshPropNames) : gett(entity, refreshPropNames);

            if (dbEntity == null) {
                return false;
            } else {
                N.merge(dbEntity, entity);

                if (entity instanceof DirtyMarker) {
                    ((DirtyMarker) entity).markDirty(refreshPropNames, false);
                }

                return true;
            }
        }

        public int update(final T entity) {
            return update(entity, (Collection) null);
        }

        public int update(final T entity, final Collection updatePropNames) {
            return update((Connection) null, entity, updatePropNames);
        }

        public int update(final Object id, final Map props) {
            return update((Connection) null, id, props);
        }

        public int update(final Map props, final Condition whereCause) {
            return update((Connection) null, props, whereCause);
        }

        public int update(final Connection conn, final T entity) {
            return update(conn, entity, (Collection) null);
        }

        @SuppressWarnings("deprecation")
        public int update(final Connection conn, final T entity, final Collection updatePropNames) {
            N.checkArgNotNull(entity);

            if (updatePropNames == null && entity instanceof DirtyMarker && !((DirtyMarker) entity).isDirty()) {
                return 0;
            }

            final SP pair = prepareUpdate(entity, updatePropNames);

            final int updateCount = sqlExecutor.update(conn, pair.sql, pair.parameters.toArray());

            postUpdate(entity, updatePropNames);

            return updateCount;
        }

        public int update(final Connection conn, final Object id, final Map props) {
            N.checkArgNotNull(id);

            return update(conn, props, id2Cond(id, false));
        }

        public int update(final Connection conn, final Map props, final Condition whereCause) {
            N.checkArgNotNull(props);
            N.checkArgNotNull(whereCause);

            if (N.isNullOrEmpty(props)) {
                return 0;
            }

            final SP pair = prepareUpdate(whereCause, props);

            return sqlExecutor.update(conn, pair.sql, pair.parameters.toArray());
        }

        //        /**
        //         * Update All the records one by one in transaction.
        //         * 
        //         * @param entities
        //         * @return
        //         * @deprecated replaced by {@code #batchUpdate(Collection)}
        //         */
        //        @Deprecated
        //        public int updateAll(final Collection entities) {
        //            return updateAll(entities, (Collection) null);
        //        }
        //
        //        /**
        //         * 
        //         * @param entities
        //         * @param updatePropNames
        //         * @return
        //         * @deprecated replaced by {@code #batchUpdate(Collection)}
        //         */
        //        @Deprecated
        //        public int updateAll(final Collection entities, final Collection updatePropNames) {
        //            return updateAll(entities, updatePropNames, IsolationLevel.DEFAULT);
        //        }
        //
        //        /**
        //         * 
        //         * @param entities
        //         * @param isolationLevel
        //         * @return
        //         * @deprecated replaced by {@code #batchUpdate(Collection)}
        //         */
        //        @Deprecated
        //        public int updateAll(final Collection entities, final IsolationLevel isolationLevel) {
        //            return updateAll(entities, (Collection) null, isolationLevel);
        //        }
        //
        //        /**
        //         * Update All the records one by one in transaction.
        //         * 
        //         * @param entities
        //         * @param updatePropNames
        //         * @param isolationLevel
        //         * @return
        //         * @deprecated replaced by {@code #batchUpdate(Collection)}
        //         */
        //        @Deprecated
        //        public int updateAll(final Collection entities, final Collection updatePropNames, final IsolationLevel isolationLevel) {
        //            if (N.isNullOrEmpty(entities)) {
        //                return 0;
        //            }
        //
        //            final SQLTransaction tran = sqlExecutor.beginTransaction(isolationLevel);
        //            int result = 0;
        //            boolean isOk = false;
        //
        //            try {
        //                for (T entity : entities) {
        //                    result += update(entity, updatePropNames);
        //                }
        //
        //                isOk = true;
        //            } finally {
        //                if (tran != null) {
        //                    if (isOk) {
        //                        tran.commit();
        //                    } else {
        //                        tran.rollback();
        //                    }
        //                }
        //            }
        //
        //            return result;
        //        }
        //
        //        /**
        //         * 
        //         * @param conn
        //         * @param entities
        //         * @return
        //         * @deprecated replaced by {@code #batchUpdate(Collection)}
        //         */
        //        @Deprecated
        //        public int updateAll(final Connection conn, final Collection entities) {
        //            return updateAll(conn, entities, (Collection) null);
        //        }
        //
        //        /**
        //         * Update All the records one by one with specified {@code Connection}.
        //         * 
        //         * @param conn
        //         * @param entities
        //         * @param updatePropNames
        //         * @return
        //         * @deprecated replaced by {@code #batchUpdate(Collection)}
        //         */
        //        @Deprecated
        //        public int updateAll(final Connection conn, final Collection entities, final Collection updatePropNames) {
        //            if (N.isNullOrEmpty(entities)) {
        //                return 0;
        //            }
        //
        //            if (conn == null) {
        //                return updateAll(entities, updatePropNames);
        //            }
        //
        //            int result = 0;
        //
        //            for (T entity : entities) {
        //                result += update(conn, entity, updatePropNames);
        //            }
        //
        //            return result;
        //        }

        /**
         * Update All the records by batch operation.
         * 
         * @param entities which must have the same updated properties set.
         * @return
         */
        public int batchUpdate(final Collection entities) {
            return batchUpdate(entities, (Collection) null);
        }

        /**
         * 
         * @param entities
         * @param updatePropNames
         * @return
         */
        public int batchUpdate(final Collection entities, final Collection updatePropNames) {
            return batchUpdate(entities, updatePropNames, JdbcSettings.DEFAULT_BATCH_SIZE);
        }

        public int batchUpdate(final Collection entities, final int batchSize) {
            return batchUpdate(entities, (Collection) null, batchSize);
        }

        /**
         * Update All the records by batch operation.
         * 
         * @param entities which must have the same updated properties set.
         * @param updatePropNames
         * @param batchSize Default value is 200.
         * @return
         */
        public int batchUpdate(final Collection entities, final Collection updatePropNames, final int batchSize) {
            return batchUpdate(entities, updatePropNames, batchSize, IsolationLevel.DEFAULT);
        }

        public int batchUpdate(final Collection entities, final int batchSize, final IsolationLevel isolationLevel) {
            return batchUpdate(entities, (Collection) null, batchSize, isolationLevel);
        }

        public int batchUpdate(final Collection entities, final Collection updatePropNames, final int batchSize,
                final IsolationLevel isolationLevel) {
            return batchUpdate((Connection) null, entities, updatePropNames, batchSize, isolationLevel);
        }

        public int batchUpdate(final Connection conn, final Collection entities) {
            return batchUpdate(conn, entities, (Collection) null);
        }

        public int batchUpdate(final Connection conn, final Collection entities, final Collection updatePropNames) {
            return batchUpdate(conn, entities, updatePropNames, JdbcSettings.DEFAULT_BATCH_SIZE);
        }

        public int batchUpdate(final Connection conn, final Collection entities, final int batchSize) {
            return batchUpdate(conn, entities, (Collection) null, batchSize);
        }

        public int batchUpdate(final Connection conn, final Collection entities, final Collection updatePropNames, final int batchSize) {
            return batchUpdate(conn, entities, updatePropNames, batchSize, IsolationLevel.DEFAULT);
        }

        private int batchUpdate(final Connection conn, final Collection entities, final Collection updatePropNames, final int batchSize,
                final IsolationLevel isolationLevel) {
            N.checkArgPositive(batchSize, "batchSize");

            if (N.isNullOrEmpty(entities)) {
                return 0;
            }

            final SP pair = prepareUpdate(entities.iterator().next(), updatePropNames);
            final JdbcSettings jdbcSettings = JdbcSettings.create().setBatchSize(batchSize).setIsolationLevel(isolationLevel);
            final List parametersList = entities instanceof List ? (List) entities : new ArrayList<>(entities);

            final int updateCount = sqlExecutor.batchUpdate(conn, pair.sql, StatementSetter.DEFAULT, jdbcSettings, parametersList);

            if (N.firstNonNull(entities).orNull() instanceof DirtyMarker) {
                for (Object entity : entities) {
                    postUpdate(entity, updatePropNames);
                }
            }

            return updateCount;
        }

        @SuppressWarnings("deprecation")
        private SP prepareUpdate(final T entity, final Collection updatePropNames) {
            checkEntity(entity);

            final boolean isDirtyMarkerEntity = entity instanceof DirtyMarker;
            Collection updatingpropNames = updatePropNames;

            if (updatingpropNames == null) {
                if (isDirtyMarkerEntity) {
                    updatingpropNames = ((DirtyMarker) entity).dirtyPropNames();
                } else {
                    updatingpropNames = SQLBuilder.getUpdatePropNamesByClass(targetClass, null);
                }
            }

            final Map props = N.newHashMap(N.initHashCapacity(updatingpropNames.size()));

            for (String propName : updatingpropNames) {
                props.put(propName, ClassUtil.getPropValue(entity, propName));
            }

            if (idPropNameList.size() == 1) {
                props.remove(idPropName);
            } else {
                Maps.removeKeys(props, idPropNameList);
            }

            return prepareUpdate(id2Cond(entity, true), props);
        }

        private SP prepareUpdate(final Condition whereCause, final Map props) {
            switch (namingPolicy) {
                case LOWER_CASE_WITH_UNDERSCORE:
                    return NE.update(targetClass).set(props).where(whereCause).pair();

                case UPPER_CASE_WITH_UNDERSCORE:
                    return NE2.update(targetClass).set(props).where(whereCause).pair();

                case LOWER_CAMEL_CASE:
                    return NE3.update(targetClass).set(props).where(whereCause).pair();

                default:
                    throw new RuntimeException("Unsupported naming policy: " + namingPolicy);
            }
        }

        @SuppressWarnings("deprecation")
        private void postUpdate(final Object entity, final Collection updatePropNames) {
            if (entity instanceof DirtyMarker) {
                if (updatePropNames == null) {
                    ((DirtyMarker) entity).markDirty(false);
                } else {
                    ((DirtyMarker) entity).markDirty(updatePropNames, false);
                }
            }
        }

        public int delete(final Object idOrEntity) {
            return delete(null, idOrEntity);
        }

        public int delete(final Condition whereCause) {
            return delete(null, whereCause);
        }

        public int delete(final Connection conn, final Object idOrEntity) {
            N.checkArgNotNull(idOrEntity);
            checkEntity(idOrEntity);

            return sqlExecutor.update(conn, sql_delete_by_id, idOrEntity);
        }

        public int delete(final Connection conn, final Condition whereCause) {
            N.checkArgNotNull(whereCause);

            if (idPropNameList.size() == 1 && whereCause instanceof Equal && ((Equal) whereCause).getPropName().equals(idPropName)) {
                final Object id = ((Equal) whereCause).getPropValue();
                return sqlExecutor.update(conn, sql_delete_by_id, id);
            }

            final SP pair = prepareDelete(whereCause);

            return sqlExecutor.update(conn, pair.sql, pair.parameters.toArray());
        }

        //        /**
        //         * Delete All records by ids or entities in one transaction.
        //         * 
        //         * @param idsOrEntities
        //         * @return
        //         * @deprecated replaced by {@code #deleteAll(Collection)}
        //         */
        //        @Deprecated
        //        public int deleteAll(final Collection idsOrEntities) {
        //            return deleteAll(idsOrEntities, IsolationLevel.DEFAULT);
        //        }
        //
        //        /**
        //         * Delete All records by ids or entities in one transaction.
        //         * 
        //         * @param idsOrEntities
        //         * @param isolationLevel
        //         * @return
        //         * @deprecated replaced by {@code #deleteAll(Collection)}
        //         */
        //        @Deprecated
        //        public int deleteAll(final Collection idsOrEntities, final IsolationLevel isolationLevel) {
        //            if (N.isNullOrEmpty(idsOrEntities)) {
        //                return 0;
        //            }
        //
        //            final SQLTransaction tran = sqlExecutor.beginTransaction(isolationLevel);
        //            int result = 0;
        //            boolean isOk = false;
        //
        //            try {
        //                for (Object idOrEntity : idsOrEntities) {
        //                    result += delete(idOrEntity);
        //                }
        //
        //                isOk = true;
        //            } finally {
        //                if (tran != null) {
        //                    if (isOk) {
        //                        tran.commit();
        //                    } else {
        //                        tran.rollback();
        //                    }
        //                }
        //            }
        //
        //            return result;
        //        }
        //
        //        /**
        //         * Delete All records by ids or entities with specified {@code Connection}.
        //         * 
        //         * @param conn
        //         * @param idsOrEntities
        //         * @return
        //         * @deprecated replaced by {@code #deleteAll(Collection)}
        //         */
        //        @Deprecated
        //        public int deleteAll(final Connection conn, final Collection idsOrEntities) {
        //            if (N.isNullOrEmpty(idsOrEntities)) {
        //                return 0;
        //            }
        //
        //            if (conn == null) {
        //                return deleteAll(idsOrEntities);
        //            }
        //
        //            int result = 0;
        //
        //            for (Object idOrEntity : idsOrEntities) {
        //                result += delete(conn, idOrEntity);
        //            }
        //
        //            return result;
        //        }

        /**
         * Delete all the records by batch operation.
         * 
         * @param idsOrEntities
         * @return
         */
        public int batchDelete(final Collection idsOrEntities) {
            return batchDelete(idsOrEntities, JdbcSettings.DEFAULT_BATCH_SIZE);
        }

        /**
         * Delete all the records by batch operation.
         * 
         * @param idsOrEntities
         * @param batchSize Default value is 200.
         * @return
         */
        public int batchDelete(final Collection idsOrEntities, final int batchSize) {
            return batchDelete(idsOrEntities, batchSize, IsolationLevel.DEFAULT);
        }

        /**
         * Delete all the records by batch operation.
         * 
         * @param idsOrEntities
         * @param batchSize Default value is 200.
         * @param isolationLevel
         * @return
         */
        public int batchDelete(final Collection idsOrEntities, final int batchSize, final IsolationLevel isolationLevel) {
            return batchDelete(null, idsOrEntities, batchSize, isolationLevel);
        }

        /**
         * Delete all the records by batch operation.
         * 
         * @param conn
         * @param idsOrEntities
         * @return
         */
        public int batchDelete(final Connection conn, final Collection idsOrEntities) {
            return batchDelete(conn, idsOrEntities, JdbcSettings.DEFAULT_BATCH_SIZE);
        }

        /**
         * Delete all the records by batch operation.
         * 
         * @param conn
         * @param idsOrEntities
         * @param batchSize
         * @return
         */
        public int batchDelete(final Connection conn, final Collection idsOrEntities, final int batchSize) {
            return batchDelete(conn, idsOrEntities, batchSize, IsolationLevel.DEFAULT);
        }

        private int batchDelete(final Connection conn, final Collection idsOrEntities, final int batchSize, final IsolationLevel isolationLevel) {
            N.checkArgPositive(batchSize, "batchSize");

            if (N.isNullOrEmpty(idsOrEntities)) {
                return 0;
            }

            final List