Get database connection in magento 2

Question

I wish to have code equivalent in Magento 2.

The below one is Magento 1

$resource = Mage::getSingleton('core/resource'); $connection = $resource->getConnection('core_read');

I need the above code in Magento 2.

Answer 1

Without a Mage class, how does a developer instantiate model or magento-singleton object? The Mage::getModel and Mage::getSingleton methods have been retired, and in their place Magento has a new “Object Manager” object.

Right now this object manager is a PHP singleton, which you can grab with the following call.

$object_manager = MagentoCoreModelObjectManager::getInstance();

Magento is moving towards an architecture where this object will be automatically available in appropriate places as the _objectManager property.

$this->_objectManager

protected $_resource;
public function __construct(
   ...
   \Magento\Framework\App\ResourceConnection $resource
   ...
   ) {
   $this->_resource = $resource;
}

$connection = $this->_resource->getConnection();

For more information check alanstorm.com

Answer 2

Use \Magento\Framework\App\ResourceConnection class to get resource connection

protected $_resource;
public function __construct(
    ...
    \Magento\Framework\App\ResourceConnection $resource
    ...
) {
    $this->_resource = $resource;
}

/**
 * $this->_resource = Mage::getSingleton('core/resource');
 * $connection = $resource->getConnection('core_read');
 */ 

$connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION);
$tablename = $connection->getTableName('sales_order');
$query = "Sql Query";
$connection->query($query);

Using Object Manager:

$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); // Instance of object manager
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$tableName = $resource->getTableName('table_name');
$query = "Sql Query";
$connection->query($query);

Answer 3

$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); // Instance of object manager
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$tableName = $resource->getTableName('employee'); //gives table name with prefix

Select Data from table

$sql = "Select * FROM " . $tableName;
$result = $connection->fetchAll($sql); // gives associated array, table fields as key in array.

Delete Data from table

$sql = "Delete FROM " . $tableName." Where emp_id = 10";
$connection->query($sql);

Insert Data into table

$sql = "Insert Into " . $tableName . " (emp_id, emp_name, emp_code, emp_salary) Values ('','XYZ','ABD20','50000')";
$connection->query($sql);

Update Data into table

$sql = "Update " . $tableName . "Set emp_salary = 20000 where emp_id = 12";
$connection->query($sql);

Answer 4

public function __construct(
    \Magento\Framework\App\ResourceConnection $resourceConnection
) {
    $this->resourceConnection = $resourceConnection;
}

Now

$connection = $this->resourceConnection->getConnection();

Answer 5

Below code will be useful when you want to work from Magento 2 root directory

<?php  
    ini_set('display_errors', 1);
    ini_set('display_startup_errors', 1);
    ini_set('memory_limit', '5G');
    error_reporting(E_ALL);

    use Magento\Framework\App\Bootstrap;
    require __DIR__ . '/app/bootstrap.php';

    $bootstrap = Bootstrap::create(BP, $_SERVER);


    $objectManager = $bootstrap->getObjectManager();

    $state = $objectManager->get('Magento\Framework\App\State');
    $state->setAreaCode('frontend');

    $resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
    $connection = $resource->getConnection();
    $tableName = $resource->getTableName('setup_module'); //gives table name with prefix
    //$moduleName = "Your_Module";
    $sql = "Delete FROM " . $tableName." Where module = 'Your_Module'";
    $connection->query($sql);
    echo "Your_Module entry deleted.";
 ?>