S2JDBC allows users to write unrestricted SQL statements without the hassle of JDBC codes.

Configuration

HSQLDB is included to try out S2JDBC.

Execute HSQLDB by executing bin/runHsqldb.bat (on MS Windows) before using S2JDBC. lib/hsqldb.jar is necessary to execute HSQLDB but is unnecessary if it is not used in the production system.

Inquiry(BeanList)

Let's try inquiring several JavaBeans. BasicSelectHandler is used for inquiry. Following properties are supported:

org.seasar.extension.jdbc.impl.BasicSelectHandler

<component
  class="org.seasar.extension.jdbc.impl.BeanListResultSetHandler">
  <arg>@examples.jdbc.Employee@class</arg>
</component>

examples/jdbc/SelectBeanList.dicon

<components>
    <include path="j2ee.dicon"/>
    <component name="selectBeanListHandler"
        class="org.seasar.extension.jdbc.impl.BasicSelectHandler">
        <property name="sql">
        "SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp"
        </property>
        <property name="resultSetHandler">
            <component class="org.seasar.extension.jdbc.impl.BeanListResultSetHandler">
                <arg>@examples.jdbc.Employee@class</arg>
            </component>
        </property>
    </component>
</components>

examples.jdbc.SelectBeanListClient

package examples.jdbc;

import java.util.List;

import org.seasar.extension.jdbc.SelectHandler;
import org.seasar.framework.container.S2Container;
import org.seasar.framework.container.factory.S2ContainerFactory;

public class SelectBeanListClient {

    private static final String PATH =
        "examples/jdbc/SelectBeanList.dicon";

    public static void main(String[] args) {
        S2Container container = S2ContainerFactory.create(PATH);
        container.init();
        try {
            SelectHandler handler = (SelectHandler)
            container.getComponent("selectBeanListHandler");
            List result = (List) handler.execute(null);
            for (int i = 0; i < result.size(); ++i) {
                System.out.println(result.get(i));
            }
        } finally {
            container.destroy();
        }

    }
}

Result

DEBUG 2004-04-23 11:40:45,086 [main] SELECT empno, ename, job, mgr, hiredate, sal,
  comm, deptno FROM emp
DEBUG 2004-04-23 11:40:46,408 [main] Connection opened
DEBUG 2004-04-23 11:40:46,408 [main] Connection opened
DEBUG 2004-04-23 11:40:46,749 [main] Connection closed
7369, SMITH, CLERK, 7902, 1980-12-17 00:00:00.0, 800.0, 0.0, 20
7499, ALLEN, SALESMAN, 7698, 1981-02-20 00:00:00.0, 1600.0, 300.0, 30
7521, WARD, SALESMAN, 7698, 1981-02-22 00:00:00.0, 1250.0, 500.0, 30
7566, JONES, MANAGER, 7839, 1981-04-02 00:00:00.0, 2975.0, 0.0, 20
7654, MARTIN, SALESMAN, 7698, 1981-09-28 00:00:00.0, 1250.0, 1400.0, 30
7698, BLAKE, MANAGER, 7839, 1981-05-01 00:00:00.0, 2850.0, 0.0, 30
7782, CLARK, MANAGER, 7839, 1981-06-09 00:00:00.0, 2450.0, 333.0, 10
7788, SCOTT, ANALYST, 7566, 1982-12-09 00:00:00.0, 3000.0, 0.0, 20
7839, KING, PRESIDENT, 0, 1981-11-17 00:00:00.0, 5000.0, 444.0, 10
7844, TURNER, SALESMAN, 7698, 1981-09-08 00:00:00.0, 1500.0, 0.0, 30
7876, ADAMS, CLERK, 7788, 1983-01-12 00:00:00.0, 1100.0, 0.0, 20
7900, JAMES, CLERK, 7698, 1981-12-03 00:00:00.0, 950.0, 0.0, 30
7902, FORD, ANALYST, 7566, 1981-12-03 00:00:00.0, 3000.0, 0.0, 20
7934, MILLER, CLERK, 7782, 1982-01-23 00:00:00.0, 1300.0, 555.0, 10
DEBUG 2004-04-23 11:40:46,799 [main] Connection closed

Bind variables are passed as argument to execute(Object[]). Argument is set to null in this example because there is no bind variable. All that is necessary is to have S2Container constructs the components and call execute() afterwards. If a column name contains an underscore character (_), that character is omitted when mapped to a property.

Inquiry(Bean)

BasicSelectHandler is also used to inquire 1 JavaBeans. It is the same as the above example except that BeanResultSetHandler is used. Inquiry result is returned as a JavaBeans instead of a list.

examples/jdbc/SelectBean.dicon

<components>
    <include path="j2ee.dicon"/>
    <component name="selectBeanHandler"
        class="org.seasar.extension.jdbc.impl.BasicSelectHandler">
        <property name="sql">
"SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp WHERE empno = ?"
        </property>
        <property name="resultSetHandler">
            <component class="org.seasar.extension.jdbc.impl.BeanResultSetHandler">
                <arg>@examples.jdbc.Employee@class</arg>
            </component>
        </property>
    </component>
</components>

examples.jdbc.SelectBeanClient

package examples.jdbc;

import org.seasar.extension.jdbc.SelectHandler;
import org.seasar.framework.container.S2Container;
import org.seasar.framework.container.factory.S2ContainerFactory;

public class SelectBeanClient {

    private static final String PATH =
        "examples/jdbc/SelectBean.dicon";

    public static void main(String[] args) {
        S2Container container = S2ContainerFactory.create(PATH);
        container.init();
        try {
            SelectHandler handler =
                (SelectHandler) container.getComponent("selectBeanHandler");
            Employee result = (Employee) handler.execute(
                new Object[]{new Integer(7788)});
            System.out.println(result);
        } finally {
            container.destroy();
        }

    }
}

Result

DEBUG 2004-04-23 16:59:24,989 [main] SELECT empno, ename, job, mgr, hiredate, sal,
  comm, deptno FROM emp WHERE empno = 7788
DEBUG 2004-04-23 16:59:26,452 [main] Connection opened
DEBUG 2004-04-23 16:59:26,452 [main] Connection opened
DEBUG 2004-04-23 16:59:26,792 [main] Connection closed
7788, SCOTT, ANALYST, 7566, 1982-12-09 00:00:00.0, 3000.0, 0.0, 20
DEBUG 2004-04-23 16:59:26,802 [main] Connection closed

Inquiry(MapList)

It is possible to get several Map. MapListResultSetHandler is used as a ResultSetHandler. If a column name contains an underscore character (_), that character omitted when mapped to a key.

examples/jdbc/SelectMapList.dicon

<components>
    <include path="j2ee.dicon"/>
    <component name="selectMapListHandler"
        class="org.seasar.extension.jdbc.impl.BasicSelectHandler">
        <property name="sql">
"SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp"
        </property>
        <property name="resultSetHandler">
            <component class="org.seasar.extension.jdbc.impl.MapListResultSetHandler"/>
        </property>
    </component>
</components>

examples.jdbc.SelectMapListClient

package examples.jdbc;

import java.util.List;

import org.seasar.extension.jdbc.SelectHandler;
import org.seasar.framework.container.S2Container;
import org.seasar.framework.container.factory.S2ContainerFactory;

public class SelectMapListClient {

    private static final String PATH =
        "examples/jdbc/SelectMapList.dicon";

    public static void main(String[] args) {
        S2Container container = S2ContainerFactory.create(PATH);
        container.init();
        try {
            SelectHandler handler = (SelectHandler)
            container.getComponent("selectMapListHandler");
            List result = (List) handler.execute(null);
            for (int i = 0; i < result.size(); ++i) {
                System.out.println(result.get(i));
            }
        } finally {
            container.destroy();
        }

    }
}

Result

DEBUG 2004-04-23 18:27:08,748 [main] SELECT empno, ename, job, mgr, hiredate, sal,
  comm, deptno FROM emp
DEBUG 2004-04-23 18:27:10,150 [main] Connection opened
DEBUG 2004-04-23 18:27:10,150 [main] Connection opened
DEBUG 2004-04-23 18:27:11,032 [main] Connection closed
{empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=1980-12-17 00:00:00.0, sal=800,
  comm=null, deptno=20}
{empno=7499, ename=ALLEN, job=SALESMAN, mgr=7698, hiredate=1981-02-20 00:00:00.0, sal=1600,
  comm=300, deptno=30}
{empno=7521, ename=WARD, job=SALESMAN, mgr=7698, hiredate=1981-02-22 00:00:00.0, sal=1250,
  comm=500, deptno=30}
{empno=7566, ename=JONES, job=MANAGER, mgr=7839, hiredate=1981-04-02 00:00:00.0, sal=2975,
  comm=null, deptno=20}
{empno=7654, ename=MARTIN, job=SALESMAN, mgr=7698, hiredate=1981-09-28 00:00:00.0, sal=1250,
  comm=1400, deptno=30}
{empno=7698, ename=BLAKE, job=MANAGER, mgr=7839, hiredate=1981-05-01 00:00:00.0, sal=2850,
  comm=null, deptno=30}
{empno=7782, ename=CLARK, job=MANAGER, mgr=7839, hiredate=1981-06-09 00:00:00.0, sal=2450,
  comm=333, deptno=10}
{empno=7788, ename=SCOTT, job=ANALYST, mgr=7566, hiredate=1982-12-09 00:00:00.0, sal=3000,
  comm=null, deptno=20}
{empno=7839, ename=KING, job=PRESIDENT, mgr=null, hiredate=1981-11-17 00:00:00.0, sal=5000,
  comm=444, deptno=10}
{empno=7844, ename=TURNER, job=SALESMAN, mgr=7698, hiredate=1981-09-08 00:00:00.0, sal=1500,
  comm=0, deptno=30}
{empno=7876, ename=ADAMS, job=CLERK, mgr=7788, hiredate=1983-01-12 00:00:00.0, sal=1100,
  comm=null, deptno=20}
{empno=7900, ename=JAMES, job=CLERK, mgr=7698, hiredate=1981-12-03 00:00:00.0, sal=950,
  comm=null, deptno=30}
{empno=7902, ename=FORD, job=ANALYST, mgr=7566, hiredate=1981-12-03 00:00:00.0, sal=3000,
  comm=null, deptno=20}
{empno=7934, ename=MILLER, job=CLERK, mgr=7782, hiredate=1982-01-23 00:00:00.0, sal=1300,
  comm=555, deptno=10}
DEBUG 2004-04-23 18:27:11,142 [main] Connection closed

Inquiry(Map)

It is possible to inquiry just 1 Map. MapResultSetHandler is used as a ResultSetHandler.

examples/jdbc/SelectMap.dicon

<components>
    <include path="j2ee.dicon"/>
    <component name="selectMapListHandler"
        class="org.seasar.extension.jdbc.impl.BasicSelectHandler">
        <property name="sql">
"SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp WHERE empno = ?"
        </property>
        <property name="resultSetHandler">
            <component class="org.seasar.extension.jdbc.impl.MapResultSetHandler"/>
        </property>
    </component>
</components>

examples.jdbc.SelectMapClient

package examples.jdbc;

import java.util.Map;

import org.seasar.extension.jdbc.SelectHandler;
import org.seasar.framework.container.S2Container;
import org.seasar.framework.container.factory.S2ContainerFactory;

public class SelectMapClient {

    private static final String PATH =
        "examples/jdbc/SelectMap.dicon";

    public static void main(String[] args) {
        S2Container container = S2ContainerFactory.create(PATH);
        container.init();
        try {
            SelectHandler handler =
                (SelectHandler) container.getComponent("selectMapHandler");
            Map result = (Map) handler.execute(
                new Object[]{new Integer(7788)});
            System.out.println(result);
        } finally {
            container.destroy();
        }

    }
}

Result

DEBUG 2004-04-23 18:59:52,652 [main] SELECT empno, ename, job, mgr, hiredate, sal, comm,
    deptno FROM emp WHERE empno = 7788
DEBUG 2004-04-23 18:59:53,924 [main] Connection opened
DEBUG 2004-04-23 18:59:53,924 [main] Connection opened
DEBUG 2004-04-23 18:59:54,235 [main] Connection closed
{empno=7788, ename=SCOTT, job=ANALYST, mgr=7566, hiredate=1982-12-09 00:00:00.0, sal=3000,
  comm=null,deptno=20}
DEBUG 2004-04-23 18:59:54,255 [main] Connection closed

Update

BasicUpdateHandler is used to update. Following properties are supported:

org.seasar.extension.jdbc.impl.BasicUpdateHandler

examples/jdbc/Update.dicon

<components>
    <include path="j2ee.dicon"/>
    <component name="updateHandler"
        class="org.seasar.extension.jdbc.impl.BasicUpdateHandler">
        <property name="sql">
        "UPDATE emp SET ename = ? WHERE empno = ?"
        </property>
    </component>
</components>

examples.jdbc.UpdateClient

package examples.jdbc;

import org.seasar.extension.jdbc.UpdateHandler;
import org.seasar.framework.container.S2Container;
import org.seasar.framework.container.factory.S2ContainerFactory;

public class UpdateClient {

    private static final String PATH = "examples/jdbc/Update.dicon";

    public static void main(String[] args) {
        S2Container container = S2ContainerFactory.create(PATH);
        container.init();
        try {
            UpdateHandler handler =
                (UpdateHandler) container.getComponent("updateHandler");
            int result =
                handler.execute(new Object[] { "SCOTT", new Integer(7788)});
            System.out.println(result);
        } finally {
            container.destroy();
        }

    }
}

Result

DEBUG 2004-04-23 19:18:40,134 [main] UPDATE emp SET ename = 'SCOTT' WHERE empno = 7788
DEBUG 2004-04-23 19:18:41,455 [main] Established connection
DEBUG 2004-04-23 19:18:41,455 [main] Established connection
DEBUG 2004-04-23 19:18:41,766 [main] Connection closed
1
DEBUG 2004-04-23 19:18:41,776 [main] Connection closed

Batch update

BasicBatchHandler is used to update several data at once. Argument in BatchHandler.execute(List argsList) is an Object[] array.

BasicBatchHandler has the following properties:(Unfortunately they are not supported in HSQLDB(1.7.1)).

org.seasar.extension.jdbc.impl.BasicBatchHandler

examples/jdbc/Batch.dicon

<components>
    <include path="j2ee.dicon"/>
    <component name="batchHandler"
        class="org.seasar.extension.jdbc.impl.BasicBatchHandler">
        <property name="sql">
        "UPDATE emp SET ename = ? WHERE empno = ?"
        </property>
    </component>
</components>

examples.jdbc.BatchClient

package examples.jdbc;

import java.util.ArrayList;
import java.util.List;

import org.seasar.extension.jdbc.BatchHandler;
import org.seasar.framework.container.S2Container;
import org.seasar.framework.container.factory.S2ContainerFactory;

public class BatchClient {

    private static final String PATH = "examples/jdbc/Batch.dicon";

    public static void main(String[] args) {
        S2Container container = S2ContainerFactory.create(PATH);
        container.init();
        try {
            BatchHandler handler =
                (BatchHandler) container.getComponent("batchHandler");
            List argsList = new ArrayList();
            argsList.add(new Object[] { "SMITH", new Integer(7369)});
            argsList.add(new Object[] { "SCOTT", new Integer(7788)});
            int result = handler.execute(argsList);
            System.out.println(result);
        } finally {
            container.destroy();
        }

    }
}

Result

DEBUG 2004-04-23 20:12:38,680 [main] Connection opened
DEBUG 2004-04-23 20:12:38,710 [main] Connection opened
DEBUG 2004-04-23 20:12:38,761 [main] UPDATE emp SET ename = 'SMITH' WHERE empno = 7369
DEBUG 2004-04-23 20:12:38,941 [main] UPDATE emp SET ename = 'SCOTT' WHERE empno = 7788
DEBUG 2004-04-23 20:12:38,971 [main] Connection closed
2
DEBUG 2004-04-23 20:12:38,981 [main] Connection closed