JdbcXYDataset and multiple queries

A discussion forum for JFreeChart (a 2D chart library for the Java platform).
Post Reply
Stephen Gordon

JdbcXYDataset and multiple queries

Post by Stephen Gordon » Wed Aug 07, 2002 9:47 pm

I have a table that looks like this

stats date is a date
id is a number(5)
nf_index is a number(9,3)

stats_date id nf_index
01-AUG-02 4899 1.212
02-AUG-02 4899 1.317
03-AUG-02 2893 1.197
03-AUG-02 4899 0.965
04-AUG-02 2893 1.099
04-AUG-02 4899 0.944

My code is basically

JdbcXYDataset jdbcData = new JdbcXYDataset(conn);
String sql1 = "select stats_date, nf_index from posdomstats where id = 4899 order by stats_date";
String sql2 = "select stats_date, nf_index from posdomstats where id = 2893 order by stats_date";

jdbcData.executeQuery(sql1);

JFreeChart chart = ChartFactory.createTimeSeriesChart("","","",jdbcData,true);

XYPlot xyplot = (XYPlot)chart.getPlot();
ChartPanel panel = new ChartPanel(chart);
add(panel);

The problem is that I want to execute the second query(sql2) and that data would be another series on the chart with date as the X axis.

I can't figure out how to do this with a JdbcXYDataset.

Any help you can provide would be greatly appreciated.
Steve

Bryan

Re: JdbcXYDataset and multiple queries

Post by Bryan » Wed Aug 07, 2002 11:56 pm

Stephen

You have hit on a current limitation of using the JDBCDataset series. They are only designed for a single sql query.

This has been asked before and I am considering adding the capability when I get a chance. If you want / need this functionality now I would be interested in the code you use.

Bryan

Stephen Gordon

Re: JdbcXYDataset and multiple queries

Post by Stephen Gordon » Thu Aug 08, 2002 3:28 am

I actually have a workaround for now, but when I get a chance I might write a class that can handle multiple queries. For now, I'm just using a much more complicated SQL Statement.

select nvl(a.stats_date,b.stats_date), a.nf_index, b.nf_index
from
(select stats_date, nf_index
from posdomstats
where id = 4899
order by stats_date) a,
(select stats_date, nf_index
from posdomstats
where id = 2893
order by stats_date) b
where a.stats_date = b.stats_date (+)
/

nullspace
Posts: 1
Joined: Tue Nov 01, 2011 9:58 pm
antibot: No, of course not.

Re: JdbcXYDataset and multiple queries

Post by nullspace » Tue Nov 01, 2011 10:01 pm

Hi,

Have there been any updates to enable the multiple query feature? I would still love to perform multiple queries and plot each query on the same plot, even when the x-axis data for each of my queries are different. Any help would be appreciated!

Thanks,
Brian

rowdy
Posts: 1
Joined: Mon Mar 26, 2018 2:36 pm
antibot: No, of course not.

Re: JdbcXYDataset and multiple queries

Post by rowdy » Mon Mar 26, 2018 3:00 pm

I know this is an old thread, but I haven't seen any evidence of multiple query support having been added. I've written a couple of classes to do that in case they are of use to anyone else.

One called MultipleSqlSharedDomainJdbcXYDataset is designed for use in charts where the series have to have the same domain values (x-axis). To use this class you have to call initialiseDomainAxis with a query that will give the list of all domain axis values that will be in use by one or more of the series. When constructing the class, or executing queries you can specify what value should be used in a series for any domain values that series' query didn't return. For example you could pass in null and on a timeseries chart this would break the line, or you could decide that missing values should be treated as zero.

I know the timeseries chart doesn't require all series to have shared domain values, but using this class allows you to define what value the missing points would take - so you can break your timeseries lines by passing in null instead of having the values interpolated.

This class also uses the RowComparator class - this is to sort the list of rows into order by the x/domain value (first entry in each row).

The second is called MultipleSqlNonSharedDomainJdbcXYDataset and is designed for charts where the domains don't have to be shared. If used for timeseries missing values in any series will be interpolated (provided that series has values after the missing one(s)). This one does use commons-lang3 Pair & Immutable pair. This just seemed more logical than storing a list of lists for the series with the inner list always holding two values - the x & y.

I based both of them on the JdbcXYDataset and both have the requirement that you call finaliseData after you've execute the last query - this is to save running the min/max check as each query is executed and for the MultipleSqlSharedDomainJdbcXYDataset getting the internal data structure ready for use in the getX, getY etc methods.

All queries executed must contain the x-axis/domain value plus one or more additional columns for the series - that's even in the MultipleSqlSharedDomainJdbcXYDataset where the initialiseDomainAxis provides all possible domain values as in the individual queries this is used to put the range values in the correct list.

In both you can specify a series name for a query when executing it. If you provide null the column names will be used. If you provide a series name and it has more than the domain column plus one other then the supplied series name will be prefixed to each column to form the final series name.

I've provided each class below - without package declaration, but otherwise complete. Aside from the above notes hopefully the use should be self explanatory (the same as JdbcXYDataset).

Regards,
Richard.

MultipleSqlSharedDomainJdbcXYDataset:

Code: Select all

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.jfree.data.Range;
import org.jfree.data.RangeInfo;
import org.jfree.data.general.Dataset;
import org.jfree.data.xy.AbstractXYDataset;
import org.jfree.data.xy.TableXYDataset;
import org.jfree.data.xy.XYDataset;

/**
 * This class handles having multiple Queries to build up different series in the dataset.
 * All series must share the same Domain values - a query to set all required domain values
 * must be provided. This allows the class to fill in values for any domain points one
 * of the series is missing - using specified value for missing points
 * (most likely null or zero)
 * @author rhurrell
 *
 */
public class MultipleSqlSharedDomainJdbcXYDataset extends AbstractXYDataset implements XYDataset, TableXYDataset, RangeInfo {
	private static final long serialVersionUID = 1L;

	/** The database connection. */
	private transient Connection connection;

	/** Seriesnames */
	private List<String> seriesNames = new ArrayList<String>();

	/** RowMap. Key is the x-value*/
	private Map<String, List<Object>> rowMap;
	
	/** List created from the map once all series are set */
	private List<List<Object>> rowList;
	
	/** The value to use for any domain point the series is missing */
	private Object missingEntryValue;

	/** The maximum y value of the returned result set */
	private double maxValue = 0.0;

	/** The minimum y value of the returned result set */
	private double minValue = 0.0;

	/** Is this dataset a timeseries ? */
	private boolean isTimeSeries = false;

	/**
     * Creates a new JDBCXYDataset (initially empty) with no database
     * connection.
     */
    private MultipleSqlSharedDomainJdbcXYDataset() {
    }

	/**
     * Creates a new dataset (initially empty) and establishes a new database
     * connection.
     *
     * @param  url  URL of the database connection.
     * @param  driverName  the database driver class name.
     * @param  user  the database user.
     * @param  password  the database user's password.
     * @param  missingEntryValue The value to use for any domain point the series is missing
     *
     * @throws ClassNotFoundException if the driver cannot be found.
     * @throws SQLException if there is a problem connecting to the database.
     */
    public MultipleSqlSharedDomainJdbcXYDataset(String url, String driverName, String user, String password, Object missingEntryValue) throws SQLException, ClassNotFoundException {
        this();
        Class.forName(driverName);
        this.connection = DriverManager.getConnection(url, user, password);
        this.missingEntryValue = missingEntryValue;
    }

	/**
     * Creates a new dataset (initially empty) using the specified database
     * connection.
     *
     * @param  con  the database connection.
     * @param  missingEntryValue The value to use for any domain point the series is missing
     *
     * @throws SQLException if there is a problem connecting to the database.
     */
    public MultipleSqlSharedDomainJdbcXYDataset(Connection con, Object missingEntryValue) throws SQLException {
        this();
        this.connection = con;
        this.missingEntryValue = missingEntryValue;
    }

	/**
     * Creates a new dataset using the specified database connection, and
     * populates it using data obtained with the supplied query.
     *
     * @param con  the connection.
     * @param query  the SQL query.
	 * @param seriesName the name to give the series generated by this query 
	 * 		- or to prefix column names with where the query has more than 2 columns
     * @param  missingEntryValue The value to use for any domain point the series is missing
     *
     * @throws SQLException if there is a problem executing the query.
     */
    public MultipleSqlSharedDomainJdbcXYDataset(Connection con, String query, String seriesName, Object missingEntryValue) throws SQLException {
        this(con, missingEntryValue);
        executeQuery(query, seriesName);
    }
    
	/**
	 * Returns {@code true} if the dataset represents time series data,
	 * and {@code false} otherwise.
	 *
	 * @return A boolean.
	 */
	public boolean isTimeSeries() {
		return this.isTimeSeries;
	}

	/**
	 * Sets a flag that indicates whether or not the data represents a time
	 * series.
	 *
	 * @param timeSeries
	 *            the new value of the flag.
	 */
	public void setTimeSeries(boolean timeSeries) {
		this.isTimeSeries = timeSeries;
	}

	/**
	 * ExecuteQuery will attempt execute the query passed to it against the
	 * existing database connection. If no connection exists then no action
	 * is taken.
	 * The results from the query are extracted and cached locally, thus
	 * applying an upper limit on how many rows can be retrieved successfully.
	 * Uses the missingEntryValue passed in in the constructor
	 *
	 * @param query the query to be executed.
	 * @param seriesName the name to give the series generated by this query 
	 * 		- or to prefix column names with where the query has more than 2 columns
	 * @throws SQLException
	 *             if there is a problem executing the query.
	 */
	public void executeQuery(String query, String seriesName) throws SQLException {
		executeQuery(this.connection, query, seriesName, this.missingEntryValue);
	}
	
	/**
	 * ExecuteQuery will attempt execute the query passed to it against the
	 * existing database connection. If no connection exists then no action
	 * is taken.
	 * The results from the query are extracted and cached locally, thus
	 * applying an upper limit on how many rows can be retrieved successfully.
	 * Uses the missingEntryValue passed in in the constructor
	 *
	 * @param query the query to be executed.
	 * @param seriesName the name to give the series generated by this query 
	 * 		- or to prefix column names with where the query has more than 2 columns
	 * @param  missingValue The value to use for any domain point the series is missing
	 * @throws SQLException
	 *             if there is a problem executing the query.
	 */
	public void executeQuery(String query, String seriesName, Object missingValue) throws SQLException {
		executeQuery(this.connection, query, seriesName, missingValue);
	}

    private void closeResultSet(ResultSet resultSet) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (Exception e) {}
		}
    }
    
    private void closeStatement(Statement statement) {
		if (statement != null) {
			try {
				statement.close();
			} catch (Exception e) {}
		}
    }
    
	/**
	 * initialiseDomainAxis this method sets all the possible values of the Domain (x-axis) we want to plot against
	 * this allows the class to accept multiple queries which might all provide all possible Domain values 
     */
    public void initialiseDomainAxis(String query) throws SQLException {
		ResultSet resultSet = null;
		Statement statement = null;
		this.rowMap = new HashMap<String, List<Object>>();
		try {
			boolean firstRow = true;
			statement = this.connection.createStatement();
			resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				Object xAxisValue = resultSet.getObject(1);
				List<Object> row = new ArrayList<Object>();
				if (firstRow) {
					ResultSetMetaData metaData = resultSet.getMetaData();
					int type = metaData.getColumnType(1);
					firstRow = false;
					// Are we working with a time series.
					switch (type) {
						case Types.DATE:
						case Types.TIME:
						case Types.TIMESTAMP:
							this.isTimeSeries = true;
							break;
						default:
							this.isTimeSeries = false;
							break;
					}
				}
				if (this.isTimeSeries) {
					row.add(new Long(((Date) xAxisValue).getTime()));
				} else {
					row.add(xAxisValue);
				}
				this.rowMap.put(xAxisValue.toString(), row);
			}
		} finally {
			this.closeResultSet(resultSet);
			this.closeStatement(statement);
		}
    }
    
    /**
	 * finaliseData runs the methods such as working out min and max values that
	 * would usually be done in executeQuery, but as we might have more than one
	 * query are split into their own method and must be run before methods to use
	 * values it works out
	 */
    public  void finaliseData(){
    	if (this.rowMap == null) {
    		throw new RuntimeException("Must call initialiseDomainAxis before you can finaliseData");
    	}
		/// Determine max and min values.
    	this.rowList = new ArrayList<List<Object>>(this.rowMap.values());
    	Collections.sort(this.rowList, new RowComparator());
    	
		if (this.rowList.size() < 1) {
			this.maxValue = 0.0;
			this.minValue = 0.0;
		} else {
			this.maxValue = Double.NEGATIVE_INFINITY;
			this.minValue = Double.POSITIVE_INFINITY;
			for (int rowNum = 0; rowNum < this.rowList.size(); ++rowNum) {
				List<Object> row = this.rowList.get(rowNum);
				// First value in row is x value, which we're not getting min/max of
				for (int column = 1; column < row.size(); column++) {
					Object testValue = row.get(column);
					if (testValue != null) {
						double test = ((Number) testValue).doubleValue();

						if (test < this.minValue) {
							this.minValue = test;
						}
						if (test > this.maxValue) {
							this.maxValue = test;
						}
					}
				}
			}
		}

		fireDatasetChanged(); // Tell the listeners a new table has arrived.
    }
	
	/**
	 * ExecuteQuery will attempt execute the query passed to it against the
	 * provided database connection. If connection is null then no action is
	 * taken.
	 * The results from the query are extracted and cached locally, thus
	 * applying an upper limit on how many rows can be retrieved successfully.
	 *
	 * @param query the query to be executed.
	 * @param con the connection the query is to be executed against.
	 * @param seriesName the name to give the series generated by this query 
	 * 		- or to prefix column names with where the query has more than 2 columns
	 * @param  missingValue The value to use for any domain point the series is missing
	 * @throws SQLException
	 *             if there is a problem executing the query.
	 */
	public void executeQuery(Connection con, String query, String seriesName, Object missingValue) throws SQLException {
		if (con == null) {
			throw new SQLException("There is no database to execute the query.");
		}
		
		if (this.rowMap == null) {
			throw new SQLException("You must call initialiseDomainAxis first with a query that returns all the x-values needed");
		}

		ResultSet resultSet = null;
		Statement statement = null;
		try {
			statement = con.createStatement();
			resultSet = statement.executeQuery(query);
			ResultSetMetaData metaData = resultSet.getMetaData();

			int numberOfColumns = metaData.getColumnCount();
			int numberOfValidColumns = 0;
			int[] columnTypes = new int[numberOfColumns];
			for (int column = 0; column < numberOfColumns; column++) {
				try {
					int type = metaData.getColumnType(column + 1);
					switch (type) {

						case Types.NUMERIC:
						case Types.REAL:
						case Types.INTEGER:
						case Types.DOUBLE:
						case Types.FLOAT:
						case Types.DECIMAL:
						case Types.BIT:
						case Types.DATE:
						case Types.TIME:
						case Types.TIMESTAMP:
						case Types.BIGINT:
						case Types.SMALLINT:
							++numberOfValidColumns;
							columnTypes[column] = type;
							break;
						default:
							columnTypes[column] = Types.NULL;
							break;
					}
				} catch (SQLException e) {
					columnTypes[column] = Types.NULL;
					throw e;
				}
			}

			if (numberOfValidColumns <= 1) {
				throw new SQLException("Not enough valid columns where generated by query.");
			}

			/// Get the column names and cache them.
			for (int column = 1; column < numberOfColumns; column++) {
				if (columnTypes[column] != Types.NULL) {
					if (seriesName == null) {
						seriesNames.add(metaData.getColumnLabel(column + 1));
					} else if (numberOfValidColumns == 2) {
						// x-axis plus one other, use the seriesName to label it
						seriesNames.add(seriesName);
					} else {
						// more than x-axis and one other, use the seriesName and column name to label it
						seriesNames.add(seriesName + ": " + metaData.getColumnLabel(column + 1));
					}
				}
			}

			// Get all rows.
			while (resultSet.next()) {
				List<Object> currentRow = this.rowMap.get(resultSet.getObject(1).toString());
				// x axis values already added to the row, so start at the second column 
				for (int column = 1; column < numberOfColumns; column++) {
					Object xObject = resultSet.getObject(column + 1);
					switch (columnTypes[column]) {
						case Types.NUMERIC:
						case Types.REAL:
						case Types.INTEGER:
						case Types.DOUBLE:
						case Types.FLOAT:
						case Types.DECIMAL:
						case Types.BIT:
						case Types.BIGINT:
						case Types.SMALLINT:
							currentRow.add(xObject);
							break;

						case Types.DATE:
						case Types.TIME:
						case Types.TIMESTAMP:
							currentRow.add(new Long(((Date) xObject).getTime()));
							break;
						case Types.NULL:
							break;
						default:
							System.err.println("Unknown data");
							columnTypes[column] = Types.NULL;
							break;
					}
				}
			}
			
			// Now need to fill in zeros for any x-axis values that didn't get something added
			// If we don't then subsequent datapoints for those x-axis values will be ascribed to the wrong series
			for (List<Object> row : this.rowMap.values()) {
				// first entry in row is the x-axis, so need to subtract 1 from row size
				int numMissing= this.seriesNames.size() - (row.size() -1);
				for (int i = 0; i < numMissing; i++) {
					row.add(missingValue);
				}
			}
		} finally {
			this.closeResultSet(resultSet);
			this.closeStatement(statement);
		}

	}

	/**
	 * Returns the x-value for the specified series and item. The
	 * implementation is responsible for ensuring that the x-values are
	 * presented in ascending order.
	 *
	 * @param seriesIndex
	 *            the series (zero-based index).
	 * @param itemIndex
	 *            the item (zero-based index).
	 * @return The x-value
	 * @see XYDataset
	 */
	@Override
	public Number getX(int seriesIndex, int itemIndex) {
		if (this.rowList == null) {
			throw new RuntimeException("Must call finaliseData before using dataset");
		}
		List<Object> row = this.rowList.get(itemIndex);
		return (Number) row.get(0);
	}

	/**
	 * Returns the y-value for the specified series and item.
	 *
	 * @param seriesIndex
	 *            the series (zero-based index).
	 * @param itemIndex
	 *            the item (zero-based index).
	 * @return The yValue value
	 * @see XYDataset
	 */
	@Override
	public Number getY(int seriesIndex, int itemIndex) {
		if (this.rowList == null) {
			throw new RuntimeException("Must call finaliseData before using dataset");
		}
		List<Object> row = this.rowList.get(itemIndex);
		return (Number) row.get(seriesIndex + 1);
	}

	/**
	 * Returns the number of items in the specified series.
	 *
	 * @param seriesIndex
	 *            the series (zero-based index).
	 * @return The itemCount value
	 * @see XYDataset
	 */
	@Override
	public int getItemCount(int seriesIndex) {
		if (this.rowList == null) {
			throw new RuntimeException("Must call finaliseData before using dataset");
		}
		return this.rowList.size();
	}

	/**
	 * Returns the number of items in all series. This method is defined by
	 * the {@link TableXYDataset} interface.
	 *
	 * @return The item count.
	 */
	@Override
	public int getItemCount() {
		return getItemCount(0);
	}

	/**
	 * Returns the number of series in the dataset.
	 *
	 * @return The seriesCount value
	 * @see XYDataset
	 * @see Dataset
	 */
	@Override
	public int getSeriesCount() {
		return this.seriesNames.size();
	}

	/**
	 * Returns the key for the specified series.
	 *
	 * @param seriesIndex
	 *            the series (zero-based index).
	 * @return The seriesName value
	 * @see XYDataset
	 * @see Dataset
	 */
	@Override
	public Comparable<String> getSeriesKey(int seriesIndex) {

		if ((seriesIndex < this.seriesNames.size()) && (this.seriesNames.get(seriesIndex) != null)) {
			return this.seriesNames.get(seriesIndex);
		} else {
			return "";
		}

	}

	/**
	 * Close the database connection
	 */
	public void close() {

		try {
			this.connection.close();
		} catch (Exception e) {
			System.err.println("JdbcXYDataset: swallowing exception.");
		}

	}

	/**
	 * Returns the minimum y-value in the dataset.
	 *
	 * @param includeInterval
	 *            a flag that determines whether or not the
	 *            y-interval is taken into account.
	 * @return The minimum value.
	 */
	@Override
	public double getRangeLowerBound(boolean includeInterval) {
		return this.minValue;
	}

	/**
	 * Returns the maximum y-value in the dataset.
	 *
	 * @param includeInterval
	 *            a flag that determines whether or not the
	 *            y-interval is taken into account.
	 * @return The maximum value.
	 */
	@Override
	public double getRangeUpperBound(boolean includeInterval) {
		return this.maxValue;
	}

	/**
	 * Returns the range of the values in this dataset's range.
	 *
	 * @param includeInterval
	 *            a flag that determines whether or not the
	 *            y-interval is taken into account.
	 * @return The range.
	 */
	@Override
	public Range getRangeBounds(boolean includeInterval) {
		return new Range(this.minValue, this.maxValue);
	}
}
MultipleSqlNonSharedDomainJdbcXYDataset:

Code: Select all

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.tuple.ImmutablePair;
import org.apache.commons.lang3.tuple.Pair;
import org.jfree.data.Range;
import org.jfree.data.RangeInfo;
import org.jfree.data.general.Dataset;
import org.jfree.data.xy.AbstractXYDataset;
import org.jfree.data.xy.TableXYDataset;
import org.jfree.data.xy.XYDataset;

/**
 * This class handles having multiple Queries to build up different series in the dataset.
 * The series do not have to share domain points, so can be used for, for example,
 * to create a timeseries chart where not all series have points at the same times.
 * @author rhurrell
 *
 */
public class MultipleSqlNonSharedDomainJdbcXYDataset extends AbstractXYDataset implements XYDataset, TableXYDataset, RangeInfo {
	private static final long serialVersionUID = 1L;

	/** The database connection. */
	private transient Connection connection;

	/** Seriesnames */
	private List<String> seriesNames = new ArrayList<String>();

	/** RowMap. Key is the series id, the pairs are x & y values */
	private Map<Integer, List<Pair<Object, Object>>> rowMap;

	/** The maximum y value of the returned result set */
	private double maxValue = 0.0;

	/** The minimum y value of the returned result set */
	private double minValue = 0.0;

	/** Is this dataset a timeseries ? */
	private boolean isTimeSeries = false;
	
	/** Has the dataset been finalised - by trying to make use of it */ 
	private boolean dataFinalised = false;

	/**
     * Creates a new JDBCXYDataset (initially empty) with no database
     * connection.
     */
    private MultipleSqlNonSharedDomainJdbcXYDataset() {
    }

	/**
     * Creates a new dataset (initially empty) and establishes a new database
     * connection.
     *
     * @param  url  URL of the database connection.
     * @param  driverName  the database driver class name.
     * @param  user  the database user.
     * @param  password  the database user's password.
     *
     * @throws ClassNotFoundException if the driver cannot be found.
     * @throws SQLException if there is a problem connecting to the database.
     */
    public MultipleSqlNonSharedDomainJdbcXYDataset(String url, String driverName, String user, String password) throws SQLException, ClassNotFoundException {
        this();
        Class.forName(driverName);
        this.connection = DriverManager.getConnection(url, user, password);
    }

	/**
     * Creates a new dataset (initially empty) using the specified database
     * connection.
     *
     * @param  con  the database connection.
     *
     * @throws SQLException if there is a problem connecting to the database.
     */
    public MultipleSqlNonSharedDomainJdbcXYDataset(Connection con) throws SQLException {
        this();
        this.connection = con;
    }

	/**
     * Creates a new dataset using the specified database connection, and
     * populates it using data obtained with the supplied query.
     *
     * @param con  the connection.
     * @param query  the SQL query.
	 * @param seriesName the name to give the series generated by this query 
	 * 		- or to prefix column names with where the query has more than 2 columns
     *
     * @throws SQLException if there is a problem executing the query.
     */
    public MultipleSqlNonSharedDomainJdbcXYDataset(Connection con, String query, String seriesName) throws SQLException {
        this(con);
        executeQuery(query, seriesName);
    }
    
	/**
	 * Returns {@code true} if the dataset represents time series data,
	 * and {@code false} otherwise.
	 *
	 * @return A boolean.
	 */
	public boolean isTimeSeries() {
		return this.isTimeSeries;
	}

	/**
	 * Sets a flag that indicates whether or not the data represents a time
	 * series.
	 *
	 * @param timeSeries
	 *            the new value of the flag.
	 */
	public void setTimeSeries(boolean timeSeries) {
		this.isTimeSeries = timeSeries;
	}

	/**
	 * ExecuteQuery will attempt execute the query passed to it against the
	 * existing database connection. If no connection exists then no action
	 * is taken.
	 * The results from the query are extracted and cached locally, thus
	 * applying an upper limit on how many rows can be retrieved successfully.
	 *
	 * @param query the query to be executed.
	 * @param seriesName the name to give the series generated by this query 
	 * 		- or to prefix column names with where the query has more than 2 columns
	 * @throws SQLException
	 *             if there is a problem executing the query.
	 */
	public void executeQuery(String query, String seriesName) throws SQLException {
		executeQuery(this.connection, query, seriesName);
	}

    private void closeResultSet(ResultSet resultSet) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (Exception e) {}
		}
    }
    
    private void closeStatement(Statement statement) {
		if (statement != null) {
			try {
				statement.close();
			} catch (Exception e) {}
		}
    }
    
    /**
	 * finaliseData runs the methods such as working out min and max values that
	 * would usually be done in executeQuery, but as we might have more than one
	 * query are split into their own method and must be run before methods to use
	 * values it works out
	 */
    public void finaliseData(){
		/// Determine max and min values.

    	if ((this.rowMap == null) || (this.rowMap.size() < 1)) {
			this.maxValue = 0.0;
			this.minValue = 0.0;
		} else {
			this.maxValue = Double.NEGATIVE_INFINITY;
			this.minValue = Double.POSITIVE_INFINITY;
			for (List<Pair<Object, Object>> seriesData : this.rowMap.values()) {
				for (int rowNum = 0; rowNum < seriesData.size(); ++rowNum) {
					Pair<Object, Object> row = seriesData.get(rowNum);
					// left value is x which we're not setting min/max for. The right value is y
					Object testValue = row.getRight();
					if (testValue != null) {
						double test = ((Number) testValue).doubleValue();
						if (test < this.minValue) {
							this.minValue = test;
						}
						if (test > this.maxValue) {
							this.maxValue = test;
						}
					}
				}
			}
		}

		fireDatasetChanged(); // Tell the listeners a new table has arrived.
		this.dataFinalised = true;
    }
	
	/**
	 * ExecuteQuery will attempt execute the query passed to it against the
	 * provided database connection. If connection is null then no action is
	 * taken.
	 * The results from the query are extracted and cached locally, thus
	 * applying an upper limit on how many rows can be retrieved successfully.
	 *
	 * @param query the query to be executed.
	 * @param con the connection the query is to be executed against.
	 * @param seriesName the name to give the series generated by this query 
	 * 		- or to prefix column names with where the query has more than 2 columns
	 * @throws SQLException
	 *             if there is a problem executing the query.
	 */
	public void executeQuery(Connection con, String query, String seriesName) throws SQLException {
		if (con == null) {
			throw new SQLException("There is no database to execute the query.");
		}
		
		if (this.rowMap == null) {
			this.rowMap = new HashMap<Integer, List<Pair<Object, Object>>>();
		}

		ResultSet resultSet = null;
		Statement statement = null;
		int previousSeriesCount = this.seriesNames.size();
		try {
			statement = con.createStatement();
			resultSet = statement.executeQuery(query);
			ResultSetMetaData metaData = resultSet.getMetaData();

			int numberOfColumns = metaData.getColumnCount();
			int numberOfValidColumns = 0;
			int[] columnTypes = new int[numberOfColumns];
			for (int column = 0; column < numberOfColumns; column++) {
				try {
					int type = metaData.getColumnType(column + 1);
					switch (type) {

						case Types.NUMERIC:
						case Types.REAL:
						case Types.INTEGER:
						case Types.DOUBLE:
						case Types.FLOAT:
						case Types.DECIMAL:
						case Types.BIT:
						case Types.DATE:
						case Types.TIME:
						case Types.TIMESTAMP:
						case Types.BIGINT:
						case Types.SMALLINT:
							++numberOfValidColumns;
							columnTypes[column] = type;
							break;
						default:
							columnTypes[column] = Types.NULL;
							break;
					}
				} catch (SQLException e) {
					columnTypes[column] = Types.NULL;
					throw e;
				}
			}

			if (numberOfValidColumns <= 1) {
				throw new SQLException("Not enough valid columns where generated by query.");
			}

			/// Get the column names and cache them.
			for (int column = 1; column < numberOfColumns; column++) {
				if (columnTypes[column] != Types.NULL) {
					if (seriesName == null) {
						seriesNames.add(metaData.getColumnLabel(column + 1));
					} else if (numberOfValidColumns == 2) {
						// x-axis plus one other, use the seriesName to label it
						seriesNames.add(seriesName);
					} else {
						// more than x-axis and one other, use the seriesName and column name to label it
						seriesNames.add(seriesName + ": " + metaData.getColumnLabel(column + 1));
					}
				}
			}
			
			// This does assume user is only using queries that are all timeSeries or all not - I don't know if charts can handle mixed.
			switch (columnTypes[0]) {
				case Types.DATE:
				case Types.TIME:
				case Types.TIMESTAMP:
					this.isTimeSeries = true;
					break;
				default:
					this.isTimeSeries = false;
					break;
			}

			// Get all rows.
			while (resultSet.next()) {
				Object xValue = resultSet.getObject(1);
				if (this.isTimeSeries) {
					xValue = new Long(((Date) xValue).getTime());
				}
				for (int column = 1; column < numberOfColumns; column++) {
					// Series index is zero based, so subtract one
					int seriesId = previousSeriesCount + column - 1;
					List<Pair<Object, Object>> seriesRows = this.rowMap.get(seriesId);
					Object yValue = resultSet.getObject(column + 1);
					
					if (seriesRows == null) {
						seriesRows = new ArrayList<Pair<Object, Object>>();
						this.rowMap.put(seriesId, seriesRows);
					}
					
					switch (columnTypes[column]) {
						case Types.NUMERIC:
						case Types.REAL:
						case Types.INTEGER:
						case Types.DOUBLE:
						case Types.FLOAT:
						case Types.DECIMAL:
						case Types.BIT:
						case Types.BIGINT:
						case Types.SMALLINT:
							seriesRows.add(new ImmutablePair<Object, Object>(xValue, yValue));
							break;

						case Types.DATE:
						case Types.TIME:
						case Types.TIMESTAMP:
							seriesRows.add(new ImmutablePair<Object, Object>(xValue, new Long(((Date) yValue).getTime())));
							break;
						case Types.NULL:
							break;
						default:
							System.err.println("Unknown data");
							columnTypes[column] = Types.NULL;
							break;
					}
				}
			}
		} finally {
			this.closeResultSet(resultSet);
			this.closeStatement(statement);
		}

	}

	/**
	 * Returns the x-value for the specified series and item. The
	 * implementation is responsible for ensuring that the x-values are
	 * presented in ascending order.
	 *
	 * @param seriesIndex
	 *            the series (zero-based index).
	 * @param itemIndex
	 *            the item (zero-based index).
	 * @return The x-value
	 * @see XYDataset
	 */
	@Override
	public Number getX(int seriesIndex, int itemIndex) {
		if (!this.dataFinalised) {
			throw new RuntimeException("Must call finaliseData before using dataset");
		}
		List<Pair<Object, Object>> rowList = this.rowMap.get(seriesIndex);
		return (Number) rowList.get(itemIndex).getLeft();
	}

	/**
	 * Returns the y-value for the specified series and item.
	 *
	 * @param seriesIndex
	 *            the series (zero-based index).
	 * @param itemIndex
	 *            the item (zero-based index).
	 * @return The yValue value
	 * @see XYDataset
	 */
	@Override
	public Number getY(int seriesIndex, int itemIndex) {
		if (!this.dataFinalised) {
			throw new RuntimeException("Must call finaliseData before using dataset");
		}
		List<Pair<Object, Object>> rowList = this.rowMap.get(seriesIndex);
		return (Number) rowList.get(itemIndex).getRight();
	}

	/**
	 * Returns the number of items in the specified series.
	 *
	 * @param seriesIndex
	 *            the series (zero-based index).
	 * @return The itemCount value
	 * @see XYDataset
	 */
	@Override
	public int getItemCount(int seriesIndex) {
		if (!this.dataFinalised) {
			throw new RuntimeException("Must call finaliseData before using dataset");
		}
		List<Pair<Object, Object>> rowList = this.rowMap.get(seriesIndex);
		return rowList.size();
	}

	/**
	 * Returns the number of items in all series. This method is defined by
	 * the {@link TableXYDataset} interface.
	 *
	 * @return The item count.
	 */
	@Override
	public int getItemCount() {
		return getItemCount(0);
	}

	/**
	 * Returns the number of series in the dataset.
	 *
	 * @return The seriesCount value
	 * @see XYDataset
	 * @see Dataset
	 */
	@Override
	public int getSeriesCount() {
		return this.seriesNames.size();
	}

	/**
	 * Returns the key for the specified series.
	 *
	 * @param seriesIndex
	 *            the series (zero-based index).
	 * @return The seriesName value
	 * @see XYDataset
	 * @see Dataset
	 */
	@Override
	public Comparable<String> getSeriesKey(int seriesIndex) {

		if ((seriesIndex < this.seriesNames.size()) && (this.seriesNames.get(seriesIndex) != null)) {
			return this.seriesNames.get(seriesIndex);
		} else {
			return "";
		}

	}

	/**
	 * Close the database connection
	 */
	public void close() {

		try {
			this.connection.close();
		} catch (Exception e) {
			System.err.println("JdbcXYDataset: swallowing exception.");
		}

	}

	/**
	 * Returns the minimum y-value in the dataset.
	 *
	 * @param includeInterval
	 *            a flag that determines whether or not the
	 *            y-interval is taken into account.
	 * @return The minimum value.
	 */
	@Override
	public double getRangeLowerBound(boolean includeInterval) {
		return this.minValue;
	}

	/**
	 * Returns the maximum y-value in the dataset.
	 *
	 * @param includeInterval
	 *            a flag that determines whether or not the
	 *            y-interval is taken into account.
	 * @return The maximum value.
	 */
	@Override
	public double getRangeUpperBound(boolean includeInterval) {
		return this.maxValue;
	}

	/**
	 * Returns the range of the values in this dataset's range.
	 *
	 * @param includeInterval
	 *            a flag that determines whether or not the
	 *            y-interval is taken into account.
	 * @return The range.
	 */
	@Override
	public Range getRangeBounds(boolean includeInterval) {
		return new Range(this.minValue, this.maxValue);
	}
}
RowComparator:

Code: Select all

import java.util.Comparator;
import java.util.List;

public class RowComparator implements Comparator<List<Object>> {

	@Override
	public int compare(List<Object> o1, List<Object> o2) {
		if (o1 == o2) {
			return 0;
		}
		if (o1 == null) {
			return -1;
		}
		if (o2 == null) {
			return 1;
		}
		if (o1.isEmpty() && o2.isEmpty()) {
			return 0;
		}
		if (o1.isEmpty()) {
			return -1;
		}
		if (o2.isEmpty()) {
			return 1;
		}
		if (((Number)o1.get(0)).doubleValue() < ((Number)o2.get(0)).doubleValue()) {
			return -1;
		}
		if (((Number)o1.get(0)).doubleValue() > ((Number)o2.get(0)).doubleValue()) {
			return 1;
		}
		return 0;
	}
}

Post Reply