How To Establish JDBC Connections From the CLI

A quick guide to using the CLI tool SQLLine and any JDBC driver to construct, test, and use connections to any database

Many applications connect to databases using JDBC drivers, often configured by JDBC Connection URLs. As a system or platform administrator, constructing these strings and testing credentials or permissions is often painful, especially as databases are often only accessible from specific networks; connecting to them from your laptop using graphical tools like DBeaver or SQuirrel may not be an option.

Testing JBDC Connection URLs as an administrator can be a painful experience

sqlline is a command-line shell for issuing SQL to relational databases via JDBC. It can connect to any database for which there exist JDBC drivers. This makes it easy to quickly construct and test JDBC connection URIs to connect to your database. Although sqlline is not under very active development, I have found it to work reliably and for various database types (MySQL, Oracle, PostgreSQL, …).

GitHub - julianhyde/sqlline: Shell for issuing SQL to relational databases via JDBC

Installation

You can download the sqlline JAR pre-packaged together with all its dependencies from the Sonatype Maven Central Repository:

ver=1.12.0
wget https://repo1.maven.org/maven2/sqlline/sqlline/$ver/sqlline-$ver-jar-with-dependencies.jar

You should now be able to run sqlline:

java -jar sqlline-$ver-jar-with-dependencies.jar --help
# Usage: java sqlline.SqlLine 
#   -u <database url>               the JDBC URL to connect to
#   -n <username>                   the username to connect as
#   -p <password>                   the password to connect as
#   -d <driver class>               the driver class to use

You will also want to download JDBC drivers, for example, Oracle’s JDBC driver if you want to connect to Oracle servers:

wget https://download.oracle.com/otn-pub/otn_software/jdbc/233/ojdbc11.jar

If you have special requirements, you can follow the instructions in the sqlline repository to compile sqlline from source.

Usage

You can now start sqlline and use it to connect to a database:

java -cp "*" sqlline.SqlLine \
  -n myusername -p supersecretpassword \
  -u "jdbc:oracle:thin:@my.host.name:1521:my-sid"

This will open up an interactive interface into which you can type commands. For example, you can issue the order !tables to list all available tables. Type !help to get a list of all commands.

You can run SQL queries by typing !sql followed by SQL command:

0: jdbc:oracle:thin:@my.host.name> !sql SELECT COUNT(1) FROM mytable;
+----------+
| COUNT(1) |
+----------+
| 3358     |
+----------+
1 row selected (0.02 seconds)

To quit sqlline, type !quit.

Conclusion

SQLLine is an excellent tool that you can use to quickly construct JDBC connection strings and test JDBC drivers and database credentials. It is especially useful to test connections from headless servers. As we have seen in this blog post, installation is a breeze. Stop port forwarding to DBeaver and start using SQLLine!

• 👏 If you liked this article, don’t forget to clap!
• 🗣️ Share your insights in the comments.
• 👀 For more about Data Minded, visit our website.