Magento 2: Run Custom SQL Query and DB Connection

In Class Pass Db Connection

namespace Namespace\ModuleName\Model\ResourceModel;
use Magento\Framework\Model\ResourceModel\Db\AbstractDb;
class ResourceModelName extends AbstractDb 
{ 
   
    protected function _construct() 
    {
        $this->_init('table_name', 'entity_id');
    }
    
    public function __construct(
       \Magento\Framework\Model\ResourceModel\Db\Context $context,
        $connectionName = null
    ) {
        parent::__construct($context, $connectionName);
    }
    
    public function getBrand()
    {
   
       $connection = $this->getConnection();
       $tableName = $this->getTable('tmp_table_name');
       $sql = "Select * FROM " . $tableName;
       $result = $connection->fetchAll($sql);  // Return Associated Array
        
    }  
}

Insert and update Query

$sql = "INSERT INTO " . $tableName . " (id, name, age, email) 
VALUES ('', 'Hello', 1, 'test@example.com')";
$connection->query($sql);

$id = 1; // table row id to update
$sql = "UPDATE " . $tableName . " SET name = 'Your Name', email = 'test@example.com'
 WHERE id = " . $id;
$connection->query($sql);

Instead of writing the whole select query by ourselves, we can also use the SQL SELECT Query generation functions that are already provided by Magento and Zend_Db_Select. Here’s an example code:

SELECT Query

$id = 1;
$fields = array('id', 'name');
$sql = $connection->select()
                  ->from($tableName) // to select all fields
                  //->from($tableName, $fields) // to select some particular fields                  
                  ->where('id = ?', $id)
                  ->where('age > ?', 30); // adding WHERE condition with AND
                     
$result = $connection->fetchAll($sql); 

Update Query

$tableName = $this->getTable('tbl_name');
             $data = [
                    'column_name' => $value,
                     ];
            $connection->update($tableName, $data, ['column_name = ?' => $wherevalue]);

Insert Query

$tableName = $this->getTable('tbl_name');
 $row = [
                    'column_name' => $value,
                ];
               $insertData = $this->_prepareDataForTable(
                        new \Magento\Framework\DataObject($row),
                        $tableName
                    );
               $connection->insert($tableName, $insertData);

Transaction based query

$connection->beginTransaction();
       try{ 
            ------
            -------
            $connection->commit();
       
          }catch (\Exception $e) {
                $connection->rollBack();
                throw $e;
          }