Over a million developers have joined DZone.

Awake SQL: access remote SQL databases securely from Desktop Apps using JDBC

·

Awake SQL is an Open Source framework which enables remote JDBC access through HTTP. Java Desktop App developers just need to include regular JDBC calls in their code like they would with a local database to access remote SQL databases in the cloud.

Awake SQL comes with a complete set of features: main data types are implemented, including blobs/clobs, and transactions are supported.

Security has been taken into account from the design stage: server-side configuration lets admins specify strong authentication and security rules in order to protect the databases.

Awake SQL is licensed through the GNU Lesser General Public License (LGPL v2.1): you can use it for free and without any constraints in your open source projects as well as in your commercial applications.

Why Awake SQL?

As developers of Swing client/server applications for our customers, we wanted to speed up our coding when accessing the hosts SQL data from the desktop.

Each new customer request would typically require to go through this inefficient cycle:

  1. Modify the client side to handle the new query or update with Apache HttpClient. This means coding a new action in our client/server protocol and new parameters that will be sent to the server side. We must also analyse the response, handle communication errors and application errors, etc.
  2. Develop or modify a Servlet (or REST class) and code the new SQL query or update. The class must also handle the client call, analyse the parameters and return the result on the Http Output Stream.
  3. Deploy the server classes on Tomcat.
  4. Restart Tomcat.
  5. Launch the client desktop app and test it.
  6. Go to 1 if it doesn’t work…


To gain in productivity,  we wanted to be able to add new SQL requests without any modifications or actions on the server side. With Awake SQL, we got to a much more efficient cycle:

  1. Modify the client desktop app to handle the new SQL query or update as a regular JDBC call.
  2. Launch the client desktop app and test it.
  3. Go to 1 if it fails…


There is nothing to code on the server side for a new SQL query, no client/server protocol to develop and test, and not need to restart Tomcat! The development cycle is mush easier and faster.

This is why we developed Awake SQL and we are now happy to release it as Open Source Software.

How it works

The Awake SQL framework consists of:

  1. A Client Library.
  2. A Server Manager.
  3. User Configuration classes injected at runtime (start of servlet container).

The Client Library is installed on the client side (typically a PC). The client app (typically a Swing/JavaFX Desktop App) accesses it through APIs that create a Connection instance. Each JDBC statement is then sent to the Server Manager for execution on the remote machine. The execution of each JDBC statement is conditioned by the rules defined in the Java User Configuration classes.

All communications between the PC and the Server simply go through  HTTP protocol on the standard 80 and 443 ports. (Communications may be secured using SSL/TLS).

Data transport & Optimizations

Techniques used:

  1. JSON format  with least possible meta-information is used for data transport.
  2. Large content (ResultSet, Blobs/Clobs…) are transferred using files. They are never loaded in memory on client or server side. Streaming techniques are always used to read and write their content.
  3. The server side is stateless: no user or session info are stored on the server. This allows to configure Awake SQL with any http load balancing & failover services.

Examples

This snippet shows how to create a JDBC Connection to a remote database and execute a SELECT query:

  // Define URL of the path to the AwakeSqlManager Servlet
  // We will use a secure SSL/TLS session. All uploads/downloads of SQL
  // data will be encrypted.
  String url = "https://www.acme.org/AwakeSqlManager";

  // The login info for strong authentication on server side:
  String username = "myUsername";
  char[] password = { 'm', 'y', 'P', 'a', 's', 's', 'w', 'o', 'r', 'd' }; 

  // Create the JDBC Connection to the remote database:
  Connection connection = new AwakeConnection(url, username, password);

  // We can now use our Remote JDBC Connection as a regular JDBC
  // Connection for our queries and updates:
  String sql = "SELECT CUSTOMER_ID, FNAME, LNAME FROM CUSTOMER " +
      "WHERE CUSTOMER_ID = ?";
  PreparedStatement prepStatement = connection.prepareStatement(sql);
  prepStatement.setInt(1, 1);

  ResultSet rs = prepStatement.executeQuery();
  while (rs.next()) {
      String customerId = rs.getString("customer_id");
      String fname = rs.getString("fname");
      String lname = rs.getString("lname");

      System.out.println("customer_id: " + customerId);
      System.out.println("fname      : " + fname);
      System.out.println("lname      : " + lname);
      // Etc.
  }

This snippet shows how to safely delete 2 rows inside a transaction:

  int customerId = 1;

  // Create the JDBC Connection to the remote database:
  connection = new AwakeConnection(url, username, password, httpProxy);

  // TRANSACTION BEGINS
  connection.setAutoCommit(false);

  // We will do all our (remote) deletes in one transaction
  try {
      // 1) Delete the Customer:
      String sql = "delete from customer where customer_id = ?";
      PreparedStatement prepStatement = connection.prepareStatement(sql);

      prepStatement.setInt(1, customerId);
      prepStatement.executeUpdate();
      prepStatement.close();

      // 2) Delete all orders for this Customer:
      sql = "delete from orderlog where customer_id = ?";
      PreparedStatement prepStatement2 = connection.prepareStatement(sql);

      prepStatement2.setInt(1, customerId);
      prepStatement2.executeUpdate();
      prepStatement2.close();

      // We  either do everything in a single transaction or nothing:
      connection.commit();
      System.out.println("Ok. Commit Done on remote Server!");
  } catch (SQLException e) {
      connection.rollback();
      System.out.println("Fail. Rollback Done on remote Server!");
      throw e;
  } finally {
      connection.setAutoCommit(true);
  }
  // TRANSACTION ENDS
 

Documentation, Tutorial, Source Code & Binaries

Please visit http://www.awake-sql.org.

We will be very happy to have your comments and reviews!

 

 

Topics:

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}