Strategy Pattern for Simplifying SQL queries (PHP & Java)

Mar 31st 2015 (updated Apr 3rd 2023)

devjavaphp

Correctly handling the opening and closing of database connections each time you want to look up something different can become quite tedious. For something that is critical to the majority of software applications out there, you can end up a lot of repeated or slightly different code.

If you're not using a framework to handle your connections, then here's my recommendation for keeping your database code cleaner, safer and more consistent.

The Strategy Pattern

The Strategy Design pattern is used to defer execution to an interface implementation. This means that if you have a set of steps that you want to complete repeatedly, but some of them vary depending on the scenario, you can provide an appropriate method to perform that specific step.

Therefore, instead of re-writing the common steps (or the execution order) the strategy pattern allows us to define a step outside of the executing class.

We could alternatively use the template design pattern, which would involve using an abstract method for the processing step (instead of an external interface), however in a the case of database queries you would end up with quite a lot of extending classes which would at some point make the application unwieldly.

Executing Database Queries

For executing database SQL queries we will typically have the following steps:

  1. Create and open a connection
  2. Execute query
  3. Process results
  4. Release/Close connection

For every time we want to complete a database query we would need the same code for opening, executing and closing the connection, however we want the actual processing of the results to be different depending on the query we are executing. If we weren't being smart about it we would end up repeating the code (or repeating calls) for each step. Using the strategy pattern we could just allow the results processing to be handled outside of our main query execution class.

Implementation

To create our pattern all we need to do is create a method that executes a SQL query -  like normal but  with one key difference - we pass in the SQL Query string and a function (either directly or via an interface) to process the results from the query.

The method then invokes the function, passing a row to it and getting an object returned, which we can then put into a list and return it to the calling code.

PHP

Java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
class ConnectionStrategy {

 //... members and constructor ...

    public function query($query, $rowMapperFn) {
        $objects = array();
        $mysqli = new mysqli($this->host, $this->user, $this->pass, $this->schema);
        if ($mysqli->connect_errno) {
            die('Unable to connect to database' . $mysqli->connect_errno);
        }
        $result = $mysqli->query($query);
        if (!$result) {
            die('Failed to execute query [' . $mysqli->error . ']');
        }
        while ($row = $result->fetch_array()) {
            $object = $rowMapperFn($row);
            array_push($objects, $object);
        }
        $result->free();
        $mysqli->close();

        return $objects;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
public class ConnectionStrategy {

    //... members and constructor ...

    private Connection getConnection() throws SQLException {
       //create connection code
    }

    public interface RowMapper<T> {
        public T rowToObject(ResultSet rs) throws SQLException;
    }

    public <T> List<T> query(final String query, final RowMapper<T> rowMapper) throws SQLException {
        final List<T> objects = new LinkedList<>();
        try (final Connection conn = getConnection();
                final Statement stmt = conn.createStatement();
                final ResultSet result = stmt.executeQuery(query)) {

            while (result.next()) {
                final T object = rowMapper.rowToObject(result);
                objects.add(object);
            }
        }
        return objects;
    }
}

Usage

We can then reuse our pattern wherever we need to get data from a database, without having to rewrite the code to handle the database connection. All we do is create a new instance of our ConnectionStrategy class (which we could create as a singleton and reuse) and then invoke the query method by passing in the SQL query string and an implementation of the 'row mapping' function. We then get a nice list of objects to do what we need to with - whilst knowing our connection has been handled properly.

PHP

Java

1
2
3
4
5
6
7
8
9
10
11
12
class Product {
 //...members, getters & constructor
}
$query = "SELECT name, price FROM `product` LIMIT 0, 30 ";
$connectionStrategy = new ConnectionStrategy("localhost", "root", "", "test");
$results = $connectionStrategy->query($query, function($row) {
    return new Product($row['name'], $row['price']);
});
/* @var $results Product */
foreach ($results as $product) {
    echo "Product: " . $product->getName() . " - " . $product->getPrice() . "<br />";
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
static class Product {
 //...members, getters & constructor
}

public static void main(String[] args) {
 try {
     final String query = "SELECT name, price FROM `product`";
  final ConnectionStrategy connectionStrategy = new ConnectionStrategy("localhost", "root", "", "test");
  List<Product> results = connectionStrategy.query(query, new RowMapper<Product>() {
   @Override
   public Product rowToObject(ResultSet rs) throws SQLException {
    return new Product(rs.getString("name"), rs.getDouble("price"));
   }
  });
  for (final Product product : results) {
   System.out.println("Product: " + product.getName() + " - " + product.getPrice());
  }
 } catch (ClassNotFoundException | SQLException ex) {
  Logger.getLogger(ConnectionStrategy.class.getName()).log(Level.SEVERE, null, ex);
 }
}

Improvements

  • Use the ConnectionStrategy class to pool connections
  • PHP Implementation is using a higher-order function where it should use an interface
  • Add methods to the ConnectionStrategy class for executing updates
  • Use a transactional framework like hibernate instead
  • Support Prepared Statements

Mar 21, 2015 (updated Mar 31, 2015)