• Blog
  • Talend DI
  • Testing Hive or MySQL database connections directly using JDBC driver
Wednesday, 24 February 2016 16:18

Testing Hive or MySQL database connections directly using JDBC driver

Written by 
Rate this item
(0 votes)

Every once in a while, one runs into a situation that one can not connect to a database from a tool or application. When this happens, the best way to isolate the issue is to try to connect to the same database using a quick java application using standard JDBC.

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.

 mysql1

To compile the application, change directory to the .java class file's directory, then issue the following: javac testDBConnection.java

mysql2

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

 

mysql3

 

And that's it - now we know we can connect!

 

Read 1938 times Last modified on Wednesday, 24 February 2016 17:52
Will Munji

Will Munji is a seasoned data integration, data warehousing and business intelligence (BI) architect & developer who has been working in the DW/BI space for a while. He got his start in BI working on Brio SQR (later Hyperion SQR) and the Crystal Decisions stack (Reports, Analysis & Enterprise) and SAP BusinessObjects / Microsoft BI stacks. He currently focuses on Talend Data Management Suite, Hadoop, SAP BusinessObjects BI stack as well as Jaspersoft and Tableau. He has consulted for many organizations across a variety of industries including healthcare, manufacturing, retail, insurance and banking. At Kindle Consulting, Will delivers DW/BI/Data Integration solutions that range from front-end BI development (dashboards, reports, cube development, T-SQL/ PL/SQL ...) to data services (ETL/ DI development), data warehouse architecture and development, data integration to BI Architecture design and deployment.

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

Contact Us

Kindle Consulting, Inc

4355-J Cobb Pkwy, Suite 138, Atlanta GA, 30339

Phone: 404.551.5494
Fax: 404.551.5452
Email: info@kindleconsulting.com

Talend Gold Partner