MySQLのinsertとupdate用のPDOのPrepared Statementを生成するPHPのプログラム

PHPでMySQLのinsertとupdateで使えるPDOのprepared statementを生成するプログラムを書いてみました。使用頻度の高そうなパターンのSQLを生成することを目標にしてプログラムを書きました。

SQLBuilderクラス

メインとなるクラスです。SQLBuilderクラスの内部で使っているColumnインタフェースとColumnインタフェースを実装したBindableColumn、FixedValueクラスは、SQLBuilderクラスの内部のみで使用することを想定しているので、SQLBuilder使用時に意識する必要はありません。

<?php

class SQLBuilder {
    
    private string $table;
    /**
     * @var Column[]
     */
    private array $columns = [];
    
    public function __construct($table) {
        $this->table = $table;
    }
    
    public function add($column, $type): self
    {
        $this->columns[] = new BindableColumn($column, $type);
        return $this;
    }
    
    public function addFixedValue($column, $value): self
    {
        $this->columns[] = new FixedValue($column, $value);
        return $this;
    }
    
    public function buildStatementForInsert()
    {
      $targetColumns = implode(', ', array_map(fn(Column $column) => $column->getName(),$this->columns));
      $targetValues = implode(', ', array_map(fn(Column $column) => $column->buildLineForInsert(), $this->columns));
      return 'INSERT INTO '.$this->table.'('.$targetColumns.') VALUES ('.$targetValues.')';
    }
    
    
    // update用のPrepared Statement生成メソッドは一般化のため、多少複雑になっています。
    // idカラムを1つ指定するといったように、用途が決まっている場合は単純化するのもありです。
    public function buildStatementForUpdate($whereColumnNames)
    {
      $whereColumnNames = array_flip($whereColumnNames);
      $updateColumnsArray = [];
      $whereColumnsArray = [];
      foreach($this->columns as $column)
      {
          if(isset($whereColumnNames[$column->getName()]))
          {
              $whereColumnsArray[] = $column;
          }
          else
          {
              $updateColumnsArray[] = $column;
          }
      }
      $updateColumns = implode(', ', array_map(fn(Column $column) => $column->buildLineForUpdate(), $updateColumnsArray));
      $whereColumns = implode(', ', array_map(fn(Column $column) => $column->buildLineForUpdate(), $whereColumnsArray));
      return 'UPDATE '.$this->table.' SET '.$updateColumns.' WHERE '.$whereColumns;
    }
    
    // 一度生成したPrepared Statementを再利用して、別の値をbindするためのメソッド
    public function bindValueToStatement($value, \PdoStatement $stmt)
    {
        foreach ($this->columns as $column)
        {
            $column->bindValueTo($value, $stmt);
        }
    }

    // 一回だけINSERT文を実行するためのショートカットメソッド
    public function executeInsert(\PDO $pdo, $value)
    {
        $stmt = $pdo->prepare($this->buildStatementForInsert());
        $this->bindValueToStatement($value, $stmt);
        $stmt->execute();
    }
    
    // 一回だけUPDATE文を実行するためのショートカットメソッド
    public function executeUpdate(\PDO $pdo, $value, array $whereColumnNames)
    {
        $stmt = $pdo->prepare($this->buildStatementForUpdate($whereColumnNames));
        $this->bindValueToStatement($value, $stmt);
        $stmt->execute();
    }
}

<?php

interface Column {
    public function getName();
    public function buildLineForInsert();
    public function buildLineForUpdate();
    public function bindValueTo($value, \PdoStatement $stmt);
}

<?php

class BindableColumn implements Column {
    private string $name;
    private int $type;
    
    public function __construct(string $name, int $type) {
        $this->name = $name;
        $this->type = $type;
    }
    
    public function getName() {
        return $this->name;
    }

    public function buildLineForInsert() {
        return ':'.$this->name;
    }

    public function buildLineForUpdate() {
        return $this->name.' = :'.$this->name;
    }

    public function bindValueTo($value, \PdoStatement $stmt) {
        $stmt->bindValue(':'.$this->name, $value[$this->name], $this->type);
    }
}

<?php

class FixedValue implements Column{
    private string $name;
    private $value;
 
    public function __construct(string $name, $value) {
        $this->name = $name;
        $this->value = $value;
    }
    
    public function getName() {
        return $this->name;
    }

    public function buildLineForInsert() {
        return $this->value;
    }

    public function buildLineForUpdate() {
        return $this->value;
    }

    public function bindValueTo($value, \PdoStatement $stmt) {
    }

}

使用例

まずはINSERT文の生成例です。

<?php

// INSERT文の生成
$builder = new SQLBuilder('table');
$builder
    ->add('column1', \PDO::PARAM_INT)
    ->add('column2', \PDO::PARAM_INT)
    ->addFixedValue('date', 'NOW()');

// INSERT INTO table(column1, column2, date) VALUES (:column1, :column2, NOW())
$sql = $builder->buildStatementForInsert();

// Prepared Statementに値をbindして実行
// 一回だけのbindならSQLBuilder::executeInsertメソッドを使うのもあり
$pdo = new \PDO('.....');
$value = ['column1' => 68342, 'column2' => 'ABC'];
$stmt = $pdo->prepare($sql);
$builder->bindValueToStatement($value, $stmt);
$stmt->execute();

続いてUPDATE文の生成例です。

<?php
// UPDATE文の生成
$builder = new SQLBuilder('table');
$builder
   ->add('column1', \PDO::PARAM_INT)
   ->add('column2', \PDO::PARAM_INT)
   ->add('id', \PDO::PARAM_INT)
   ->addFixedValue('date', 'NOW()');

// UPDATE table SET column1 = :column1, column2 = :column2, NOW() WHERE id = :id
$sql = $builder->buildStatementForUpdate(['id']);

// Prepared Statementに値をbindして実行
// 一回だけのbindならSQLBuilder::executeUpdateメソッドを使うのもあり
$pdo = new \PDO('.....');
$value = ['column1' => 68342, 'column2' => 'ABC', 'id' => 5638];
$stmt = $pdo->prepare($sql);
$builder->bindValueToStatement($value, $stmt);
$stmt->execute();