# 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.