Java synchronization example

Java synchronization logic works with MobiLink and common Java classes to provide you with flexibility in deploying applications using MobiLink server. The following section introduces you to this extended range of functionality using a simple example.

This section describes a working example of Java synchronization logic. Before you try to use this class or write your own class, use the following checklist to ensure you have all the pieces in place before compiling the class.

Plan your functionality using, for example, pseudocode.
Create a map of database tables and columns.
Configure the consolidated database for Java synchronization by ensuring you have specified in the MobiLink system tables the language type and location of the Java synchronization methods.

See Setting up Java synchronization logic.
Create a list of associated Java classes that are called during the running of your Java class.
Store your Java classes in a location that is in the classpath for MobiLink server.

Plan

The Java synchronization logic for this example points to the associated Java files and classes that contain functionality needed for the example to work. It shows you how to create a class CustEmpScripts. It shows you how to set up a synchronization context for the connection. Finally, the example provides Java methods to

authenticate a MobiLink user.
perform download and upload operations using cursors for each database table.

Schema

The tables to be synchronized are emp and cust. The emp table has three columns called emp_id, emp_name and manager. The cust table has three columns called cust_id, cust_name and emp_id. All columns in each table are synchronized. The mapping from consolidated to remote database is such that the table names and column names are identical in both databases. One additional table, an audit table, is added to the consolidated database.

Java class files

Setup

The following code sets up the Java synchronization logic. The import statements tell the Java VM the location of needed files. The public class statement declares the class.

// Use a package when you create your own script.
import ianywhere.ml.script.InOutInteger;
import ianywhere.ml.script.DBConnectionContext;
import ianywhere.ml.script.ServerContext;
import java.sql.*;

public class CustEmpScripts {
    // Context for this synchronization connection.
    DBConnectionContext _conn_context;

    // Same connection MobiLink uses for sync.
    // Do not commit or close this.
    Connection _sync_connection;
    Connection _audit_connection; 

    //Get a user id given the user name. On audit connection.
    PreparedStatement _get_user_id_pstmt;

    // Add record of user logins added. On audit connection.
    PreparedStatement _insert_login_pstmt;

    // Prepared statement to add a record to the audit table.
    // On audit connection.
    PreparedStatement _insert_audit_pstmt;
    
    // ...
}

The CustEmpScripts constructor sets up all the prepared statements for the authenticateUser method. It sets up member data.

public CustEmpScripts(DBConnectionContext cc) throws SQLException {
    try {
        _conn_context = cc;
        _sync_connection = _conn_context.getConnection();

        ServerContext serv_context =
        _conn_context.getServerContext();
        _audit_connection = serv_context.makeConnection();

        // Get the prepared statements ready.
        _get_user_id_pstmt =
            _audit_connection.prepareStatement(
                "select user_id from ml_user where name = ?"
            );

        _insert_login_pstmt =
            _audit_connection.prepareStatement(
                "INSERT INTO login_added(ml_user, add_time) "
                    + "VALUES (?, { fn CONVERT({ fn NOW() }, SQL_VARCHAR) })"
            );

        _insert_audit_pstmt =
            _audit_connection.prepareStatement(
                "INSERT INTO login_audit(ml_user_id, audit_time, audit_action) "
                    + "VALUES (?, { fn CONVERT({ fn NOW() }, SQL_VARCHAR) }, ?)"
            );
    }
    catch(SQLException e) {
        freeJDBCResources();
        throw e;
    }
    catch(Error e) {
        freeJDBCResources();
        throw e;
    }
}

The finalize method cleans up JDBC resources if end_connection is not called. It calls the freeJDBCResources method, which frees allocated memory and closes the audit connection.

protected void finalize() throws SQLException, Throwable {
    super.finalize();
    freeJDBCResources();
}

private void freeJDBCResources() throws SQLException {
    if (_get_user_id_pstmt != null) {
        _get_user_id_pstmt.close();
    }
    if (_insert_login_pstmt != null) {
        _insert_login_pstmt.close();
    }
    if (_insert_audit_pstmt != null) {
        _insert_audit_pstmt.close();
    }
    if (_audit_connection != null) {
        _audit_connection.close();
    }
    _conn_context       = null;
    _sync_connection    = null;
    _audit_connection   = null;
    _get_user_id_pstmt  = null;
    _insert_login_pstmt = null;
    _insert_audit_pstmt = null;
}

The endConnection method cleans up resources once the resources are not needed.

public void endConnection() throws SQLException {
    freeJDBCResources();
}

The authenticateUser method below approves all user logins and logs user information to database tables. If the user is not in the ml_user table they are logged to login_added. If the user id is found in ml_user then they are logged to login_audit. In a real system you would not ignore the user_password, but this sample approves all users for simplicity. The endConnection method throws SQLException if any of the database operations fail with an exception.

public void authenticateUser( 
    InOutInteger authentication_status,
    String user_name) throws SQLException 
{

    boolean new_user;
    int user_id;

    // Get ml_user id.
    _get_user_id_pstmt.setString(1, user_name);

    ResultSet user_id_rs =
    _get_user_id_pstmt.executeQuery();
    new_user = !user_id_rs.next();
    if (!new_user) {
        user_id = user_id_rs.getInt(1);
    }
    else {
        user_id = 0;
    }
 
    user_id_rs.close();
    user_id_rs = null;

    // In this tutorial always allow the login.
    authentication_status.setValue(1000);
  
    if (new_user) {
        _insert_login_pstmt.setString(1, user_name);
        _insert_login_pstmt.executeUpdate();
        java.lang.System.out.println("user: " + user_name + " added. ");
    }
    else {
        _insert_audit_pstmt.setInt(1, user_id);
        _insert_audit_pstmt.setString(2, "LOGIN ALLOWED");
        _insert_audit_pstmt.executeUpdate();
    }
    _audit_connection.commit();
    return;
}

The following methods use SQL statements to act as cursors on the database tables. Since these are cursor scripts, they must return a SQL string.

public static String empUploadInsertStmt() {
    return("INSERT INTO emp(emp_id, emp_name) VALUES(?, ?)");
}

public static String empUploadDeleteStmt() {
    return("DELETE FROM emp WHERE emp_id = ?");
}

public static String empUploadUpdateStmt() {
    return("UPDATE emp SET emp_name = ? WHERE emp_id = ?");
} 

public static String empDownloadCursor() {
    return("SELECT emp_id, emp_name FROM emp");
}

public static String custUploadInsertStmt() {
    return("INSERT INTO cust(cust_id, emp_id, cust_name) VALUES (?, ?, ?)");
}

public static String custUploadDeleteStmt() {
    return("DELETE FROM cust WHERE cust_id = ?");
} 

public static String custUploadUpdateStmt() {
    return("UPDATE cust SET emp_id = ?, cust_name = ? WHERE cust_id = ?");
}

public static String custDownloadCursor() {
    return("SELECT cust_id, emp_id, cust_name FROM cust");
}

Use the following command to compile the code:

javac -cp %sqlany12%\java\mlscript.jar CustEmpScripts.java

Run the MobiLink server with the location of CustEmpScripts.class in the classpath. The following is a partial command line:

mlsrv12 ... -sl java (-cp <class_location>)

Discuss this page in DocCommentXchange.