Learn more
 

Ready to get started?

Download a free trial of the FTP Driver to get started:

 Download Now

Learn more:

FTP Icon FTP JDBC Driver

An easy-to-use database-like interface for Java based applications and reporting tools access to remote files and directories.

In this article

Related articles

Create a Data Access Object for FTP Data using JDBI


A brief overview of creating a SQL Object API for FTP data in JDBI.

JDBI is a SQL convenience library for Java that exposes two different style APIs, a fluent style and a SQL object style. The CData JDBC Driver for FTP integrates connectivity to live FTP data in Java applications. By pairing these technologies, you gain simple, programmatic access to FTP data. This article walks through building a basic Data Access Object (DAO) and the accompanying code to read and write FTP data.

Create a DAO for the FTP MyDirectory Entity

The interface below declares the desired behavior for the SQL object to create a single method for each SQL statement to be implemented.

public interface MyMyDirectoryDAO { //insert new data into FTP @SqlUpdate("INSERT INTO MyDirectory (FilePath, Filename) values (:filePath, :filename)") void insert(@Bind("filePath") String filePath, @Bind("filename") String filename); //request specific data from FTP (String type is used for simplicity) @SqlQuery("SELECT Filename FROM MyDirectory WHERE FilePath = :filePath") String findFilenameByFilePath(@Bind("filePath") String filePath); /* * close with no args is used to close the connection */ void close(); }

Open a Connection to FTP

Collect the necessary connection properties and construct the appropriate JDBC URL for connecting to FTP.

To connect to FTP or SFTP servers, specify at least RemoteHost and FileProtocol. Specify the port with RemotePort.

Set User and Password to perform Basic authentication. Set SSHAuthMode to use SSH authentication. See the Getting Started section of the data provider help documentation for more information on authenticating via SSH.

Set SSLMode and SSLServerCert to secure connections with SSL.

The data provider lists the tables based on the available folders in your FTP server. Set the following connection properties to control the relational view of the file system:

  • RemotePath: Set this to the current working directory.
  • TableDepth: Set this to control the depth of folders to list as views.
  • FileRetrievalDepth: Set this to retrieve and list files recursively from the root table.

Stored Procedures are available to download files, upload files, and send protocol commands. See the Data Model chapter of the FTP data provider documentation for more information.

Built-in Connection String Designer

For assistance in constructing the JDBC URL, use the connection string designer built into the FTP JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

java -jar cdata.jdbc.ftp.jar

Fill in the connection properties and copy the connection string to the clipboard.

A connection string for FTP will typically look like the following:

jdbc:ftp:RemoteHost=MyFTPServer;

Use the configured JDBC URL to obtain an instance of the DAO interface. The particular method shown below will open a handle bound to the instance, so the instance needs to be closed explicitly to release the handle and the bound JDBC connection.

DBI dbi = new DBI("jdbc:ftp:RemoteHost=MyFTPServer;"); MyMyDirectoryDAO dao = dbi.open(MyMyDirectoryDAO.class); //do stuff with the DAO dao.close();

Read FTP Data

With the connection open to FTP, simply call the previously defined method to retrieve data from the MyDirectory entity in FTP.

//disply the result of our 'find' method String filename = dao.findFilenameByFilePath("/documents/doc.txt"); System.out.println(filename);

Write FTP Data

It is also simple to write data to FTP, using the previously defined method.

//add a new entry to the MyDirectory entity dao.insert(newFilePath, newFilename);

Since the JDBI library is able to work with JDBC connections, you can easily produce a SQL Object API for FTP by integrating with the CData JDBC Driver for FTP. Download a free trial and work with live FTP data in custom Java applications today.