com.darwinsys.sql.SQLUtils Maven / Gradle / Ivy

Go to download
package com.darwinsys.sql;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import com.darwinsys.database.DataBaseException;

/** Miscellaneous utilities for dealing with SQL.
 * XXX Consider replacing with ResultSetDecoratorHTML
 * @author ian
 */
public class SQLUtils {

	/** 
	 * Process resultset, formatting it as HTML
	 * @param rs The valid ResultSet, which will be closed.
	 * @param out A PrintWriter to generate HTML to.
	 * @param titleStyle CSS style name for title row (may be same as style1)
	 * @param style1 CSS style name for title and data rows 2, 4, 6, ...
	 * @param style2 CSS style name for data rows 1, 3, 5, ...
	 * @param keyColName The name of the key column
	 * @param link The base URL
	 * @throws SQLException If the database reports an error
	 */
	public static void resultSetToHTML(
			final ResultSet rs, final PrintWriter out,
			String titleStyle, String style1, String style2,
			String keyColName, String link)
	throws SQLException {

		ResultSetMetaData md = rs.getMetaData();
		int count = md.getColumnCount();
		out.println("");

		// Print a table row with column headings.
		out.printf("", titleStyle);
		for (int i=1; i<=count; i++) {
			out.printf("");

		// Print one table row of data for each row in the resultset.
		int rowNum = 0;
		while (rs.next()) {
			out.printf("", rowNum++ % 2 == 1 ? style1 : style2);
			for (int i=1; i<=count; i++) {
				String linkText = rs.getString(i);
				if (md.getColumnName(i).equals(keyColName)) {
					linkText = String.format(
						"%s", link, linkText, linkText);
				}
				out.printf("", linkText);
			}
			out.println("");
		}

		// All done.
		out.println("%s%n", md.getColumnName(i));
		}
		out.println("
%s");
	}

	public static void resultSetToHTML(ResultSet rs, PrintWriter out) throws SQLException {
		resultSetToHTML(rs, out, null, null, null, null, null);
	}

	/** 
	 * Close a resultset, statement and connection in the correct order.
	 * @param rs ResultSet
	 * @param st Statement
	 * @param conn Connection
	 */
	public static void cleanup(ResultSet rs, Statement st, Connection conn) {
		try {
			if (rs != null)
				rs.close();
			if (st != null)
				st.close();
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			throw new DataBaseException("Cleanup caught exception: " + e);
		}
	}
}