The following is a simple class that can be used to test connecting to any database that has a JDBC driver - Hive, Oracle, MySQL, SQL Server... All you need to have is the driver jar file for the database in question. Simply update the values in bold to the values for your database and table.
package dbConnectionPackage;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class testDBConnection {
public static void main(String[] args) {
String dbType = "mysql";
String dbDriver = "com.mysql.jdbc.Driver";
String dbName = "riofoodsdb";
String dbHost = "localhost";
String dbPort = "3306";
String dbUserName = "myUserName";
String dbUserPwd = "myPwd";
String tableName = "customers";
String fieldName = "CompanyName";
int numRecordsToPrint = 3;
System.out.println("\n" + "\n" + "------------ " + dbType + " JDBC Connection Testing ------------");
try {
Class.forName(dbDriver);
} catch (ClassNotFoundException e) {
System.out.println(dbType + " JDBC Driver is missing!");
e.printStackTrace();
return;
}
System.out.println(dbType + " JDBC Driver found in class path and registered!");
Connection dbconn = null;
try {
System.out.println("Testing connection to " + dbType + " database '" + dbName + "'...");
dbconn = DriverManager
.getConnection("jdbc:" + dbType + "://" + dbHost + ":" + dbPort + "/" + dbName, dbUserName, dbUserPwd);
} catch (SQLException e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return;
}
if (dbconn != null) {
System.out.println("DB Connection succeeded! Successfully connected to " + dbType + " database '" + dbName + "' running on " + dbHost);
System.out.println("Printing " + numRecordsToPrint + " records from " + tableName + "..." + "\n");
try {
PreparedStatement ps = dbconn.prepareStatement("SELECT " + fieldName + " FROM " + dbName + "." + tableName + " limit " + numRecordsToPrint);
/* if Hive UDF, and need to pass params, can do something like:
...prepareStatement("select * from UDF('" + UDFinputVal + "')" */
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String field1 = rs.getString(1);
System.out.println(field1);
}
rs.close();
ps.close();
System.out.println("\n" + "Done! :)");
}
catch (Exception e) {
e.printStackTrace();
}
} else {
System.out.println("Connection to " + dbName + " database failed!");
}
}
}
Save the file in a folder - in my case, C:\Workbench\Forum\dbconnectionTest\mysql, as a file named testDBConnection.java (same as the class name above). In that directory, create a subdirectory called dbConnectionPackage, which matches the name of the package above.
To compile the application, change directory to the .java class file's directory, then issue the following: javac testDBConnection.java
Successful compilation results in creation of a CLASS file. Move the .CLASS file into the dbConnectionPackage directory
Test run the application:
// On Windows O/S machines (use semi-colons to separate jars in list)
java -cp .;mysql-connector-java-bin.jar dbConnectionPackage/testDBConnection
// On Linux O/S machines (use colons to separate jars in list)
java -cp .:mysql-connector-java-bin.jar dbConnectionPackage/testDBConnection
And that's it - now we know we can connect!