• Home
• com.landawn
• abacus-util-all
• 0.9.79
• 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.Method;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.Callable;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;

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.condition.Condition;
import com.landawn.abacus.condition.ConditionFactory.L;
import com.landawn.abacus.condition.Equal;
import com.landawn.abacus.core.RowDataSet;
import com.landawn.abacus.core.sql.dataSource.SQLDataSource;
import com.landawn.abacus.dataChannel.StatementDataChannel;
import com.landawn.abacus.exception.AbacusException;
import com.landawn.abacus.exception.NonUniqueResultException;
import com.landawn.abacus.exception.UncheckedSQLException;
import com.landawn.abacus.logging.Logger;
import com.landawn.abacus.logging.LoggerFactory;
import com.landawn.abacus.util.Options.Query;
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.function.Consumer;
import com.landawn.abacus.util.function.Function;
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.queryForEntity(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.queryForEntity(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(tran.getConnection(), ...);
            // sqlExecutor.update(tran.getConnection(), ...);
            // sqlExecutor.query(tran.getConnection(), ...);

            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 http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html
 * @see http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html
 * @see http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html
 * @see http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html
 */
public final class SQLExecutor implements Closeable {
    private static final Logger logger = LoggerFactory.getLogger(SQLExecutor.class);

    static final String ID = "id";

    static final StatementSetter DEFAULT_STATEMENT_SETTER = new DefaultStatementSetter();

    static final ResultSetExtractor DEFAULT_RESULT_SET_EXTRACTOR = new DefaultResultSetExtractor();

    static final ResultSetExtractor ROW_ITERATOR_RESULT_SET_EXTRACTOR = new AbstractResultSetExtractor() {
        @Override
        public RowIterator extractData(final Class cls, final NamedSQL namedSQL, final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            return RowIterator.of(rs, jdbcSettings.getOffset(), jdbcSettings.getCount());
        }
    };

    static final ResultSetExtractor EXISTS_RESULT_SET_EXTRACTOR = new AbstractResultSetExtractor() {
        @Override
        public Boolean extractData(final Class cls, final NamedSQL namedSQL, final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            long offset = jdbcSettings.getOffset();

            while ((offset-- > 0) && rs.next()) {
            }

            return offset <= 0 && rs.next();
        }
    };

    static final ResultSetExtractor> SINGLE_RESULT_SET_EXTRACTOR = new AbstractResultSetExtractor>() {
        @Override
        public NullabLe extractData(final Class cls, final NamedSQL namedSQL, final ResultSet rs, final JdbcSettings jdbcSettings) throws SQLException {
            long offset = jdbcSettings.getOffset();

            while ((offset-- > 0) && rs.next()) {
            }

            if (offset <= 0 && rs.next()) {
                return NullabLe.of(rs.getObject(1));
            }

            return NullabLe.empty();
        }
    };

    @SuppressWarnings("rawtypes")
    private static final ResultSetExtractor ENTITY_RESULT_SET_EXTRACTOR = new AbstractResultSetExtractor