I’ve been working to refine my OO approach to PHP. Currently, I’m unable to come to a conclusion on the best approach to handling database connections.
I have 3 main layers to my database interaction:
The process layer which redirects requests and data to the appropriate objects.
The objects that contain logic for handling the data
A database object which handles connecting to the database and queries.
For the objects in layer 2, each object initializes a database connection in its constructor and stores it as a member. This can then be accessed by all the objects methods non-static methods.
The problem scenario:
I have an import class which reads in an excel file (several hundred lines). Each line corresponds to an object. The result is I end up with an array of objects (all of the same type). In each of these objects I have an insert method which connects to the database and stores the data. Because the database connection is established in the constructor I end up with a large number of open connections (or at least I think I do based on my understanding of PDO/OO). When I try to run my inserts for each object in the array I end up exceeding my max-allowed-connections.
Where and how do you handle your database connection so as not to run into this problem?
Here are some code excerpts to understand my problem better:
Main File:
//User uploads excel document which I parse into an array
$car = array();
foreach($array as $index => $data){
$car[$index] = new Car(null,$data["make"],$data["model"]);
$car[$index]->insert();
}
//return car array of objects
//Car Class
class Car{
protected $pkey;
protected $make;
protected $model;
protected $db;
public function __construct($pkey,$make,$model){
$this->pkey = $pkey;
if(isset($make) && ($make != '')){
$this->make = $make;
}else{
throw new Exception("Car must have make");
}
if(isset($model) && ($model != '')){
$this->model = $model;
}else{
throw new Exception("Car must have model");
}
$this->db = new Database();
}
public function insert(){
$sql = "INSERT INTO TABLE (...) VALUES (..)";
$data = array(
":make"=>$this->make,
":model"=>$this->model,
);
try{
$this->pkey = $this->db->insert($sql,$data);
return true;
}catch(Exception $err){
//catch errors
return false;
}
}
}
class Database {
protected $conn;
protected $dbstr;
public function __construct() {
$this->conn = null;
$this->dbstr = "jndi connection string";
$this->connect();
}
public function connect(){
try{
$this->conn = new PDO($this->dbstr); // Used with jndi string
} catch (PDOException $e){
// print $e->getMessage();
}
return "";
}
public function insert($query, $data){
try{
$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/* Execute a prepared statement by passing an array of values */
$sth = $this->conn->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$count = $sth->execute($data);
return $this->oracleLastInsertId($query);
}catch(PDOException $e){
throw new Exception($e->getMessage());
}
}
public function oracleLastInsertId($sqlQuery){
// Checks if query is an insert and gets table name
if( preg_match("/^INSERT[\ \
]+INTO[\ \
]+([a-z0-9\\_\\-]+)/is", $sqlQuery, $tablename) ){
// Gets this table's last sequence value
$query = "select ".$tablename[1]."_SEQ.currval AS last_value from dual";
try{
$temp_q_id = $this->conn->prepare($query);
$temp_q_id->execute();
if($temp_q_id){
$temp_result = $temp_q_id->fetch(PDO::FETCH_ASSOC);
return ( $temp_result ) ? $temp_result['LAST_VALUE'] : false;
}
}catch(Exception $err){
throw new Exception($err->getMessage());
}
}
return false;
}
public function close(){
$this->conn = null;
}
}
You’ll probably want to use the singleton pattern here to ensure there is only one and never more than one Database object.
The main idea is to create a static function that returns the instance of the class if there already was on initialized, or creates one, stores it for later use, and then returns it.
For this to work the constructor of the class must be made private (or protected) so it can only be called from within the class itself, ensuring no dynamic instances can ever be created.
So basically, something like
class Database {
protected $conn;
protected $dbstr;
// keep the one and only instance of the Database object in this variable
protected $instance;
// visibility changed from public to private to disallow dynamic instances
private function __construct() {
$this->conn = null;
$this->dbstr = "jndi connection string";
$this->connect();
}
// added this method
public static function getInstance() {
if (!isset(self::$instance)) {
self::$instance = new Database();
}
return self::$instance;
}
// everything below this comment is as it was; I made no changes here
public function connect(){
try{
$this->conn = new PDO($this->dbstr); // Used with jndi string
} catch (PDOException $e){
// print $e->getMessage();
}
return "";
}
public function insert($query, $data){
try{
$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/* Execute a prepared statement by passing an array of values */
$sth = $this->conn->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$count = $sth->execute($data);
return $this->oracleLastInsertId($query);
}catch(PDOException $e){
throw new Exception($e->getMessage());
}
}
public function oracleLastInsertId($sqlQuery){
// Checks if query is an insert and gets table name
if( preg_match("/^INSERT[\ \
]+INTO[\ \
]+([a-z0-9\\_\\-]+)/is", $sqlQuery, $tablename) ){
// Gets this table's last sequence value
$query = "select ".$tablename[1]."_SEQ.currval AS last_value from dual";
try{
$temp_q_id = $this->conn->prepare($query);
$temp_q_id->execute();
if($temp_q_id){
$temp_result = $temp_q_id->fetch(PDO::FETCH_ASSOC);
return ( $temp_result ) ? $temp_result['LAST_VALUE'] : false;
}
}catch(Exception $err){
throw new Exception($err->getMessage());
}
}
return false;
}
public function close(){
$this->conn = null;
}
}
Then in the Car class, instead of setting $this->db = new Database(), use $this->db = Database::getInstance();
That works perfectly. There is a small typo though for anyone else who might want to use the example. The $instance variable should be declared static.
This class has been working great, but my project requirements have expanded and I’m now a little confused as to how I can modify this approach to my new requirements. I now have to handle connections to multiple database.
I have 1 database setup that this class was designed for. This database continues to exists and be used. However, I also now have a second type of database to connect to. I will not know the connection_string or credentials for this database until run time (when the user selects 1 of the 4 available databases).
At most, the app will have to handle 2 separate database connections. As I understand it, this begins to move away from the singleton example. But if I wanted to keep the code as is and adjust for this new database (short-term fix) would it be as simple as adding a second class (under a different name) with almost identical code? Then I believe I would be able to maintain two simultaneous connections.
In addition to the short-term approach outlined above, do you have any suggestions for better overall approaches?
Yes, you could create a copy of that class, but it’s not a very good idea as it totally goes against the DRY (don’t repeat yourself) principle.
Instead, I’d turn the Singleton into a Factory. Something like this:
class Database {
protected $conn;
protected $dbstr;
// keep the Database instances of the Database object in this variable
protected $instances;
// visibility changed from public to private to disallow dynamic instances
private function __construct($dbstr) {
$this->conn = null;
$this->dbstr = $dbstr;
$this->connect();
}
// added this method
public static function getInstance($dbstr) {
$id = md5($dbstr);
if (!isset(self::$instances[$id])) {
self::$instances[$id] = new Database($dbstr);
}
return self::$instances[$id];
}
// everything below this comment is as it was; I made no changes here
public function connect(){
try{
$this->conn = new PDO($this->dbstr); // Used with jndi string
} catch (PDOException $e){
// print $e->getMessage();
}
return "";
}
public function insert($query, $data){
try{
$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/* Execute a prepared statement by passing an array of values */
$sth = $this->conn->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$count = $sth->execute($data);
return $this->oracleLastInsertId($query);
}catch(PDOException $e){
throw new Exception($e->getMessage());
}
}
public function oracleLastInsertId($sqlQuery){
// Checks if query is an insert and gets table name
if( preg_match("/^INSERT[\ \
]+INTO[\ \
]+([a-z0-9\\_\\-]+)/is", $sqlQuery, $tablename) ){
// Gets this table's last sequence value
$query = "select ".$tablename[1]."_SEQ.currval AS last_value from dual";
try{
$temp_q_id = $this->conn->prepare($query);
$temp_q_id->execute();
if($temp_q_id){
$temp_result = $temp_q_id->fetch(PDO::FETCH_ASSOC);
return ( $temp_result ) ? $temp_result['LAST_VALUE'] : false;
}
}catch(Exception $err){
throw new Exception($err->getMessage());
}
}
return false;
}
public function close(){
$this->conn = null;
}
}
Then you can make as many connections as you like:
