import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import javax.sql.RowSetMetaData;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetMetaDataImpl;

import com.sun.rowset.CachedRowSetImpl;


public class CachedRowSetTest {

	private static final int PAGE_SIZE = 50;
	private static final int MAX_POINTS_IN_MEM = 10000;

	/**
	 * @param args
	 */
	public static void main(String[] args) {
			

		Connection connection;
		try {
			connection = connect();
		} catch (SQLException e1) {
			e1.printStackTrace();
			return;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return;
		}
		
		
		if (!tableExists(connection)) {
			try {
				createTable(connection);
			} catch (SQLException e) {
				e.printStackTrace();
				shutdown(connection);
				return;
			}
		}

		try {
			
			
			int canId = 0;
			
			CachedRowSet crs = new CachedRowSetImpl();
			
			
			crs.setCommand("SELECT * FROM rawData WHERE canId = ?");
			crs.setInt(1, canId);
			
			crs.setMaxRows(MAX_POINTS_IN_MEM);
			crs.setPageSize(PAGE_SIZE);
						
			crs.execute(connection);
			
			while(crs.next()) {
				System.out.println("timestamp: " + crs.getLong("timestamp"));
				System.out.println("value: " + crs.getInt("value"));
				System.out.println("canId: " + crs.getInt("canId"));
				System.out.println("---------");
			}
			
			
			RowSetMetaData rsmd = new RowSetMetaDataImpl();
			rsmd.setColumnCount(3);
			rsmd.setColumnName(1, "timestamp");
			rsmd.setColumnType(1, Types.VARCHAR);
			rsmd.setColumnName(2, "value");
			rsmd.setColumnType(2, Types.VARCHAR);
			rsmd.setColumnName(3, "canId");
			rsmd.setColumnType(3, Types.VARCHAR);

			rsmd.setTableName(1, "rawData");
			rsmd.setTableName(2, "rawData");
			rsmd.setTableName(3, "rawData");
			
			crs = new CachedRowSetImpl();
			crs.setMetaData(rsmd);
			
			crs.moveToInsertRow();
			crs.updateString(1, Long.toString(System.currentTimeMillis()));
			crs.updateString(2, "122131");
			crs.updateString(3, "0");
			crs.insertRow();
			
			crs.acceptChanges(connection);
			
			shutdown(connection);
			
		} catch (SQLException e1) {
			e1.printStackTrace();
			shutdown(connection);
		}
		
		
	}

	private static void shutdown(Connection connection) {
		try {
			Statement statement = connection.createStatement();
			statement.executeQuery("SHUTDOWN");
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private static boolean tableExists(Connection connection) {
		try {
			Statement statement = connection.createStatement();
			statement.executeQuery("SELECT * FROM rawData");
			return true;
		} catch (SQLException e) {
			return false;
		}
	}

	private static void createTable(Connection connection) throws SQLException {
		Statement statement = connection.createStatement();
		
		statement.executeQuery("CREATE CACHED TABLE rawData(timestamp BIGINT, value INTEGER, canId INTEGER)");
		statement.executeQuery("CREATE INDEX timeIndex ON rawData(timestamp)");
		statement.executeQuery("CREATE INDEX canIndex ON rawData(canId)");
	}

	private static Connection connect() throws SQLException, ClassNotFoundException {
		
		Class.forName("org.hsqldb.jdbcDriver");
		
		return DriverManager.getConnection("jdbc:hsqldb:file:testdb", "sa", "");
	}

}