SQL Zone is brought to you in partnership with:

Founder & CEO of KawanSoft. We are a french privately held company located in Paris and specialize in Swing, Java EE and client-server developments. We developp and support the OSS Awake Framework that allows file tranfers and JDBC calls through HTTP. Nicolas has posted 5 posts at DZone. You can read more from them at their website. View Full User Profile

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

05.10.2012
| 2326 views |
  • submit to reddit
Location: 
Software

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!

 

 

0
Published at DZone with permission of its author, Nicolas De Pomereu.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Nicolas De Pomereu replied on Thu, 2012/05/10 - 9:14am


Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.