/*
 * GRAL: GRAphing Library for Java(R)
 *
 * (C) Copyright 2009-2016 Erich Seifert ,
 * Michael Seifert 
 *
 * This file is part of GRAL.
 *
 * GRAL is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * GRAL is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with GRAL.  If not, see .
 */
package de.erichseifert.gral.data;

import java.io.IOException;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;

/**
 * Data source for database tables accessed through a JDBC connection.
 */
public class JdbcData extends AbstractDataSource {
	/** Version id for serialization. */
	private static final long serialVersionUID = 5196527358266585129L;

	/** The JDBC connection. */
	private final Connection connection;
	/** The name of the table containing the data. */
	private final String table;

	/** Flag that tells whether this object uses buffering. */
	private boolean buffered;
	/** Buffered number of rows. Only valid when the object is buffered. */
	private int bufferedRowCount;
	/** Buffered result of the JDBC data query. Only valid when the object is
	buffered. */
	private ResultSet bufferedQuery;
	/** Last row accessed by the {@link #get(int, int)} method.
	Only valid when the object is buffered. */
	private int bufferedQueryRow;

	/**
	 * Initializes a new instance to query the data from a specified table
	 * using a specified JDBC connection. It is assumed the table columns
	 * are constant during the connection.
	 * @param connection JDBC connection object.
	 * @param table Properly quoted name of the table.
	 * @param buffered Turns on buffering of JDBC queries.
	 */
	@SuppressWarnings("unchecked")
	public JdbcData(Connection connection, String table, boolean buffered) {
		this.connection = connection;
		this.table = table;
		setBuffered(buffered);

		try {
			setColumnTypes(getJdbcColumnTypes());
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Initializes a new buffered instance to query the data from a specified
	 * table using a specified JDBC connection.
	 * @param connection JDBC connection object.
	 * @param table Properly quoted name of the table.
	 */
	public JdbcData(Connection connection, String table) {
		this(connection, table, true);
	}

	/**
	 * Returns the row with the specified index.
	 * @param col index of the column to return
	 * @param row index of the row to return
	 * @return the specified value of the data cell
	 */
	public Comparable get(int col, int row) {
		try {
			ResultSet result = bufferedQuery;
			if (!isBuffered() || result == null) {
				PreparedStatement stmt = connection.prepareStatement(
						"SELECT * FROM " + table + "", //$NON-NLS-1$ //$NON-NLS-2$
						ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				result = stmt.executeQuery();

				if (isBuffered()) {
					bufferedQuery = result;
				}
			}
			if (!isBuffered() || row != bufferedQueryRow) {
				result.absolute(row + 1);
				bufferedQueryRow = row;
			}
			return jdbcToJavaValue(result, col);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public int getColumnCount() {
		if (getColumnTypes() != null) {
			return getColumnTypes().length;
		}
		return 0;
	}

	/**
	 * Returns the number of rows of the data source.
	 * @return number of rows in the data source.
	 */
	public int getRowCount() {
		int rowCount = bufferedRowCount;
		if (!isBuffered() || rowCount < 0) {
			try {
				PreparedStatement stmt = connection.prepareStatement(
					"SELECT COUNT(*) FROM " + table, //$NON-NLS-1$
					ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
				ResultSet result = stmt.executeQuery();
				if (result.first()) {
					rowCount = result.getInt(1);
					bufferedRowCount = rowCount;
				} else {
					rowCount = 0;
				}
				result.close();
			} catch (SQLException e) {
				e.printStackTrace();
				rowCount = 0;
			}
		}
		return rowCount;
	}

	/**
	 * Fetches the column types as Java {@code Class} objects from the
	 * JDBC table.
	 * @return Column types as Java {@code Class} objects
	 * @throws SQLException if an error occurs during access to JDBC table.
	 */
	@SuppressWarnings("unchecked")
	private Class>[] getJdbcColumnTypes()
			throws SQLException {
		PreparedStatement stmt = connection.prepareStatement(
			"SELECT * FROM " + table + " WHERE 1 = 0"); //$NON-NLS-1$ //$NON-NLS-2$
		ResultSetMetaData metadata = stmt.getMetaData();
		int colCount = metadata.getColumnCount();
		Class[] types = new Class[colCount];
		for (int colIndex = 0; colIndex < colCount; colIndex++) {
			int sqlType = metadata.getColumnType(colIndex + 1);
			Class> type = null;
			switch (sqlType) {
			case Types.TINYINT:
				type = Byte.class;
				break;
			case Types.SMALLINT:
				type = Short.class;
				break;
			case Types.INTEGER:
				type = Integer.class;
				break;
			case Types.BIGINT:
				type = Long.class;
				break;
			case Types.REAL:
				type = Float.class;
				break;
			case Types.FLOAT:
			case Types.DOUBLE:
				type = Double.class;
				break;
			case Types.DATE:
				type = Date.class;
				break;
			case Types.TIME:
				type = Time.class;
				break;
			case Types.TIMESTAMP:
				type = Timestamp.class;
				break;
			case Types.CHAR:
			case Types.NCHAR:
			case Types.VARCHAR:
			case Types.LONGVARCHAR:
			case Types.NVARCHAR:
			case Types.LONGNVARCHAR:
				type = String.class;
			default:
				break;
			}
			types[colIndex] = type;
		}
		return (Class>[]) types;
	}

	/**
	 * Converts a value of a JDBC {@code ResultSet} to a Java compatible
	 * data value. If the data type is unknown {@code null} will be
	 * returned.
	 * @param row ResultSet object.
	 * @param col Column.
	 * @return Converted value.
	 * @throws SQLException if an error occurs during conversion or accessing
	 *         the result set.
	 */
	private Comparable jdbcToJavaValue(ResultSet row, int col) throws SQLException {
		// TODO Add getColumn(int).getType() method to Column
		// AbstractDataSource.getColumnTypes() makes a defensive copy.
		Class> colType = getColumnTypes()[col];
		int sqlCol = col + 1;
		if (Byte.class.equals(colType)) {
			return row.getByte(sqlCol);
		} else if (Short.class.equals(colType)) {
			return row.getShort(sqlCol);
		} else if (Integer.class.equals(colType)) {
			return row.getInt(sqlCol);
		} else if (Long.class.equals(colType)) {
			return row.getLong(sqlCol);
		} else if (Float.class.equals(colType)) {
			return row.getFloat(sqlCol);
		} else if (Double.class.equals(colType)) {
			return row.getDouble(sqlCol);
		} else if (Date.class.equals(colType)) {
			return row.getDate(sqlCol);
		} else if (Time.class.equals(colType)) {
			return row.getTime(sqlCol);
		} else if (Timestamp.class.equals(colType)) {
			return row.getTimestamp(sqlCol);
		} else if (String.class.equals(colType)) {
			return row.getString(sqlCol);
		} else {
			return null;
		}
	}

	/**
	 * Returns whether this data source is buffered.
	 * @return {@code true} when this object uses buffering,
	 *         {@code false} otherwise
	 */
	public boolean isBuffered() {
		return buffered;
	}

	/**
	 * Determines whether this data source should buffer intermediate results.
	 * This implies that the data doesn't change during access.
	 * @param buffered {@code true} when this object should use buffering,
	 *                 {@code false} otherwise
	 */
	public void setBuffered(boolean buffered) {
		this.buffered = buffered;
		this.bufferedRowCount = -1;
		this.bufferedQuery = null;
		this.bufferedQueryRow = -1;
	}

	/**
	 * Custom serialization method.
	 * @param out Output stream.
	 * @throws ClassNotFoundException if a deserialized class does not exist.
	 * @throws IOException if there is an error while writing data to the
	 *         output stream.
	 */
	private void writeObject(ObjectOutputStream out)
			throws ClassNotFoundException, IOException {
		throw new UnsupportedOperationException("JDBC data sources cannot be serialized.");
	}
}

 

Ads help maintain this website.