JMX and Spring is Magic

Adding JMX to your spring project is a snap. If you need to ask what JMX is, basically think of the most awesome way to manage your Java beans and applications and multiply it by 50. Then divide by 25. But seriously, JMX provides you a way to execute methods from your Java beans by connecting to your running application via RMI. It’s a great way to check on the status of objects in your app while it is running. Best of all, you get it all for almost free. Just add this bean to your application context:

<bean id="exporter" class="org.springframework.jmx.export.MBeanExporter">
    <property name="beans">
        <map>
            <entry key="someCategory:name=cacheManager"
                   value-ref="cacheManager"/>
            <entry key="fooCategory:type=fooSubCategory,name=FooBar"
                   value-ref="fooBar"/>
        </map>
    </property>
</bean>

Inside the map, insert an entry for every bean you want to expose. When a bean is exposed, its public functions will be available to JMX clients. This is particularly useful for methods that take primitives as parameters and/or return primitives. The value-ref field points to a bean in your application context. The key defines a folder for your bean, an optional subfolder, and the name of your bean. This helps organize things in a visual JMX application like JConsole.
To get your JMX server running, execute your Java program with the following command line options:

-Dcom.sun.management.jmxremote
-Dcom.sun.management.jmxremote.port=13590
-Dcom.sun.management.jmxremote.ssl=false -Dcom.sun.management.jmxremote.authenticate=false

The first parameter turns on the server/RMI. The second parameter makes JMX available on a specified port (just a little useful for remote connections). Turning SSL off and authenticate off in the third and fourth parameters is useful for internal networks. If you want to expose your JMX console to the world, you’ll want to research adding some sort of authentication via these options. If you’re putting your application inside Tomcat, you can modify the JAVA_OPTS variable inside bin/catalina.sh or bin/catalina.bat. Just add the same options and restart Tomcat and you’ll be ready to go.

Finally, you’ll probably want to see the results of all this configuration. In Java 5 and 6, all you need to do is run:

jconsole

at the command prompt and it will detect all running JMX applications on your localhost. Alternatively, you can use it to connect to a remote host running JMX.

I think JMX is a great tool both for development and production and is yet another reason to develop your apps in Java. Check it out today if you haven’t already.

Exporting a MySQL table to Excel XLS in Java

I decided to get things rolling on this blog with a short but useful example using JDBC and Apache POI to output a MySQL database table to an Excel spreadsheet. You’ll need two jars to get this code compiled and running: POI 3.0 and the MySQL java connector. Of course, you’ll also need a MySQL database… but that’s a whole other bag of worms.

I tried to keep this code as simple as possible. The MysqlToXls object is initialized with the database name, user name, and password. Its generateXls() function takes the name of the table to output and the filename to output to. Finally, a close() method closes the database connection… and that’s all there is to it! I included a main method that uses this object to connect to a database named test as user root with an empty password. It then dumps the person table as person.xls.

import java.io.*;
import java.sql.*;
import org.apache.poi.hssf.usermodel.*;
import java.util.*;

public class MysqlToXls {

  private Connection connection = null;

  public MysqlToXls(String database, String user, String password)
  	throws ClassNotFoundException, SQLException {

		// Create MySQL database connection
    Class.forName("com.mysql.jdbc.Driver");

    String url = "jdbc:mysql://localhost:3306/" + database;
    connection = DriverManager.getConnection(url, user, password);
  }

  public void generateXls(String tablename, String filename)
  	throws SQLException, FileNotFoundException, IOException {

    // Create new Excel workbook and sheet
    HSSFWorkbook xlsWorkbook = new HSSFWorkbook();
    HSSFSheet xlsSheet = xlsWorkbook.createSheet();
    short rowIndex = 0;

    // Execute SQL query
    PreparedStatement stmt =
    connection.prepareStatement("select * from " + tablename);
    ResultSet rs = stmt.executeQuery();

    // Get the list of column names and store them as the first
    // row of the spreadsheet.
    ResultSetMetaData colInfo = rs.getMetaData();
    List<String> colNames = new ArrayList<String>();
    HSSFRow titleRow = xlsSheet.createRow(rowIndex++);

    for (int i = 1; i <= colInfo.getColumnCount(); i++) {
      colNames.add(colInfo.getColumnName(i));
      titleRow.createCell((short) (i-1)).setCellValue(
        new HSSFRichTextString(colInfo.getColumnName(i)));
      xlsSheet.setColumnWidth((short) (i-1), (short) 4000);
    }

    // Save all the data from the database table rows
    while (rs.next()) {
      HSSFRow dataRow = xlsSheet.createRow(rowIndex++);
      short colIndex = 0;
      for (String colName : colNames) {
        dataRow.createCell(colIndex++).setCellValue(
          new HSSFRichTextString(rs.getString(colName)));
      }
    }

    // Write to disk
    xlsWorkbook.write(new FileOutputStream(filename));
  }

  // Close database connection
  public void close() throws SQLException {
    connection.close();
  }

  public static void main(String[] args) {
    try {
      MysqlToXls mysqlToXls = new MysqlToXls("test", "root", "");
      mysqlToXls.generateXls("person", "person.xls");
      mysqlToXls.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

So there you have it! Take your data out of a great storage mechanism and throw it into a crappy spreadsheet! This code could easily be ported to Postgres or Oracle with the proper JDBC connector.

You might also want to play around with setting the column type in POI. The HSSFCell.setCellValue() can take an int, a boolean, a Calendar object, a Date, or a double instead of just a string.

A few things you might want to watch out for: POI has an unholy obsession with using shorts. Also, JDBC refers to columns starting with #1 instead of the usual 0 index. Finally, I have no idea where these column width numbers come from… but 4000 looked good to me!