| 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
Property | Description | S2Container Example Value
--- | --- | ---
dataSource | DataSource | Usually set automatically
sql | SQL command to execute | "SELECT * FROM emp"
resultSetHandler | Handler class to process ResultSet | When several JavaBeans are to be returned, BeanListResultSetHandler is used.
    <component
      class="org.seasar.extension.jdbc.impl.BeanListResultSetHandler">
      <arg>@examples.jdbc.Employee@class</arg>
    </component>
fetchSize | Number of rows returned by 1 fetch operation | 100
maxRows | Maximum number of rows to be returned. To disable this property, set it to -1. |
### 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
Property | Description | S2Container Example Value
--- | --- | ---
dataSource | DataSource | Usually set automatically
sql | SQL command to execute | "UPDATE emp SET ename = ? WHERE empno = ?"
### 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
Property | Description | S2Container Example Value
--- | --- | ---
dataSource | JDBC DataSource | SQL command to execute.
sql | SQL command to execute | "UPDATE emp SET ename = ? WHERE empno = ?"
batchSize | Number of data to execute at once | Automatically set if unspecified.
### 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
Trans. by H.Ozawa | Copyright© 2004-2005, The Seasar Foundation and the others. All rights reserved.