Java client/server development can be slow, difficult and agonizing

We specialize in developing Android, Swing and JavaFX client/server applications for our customers. We wanted to speed up our coding when accessing the host's SQL data from the client side.

Each new customer request would typically follow 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, as well as analyzing the response, handling 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, analyze 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 Android, Swing or JavaFX application and test it.
  6. Go to 1 if it doesn’t work…

To boost productivity, we wanted to be able to add new SQL requests without any modifications or interventions on the server side. With AceQL, we have a much more efficient development cycle using JDBC over HTTP:

  1. Modify the Android or Java client desktop application to handle the new SQL query or update as a standard JDBC call.
  2. Launch the Android or Java client desktop application and test it.
  3. Go to 1 if it doesn't work.

There is nothing to code on the server side for a new SQL query, no client/server protocol to develop and test, and no need to restart the server side! The development cycle is much easier and faster with JDBC over HTTP.

This is why we developed AceQL. We are now pleased to release it so that others can benefit from its advantages.



     

AceQL allows Android, Swing and JavaFX developers to code standard JDBC calls to access remote SQL databases

The AceQL framework was designed to provide these advantages:


Examples of JDBC over HTTP with AceQL

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 ServerSqlManager Servlet and prefix
    // it with with "jdbc:aceql:"
    // We will use a secure SSL/TLS session. All uploads/downloads of SQL
    // commands & data will be encrypted.
    String url = "jdbc:aceql:https://www.acme.org:9443/ServerSqlManager";

    // The login info for strong authentication on server side.
    // These are *not* the username/password of the remote JDBC Driver,
    // but are the auth info checked by remote
    // CommonsConfigurator.login(username, password) method.
    String username = "MyUsername";
    String password = "MyPsassword";

    // Required for Android, optional for others environments:
    Class.forName("org.kawanfw.sql.api.client.RemoteDriver");
    
    // Attempts to establish a connection to the remote database:
    Connection connection = DriverManager.getConnection(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(11);

    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;
    
    // Required for Android, optional for others environments:
    Class.forName("org.kawanfw.sql.api.client.RemoteDriver");
    
    // Attempts to establish a connection to the remote database:
    connection = DriverManager.getConnection(url, username,
        password);

    // TRANSACTION BEGIN
    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 do either 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 END