我目前正在使用PHP, ORACLE, PDO和JNDI的组合来连接我的应用程序到数据库。我很难理解在面向对象方法中管理连接池的最佳方法。因此，当我尝试进行批量插入(大于32个插入(我的最大池大小设置为))时，我得到了最大连接警告。
考虑这个例子:
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
On the Class:
//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;
}
}
}
在本例中，假设max-pool设置为32，任何大于32的数组都将导致我超过max-pool-size，因为每个car对象都是通过活动db连接存储的。为了解决这个问题，我尝试对类实现以下修复。
//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(); //Moved out of the constructor
}
public function insert(){
$this->establishDBConn();
$sql = "INSERT INTO TABLE (...) VALUES (...)";
$data = array(
":make"=>$this->make,
":model"=>$this->model,
);
try{
$this->pkey = $this->db->insert($sql,$data);
$this->closeDBConn();
return true;
}catch(Exception $err){
//catch errors
$this->closeDBConn();
return false;
}
}
protected function establishDBConn(){
if(!$this->db){
$this->db = new Database();
}
}
public function closeDBConn(){
if($this->db){
$this->db->close();
$this->db = null;
}
}
}
理论上，这个更改应该只强制在实际插入过程中维护一个活动连接。但是，由于这个更改，我继续达到我的最大连接池限制。作为最后的努力，我将所有的插入逻辑移出了car类，并创建了一个批量插入函数。这个函数忽略了对象的概念，相反，它只接收一个数据数组，它循环遍历并插入到单个数据连接上。这是有效的，但我想找到一种方法来解决我的问题，在面向对象编程的约束。
关于如何改进我的代码，以便更有效地使用对象和数据库连接的任何建议?
作为参考，这是我的数据库类看起来像:
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['t'n ]+INTO['t'n ]+([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;
}
}
正确的方法似乎是使用基于单例的数据库类:
class Database {
protected $conn;
protected $dbstr;
// keep the one and only instance of the Database object in this variable
protected static $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['t'n ]+INTO['t'n ]+([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;
}
}