TYPO3 QueryBuilder example use

_Introduction.txt

I'm inspired through the slides from the talk from Nicole 
(see https://de.slideshare.net/cpsitgmbh/certifuncation-2017-best-practices-extension-development-for-typo3-8-lts) 
and doctrine in TYPO3.
As far as I understand the variable $GLOBALS['TYPO3_DB'] which contains the DatabaseConnection class will not be available 
in future versions of TYPO3. 

That means that common functions are not available any more:
exec_SELECTquery()
exec_SELECTgetRows()
exec_SELECTgetSingleRow()
exec_DELETEquery()
exec_UPDATEquery()
exec_INSERTquery()

Nevertheless there is a QueryBuilder class that can be used. I played a bit with it in TYPO3 8.7.1 and stored some examples 
for me and of course for everybody who wants to know.

Find out more in the documentation: https://docs.typo3.org/typo3cms/CoreApiReference/ApiOverview/Database/QueryBuilder/Index.html
or in the PHP Class \TYPO3\CMS\Core\Database\Query\QueryBuilder

InsertExample.php

<?php
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;

// insert into tt_content (header, crdate, pid) VALUES ("New content", 123456789, 123); 
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tt_content');
$queryBuilder
    ->insert('tt_content')
    ->values([
        'header' => 'New content',
        'crdate' => time(),
        'pid' => 123,
    ])
    ->execute();   

SelectExample1.php

<?php
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;

// select * from tt_content where colPos=0 limit 3;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tt_content');
$result = $queryBuilder
    ->select('*')
    ->from('tt_content')
    ->where('colPos=0')
    ->orderBy('sorting')
    ->setMaxResults(3)
    ->execute();
$rows = $result->fetchAll();
var_dump($rows);

SelectExample2.php

<?php
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;

// select header from tt_content where colPos=0;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tt_content');
$result = $queryBuilder
    ->select('header')
    ->from('tt_content')
    ->where('colPos=0')
    ->execute();
while ($row = $result->fetch()) {
    var_dump($row);
}

UpdateExample.php

<?php
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;

// update tt_content set header = "new" where uid = 120;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tt_content');
$queryBuilder
    ->update('tt_content')
    ->where(
        $queryBuilder->expr()->eq('uid', 120) // if 120 would be a user parameter, use $queryBuilder->createNamedParameter($param) for security reasons
    )
    ->set('header', 'new')
    ->execute();