Skip to content

Instantly share code, notes, and snippets.

@ulisseslima
Last active March 7, 2018 16:45
Show Gist options
  • Save ulisseslima/0766987c9b6881fbe5606eac98564f8c to your computer and use it in GitHub Desktop.
Save ulisseslima/0766987c9b6881fbe5606eac98564f8c to your computer and use it in GitHub Desktop.
JDBC Example for Oracle database to perform queries and return dynamic results
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!");
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment