Java Message Format Using Named Placeholder

The Java MessageFormat class allows user to pre-define a string with placeholders and then fill the placeholders with actual strings later to construct a proper message.

It's all fine if you're used to numbered placeholders e.g. {0} and {1}. Since I'm used to Drupal's format_string() function, here's a better alternative. Apache Commons has a StrSubstitutor class which allows use of named placeholders. Instead of using:

String template = "Welcome {0}!  Your last login was {1}";
String output = MessageFormat.format(template1, new Object[] { "gabe", new Date().toString() });

You can now do:

String template = "Welcome ${username}!  Your last login was ${lastlogin}";

Map data = new HashMap();
data.put("username", "gabe");
data.put("lastlogin", new Date().toString());
  
String output2 = StrSubstitutor.replace(template2, data);

Although StrSubstitutor is a bit more verbose, but it helps when you're handling lots of key/value pairs.

SQL UNION using Drupal db_select

Here's how to use Drupal's db_select function to perform a SQL UNION like below:

select uid from users where type = 'i'
union
select uid from users_archive where type = 'i'

Bad example, but I can't think of a proper example now :)

Drupal db_select version:

$q1 = db_select('users', 'u')
       ->fields('u', array('uid'))
       ->condition('type', 'i');

$q2 = db_select('users_archive', 'ua')
       ->fields('ua', array('uid'))
       ->condition('type', 'i');

$q1->union($q2);

$results = $q1->execute();

Getting last run SQL from CodeIgniter's DB class

Here's a convenient way to obtain the last executed SQL query when using CodeIgniter's database class.

Code snippet for this example:

$this->db->select('status');
$this->db->from('tbl_user');
$this->db->where('user_id', $user_id);

$results = $this->db->get();

echo $this->db->last_query();

The last call to last_query() will output the actual SQL executed.

SQL JOINS using Drupal db_select

Here's how to use Drupal's db_select function to perform a SQL INNER JOIN like below:

SELECT n.*, u.name FROM node n 
INNER JOIN users u on n.uid = u.uid
WHERE u.id = 3

Drupal db_select version:

$q = db_select('node', 'n');
$q->join('users', 'u', 'u.uid = n.uid');

$q->fields('n');
$q->fields('u', array('name'));

$q->condition('u.uid', 3);
  
$results = $q->execute();

Output XLS file to servlet output stream using JExcelAPI

Here's a quick way (although not the best one) to stream an Excel file generated by JExcelAPI (http://jexcelapi.sourceforge.net/). I'm using the servlet output stream in this case.

HttpServletResponse response = getContext().getResponse();

ServletOutputStream sos = response.getOutputStream();

WritableWorkbook workbook = Workbook.createWorkbook(sos);
WritableSheet sheet = workbook.createSheet("Report", 0);
sheet.addCell(new Label(1, 1, "asdfasdfasdf"));

response.setContentType("application/vnd.ms-excel");
response.setHeader("Expires", "0");
response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
response.setHeader("Pragma", "public");
response.setHeader("Content-Disposition", "inline; filename=report-.xls");

workbook.write();
workbook.close();
sos.close();

Formatting Drupal's UNIX timestamp dates

Drupal stores date/time value as int columns in MySQL. Its value is UNIX timestamp based. You will not be able to determine the actual date/time by selecting from the table.

Here's a convenient way to convert the date/time columns directly from SQL:

SELECT cid, data, FROM_UNIXTIME(created) FROM main_cache

You can also use this in the WHERE clause like below:

SELECT COUNT( * ) 
FROM  main_commerce_product 
WHERE FROM_UNIXTIME( created ) 
BETWEEN  '2013-07-17 00:00:00'
AND  '2013-07-17 23:59:59'

Here's the result: