import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Connect to an Oracle database and perform queries/updates. Easily adaptable
 * to other databases.
 * <p>
 * It will print the query column names/results dynamically, just like with the
 * PSQL client or SQLPLUS.
 * <p>
 * TODO: decide column width to print based on column value. Currently it's just
 * using two tabs.
 * <p>
 * Example of execution:
 * java -cp .:ojdbc5.jar OracleJdbcClient 127.0.0.1 1521 xe user pwd "SELECT 1 from dual"
 *
 * @since Mar 7, 2018
 * @author Ulisses Lima
 */
public class OracleJdbcClient {
	public static void main(String[] argv) throws SQLException {
		PrintStream out = System.out;
		out.println("-------- Oracle JDBC Connection Testing ------");

		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			out.println("Where is your Oracle JDBC Driver? Pass the jar with -cp");
			e.printStackTrace();
			return;
		}

		out.println("Oracle JDBC Driver Registered!");

		Connection connection = null;
		try {
			out.println("host " + argv[0]);
			out.println("port " + argv[1]);
			out.println("sid " + argv[2]);
			out.println("user " + argv[3]);
			out.println("pass " + argv[4]);

			connection = DriverManager.getConnection("jdbc:oracle:thin:@" + argv[0] + ":" + argv[1] + ":" + argv[2],
					argv[3], argv[4]);
		} catch (SQLException e) {
			out.println("Connection Failed! Check output console");
			e.printStackTrace();
			return;
		}

		if (connection != null) {
			out.println("connection ok");
			String sql = argv[5];

			Statement stmt = connection.createStatement();
			out.println("command: " + sql);
			ResultSet rs = null;
			if (sql.toLowerCase().startsWith("select")) {
				rs = stmt.executeQuery(sql);

				int cols = rs.getMetaData().getColumnCount();
				for (int i = 1; i < cols + 1; i++) {
					out.print(rs.getMetaData().getColumnName(i) + "\t\t");
				}
				out.println();

				while (rs.next()) {
					for (int i = 1; i < cols + 1; i++) {
						out.print(rs.getObject(i) + "\t\t");
					}
					out.println();
				}
			} else {
				int n = stmt.executeUpdate(sql);
				out.println("rows affected: " + n);
			}
		} else {
			out.println("Failed to make connection!");
		}
	}
}