When Atomikos meets Sql2o

 Today we play with something we don't normally do in a live application. We are going to married Atomikos and Sql2o.

Atomikos is a lightweight transaction manager for Java that enables applications using distributed transactions to be self-contained.

Sql2o is a small Java library, that makes it easy to execute sql statements against your JDBC compliant database.

We normally use Atomikos to manage the transaction. Sql2o also has it's own way for transaction management. 

// Sql2o Transaction
String sql1 = "INSERT INTO SomeTable(id, value) VALUES (:id, :value)";
String sql2 = "UPDATE SomeOtherTable SET value = :val WHERE id = :id";

try (Connection con = sql2o.beginTransaction()) {
    con.createQuery(sql1).addParameter("id", idVariable1).addParameter("val", valueVariable1).executeUpdate();
    con.createQuery(sql2).addParameter("id", idVariable2).addParameter("val", valueVariable2).executeUpdate();
    con.commit();
} 

So if we mix the two together, what kind of chemistry they will create?

Delphi Data Access Utility

I want to share this one out as if

1. Any one like to use it, be my guest.

2. Any one has any comment on it, please :)

Note: I am a biginner in Delphi, so please manage your expectation. Thanks.

[

unit LIB_DATA_ACCESS;

interface

uses

  Windows, SysUtils, Variants, DB, ADODB;

const

  CONNECTION_STRING='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\test.mdb;Persist Security Info=False;Jet OLEDB:Database Password=******';

Type

  TResultSet = Array of Array of Variant;

function ExecuteQuery( Conn:TADOConnection; Query:TADOQuery; Sql : String;

    Parameters : Array of String;

    Values : Array of Variant): TResultSet;

function ExecuteUpdate( Conn:TADOConnection; Query:TADOQuery; Sql : String; 

    Parameters : Array of String; Values : Array of Variant ): Integer;

implementation

function ExecuteQuery(Conn:TADOConnection; Query:TADOQuery; Sql : String;

    Parameters : Array of String;

    Values : Array of Variant): TResultSet;

var

  tempArray : TResultSet;

  row_index : Integer;

  col_index : Integer;

  i : Integer;

  ColumnSize : Integer;

begin

  Conn.ConnectionString := CONNECTION_STRING;

  Conn.LoginPrompt := False;

  Query.Connection := CONN;

  if Query.Active then

  begin

    Query.Close;

  end;

  try

    Query.SQL.Clear;

    Query.SQL.Add(Sql);

    if Length(Parameters) > 0 then

    begin

      for  i := 0 to (Length(Parameters) - 1) do

      begin

        Query.Parameters.ParamByName(Parameters[i]).Value := Values[i];

      end;

    end;

    Query.Open;

    SetLength(tempArray, Query.RecordCount);

    row_index := 0;

    while not Query.Eof do

    begin

      ColumnSize := Query.Recordset.Fields.Count;

      SetLength(tempArray[row_index], ColumnSize);

      col_index := 0;

      for i := 0 to (ColumnSize - 1) do

      begin

        tempArray[row_index][col_index] := Query.Recordset.Fields[i].Value;

        col_index := col_index + 1;

      end;

      row_index := row_index + 1;

      Query.Next;

    end;

  finally

    Query.Close;

    Conn.Close;

    Result := tempArray;

  end;

end;

function ExecuteUpdate( Conn:TADOConnection; Query:TADOQuery; Sql : String;

    Parameters : Array of String;

    Values : Array of Variant): Integer;

var

  i : Integer;

begin

  Conn.ConnectionString := CONNECTION_STRING;

  Conn.LoginPrompt := False;

  Query.Connection := CONN;

  if Query.Active then

  begin

    Query.Close;

  end;

  try

    Query.SQL.Clear;

    Query.SQL.Add(Sql);

    if Length(Parameters) > 0 then

    begin

      for  i := 0 to (Length(Parameters) - 1) do

      begin

        Query.Parameters.ParamByName(Parameters[i]).Value := Values[i];

      end;

    end;

//  Query.Open;

    Result := Query.ExecSQL;

  finally

    Query.Close;

    Conn.Close;

  end;

end;

end.

]

Get record from Table into an Array

public List getArrayList(String sql) throws WCMException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

List list = new ArrayList();
// Open Connection
try {

Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup(Global.DB_CONTEXT_NAME);
DataSource ds = (DataSource) envContext.lookup(Global.DB_RESOURCE_NAME);
conn = ds.getConnection();
ps = conn.prepareStatement(sql);
// Execute Query
rs = ps.executeQuery();

// Retrieve Record
while(rs.next()){
// Generate String array
String[] temp = new String[rs.getMetaData().getColumnCount()];
for(int i=0; i temp[i] = rs.getString(i+1);
}
list.add(temp);
}

} catch (NamingException e) {
e.printStackTrace();
throw new WCMException(e);
} catch (SQLException e) {
e.printStackTrace();
throw new WCMException(e);
}
// Close Connection
finally{
try {
if (ps != null) {
ps.close();
ps = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
throw new WCMException(e);
}

}
return list;
}

MySQL Connection Pool in Tomcat

Connection Pool in Tomcat

---

Configure the JNDI DataSource in Tomcat by adding a declaration for your resource to your context.xml.
<Context>

<Resource name="jdbc/dbname" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/dbname?autoReconnect=true"/>
Context>

web.xml configuration

---

<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
version="2.4">
<description>MySQL Test Appdescription>

<resource-ref>
<description>DB Connectiondescription>
<res-ref-name>jdbc/dbnameres-ref-name>
<res-type>javax.sql.DataSourceres-type>
<res-auth>Containerres-auth>
<resource-ref><web-app>

Code sample in JSP/JSTL

---

<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<sql:query var="rs" dataSource="jdbc/dbname">
select id, foo, bar from testdata
</sql:query>

<html>
<head>
<title>DB Testtitle>
<head>

<body>
<h2>Resultsh2>
<c:forEach var="row" items="${rs.rows}">
Foo ${row.foo}<br/>
Bar ${row.bar}<br/>
c:forEach>
<body>
<html>

Code sample in Java

---

Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/dbname");
Connection conn = ds.getConnection();
//etc.

Database Archiving

Ever encounter this problem before?

“Database is not a black hole.”

Did you know that? It will overflow! So? You need to do housekeeping. Take some old records away and put it some where else. Why? Because it will affect your system performance. And not everybody afford to have an Oracle in their company.

You would be lucky if your system needs to do it once a year. What if you need to do it once a month? Or daily? Pain in the ass, right?

So, if there an easy way to do it? Well, I am not a pro in database administration. Never worked with one before in my experience. I just want to emphasize the importance of including the concept of database archiving while you design your system.

Some important points in archiving a database are:

• How often do you need to archive the database?
• What tables do you need to archive?
• How your program can link back to the archived database?

Make sure your system can last for a long run. Don’t let people come in and tell you that you need to archive your database and will charge you this amount.

How many databases do you need?

When been asked, how many databases do you have in your system? One, is it enough?
“Well, my system is small, one should be enough…”

Yeah, I use to think like that too. But not after I encounter this issue in my ex-company, I totally understand how important a good design can help is some critical situation.

But what I mean by good database design? Well you should throw all those ‘table design’, ‘normalize’, and ‘relational table’ things behind first. We just touch the basic, but it’s critical to your application.

For a simple application, you won’t run away from an Application module which interact with the users and a Report module that generates all the report.

One significant problem that countered in this design is, it took minutes for a page to load a report. Of course you won’t see this symptom in the beginning. But after the program runs for some time, enough records populated in the database, then that is when your nightmare begins. (lol)

So what alternative we have here?

Although it makes your application looks sophisticated, but it has its value.

• Then application performance will not be affected by the report extraction.
• Reports are extracted by an engine, which should be scheduled to do so accordingly.
• The extracted report will be saved in another database. Displaying of the report is basically a simple database query. (very fast)

Logically, this design will run longer than the previous one. Next time an IT consultant proposing a system to you, ask for how the put their database. :)
Good to know.