Category Archives: Database

MySQL Master/Slave configuration with EJB3 and JPA

Well this turned out to be quite an exercise.

The goal: scalable reads with MySQL in master-slave configuration, writing to the master, and reading from N slaves, load balanced in round-robin fashion (or something).

The problem: using JPA (Java Persistence API) instead of direct JDBC calls. Turns out the MySQL ReplicationDriver (used to load balance reads to slaves and send writes to the master) relies on the readOnly state of the Connection in order to decide whether it’s a read or a write. With direct JDBC calls, I could get the Connection and toggle the readOnly state as needed.
Continue reading

IP Addresses in PHP/MySQL

I’ve been working on a web-based tool that stores, among other network-related things, IP addresses. When I first started I stored each IP address as four TINYINTS (0-255 for each octet):

mysql> DESC ipaddresses;
+----------+---------------------+------+-----+---------+----------------+
| FIELD    | TYPE                | NULL | KEY | DEFAULT | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | INT(10) UNSIGNED    | NO   | PRI | NULL    | AUTO_INCREMENT | 
| A        | tinyint(3) UNSIGNED | NO   |     | NULL    |                | 
| B        | tinyint(3) UNSIGNED | NO   |     | NULL    |                | 
| C        | tinyint(3) UNSIGNED | NO   |     | NULL    |                | 
| D        | tinyint(3) UNSIGNED | NO   |     | NULL    |                | 
+----------+---------------------+------+-----+---------+----------------+
5 ROWS IN SET (0.00 sec)
 
mysql> SELECT * FROM ipaddresses WHERE id=1
+----+----+----+----+-----+
| id | A  | B  | C  | D   |
+----+----+----+----+-----+
|  1 | 10 | 20 | 30 | 131 |
+----+----+----+----+-----+
1 ROW IN SET (0.02 sec)

As I started manipulating these addresses I found it awkward to do common binary math (like bitwise ANDs). I decided instead to store these 32-bit values as unsigned integers (of length 32). To make my life easier yet, MySQL and PHP both have native functions to convert IP addresses between my old and new formats to make this migration extremely easy.
Continue reading

Building Multi-Criteria Search Queries in Hibernate

In this post I am going to show how to write queries multi-criteria search screens. There are two approaches for making this possible.

  • HQL for building the Query
  • Building Query using Criteria API

HQL for building the Query

Here I am going to show 2 approaches to building the HQL and try to point out the better approach.

Approach I:String concatenation
This approach uses String concatenation and setting up the values directly in the query.

if (startDate != null) {
   if (firstClause) {
      query = query + " where ";
   }
   else {
      query = query + " and ";
   }
 
   query += " s.date >= '" + startDate + "'";
}

Using the above approach there might be a chance of SQL Injection attack and using string concatenation is inherently error-prone.

Approach 2: Criteria as Named Parameters

In this one we create two map to hold parameter name and value which could be binded to the HQL during the execution.

Here is brief example of the approach.

public List search() {
   StringBuilder aQuery = 
      new StringBuilder(" from document p where p.id is not null ");
   HashMap parameterMap = new HashMap();
   HashMap parameterListMap =  new HashMap();
 
   //Collection Criteria
   if (countyList != null) {
      buildCollectionCriterion(aQuery, parameterListMap, "listID", countyList);
   }
 
   //Date Criteria
   if (startDate!= null || endDate != null) {
      buildDateCriterion(aQuery, parameterMap, "dateField",startDate, endDate);
   }
 
   Query query = hibSession.createQuery(aQuery.toString());
 
   for (String key : parameterMap.keySet()) {
      query.setParameter(key, parameterMap.get(key));
   }
 
   for (String key : parameterListMap.keySet()) {
      query.setParameterList(key, parameterListMap.get(key));
   }
 
   List results = query.list();
}
 
//Helper Methods for different type of criteria
 
protected void buildCollectionCriterion(StringBuilder aQuery, 
                                        Map parameterListMap, 
                                        String aFieldName, 
                                        Collection aList) {
   if (aList != null && !aList.isEmpty()) {
      aQuery
         .append(" and p.")
         .append(aFieldName)
         .append(" in (:")
         .append(aFieldName)
         .append(")");
      parameterListMap.put(aFieldName, aList);
   }
}
 
public void buildDateCriterion(StringBuilder aQuery, 
                               Map parameterMap, 
                               String aFieldName, 
                               Date aStartDate, 
                               Date anEndDate) {
   if (aStartDate != null && anEndDate != null) {
      aQuery
         .append(" and ( p."
         .append(aFieldName)
         .append(" between :aStartDate and :anEndDate)");
      parameterMap.put("aStartDate", aStartDate);
      parameterMap.put("anEndDate", anEndDate);
   }
   else if (aStartDate != null) {
      aQuery
         .append(" and  (p.")
         .append(aFieldName)
         .append(" >= :aStartDate)");
      parameterMap.put("aStartDate", aStartDate);
   } 
   else if (anEndDate != null) {
      aQuery
         .append(" and (p.")
         .append(aFieldName)
         .append(" <=:anEndDate");
      parameterMap.put("anEndDate", anEndDate);
   }
}

Building Query using Criteria API

public List search() {
        Criteria c = hibSession.createCriteria(Document.class);
        c.add(Restrictions.notNull("id"));
 
        if(countryList != null) {
                c.add(Restrictions.in("listId", countryList));
        }
 
        if(startDate != null) {
                c.add(Restrictions.ge("dateField", startDate);
        }
 
        if(endDate != null) {
                c.add(Restrictions.le("dateField", endDate);
        }
 
        return c.list();
}

Conclusion

The Hibernate Criteria API is a powerful and elegant library which is well adapted for implementing multi-criteria search functionality and also HQL queries must be built ‘on-the-fly’. Using it in appropriate circumstances will result in cleaner, clearer, more reliable and more maintainable code.

PL/SQL Variables and Connection Pooling

I recently had to implement a common feature across multiple applications and app servers, all of which point to the same Oracle database. For reasons unrelated, I chose to implement this feature using PL/SQL. You can all stop laughing now. I ended up with something resembling:

CREATE OR REPLACE PACKAGE BODY MY_PKG IS
   g_enabled BOOLEAN := TRUE;
 
   PROCEDURE enable_sp() IS
      BEGIN
         g_enabled := TRUE;
      END;
 
   PROCEDURE disable_sp() IS
      BEGIN
         g_enabled := FALSE;
      END;
 
   FUNCTION is_enabled_fn() IS
      l_return_value NUMBER;
 
      BEGIN
         IF g_enabled THEN
            l_return_value := 1;
         ELSE
            l_return_value := 0;
         END IF;
 
         RETURN l_return_value;
      END;
END MY_PKG;

Seems pretty innocent, doesn’t it?

So, I write unit tests in java to test everything. It all seems to be working. It goes through code review. Nothing more serious than cosmetic issues were found. It goes through the qa cycles. It all seems to be working. It gets deployed to production. At first, it seems to be working. Then I start to see some interesting behavior.

Lesson #1:

PL/SQL data types aren’t as straight forward as I would have liked. The variable g_enabled is of type BOOLEAN. This is a data type built into PL/SQL. It is not a data type in Oracle’s version of SQL. Why is this significant? Consider the two following scenario’s and their result (assuming that is_enabled=TRUE) –

BEGIN
 
  DBMS_OUTPUT.put_line(my_pkg.is_enabled_fn());
 
END;

This will print out a 1 to the standard output.

SELECT my_pkg.is_enabled_fn() FROM dual;

This query will return 0.

This was not what I expected. I would have expected the second query to return a 1. The reason it doesn’t, is that the query is not run in a PL/SQL block. This results in the BOOLEAN data type to not be defined and the if statement in is_enabled_fn() to always evaluate to false.

Lesson #2:

Package variables are connection private. I create two connections, A and B, to the database. If I call disable_sp() in connection A, is_enabled_fn() will still return a 1 in connection B. The value of the variable is not shared across the connections. If I were then to create a connection C, is_enabled_fn() will return 0. This is because connection C was created after the value was set in connection A. While it wasn’t what I initially expected, it makes some sense in that the connections don’t share memory. The really big problem comes into play when app servers pool connections and might not close them for weeks at a time.