package buzzerproxy.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author Tom
 */
public class DerbyShowTableContent {

	/**
	 * @param args
	 */
	public static void main(String[] args) throws Exception {
		Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
		Properties properties = new Properties();
		// properties.put("user", "user1");
		// properties.put("password", "user1");
		Connection connection = DriverManager.getConnection(
				"jdbc:derby:www.buzzerstar.com", properties);

		// createTableTestIfItDoesntExistYet(connection);
		// populateTableTestIfItHasNotBeenPopulatedYet(connection);
		showContentsOfTableTest(connection);

		connection.close();
	}

	/**
	 * @param connection
	 * @throws SQLException
	 */
	private static void showContentsOfTableTest(Connection connection)
			throws SQLException {
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery("SELECT * FROM logging");
		int columnCnt = resultSet.getMetaData().getColumnCount();
		
		System.out.println("Colums: " + columnCnt);
		int count = 0;
		while (resultSet.next()) {
			System.out.println("Count: " + count);
			for (int i = 1; i <= columnCnt; i++) {
				System.out.print("(" + i + ")" + resultSet.getString(i) + " ");
			}
			System.out.println();
			count++;
		}
		resultSet.close();
		statement.close();
	}

	private static void populateTableTestIfItHasNotBeenPopulatedYet(
			Connection connection) throws Exception {

		boolean shouldPopulateTable = true;
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement
				.executeQuery("SELECT COUNT(*) FROM test");
		if (resultSet.next()) {
			shouldPopulateTable = resultSet.getInt(1) == 0;
		}
		resultSet.close();
		statement.close();

		if (shouldPopulateTable) {
			System.out.println("Populating Table test...");
			PreparedStatement preparedStatement = connection
					.prepareStatement("INSERT INTO test VALUES (?,?)");
			String[] data = { "AAA", "BBB", "CCC", "DDD", "EEE" };
			for (int i = 0; i < data.length; i++) {
				preparedStatement.setInt(1, i);
				preparedStatement.setString(2, data[i]);
				preparedStatement.execute();
			}
			preparedStatement.close();
		}
	}

	private static void createTableTestIfItDoesntExistYet(Connection connection)
			throws Exception {
		ResultSet resultSet = connection.getMetaData().getTables("%", "%", "%",
				new String[] { "TABLE" });
		int columnCnt = resultSet.getMetaData().getColumnCount();
		boolean shouldCreateTable = true;
		while (resultSet.next() && shouldCreateTable) {
			if (resultSet.getString("TABLE_NAME").equalsIgnoreCase("TEST")) {
				shouldCreateTable = false;
			}
		}
		resultSet.close();
		if (shouldCreateTable) {
			System.out.println("Creating Table test...");
			Statement statement = connection.createStatement();
			statement
					.execute("create table test (id int not null, data varchar(32))");
			statement.close();
		}
	}
}