ToC path:

eXtremeSQL Java Example

The following code snippet demonstrates the basic embedded eXtremeSQL Java application implementation:

 
    import com.mcobject.extremedb.*;
    import java.io.Console;
     
    @Persistent // class will be stored in eXtremeDB database
    class MyTable
    {
        int pk;
        String value;
    };
     
    public class Sql
    {
        static final int PAGE_SIZE = 128;
        static final int DISK_PAGE_SIZE = 4096;
        static final int DISK_CACHE_SIZE = 8*1024*1024;
        static final int DATABASE_SIZE = 16*1024*1024;
         
        public static void main(String[] args)
        {
            Database db;
            int config = Database.MCO_CFG_SQL_SUPPORT;
            boolean is_dptr = false;
            Database.Device devs[];
            Database.Parameters params = new Database.Parameters();
             
            // get config parameters from the command line
            for (String s : args) 
            {
                if ("disk".equals(s))  config |= Database.MCO_CFG_DISK_SUPPORT;
                if ("shm".equals(s))   config |= Database.MCO_CFG_SHARED_MEMORY;
                if ("debug".equals(s)) config |= Database.MCO_CFG_DEBUG_LIBRARY;
                if ("mvcc".equals(s))  config |= Database.MCO_CFG_MVCC_TRANSACTION_MANAGER;
                if ("dptr".equals(s))  is_dptr = true;
                if (s.startsWith("license:")) params.license_key = s.substring(8);
            }
             
            params.memPageSize = PAGE_SIZE; // memory page size
            params.classes = new Class[] { MyTable.class }; // list of classes which should
            // be stored in eXremeDB
            // database. The classes must use
            // the @Persistent annotation
            params.maxConnections = 10;
     
            if ((config & Database.MCO_CFG_DISK_SUPPORT) != 0) 
            { // on-disk configuration
                java.io.File file = new java.io.File("opendb.dbs");
                file.delete();
                file = new java.io.File("opendb.log");
                file.delete();
                params.diskPageSize = DISK_PAGE_SIZE; // disk page size
                params.diskClassesByDefault = true; // mark @Persistent classes as on-disk
                // classes by default
                // declare memory devices
                devs = new Database.Device[4];
                 
                if ((config & Database.MCO_CFG_SHARED_MEMORY) != 0)
                 {
                    devs[0] = new Database.SharedMemoryDevice(Database.Device.Kind.Data,
                    "opendb-db", 0, DATABASE_SIZE);
                    devs[1] = new Database.SharedMemoryDevice(Database.Device.Kind.DiskCache,
                    "opendb-cache", 0,
                    DISK_CACHE_SIZE);
                     
                }
                 else 
                {
                     
                    devs[0] = new Database.PrivateMemoryDevice(Database.Device.Kind.Data,
                    DATABASE_SIZE);
                    devs[1] = new atabase.PrivateMemoryDevice(Database.Device.Kind.DiskCache,
                    DISK_CACHE_SIZE);
                }
     
                devs[2] = new Database.FileDevice(Database.Device.Kind.Data, "opendb.dbs");
                devs[3] = new Database.FileDevice(Database.Device.Kind.TransactionLog,
                "opendb.log");
                 
            } 
            else 
            { // in-memory configuration
                     
                devs = new Database.Device[1];
                if ((config & Database.MCO_CFG_SHARED_MEMORY) != 0) 
                {
                    long hint = 0;
                    if (is_dptr)
                    hint = 0x20000000;
                    devs[0] = new Database.SharedMemoryDevice(Database.Device.Kind.Data,
                    "opendb-db", hint,
                    DATABASE_SIZE);
                     
                } else 
                {
                 
                    devs[0] = new Database.PrivateMemoryDevice(Database.Device.Kind.Data,
                    DATABASE_SIZE);
                }
            }
             
            // create Database object
            db = new Database(config);
            db.open("sqldb", params, devs); // open database.

Once the database has been opened, a connection must be created to access it. Applications can create connections via the Database class, for example:

 
    Database db = new Database(Database.Mode.DebugSupport,“../../target/bin.so");
    int maxAttempts = 10;
    SqlLocalConnection conLocal = db.connectSql();
    SqlRemoteConnection conRemote = db.connectRemoteSql(host, port, maxAttempts);
     
Or connections can be instantiated by calling the SqlLocalConnection or SqlRemoteConnection constructor. Note that one limitation for remote connections is that it is currently not possible to execute more than one query simultaneously in a single connection. So, for example, something like the following will not work:
     
    SqlResultSet rs1 = conRemote.executeQuery("select * from Class1");
    SqlResultSet rs2 = conRemote.executeQuery("select * from Class2");
    Iterator<Class1>  i1 = rs1.iterator();
    Iterator<Class2>  i2 = rs2.iterator();
    while (i1.hasNext() && i2.hasNext()) 
    {
        ...
    }
     
SQL queries are then performed using the executeQuery() method which executes an SQL select and returns an SqlResultSet or the executeStatement() method which executes an SQL insert, update or delete statement and returns the number of rows affected. For example:
 
            SqlLocalConnection con = db.connectSql();
            con.startTransaction(Database.TransactionType.ReadWrite);
            con.executeStatement("insert into MyTable (pk,value) values (?,?)", 2012,
                            "Good bye");
            con.executeStatement("insert into MyTable (pk,value) values (?,?)", 2013,
                        "Hello");
            con.commitTransaction();
            con.startTransaction(Database.TransactionType.ReadOnly);
            SqlResultSet result = con.executeQuery("select pk,value from MyTable”
                                “ where pk>=?", 2013);
            for (String column : result.getColumnNames())
             {
                System.out.print(column + ", ");
            }
            System.out.println();
            for (SqlTuple tuple : result) 
            {
                System.out.println(tuple.getInt(0) + ", " + tuple.get("value"));
            }
        con.commitTransaction();
        con.disconnect();
        db.close();
    }
     
Note that a database transaction must be explicitly started with con.startTransaction()and closed with con.commitTransaction() or con.rollbackTransaction(). And the connection must be closed with con.disconnect() and the database closed with db.close(). Also note that both methods executeQuery() and executeStatement() allow substitution of parameters into the statement for placeholders marked by ? or %x, where x specifies a parameter type which can be one of the following:
 
    %b = bool
    %i = signed<4>
    %u = unsigned<4>
    %l = signed<8>
    %t = datetime
    %s = string
    %w = nstring
     
In most cases it is possible to use the traditional ODBC/JDBC placeholder ?. The specific parameter type specifiers are needed if you want to pass a string as unicode (wide character) string (%w ) rather than a multibyte-character string (%s). For example:
     
    con.executeQuery("select * from Employee where department=? and salary > ?", "HR", 50000);
     
executeQuery() returns an SqlResultSet object which implements the IEnumerable<SqlTuple> interface. The tuple values are then extracted in a for loop as shown in the code snippet above.

It is also possible to get the number and list of the result column names. An SqlCursor instance is returned by SqlResultSet.getEnumerator() which implements the IEnumerator<SqlTuple> interface and provides a one-way iteration through the selected tuples. (Note that it is not possible to reset the iterator to restart traversal.) The SqlTuple class represents a collection of column values. It provides methods for getting the number of columns (though it is better to get this number from SqlResultSet set rather than from the tuple) and for extracting the value of a particular column.

Columns can be accessed either by index (0-based), or by name as in the example above. The type of the returned value is:

bool for boolean types,

long for integer types,

double for floating point types,

decimal for floating numeric types,

String for character types

byte[] for blob types, and

DateTime for the datetime type.

Accessing a tuple's value by index is recommended since using the column name requires a hash lookup. The column name can be used once by the SqlResultSet.getColumnNo(string columnName) method to get the column’s index; then use the returned index to fetch the tuple's value.